Тема: Використання мови 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)

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 [Довідник несправностей].Назва_несправності, [Результати тестування].[Код_системи_двигуна#], [Довідник несправностей].[Ціна тестування], [Довідник несправностей].Контролер

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 [Вартість тестування]

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)

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 Кількість

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