Національний технічний університет України «Київський політехнічний інститут імені Ігоря Сікорського» Теплоенергетичний факультет Кафедра автоматизації проектування енергетичних процесів і систем ЛАБОРАТОРНА РОБОТА №5 з дисципліни «Системи баз даних» Варіант № 9 Тема: Використання виразу CASE у вибірках даних. Оператор UNION. Мета: Навчитися застосовувати умовний оператор в команді SELECT Завдання: написати пошуковий запит SELECT з використанням двох типів виразу CASE(простий вираз / логічний вираз); написати запит з використанням інструкції IIF / IF (MS SQL / MySQL); написати запит SELECT з використанням CASE або IIF в різних розділах пошукового запиту (SELECT, IN, WHERE, ORDER BY, HAVING) навести приклади використання CASE або IIF в інших запитах (UPDATE, DELETE) написати запит з використанням функції CHOOSE / COALESCE (MS SQL /MySQL); Написати запит з використанням оператора UNION. Пояснити відмінність UNION від JOIN. Скрипт: #Створення бази даних CREATE DATABASE IF NOT EXISTS lab; USE lab; #Створення таблиці відділів create table IF NOT EXISTS department( ID_department INT auto_increment primary key, Department varchar(30) not null unique ) engine = INNODB; #Створення таблиці працівників CREATE TABLE IF NOT EXISTS person ( ID_person INT AUTO_INCREMENT PRIMARY KEY, Connector_ID_department INT, FOREIGN KEY (Connector_ID_department) REFERENCES department(ID_department), Table_number INT NOT NULL UNIQUE, FIO_person VARCHAR(50) NOT NULL, Salary INT CHECK (salary > 0), Gained BOOL # Отримав або ні ) ENGINE=INNODB; create table IF NOT EXISTS money( ID_money INT auto_increment primary key, Connector_ID_person INT, FOREIGN KEY(Connector_ID_person) REFERENCES person(ID_person), Code_money INT unique not null, Amount_money Int not null, Type_money varchar(50) ) engine = INNODB; #Заповнення таблиці відділів INSERT INTO department (Department) VALUES ('Marketing department'), ('Sales department'), ('Production department'); #Заповнення таблиці працівників INSERT INTO person (Connector_ID_department, Table_number, FIO_person, Salary, Gained) VALUES (1, 0001, '[ім’я автора приховано]', 1900, true), (2, 0002, '[ім’я автора приховано]', 2100, true), (3, 0003, '[ім’я автора приховано]', 1800, true), (2, 0004, '[ім’я автора приховано]', 1850, false), (2, 0005, '[ім’я автора приховано]', 1950, true), (3, 0006, '[ім’я автора приховано]', 2050, false); #Заповнення таблиці надходжень INSERT INTO money (Connector_ID_person, Code_money, Amount_money, Type_money) VALUES (1, 001, 1900, 'Earnings'), (2, 002, 200, 'Award'), (3, 101, 100, 'Penalty for inappropriate behavior'), (4, 102, 50, 'Late penalty'); # Завдання - 1 SELECT * , CASE Connector_ID_department WHEN 1 THEN "Marketing department" WHEN 2 THEN "Sales department" WHEN 3 THEN "Production department" END AS Department_name FROM person; SELECT * , CASE WHEN Salary <1900 THEN "Novice worker" WHEN Salary>= 1900 OR Salary <2500 THEN "Avarage worker" ELSE "Good worker" END AS WorkerRank FROM person; # Завдання - 2 SELECT * , IF( Connector_ID_department = 3, "1-st floor","2-nd floor") AS OfficeFloor FROM person; # Завдання - 3 SELECT Connector_ID_department, count(Connector_ID_department), IF( Connector_ID_department = 3, "1-st floor","2-nd floor") AS OfficeFloor FROM person GROUP BY Connector_ID_department HAVING count(Connector_ID_department) ORDER BY Connector_ID_department; # Завдання - 4 DELETE FROM person WHERE 1 = CASE WHEN Gained = 1 AND Salary >=1900 THEN 1 ELSE 0 END; UPDATE person SET Gained = 1 WHERE 1 = CASE WHEN Gained = 0 AND Salary >=1900 THEN 1 ELSE 0 END; # Завдання - 5 #Додамо для демонстрації INSERT INTO person (Connector_ID_department, Table_number, FIO_person, Salary, Gained) VALUES(1, 0007, '[ім’я автора приховано]', NULL , true); SELECT*, COALESCE( Salary ,"Not Salary") as SalaryCheck FROM person; #Видалимио після демонстрації DELETE FROM person WHERE Table_number =7; # Завдання - 6 SELECT FIO_person FROM person UNION SELECT Department FROM department; #Вивід таблиць SELECT * FROM department; SELECT * FROM person; SELECT * FROM money; #Видалення таблиць DROP table department, person, money; DROP database lab; Написати пошуковий запит SELECT з використанням двох типів виразу CASE(простий вираз / логічний вираз);. SELECT * , CASE Connector_ID_department WHEN 1 THEN "Marketing department" WHEN 2 THEN "Sales department" WHEN 3 THEN "Production department" END AS Department_name FROM person; / SELECT * ,CASE WHEN Salary <1900 THEN "Novice worker" WHEN Salary>= 1900 OR Salary <2500 THEN "Avarage worker" ELSE "Good worker" END AS WorkerRank FROM person; / Написати запит з використанням інструкції IIF / IF (MS SQL / MySQL); SELECT * , IF( Connector_ID_department = 3, "1-st floor","2-nd floor") AS OfficeFloor FROM person; / Написати запит SELECT з використанням CASE або IIF в різних розділах пошукового запиту (SELECT, IN, WHERE, ORDER BY, HAVING) SELECT Connector_ID_department, count(Connector_ID_department), IF( Connector_ID_department = 3, "1-st floor","2-nd floor") AS OfficeFloor FROM person GROUP BY Connector_ID_department HAVING count(Connector_ID_department) ORDER BY Connector_ID_department; / Навести приклади використання CASE або IIF в інших запитах (UPDATE, DELETE) Початкова таблиця: / DELETE FROM person WHERE 1 = CASE WHEN Gained = 1 AND Salary >=1900 THEN 1 ELSE 0 END; / UPDATE person SET Gained = 1 WHERE 1 = CASE WHEN Gained = 0 AND Salary >=1900 THEN 1 ELSE 0 END; / Написати запит з використанням функції CHOOSE / COALESCE (MS SQL / MySQL); SELECT*, COALESCE( Salary ,"Not Salary") as SalaryCheck FROM person; До: / Після: / Написати запит з використанням оператора UNION. Пояснити відмінність UNION від JOIN. SELECT FIO_person FROM person UNION SELECT Department FROM department; / UNION об'єднує результати двох або більше запитів у єдиний результуючий набір, що включає всі рядки, що належать всім запитам у союзі. Використовуючи JOIN, ви можете отримувати дані із двох або більше таблиць на основі логічних відносин між таблицями. З'єднання вказують, як SQL повинен використовувати дані однієї таблиці для вибору рядків в іншій таблиці. Висновок: Під час виконання лабораторної роботи було набуто навички з застосовування умовних операторів в команді SELECT. Використано вираз CASE у вибірках даних то оператор UNION.