Тема: використання мови SQL для розробки багатотабличних запитів

Мета:

· Опанувати правила встановлення відповідності записів, що вибираються із різних таблиць та запис цих правил в інструкції SELEСT.

· Навчитись використовувати правила встановлення відповідності записів при розробці запитівSQL.

Лабораторне завдання №6

1. Вирішити задачу: “Підготувати дані про результати тестування, відібраних за критерієм, що базується на даних із різних таблиць “.

2. Автоматизувати підрахунок вартості проведених тестувань для кожного виду несправностей, виявлених під час тестувань. Врахувати необхідність отримувати три різні форми вихідних документів (результуючі таблиці з різним атрибутним складом)

Хід виконання роботи:

1. Повторити теоретичний матеріал підрозділу 5.1.6. Ознайомитись з новим теоретичним матеріалом, див. підрозділи 5.2.1-5.2.2;

2. Відкрити тренінгову базу даних “Тестування авто 1”, див. Додаток А. Перейти в режим SQL (вкладка Запрос, <Cоздать>, Конструктор, <Создать>, Вид, Режим SQL);

3. Розглянути задачу “Підготувати дані про результати тестування перших трьох груп автомобілів, тестування яких проводили контролери, чиї прізвища починаються на літери від А до П, а коди частин двигуна у яких виявлено несправності можуть бути 1, 2, 3”.

4. Продумати алгоритм вирішення задачі. Визначити як записати критерій відбору записів.

5. Скласти інструкцію. Ввести текст інструкції, запустити на виконання, перевірити правильність виконання. Записати запит SQL.

6. Внести зміни у таблиці та запустити запит на виконання, проаналізувати зміни у результуючій таблиці.

7. Розглянути комплекс задач “Автоматизувати підрахунок вартості проведених тестувань для кожного виду несправностей, виявлених під час тестувань”. При цьому врахувати наявність, вказаних нижче, трьох форм вихідних таблиць. Автоматизація отримання кожної форми розглядається як відповідна задача.

8. Розробити документ Форма 1 (.Розрахунок1 вартості тестувань. Якщо несправність не була виявлена під час тестувань, то потрібно виставити вартість та кількість рівними значенню 0. Результуючу таблицю упорядкувати за спаданням вартості тестувань);

9. Розробити документ Форма 2. (У результуючу таблицю включити лише дані про виявлені несправності).

10. Розробити документ Форма 3. (У результуючу таблицю включити лише перші 4 найбільш дорогі (з точки зору тестування) несправності, що були виявлені під час тестування).

11. Визначити як записати критерій відбору відповідних записів для кожного із трьох вихідних документів.

12. Скласти інструкції SQL для кожного вихідного документа як окремий запит. Почергово ввести текст кожної інструкції, запустити запит на виконання, перевірити правильність виконання. Записати запит SQL.

13. Внести зміни до таблиць та запустити запит на виконання. Проаналізувати зміни у одержаних документах Форма1 – Форма3 комплексу документів.

14. Підготувати звіт з виконання лабораторної роботи № 6. У звіт включити:

· Титульну сторінку;

· Завдання 1: Записати постановку першої задачі та відповідну їй інструкцію.

· Завдання 2 оформити як окрему роботу, але з єдиною нумерацією сторінок. Для завдання 2 вказати:

a) Титульну сторінку з назвою роботи: Комплекс задач “Вартість тестування авто”;

b) Структуру бази даних;

c) Перелік задач (загальниий перелік задач для виведення документів Форма1 –Форма3);

d) Лістинги SQL інструкцій для кожної задачі (з вказанням коментарів для критеріїв відбору даних);

e) Отримані вихідні документи за формами 1-3 (результуючі таблиці);

15. Зробити висновки по роботі з багатотабличними запитами, до яких включити:

· Синтаксис для вибору даних із двох таблиць;

· Повну схему запису списку полів у реченні SELECT, див. (5.2.1.);

· Значення предикатів у списку полів реченні SELECТ;

· Призначення оператора JOINT;

· Стислу характеристику операцій з’єднання таблиць.

16. Відповісти на контрольні запитання.

Тривалість виконання лабораторної роботи №6 – 4 академічні години.

Контрольні запитання

1. В чому полягає правило встановлення відповідності записів, що вибираються із різних таблиць?

2. Вкажіть операції з’єднання, які ви знаєте.

3. Вкажіть синтаксис речення FROM для вибору даних із зв’язаних таблиць.

4. Що вказує на відсутність відповідного запису у зв’язаних таблицях?

5. Вкажіть синтаксис речення FROM для вибору даних із зв’язаних таблиць з різними множинами значень полів зв’язку.

6. У якому реченні вказується умова вибору записів без повторень. Як записується предикат для такого вибору?

7. Як розуміти зовнішнє об’єднання таблиць?

8. Вкажить різновиди зовнішнього об’єднання таблиць.

9. Вкажіть синтаксис речення FROM для вибору даних при лівому зовнішньому об’єднанні таблиць.

10. Вкажіть синтаксис речення FROM для вибору даних при правому зовнішньому об’єднанні таблиць.

Лабораторна робота № 7

 

Тема: Використання мови SQL для створення структури нової таблицї бази даних

Мета:

· Навчитись створювати структуру нової таблиці засобами SQL.

Зауваження. Сучасні технології висувають нові вимоги до якості інформаційного забезпечення. Вказані вимоги ініціюють модифікацію, а інколи і нову розробку програмних продуктів. SQL надає засоби для зміни структури бази даних, наприклад – створення нової таблиці бази даних на основі декомпозиції існуючої таблиці.

Лабораторне завдання №7

· Модифікувати структуру бази даних.

· Створити нову (порожню) таблицю.

· Розробити запит до бази даних нової структури бази даних.

Хід виконання роботи:

· Повторити теоретичний матеріал підрозділів 3.1., 5.2.6. - 5.3. Ознайомитись з теоретичним матеріалом, див.розділ 6.1.

· Відкрити індивідуальну базу даних;

· Розробити нову структуру бази даних з врахуванням висунутих вимог до якості проектування бази даних.

· Перейти в режим SQL (вкладка Запрос, <Cоздать>, Конструктор, <Создать>, Вид, Режим SQL);

· Розглянути задачу “Підготувати нову таблицю бази даних”

· Розробити концептуальну модель нової версії індивідуальної бази даних.

· Скласти інструкцію CREATE ТADLE. Ввести текст інструкції, запустити запит на виконання, перевірити правильність виконання. Записати запит SQL.

· Розглянути схему даних для модифікованої бази даних.

· Підготувати звіт з виконання лабораторної роботи № 7. У звіт включити:

a) Титульну сторінку;

b) Вимоги до нової бази даних;

c) Концептуальну модель структури модифікованої бази даних;

d) Синтаксис інструкції CRETE TABLE;

e) Текст розробленої інструкції;

f) Схему даних.

· Відповісти на контрольні запитання.

Тривалість виконання лабораторної роботи №7 – 2 академічні години.

Контрольні запитання до лабораторноїроботи № 7

1. В чому полягає правило встановлення відповідності записів, що вибираються із різних таблиць?

2. Назовіть операції з’єднання, які ви знаєте.

3. Яким чином вказується тип даних для нового поля.?

 

Питання до контролю

 

Завдання: Визначити призначення запитів за навединими інструкціями SQL.

 

1. SELECT [Довідник несправностей].Контролер, Count([Результати тестування].[Код_несправності#]) AS Кількість, Avg([Довідник несправностей].[Ціна тестування]) AS [Avg_Ціна тестування]

FROM [Довідник несправностей] INNER JOIN [Результати тестування] ON [Довідник несправностей].[Код_несправності#] = [Результати тестування].[Код_несправності#]

GROUP BY [Довідник несправностей].Контролер

HAVING (((Avg([Довідник несправностей]![Ціна тестування]))<50))

ORDER BY [Довідник несправностей].Контролер;

 

2. SELECT [Системи двигуна].Назва_системи, [Довідник несправностей].Контролер, [Довідник несправностей].Назва_несправності, Count([Результати тестування].Код_автомобіля) AS Count_Код_автомобіля, [Довідник несправностей].[Ціна тестування]

FROM [Системи двигуна] INNER JOIN ([Довідник несправностей] INNER JOIN [Результати тестування] ON [Довідник несправностей].[Код_несправності#] = [Результати тестування].[Код_несправності#]) ON [Системи двигуна].[Код_системи_двигуна#] = [Результати тестування].[Код_системи_двигуна#]

GROUP BY [Системи двигуна].Назва_системи, [Довідник несправностей].Контролер, [Довідник несправностей].Назва_несправності, [Довідник несправностей].[Ціна тестування];

3. SELECT [Довідник несправностей].Назва_несправності, Count([Результати тестування].[Код_несправності#]) AS Кількість, [Довідник несправностей].[Код_несправності#], [Довідник несправностей].Контролер, [Довідник несправностей].[Ціна тестування], [Результати тестування].[Код_несправності#], Count([Результати тестування]![Код_несправності#])*([Довідник несправностей]![Ціна тестування]) AS [Вартість тестування]

FROM [Довідник несправностей] LEFT JOIN [Результати тестування] ON [Довідник несправностей].[Код_несправності#] = [Результати тестування].[Код_несправності#]

GROUP BY [Довідник несправностей].Назва_несправності, [Довідник несправностей].[Код_несправності#], [Довідник несправностей].Контролер, [Довідник несправностей].[Ціна тестування], [Результати тестування].[Код_несправності#]

ORDER BY [Довідник несправностей].[Код_несправності#];

4. (Запит 1.) SELECT TOP 4 [Довідник несправностей].[Код_несправності#], [Довідник несправностей].[Ціна тестування], [Запит14]![Кількість]*[Довідник несправностей]![Ціна тестування] AS Вартість

FROM [Довідник несправностей] RIGHT JOIN Запит14 ON [Довідник несправностей].[Код_несправності#] = Запит14.[Код_несправності#]

ORDER BY [Запит14]![Кількість]*[Довідник несправностей]![Ціна тестування] DESC;

(Результати тестування Запит)

SELECT DISTINCTROW [Результати тестування].[Код_несправності#], Count(*) AS Кількість

FROM [Результати тестування]

GROUP BY [Результати тестування].[Код_несправності#]

ORDER BY Count(*) DESC;

 

5. (Запит 10) SELECT DISTINCTROW [Результати тестування].[Код_несправності#], Count(*) AS Кількість

FROM [Результати тестування]

GROUP BY [Результати тестування].[Код_несправності#]

ORDER BY Count(*) DESC;

 

6. (Запит 11)

SELECT [Довідник несправностей].Назва_несправності, [Довідник несправностей].Контролер, [Довідник несправностей].[Ціна тестування]

FROM [Довідник несправностей]

WHERE ((([Довідник несправностей].Назва_несправності) Like "З*"));

 

7.( Запит12)

SELECT [Довідник несправностей].Назва_несправності, [Довідник несправностей].Контролер, [Довідник несправностей].[Ціна тестування]

FROM [Довідник несправностей]

WHERE ((([Довідник несправностей].Назва_несправності) Like "З*"));

8. (Запит 13)

SELECT [Довідник несправностей].Контролер, Avg([Довідник несправностей].[Ціна тестування]) AS Середня_ціна_тестування, Min([Довідник несправностей].[Ціна тестування]) AS [Min_Ціна тестування]

FROM [Довідник несправностей]

GROUP BY [Довідник несправностей].Контролер

HAVING (((Avg([Довідник несправностей].[Ціна тестування]))>50)) OR (((Min([Довідник несправностей].[Ціна тестування]))>50));

9 (Запит 14)

SELECT [Результати тестування].[Код_несправності#], Count([Результати тестування].[Код_несправності#]) AS Кількість

FROM [Результати тестування]

GROUP BY [Результати тестування].[Код_несправності#];

10. (Запит 15)

SELECT [Довідник несправностей].Назва_несправності, [Довідник несправностей].[Код_несправності#], IIf([Довідник несправностей]![Код_несправності#]=[Запит14]![Код_несправності#],[Запит14]![Кількість],0) AS Кількість, [Довідник несправностей].[Ціна тестування], IIf([Довідник несправностей]![Код_несправності#]=[Запит14]![Код_несправності#],[Запит14]![Кількість]*[Довідник несправностей]![Ціна тестування],0) AS Вартість

FROM [Довідник несправностей] LEFT JOIN Запит14 ON [Довідник несправностей].[Код_несправності#] = Запит14.[Код_несправності#]

ORDER BY IIf([Довідник несправностей]![Код_несправності#]=[Запит14]![Код_несправності#],[Запит14]![Кількість]*[Довідник несправностей]![Ціна тестування],0) DESC;

11. (Запит 16)

SELECT [Результати тестування].[Код_несправності#], Count([Результати тестування].[Код_несправності#]) AS Кількість

FROM [Результати тестування]

GROUP BY [Результати тестування].[Код_несправності#];

 

12 (Запит 17)

SELECT [Довідник несправностей].Назва_несправності, [Результати тестування].[Код_системи_двигуна#], [Довідник несправностей].[Ціна тестування], [Довідник несправностей].Контролер

FROM [Довідник несправностей] INNER JOIN [Результати тестування] ON [Довідник несправностей].[Код_несправності#] = [Результати тестування].[Код_несправності#]

ORDER BY [Довідник несправностей].Контролер;

 

13. (Запит 19)

SELECT [Довідник несправностей].Назва_несправності, [Довідник несправностей].[Ціна тестування]

FROM [Довідник несправностей]

WHERE ((([Довідник несправностей].[Ціна тестування]) In (30,50,34)));

14. (Запит 2)

SELECT *

FROM [Результати тестування]

WHERE ((([Результати тестування].[Код_несправності#])=3)) OR ((([Результати тестування].[Код_системи_двигуна#])=4));

15. (Запит 21)

SELECT [Довідник несправностей].Назва_несправності, Count([Результати тестування].[Код_несправності#]) AS Кількість, [Довідник несправностей].[Код_несправності#], [Довідник несправностей].Контролер, [Довідник несправностей].[Ціна тестування], [Результати тестування].[Код_несправності#], Count([Результати тестування]![Код_несправності#])*([Довідник несправностей]![Ціна тестування]) AS [Вартість тестування]

FROM [Довідник несправностей] LEFT JOIN [Результати тестування] ON [Довідник несправностей].[Код_несправності#] = [Результати тестування].[Код_несправності#]

GROUP BY [Довідник несправностей].Назва_несправності, [Довідник несправностей].[Код_несправності#], [Довідник несправностей].Контролер, [Довідник несправностей].[Ціна тестування], [Результати тестування].[Код_несправності#]

ORDER BY [Довідник несправностей].[Код_несправності#];

16. (Запит 3)

SELECT Max([Довідник несправностей].[Ціна тестування]) AS Найдорогше, Avg([Довідник несправностей].[Ціна тестування]) AS Середня_ціна

FROM [Довідник несправностей]

WHERE ((([Довідник несправностей].Контролер) Not Like "І*"));

17. (Запит 4)

SELECT *

FROM [Результати тестування]

WHERE ((([Результати тестування].Код_автомобіля)=5));

 

18. (Запит 5)

SELECT [Результати тестування].[Код_несправності#], Count(*) AS Кількість

FROM [Результати тестування]

GROUP BY [Результати тестування].[Код_несправності#]

ORDER BY Count(*) DESC;

 

19. (Запит 6)

SELECT [Результати тестування].Код_автомобіля, Count([Результати тестування].[Код_несправності#]) AS [Кількість протестованих авто групи#]

FROM [Результати тестування]

GROUP BY [Результати тестування].Код_автомобіля

HAVING ((([Результати тестування].Код_автомобіля)=[Введіть код автомобіля]));

 

20. (Запит 7)

SELECT [Довідник несправностей].Назва_несправності, [Результати тестування].[Код_системи_двигуна#], [Довідник несправностей].[Ціна тестування], [Довідник несправностей].Контролер

FROM [Довідник несправностей] INNER JOIN [Результати тестування] ON [Довідник несправностей].[Код_несправності#] = [Результати тестування].[Код_несправності#]

ORDER BY [Довідник несправностей].Контролер;

(Запит 8)

SELECT [Системи двигуна].*

FROM [Системи двигуна];

21. (Запит 9)

SELECT [Довідник несправностей].Назва_несправності, [Результати тестування].Код_автомобіля

FROM [Довідник несправностей] RIGHT JOIN [Результати тестування] ON [Довідник несправностей].[Код_несправності#] = [Результати тестування].[Код_несправності#];

22. (Запит Л2впр1)

SELECT Count([Довідник несправностей].[Ціна тестування]) AS [Кількість несправностей у групі тестування], Int([Довідник несправностей]![Ціна тестування]/25)*25 AS Вилка, Int([Довідник несправностей]![Ціна тестування]/25)*25+25 AS Вилка_ДО

FROM [Довідник несправностей]

GROUP BY Int([Довідник несправностей]![Ціна тестування]/25)*25;

 

23. (Підсумковий)

SELECT [Довідник несправностей].[Код_несправності#], [Довідник несправностей].Назва_несправності, [Довідник несправностей].Контролер, [Довідник несправностей].[Ціна тестування], Запит15.Кількість, [Запит15]![Кількість]*[Довідник несправностей]![Ціна тестування] AS Вартість

FROM [Довідник несправностей] INNER JOIN Запит15 ON [Довідник несправностей].[Код_несправності#] = Запит15.[Код_несправності#]

ORDER BY [Довідник несправностей].[Код_несправності#];

24. (Пр1_5_1_2)

SELECT [Результати тестування].Код_автомобіля, [Результати тестування].[Код_несправності#], [Довідник несправностей].Назва_несправності, Контролер.Пр, [Результати тестування].[Код_системи_двигуна#], [Довідник несправностей].[Ціна тестування], [Результати тестування].Довідка

FROM Контролер INNER JOIN ([Довідник несправностей] INNER JOIN [Результати тестування] ON [Довідник несправностей].[Код_несправності#] = [Результати тестування].[Код_несправності#]) ON Контролер.[Код контролера] = [Довідник несправностей].Контролер

WHERE ((([Результати тестування].Код_автомобіля)<=3) AND ((Контролер.Пр)<"П*") AND (([Результати тестування].[Код_системи_двигуна#])<4));

 

25. (розширення стовпчика)

SELECT [Довідник несправностей].[Код_несправності#], IIf([Довідник несправностей]![Код_несправності#]=[Запит14]![Код_несправності#],[Запит14]![Кількість],0) AS Кількість

FROM [Довідник несправностей] LEFT JOIN Запит14 ON [Довідник несправностей].[Код_несправності#] = Запит14.[Код_несправності#]

ORDER BY [Довідник несправностей].[Код_несправності#];