Лабораторна робота №6. Тема: Створення виразів, умов у запитах за допомогою операторів та агрегатних функцій

Тема: Створення виразів, умов у запитах за допомогою операторів та агрегатних функцій

мовою SQL.

Мета: Навчитися виконувати пошук даних з різних таблиць бази даних використовуючи

фрази та функції мови SQL.

Хід роботи:

Завдання 1: Створити запит, який виводить дані із таблиці «Бензин», тільки того поставщика, який зареєстрований під кодом 57651.

SELECT [Бензин].[Код бензину], [Бензин].[Тип бензину],

[Бензин].[Код поставщика]

FROM Бензин

WHERE [Бензин].[Код поставщика] = "57651";

Завдання 2: Створити запит, який виводить із таблиці «Облік реалізації», код працівника без повторювань тільки тоді, де працівник реалізував не менше 150 літрів бензину.

SELECT DISTINCT

[Облік реалізації].[Код працівника]

FROM

[Облік реалізації]

WHERE

[Облік реалізації].[Об'єм реалізації]>=150;

Завдання 3: Створити запит, який виводить із таблиці «Поставки» код поставщика без повторювань тільки тоді, де код бензину дорівнює 564654 або ж коли об’єм поставки більше 1500 літрів.

SELECT DISTINCT [Код поставщика]

FROM Поставки

WHERE Поставки.[Код бензину] = "564654"

OR Поставки.[Об'єм (л)] >1500;

Завдання 4: Створити запит, який виводить усі дані із таблиці «Працівники» окрім працівника Волошина Петра Івановича.

SELECT Працівники.[ПІБ працівника],

Працівники.[Телефонний номер],

Працівники.[Дата народження]

FROM Працівники

WHERE

(Працівники.[ПІБ працівника] = "Волошин Петро Іванович")=false;

Завдання 5: Створити запит, який виводить із таблиці «Бензин» значення код поставщика без повторень та зробити так, щоб значення коду бензину 214654 не вивело.

SELECT DISTINCT Бензин.[Код поставщика]

FROM Бензин

WHERE (Бензин.[Код бензину] = "214654")=false;

Висновок. Я навчився виконувати пошук даних з різних таблиць бази даних використовуючи фрази та функції мови SQL.

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

Тема: Створення запитів з сортуванням та групуванням даних мовою SQL.

Мета: Навчитися створювати запити в яких можливо вказувати рівні групування та

сортування даних.

Хід роботи:

Завдання 1: Створити запит, який підраховуватиме кількість рядків даних кожного типу бензину без урахування бензину, який має код 346542 із таблиці «Бензин».

SELECT COUNT(*) AS Підраховано

FROM Бензин

WHERE (Бензин.[Код бензину]="346542")=false

GROUP BY Бензин.[Тип бензину];

Завдання 2: Створити запит, який підраховуватиме суму реалізованого бензину та суму бензину при поставці при умові, де ціна реалізації-5 більше ціни поставки, та погрупує отримані дані по даті реалізації, якщо сума ціни реалізації більше 1000 грн.

SELECT Sum([Облік реалізації].[Ціна реалізації])

AS [Сума ціни реалізації],

Sum(Поставки.[Ціна поставки (грн)])

AS [Сума поставки],

[Облік реалізації].[Дата реалізації]

FROM [Облік реалізації], Поставки

WHERE

((([Облік реалізації].[Ціна реалізації]-5)>[Поставки].[Ціна поставки (грн)]))

GROUP BY [Облік реалізації].[Дата реалізації]

HAVING (((Sum([Облік реалізації].[Ціна реалізації]))>1000));

Завдання 3: Створити запит, який виводить усі дані поставщиків відсортувавши по зростанню за ПІБ директора.

SELECT Поставщик.[Код поставщика],

Поставщик.[Назва фірми],

Поставщик.[ПІБ директора],

Поставщик.Адреса,

Поставщик.[Телефонний номер]

FROM Поставщик

ORDER BY Поставщик.[ПІБ директора];

Завдання 4: Створити запит, що виводить всі дані про працівників за спаданням по їх даті народження.

SELECT Працівники.[Дата народження],

Працівники.[ПІБ працівника],

Працівники.[Телефонний номер],

Працівники.[Паспортний код],

Працівники.Адрес

FROM Працівники

ORDER BY Працівники.[Дата народження] DESC;

Завдання 5: Створити запит, який виводить суму ціни поставки та суму об’єму поставки бензину, при умові, що ціна поставки більше 10 грн, та погрупувати за ціною поставки і відсортувати за об’ємом поставки, при умові, де сума об’єму бензину при поставці більше 2000 літрів.

SELECT Sum(Поставки.[Ціна поставки (грн)])

AS [Сума ціни поставки], Sum(Поставки.[Об'єм (л)])

AS [Кількість поставленого об'єму бензина]

FROM Поставки

WHERE (((Поставки.[Ціна поставки (грн)])>10))

GROUP BY Поставки.[Ціна поставки (грн)]

HAVING Sum(Поставки.[Об'єм (л)])>2000

ORDER BY Sum(Поставки.[Об'єм (л)]);

Висновок. Я навчився створювати запити в яких можливо вказувати рівні групування та сортування даних.

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

Тема: Створення запитів з вкладеними запитами мовою SQL.

Мета: Навчитися використовувати у запитах SQL вкладені запити (підзапити).

Хід роботи:

Завдання 1: Створити запит, який буде виводити всі дані із таблиці «Облік реалізації» тільки тоді, де код працівника, який реалізовував товар закріплений за Возняком Володимиром Івановичем.

SELECT *

FROM [Облік реалізації]

WHERE [Облік реалізації].[Код працівника]=(SELECT Працівники.[Код працівника] FROM Працівники WHERE Працівники.[ПІБ працівника]="Возняк Володимир Іванович");

Завдання 2: Створити запит, який виводить всі дані з таблиці «Працівники» та відсортувати за ПІБ працівника, тільки в тому випадку, де даний працівник не приймав поставки.

SELECT *

FROM Працівники

WHERE Працівники.[Код працівника] NOT In (SELECT Поставки.[Код працівника] FROM Поставки)

ORDER BY Працівники.[ПІБ працівника];

Завдання 3: Створити запит, який виводить усі дані із таблиці «Працівники» тільки тих працівників, що реалізовували продаж бензину та відсортувати отримані дані за спаданням.

SELECT *

FROM Працівники

WHERE ([Працівники].[Код працівника]) IN (SELECT [Облік реалізації].[Код працівника] FROM [Облік реалізації])

ORDER BY Працівники.[ПІБ працівника] DESC;

Завдання 4: Створити запит, який виводить всі дані про бензин при умові, якщо введений код бензину є в таблиці «Бензин».

SELECT *

FROM Бензин

WHERE EXISTS

(SELECT * FROM Бензин

WHERE ["Kod benziny"] IN (

SELECT Бензин.[Код бензину] FROM Бензин));

Завдання 5: Створити запит, який виводить тільки ті дані з таблиці «Облік реалізації», де працівник реалізовував але не приймав поставки.

SELECT *

FROM [Облік реалізації]

WHERE [Облік реалізації].[Код працівника]

NOT IN (SELECT Поставки.[Код працівника] FROM Поставки);

Висновок. Я навчився використовувати у запитах SQL вкладені запити (підзапити).

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

Тема: Використання предикатів ANY, ALL, EXISTS та IN у запитах мовою SQL.

Мета: Навчитися створювати запити використовуючи предикати ANY, ALL, EXISTS та IN у

запитах мовою SQL.

Хід роботи:

Завдання 1: Створити запит, який виводить дані тих працівників, що продавали бензин та відсортувати за спаданням дані по ПІБ працівника.

SELECT * FROM Працівники

WHERE ([Працівники].[Код працівника]) = ANY (SELECT [Облік реалізації].[Код працівника] FROM [Облік реалізації])

ORDER BY Працівники.[ПІБ працівника] DESC;

Завдання 2: Створити запит, який виводить дані про поставки при умові, якщо об’єм поставки менше ніж об’єм поставки – об’єм реалізації і при цьому отриманий результат менше 1500 літрів.

SELECT * FROM Поставки

WHERE Поставки.[Об'єм (л)] <ALL (SELECT [Об'єм реалізації] FROM [Облік реалізації] WHERE ((Поставки.[Об'єм (л)] - [Об'єм реалізації]) < 1500));

Завдання 3: Створити запит, який виводить дані про поставки та суму ціни певного об’єму бензину при умові, якщо сума певного об’єму бензину не менше 15000 та при цьому щоб він був реалізований на будь який об’єм.

SELECT *, Поставки.[Ціна поставки (грн)]*Поставки.[Об'єм (л)] AS [kilkist*money]

FROM Поставки

WHERE Поставки.[Ціна поставки (грн)]*Поставки.[Об'єм (л)]>=15000 AND Поставки.[Код бензину] IN (SELECT [Облік реалізації].[Код бензину]

FROM [Облік реалізації]);

Завдання 4: Створити запит, який виводить дані реалізації бензину певного робітника при умові, якщо при введені ПІБ працівника, було знайдено його в таблиці «Працівники».

SELECT *

FROM [Облік реалізації]

WHERE [Облік реалізації].[Код працівника] IN (SELECT Працівники.[Код працівника] FROM Працівники WHERE (Працівники.[ПІБ працівника]=[Введіть ПІБ працівника]));

Завдання 5: Створити запит, який виводить всі дані із таблиці «Поставщик» при умові, якщо при введені назви фірми, назва співпала хоча б з однією назвою фірми в таблиці «Поставщик».

SELECT *

FROM Поставщик

WHERE EXISTS (SELECT * FROM Поставщик WHERE ((Поставщик.[Назва фірми])=[Введіть назву фірми]));

Висновок. Я навчився створювати запити використовуючи предикати ANY, ALL, EXISTS та IN у запитах мовою SQL.

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

Тема: Засоби маніпулювання даними.

Мета: Навчитися додавати, оновлювати, видаляти дані в таблицях.

Хід роботи:

Завдання 1: Створити запит, який добавляє дані (Код бензину, його тип та поставщика) до таблиці «Бензин».

INSERT INTO Бензин ([Код бензину],

[Тип бензину], [Код поставщика])

VALUES ("017233", "А-92", "45654");

Завдання 2: Створити запит, який оновлює дані в таблиці «Поставки», де ціна поставки подорожчала на 10%, а об’єм, який поставляють зменшився на 20%.

UPDATE Поставки SET Поставки.[Ціна поставки (грн)] = Поставки.[Ціна поставки (грн)]*1.1, Поставки.[Об'єм (л)] = Поставки.[Об'єм (л)]*0.8;

Завдання 3: Створити запит, який видаляє всі дані із таблиці «Працівники» при умові, якщо це дані Рибалко Олексій Федорович або працівника від кодом 9090.

DELETE * FROM Працівники

WHERE Працівники.[ПІБ працівника]="Рибалко Олексій Федорович" OR Працівники.[Код працівника]="9090";

Завдання 4: Створити запит, який видаляє ПІБ директора фірми поставки, якщо його ПІБ = Фомінов Олександр Вікторович.

DELETE Поставщик.[ПІБ директора]

FROM Поставщик

WHERE Поставщик.[ПІБ директора]="Фомінов Олександр Вікторович";

Завдання 5: Створити запит, який заміняє (оновлює) дані працівника, що закріплений під кодом 0343 на Федорова Кристофера Антоновича із м.Сміла, вул.Рєпіна 51/18, що народився 23.06.1988, має телефонний номер +38063-298-82-33 та його паспортний код НЕ556644.

UPDATE Працівники SET Працівники.[ПІБ працівника] = "Федоров Кристофер Антонович", Працівники.[Дата народження] = "23.06.1988", Працівники.[Телефонний номер] = "+38063-298-82-33", Працівники.[Паспортний код] = "НЕ556644", Працівники.Адрес = "м.Сміла, вул.Рєпіна 51/18"

WHERE Працівники.[Код працівника]="0343";

Висновок. Я навчився додавати, оновлювати, видаляти дані в таблицях.

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

Тема: Операції над схемою бази даних.

Мета: Розглянути можливості мови SQL щодо створення баз даних і таблиць, а також

розглянути основні операції маніпулювання даними: зміна структури таблиці,

додавання та видалення стовпців і рядків, видалення таблиці та бази даних.

Хід роботи:

Завдання 1: Створити запит, який створює таблицю «Ремонтна служба», та яка містить в собі 4 – значний код фірми (який є ключовим), назва фірми максимальною довжиною із 60 символів, її адресу (50 символів), та ім’я директора фірми (30 символів).

CREATE TABLE [Ремонтна служба] (

[Код фірми] CHAR(4) NOT NULL,

PRIMARY KEY ([Код фірми]),

[Назва фірми] CHAR(60),

[Адреса фірми] CHAR(50),

[Директор фірми] CHAR(30))

Завдання 2: Створити запит, за допомогою якого можна добавити до таблиці «Ремонтна служба» атрибут «Телефон фірми» (12 символів).

ALTER TABLE [Ремонтна служба]

ADD [Телефон фірми] CHAR(12)

Завдання 3: Створити запит, за допомогою якого можна змінювати розмір поля «Телефон фірми» з таблиці «Ремонтна служба» із 12 символів в 10 символів.

ALTER TABLE [Ремонтна служба]

ALTER COLUMN [Телефон фірми] CHAR(10)

Завдання 4: Створити запит, який видаляє атрибут «Телефон фірми» із таблиці «Ремонтна служба».

ALTER TABLE [Ремонтна служба] DROP [Телефон фірми]

Завдання 5: Створити запит, який видаляє таблицю «Ремонтна служба».

DROP TABLE [Ремонтна служба]

Висновок. Я розглянув можливості мови SQL щодо створення таблиць баз даних, а також розглянув основні операції маніпулювання даними: зміна структури таблиці, додавання та видалення стовпців і рядків та видалення таблиці.