SQLize Online / PHPize Online  /  SQLtest Online

-- Таблица для Confectionery CREATE TABLE Confectionery ( title VARCHAR(255), city VARCHAR(255), rating FLOAT, year INT, PRIMARY KEY (title, city) ); INSERT INTO Confectionery VALUES ('Sweet Dreams', 'Moscow', 4.5, 2010); INSERT INTO Confectionery VALUES ('Candy Land', 'Saint-Petersburg', 4.8, 2012); INSERT INTO Confectionery VALUES ('Chocolate World', 'Novosibirsk', 5.0, 2015); INSERT INTO Confectionery VALUES ('Cake Heaven', 'Yekaterinburg', 4.2, 2018); INSERT INTO Confectionery VALUES ('Pastry Palace', 'Kazan', 4.8, 2011); INSERT INTO Confectionery VALUES ('Dessert Oasis', 'Nizhny Novgorod', 4.5, 2019); INSERT INTO Confectionery VALUES ('Candy Castle', 'Samara', 4.0, 2013); INSERT INTO Confectionery VALUES ('Sugar Rush', 'Omsk', 4.8, 2014); INSERT INTO Confectionery VALUES ('Delicacy Depot', 'Kaliningrad', 4.9, 2016); INSERT INTO Confectionery VALUES ('Treat Factory', 'Volgograd', 4.5, 2020); SELECT * FROM Confectionery; -- Таблица для Customer CREATE TABLE Customer ( name VARCHAR(255), phoneNumber VARCHAR(20), PRIMARY KEY (name, phoneNumber) ); INSERT INTO Customer VALUES ('Alexey', '+7 (123) 456-78-90'); INSERT INTO Customer VALUES ('Maria', '+7 (987) 654-32-10'); INSERT INTO Customer VALUES ('Dmitry', '+7 (456) 789-01-23'); INSERT INTO Customer VALUES ('Anna', '+7 (321) 098-76-54'); INSERT INTO Customer VALUES ('Sergei', '+7 (678) 543-21-09'); INSERT INTO Customer VALUES ('Ekaterina', '+7 (234) 765-43-21'); INSERT INTO Customer VALUES ('Ivan', '+7 (890) 123-45-67'); INSERT INTO Customer VALUES ('Olga', '+7 (567) 890-12-34'); INSERT INTO Customer VALUES ('Maksim', '+7 (345) 678-90-12'); INSERT INTO Customer VALUES ('Elena', '+7 (901) 234-56-78'); SELECT * FROM Customer; -- Таблица для Employee CREATE TABLE Employee ( name VARCHAR(255), post VARCHAR(255), phoneNumber VARCHAR(20), year INT, salary INT, PRIMARY KEY (name, post) ); INSERT INTO Employee VALUES ('Alexander', 'Confectioner', '+7 (943) 091-54-22', 1992, 451); INSERT INTO Employee VALUES ('Maria', 'Baker', '+7 (861) 864-32-87', 1995, 789); INSERT INTO Employee VALUES ('Dmitry', 'Decorator', '+7 (712) 306-19-45', 2001, 312); INSERT INTO Employee VALUES ('Sofia', 'Confectioner', '+7 (495) 521-78-93', 1993, 647); INSERT INTO Employee VALUES ('Ivan', 'Technologist', '+7 (268) 741-83-06', 2004, 902); INSERT INTO Employee VALUES ('Ekaterina', 'Packer', '+7 (985) 409-25-41', 1990, 405); INSERT INTO Employee VALUES ('Maksim', 'Baker', '+7 (167) 538-62-79', 1996, 714); INSERT INTO Employee VALUES ('Anna', 'The collector', '+7 (624) 072-14-58', 2003, 562); INSERT INTO Employee VALUES ('Artem', 'Confectioner', '+7 (989) 653-31-27', 2001, 833); INSERT INTO Employee VALUES ('Victoria', 'Commodity expert', '+7 (334) 427-90-15', 1994, 398); SELECT * FROM Employee; -- Таблица для Ingredient CREATE TABLE Ingredient ( kind VARCHAR(255), cost INT, quantity INT, PRIMARY KEY (kind) ); INSERT INTO Ingredient VALUES ('Flour', 8, 7); INSERT INTO Ingredient VALUES ('Sugar', 23, 21); INSERT INTO Ingredient VALUES ('Eggs', 17, 13); INSERT INTO Ingredient VALUES ('Oil', 5, 6); INSERT INTO Ingredient VALUES ('Vanilla', 12, 29); INSERT INTO Ingredient VALUES ('Chocolate', 29, 18); INSERT INTO Ingredient VALUES ('Cocoa powder', 3, 4); INSERT INTO Ingredient VALUES ('Nuts', 21, 22); INSERT INTO Ingredient VALUES ('Berries', 9, 11); INSERT INTO Ingredient VALUES ('Salt', 14, 15); SELECT * FROM Ingredient; -- Таблица для Order CREATE TABLE Orders ( ID INT, order_date VARCHAR(255), cost INT, PRIMARY KEY (ID) ); INSERT INTO Orders VALUES (764, '04-04-2024', 700); INSERT INTO Orders VALUES (284, '25-09-2024', 150); INSERT INTO Orders VALUES (298, '03-06-2024', 1100); INSERT INTO Orders VALUES (466, '04-11-2024', 500); INSERT INTO Orders VALUES (555, '23-02-2024', 1200); INSERT INTO Orders VALUES (896, '08-07-2024', 750); INSERT INTO Orders VALUES (214, '14-03-2024', 900); INSERT INTO Orders VALUES (154, '25-12-2024', 1000); INSERT INTO Orders VALUES (375, '02-08-2024', 650); INSERT INTO Orders VALUES (986, '07-05-2024', 1500); INSERT INTO Orders VALUES (777, '17-05-2024', 980); SELECT * FROM Orders; -- Таблица для Supplier CREATE TABLE Supplier ( manufacturer VARCHAR(255), phoneNumber VARCHAR(20), PRIMARY KEY (manufacturer) ); INSERT INTO Supplier VALUES ('Fresh Farms', '+7 (123) 456-78-90'); INSERT INTO Supplier VALUES ('Sweet Suppliers', '+7 (234) 567-89-01'); INSERT INTO Supplier VALUES ('Quality Ingredients', '+7 (345) 678-90-12'); INSERT INTO Supplier VALUES ('Gourmet Goods', '+7 (456) 789-01-23'); INSERT INTO Supplier VALUES ('Organic Delights', '+7 (567) 890-12-34'); INSERT INTO Supplier VALUES ('Daily Harvest', '+7 (678) 901-23-45'); INSERT INTO Supplier VALUES ('Baker/s Best', '+7 (789) 012-34-56'); INSERT INTO Supplier VALUES ('Local Farms', '+7 (890) 123-45-67'); INSERT INTO Supplier VALUES ('Exotic Flavors', '+7 (901) 234-56-78'); INSERT INTO Supplier VALUES ('Global Sweets', '+7 (012) 345-67-89'); SELECT * FROM Supplier; -- Таблица для Product CREATE TABLE Product ( appellation VARCHAR(255), purchasePrice INT, retailPrice INT, PRIMARY KEY (appellation) ); INSERT INTO Product VALUES ('Chocolate Cake', 150, 250); INSERT INTO Product VALUES ('Cupcake', 50, 100); INSERT INTO Product VALUES ('Brownie', 80, 150); INSERT INTO Product VALUES ('Cookie', 30, 60); INSERT INTO Product VALUES ('Strawberry', 120, 200); INSERT INTO Product VALUES ('Muffin', 40, 80); INSERT INTO Product VALUES ('Cheesecake', 100, 180); INSERT INTO Product VALUES ('Pudding', 30, 50); INSERT INTO Product VALUES ('Tarts', 70, 120); INSERT INTO Product VALUES ('Eclairs', 90, 160); SELECT * FROM Product; -- Связь между Confectionery и Customer (посетители) CREATE TABLE Visit ( title VARCHAR(255), city VARCHAR(255), name VARCHAR(255), phoneNumber VARCHAR(20), PRIMARY KEY (title, city, name, phoneNumber), FOREIGN KEY (title, city) REFERENCES Confectionery (title, city), FOREIGN KEY (name, phoneNumber) REFERENCES Customer (name, phoneNumber) ); INSERT INTO Visit VALUES ('Sweet Dreams', 'Moscow', 'Alexey', '+7 (123) 456-78-90'); INSERT INTO Visit VALUES ('Candy Land', 'Saint-Petersburg', 'Maria', '+7 (987) 654-32-10'); INSERT INTO Visit VALUES ('Chocolate World', 'Novosibirsk', 'Dmitry', '+7 (456) 789-01-23'); INSERT INTO Visit VALUES ('Cake Heaven', 'Yekaterinburg', 'Anna', '+7 (321) 098-76-54'); INSERT INTO Visit VALUES ('Pastry Palace', 'Kazan', 'Sergei', '+7 (678) 543-21-09'); INSERT INTO Visit VALUES ('Dessert Oasis', 'Nizhny Novgorod', 'Ekaterina', '+7 (234) 765-43-21'); INSERT INTO Visit VALUES ('Candy Castle', 'Samara', 'Ivan', '+7 (890) 123-45-67'); INSERT INTO Visit VALUES ('Sugar Rush', 'Omsk', 'Olga', '+7 (567) 890-12-34'); INSERT INTO Visit VALUES ('Delicacy Depot', 'Kaliningrad', 'Maksim', '+7 (345) 678-90-12'); INSERT INTO Visit VALUES ('Treat Factory', 'Volgograd', 'Elena', '+7 (901) 234-56-78'); INSERT INTO Visit VALUES ('Candy Land', 'Saint-Petersburg', 'Ivan', '+7 (890) 123-45-67'); INSERT INTO Visit VALUES ('Sweet Dreams', 'Moscow', 'Maria', '+7 (987) 654-32-10'); SELECT * FROM Visit; -- Связь между Confectionery и Employee (сотрудники) CREATE TABLE Cooperate ( title VARCHAR(255), city VARCHAR(255), name VARCHAR(255), post VARCHAR(255), PRIMARY KEY (title, city, name, post), FOREIGN KEY (title, city) REFERENCES Confectionery (title, city), FOREIGN KEY (name, post) REFERENCES Employee (name, post) ); INSERT INTO Cooperate VALUES ('Sweet Dreams', 'Moscow', 'Alexander', 'Confectioner'); INSERT INTO Cooperate VALUES ('Candy Land', 'Saint-Petersburg', 'Maria', 'Baker'); INSERT INTO Cooperate VALUES ('Chocolate World', 'Novosibirsk', 'Dmitry', 'Decorator'); INSERT INTO Cooperate VALUES ('Cake Heaven', 'Yekaterinburg', 'Sofia', 'Confectioner'); INSERT INTO Cooperate VALUES ('Pastry Palace', 'Kazan', 'Ivan', 'Technologist'); INSERT INTO Cooperate VALUES ('Dessert Oasis', 'Nizhny Novgorod', 'Ekaterina', 'Packer'); INSERT INTO Cooperate VALUES ('Dessert Oasis', 'Nizhny Novgorod', 'Ivan', 'Technologist'); INSERT INTO Cooperate VALUES ('Candy Castle', 'Samara', 'Maksim', 'Baker'); INSERT INTO Cooperate VALUES ('Sugar Rush', 'Omsk', 'Alexander', 'Confectioner'); INSERT INTO Cooperate VALUES ('Sugar Rush', 'Omsk', 'Anna', 'The collector'); INSERT INTO Cooperate VALUES ('Sugar Rush', 'Omsk', 'Artem', 'Confectioner'); INSERT INTO Cooperate VALUES ('Delicacy Depot', 'Kaliningrad', 'Artem', 'Confectioner'); INSERT INTO Cooperate VALUES ('Treat Factory', 'Volgograd', 'Victoria', 'Commodity expert'); SELECT * FROM Cooperate; -- Связь между Confectionery и Ingredient (используемые ингредиенты) CREATE TABLE Used ( title VARCHAR(255), city VARCHAR(255), kind VARCHAR(255), PRIMARY KEY (title, city, kind), FOREIGN KEY (title, city) REFERENCES Confectionery (title, city), FOREIGN KEY (kind) REFERENCES Ingredient (kind) ); INSERT INTO Used VALUES ('Sweet Dreams', 'Moscow', 'Flour'); INSERT INTO Used VALUES ('Candy Land', 'Saint-Petersburg', 'Sugar'); INSERT INTO Used VALUES ('Chocolate World', 'Novosibirsk', 'Eggs'); INSERT INTO Used VALUES ('Cake Heaven', 'Yekaterinburg', 'Oil'); INSERT INTO Used VALUES ('Pastry Palace', 'Kazan', 'Vanilla'); INSERT INTO Used VALUES ('Dessert Oasis', 'Nizhny Novgorod', 'Chocolate'); INSERT INTO Used VALUES ('Candy Castle', 'Samara', 'Cocoa powder'); INSERT INTO Used VALUES ('Sugar Rush', 'Omsk', 'Nuts'); INSERT INTO Used VALUES ('Delicacy Depot', 'Kaliningrad', 'Berries'); INSERT INTO Used VALUES ('Treat Factory', 'Volgograd', 'Salt'); INSERT INTO Used VALUES ('Delicacy Depot', 'Kaliningrad', 'Chocolate'); INSERT INTO Used VALUES ('Cake Heaven', 'Yekaterinburg', 'Sugar'); INSERT INTO Used VALUES ('Sweet Dreams', 'Moscow', 'Eggs'); SELECT * FROM Used; -- Связь между Confectionery и Order (заказы) CREATE TABLE Take ( title VARCHAR(255), city VARCHAR(255), ID INT, PRIMARY KEY (title, city, ID), FOREIGN KEY (title, city) REFERENCES Confectionery (title, city), FOREIGN KEY (ID) REFERENCES Orders (ID) ); INSERT INTO Take VALUES ('Sweet Dreams', 'Moscow', 764); INSERT INTO Take VALUES ('Candy Land', 'Saint-Petersburg', 284); INSERT INTO Take VALUES ('Chocolate World', 'Novosibirsk', 298); INSERT INTO Take VALUES ('Cake Heaven', 'Yekaterinburg', 466); INSERT INTO Take VALUES ('Pastry Palace', 'Kazan', 555); INSERT INTO Take VALUES ('Dessert Oasis', 'Nizhny Novgorod', 896); INSERT INTO Take VALUES ('Candy Castle', 'Samara', 214); INSERT INTO Take VALUES ('Sugar Rush', 'Omsk', 154); INSERT INTO Take VALUES ('Treat Factory', 'Volgograd', 986); INSERT INTO Take VALUES ('Treat Factory', 'Volgograd', 214); SELECT * FROM Take; -- Связь между Confectionery и Supplier (поставщики) CREATE TABLE Supply ( title VARCHAR(255), city VARCHAR(255), manufacturer VARCHAR(255), PRIMARY KEY (title, city, manufacturer), FOREIGN KEY (title, city) REFERENCES Confectionery (title, city), FOREIGN KEY (manufacturer) REFERENCES Supplier (manufacturer) ); INSERT INTO Supply VALUES ('Sweet Dreams', 'Moscow', 'Fresh Farms'); INSERT INTO Supply VALUES ('Candy Land', 'Saint-Petersburg', 'Sweet Suppliers'); INSERT INTO Supply VALUES ('Chocolate World', 'Novosibirsk', 'Quality Ingredients'); INSERT INTO Supply VALUES ('Cake Heaven', 'Yekaterinburg', 'Gourmet Goods'); INSERT INTO Supply VALUES ('Pastry Palace', 'Kazan', 'Organic Delights'); INSERT INTO Supply VALUES ('Dessert Oasis', 'Nizhny Novgorod', 'Daily Harvest'); INSERT INTO Supply VALUES ('Candy Castle', 'Samara', 'Baker/s Best'); INSERT INTO Supply VALUES ('Sugar Rush', 'Omsk', 'Local Farms'); INSERT INTO Supply VALUES ('Delicacy Depot', 'Kaliningrad', 'Exotic Flavors'); INSERT INTO Supply VALUES ('Treat Factory', 'Volgograd', 'Global Sweets'); INSERT INTO Supply VALUES ('Sugar Rush', 'Omsk', 'Daily Harvest'); INSERT INTO Supply VALUES ('Candy Land', 'Saint-Petersburg', 'Quality Ingredients'); SELECT * FROM Supply; -- Связь между Confectionery и Product (продукция) CREATE TABLE Fabricate ( title VARCHAR(255), city VARCHAR(255), appellation VARCHAR(255), PRIMARY KEY (title, city, appellation), FOREIGN KEY (title, city) REFERENCES Confectionery (title, city), FOREIGN KEY (appellation) REFERENCES Product (appellation) ); INSERT INTO Fabricate VALUES ('Sweet Dreams', 'Moscow', 'Chocolate Cake'); INSERT INTO Fabricate VALUES ('Candy Land', 'Saint-Petersburg', 'Cupcake'); INSERT INTO Fabricate VALUES ('Chocolate World', 'Novosibirsk', 'Brownie'); INSERT INTO Fabricate VALUES ('Cake Heaven', 'Yekaterinburg', 'Cookie'); INSERT INTO Fabricate VALUES ('Pastry Palace', 'Kazan', 'Strawberry'); INSERT INTO Fabricate VALUES ('Dessert Oasis', 'Nizhny Novgorod', 'Muffin'); INSERT INTO Fabricate VALUES ('Candy Castle', 'Samara', 'Cheesecake'); INSERT INTO Fabricate VALUES ('Sugar Rush', 'Omsk', 'Pudding'); INSERT INTO Fabricate VALUES ('Delicacy Depot', 'Kaliningrad', 'Tarts'); INSERT INTO Fabricate VALUES ('Treat Factory', 'Volgograd', 'Eclairs'); SELECT * FROM Fabricate; -- Связь между Customer и Order (заказы клиентов) CREATE TABLE Make ( ID INT, name VARCHAR(255), phoneNumber VARCHAR(20), PRIMARY KEY (name, phoneNumber, ID), FOREIGN KEY (name, phoneNumber) REFERENCES Customer (name, phoneNumber), FOREIGN KEY (ID) REFERENCES Orders (ID) ); INSERT INTO Make VALUES (764, 'Alexey', '+7 (123) 456-78-90'); INSERT INTO Make VALUES (284, 'Maria', '+7 (987) 654-32-10'); INSERT INTO Make VALUES (298, 'Dmitry', '+7 (456) 789-01-23'); INSERT INTO Make VALUES (466, 'Anna', '+7 (321) 098-76-54'); INSERT INTO Make VALUES (555, 'Sergei', '+7 (678) 543-21-09'); INSERT INTO Make VALUES (896, 'Ekaterina', '+7 (234) 765-43-21'); INSERT INTO Make VALUES (214, 'Ivan', '+7 (890) 123-45-67'); INSERT INTO Make VALUES (154, 'Olga', '+7 (567) 890-12-34'); INSERT INTO Make VALUES (375, 'Maksim', '+7 (345) 678-90-12'); INSERT INTO Make VALUES (986, 'Elena', '+7 (901) 234-56-78'); INSERT INTO Make VALUES (777, 'Olga', '+7 (567) 890-12-34'); SELECT * FROM Make; -- Связь между Ingredient и Supplier (поставщики ингредиентов) CREATE TABLE Deliver ( manufacturer VARCHAR(255), kind VARCHAR(255), PRIMARY KEY (kind, manufacturer), FOREIGN KEY (kind) REFERENCES Ingredient (kind), FOREIGN KEY (manufacturer) REFERENCES Supplier (manufacturer) ); INSERT INTO Deliver VALUES ('Fresh Farms', 'Flour'); INSERT INTO Deliver VALUES ('Fresh Farms', 'Sugar'); INSERT INTO Deliver VALUES ('Sweet Suppliers', 'Sugar'); INSERT INTO Deliver VALUES ('Quality Ingredients', 'Eggs'); INSERT INTO Deliver VALUES ('Gourmet Goods', 'Oil'); INSERT INTO Deliver VALUES ('Gourmet Goods', 'Cocoa powder'); INSERT INTO Deliver VALUES ('Organic Delights', 'Vanilla'); INSERT INTO Deliver VALUES ('Daily Harvest', 'Chocolate'); INSERT INTO Deliver VALUES ('Baker/s Best', 'Cocoa powder'); INSERT INTO Deliver VALUES ('Fresh Farms', 'Vanilla'); INSERT INTO Deliver VALUES ('Local Farms', 'Nuts'); INSERT INTO Deliver VALUES ('Exotic Flavors', 'Berries'); INSERT INTO Deliver VALUES ('Exotic Flavors', 'Vanilla'); INSERT INTO Deliver VALUES ('Global Sweets', 'Salt'); SELECT * FROM Deliver; -- Связь между Product и Ingredient (ингредиенты в продукте) CREATE TABLE Employ ( appellation VARCHAR(255), kind VARCHAR(255), PRIMARY KEY (appellation, kind), FOREIGN KEY (appellation) REFERENCES Product (appellation), FOREIGN KEY (kind) REFERENCES Ingredient (kind) ); INSERT INTO Employ VALUES ('Chocolate Cake', 'Flour'); INSERT INTO Employ VALUES ('Chocolate Cake', 'Eggs'); INSERT INTO Employ VALUES ('Chocolate Cake', 'Sugar'); INSERT INTO Employ VALUES ('Chocolate Cake', 'Cocoa powder'); INSERT INTO Employ VALUES ('Cupcake', 'Sugar'); INSERT INTO Employ VALUES ('Brownie', 'Eggs'); INSERT INTO Employ VALUES ('Brownie', 'Cocoa powder'); INSERT INTO Employ VALUES ('Cookie', 'Oil'); INSERT INTO Employ VALUES ('Strawberry', 'Vanilla'); INSERT INTO Employ VALUES ('Muffin', 'Chocolate'); INSERT INTO Employ VALUES ('Cheesecake', 'Cocoa powder'); INSERT INTO Employ VALUES ('Pudding', 'Nuts'); INSERT INTO Employ VALUES ('Pudding', 'Vanilla'); INSERT INTO Employ VALUES ('Pudding', 'Chocolate'); INSERT INTO Employ VALUES ('Tarts', 'Berries'); INSERT INTO Employ VALUES ('Eclairs', 'Salt'); INSERT INTO Employ VALUES ('Cookie', 'Nuts'); SELECT * FROM Employ; -- Связь между Order и Product (включение продукции в заказ) CREATE TABLE Include ( ID INT, appellation VARCHAR(255), PRIMARY KEY (ID, appellation), FOREIGN KEY (ID) REFERENCES Orders (ID), FOREIGN KEY (appellation) REFERENCES Product (appellation) ); INSERT INTO Include VALUES (764, 'Chocolate Cake'); INSERT INTO Include VALUES (764, 'Cookie'); INSERT INTO Include VALUES (284, 'Cupcake'); INSERT INTO Include VALUES (298, 'Brownie'); INSERT INTO Include VALUES (466, 'Cookie'); INSERT INTO Include VALUES (764, 'Brownie'); INSERT INTO Include VALUES (555, 'Strawberry'); INSERT INTO Include VALUES (555, 'Brownie'); INSERT INTO Include VALUES (555, 'Cheesecake'); INSERT INTO Include VALUES (896, 'Muffin'); INSERT INTO Include VALUES (284, 'Strawberry'); INSERT INTO Include VALUES (214, 'Cheesecake'); INSERT INTO Include VALUES (154, 'Pudding'); INSERT INTO Include VALUES (375, 'Tarts'); INSERT INTO Include VALUES (986, 'Eclairs'); INSERT INTO Include VALUES (154, 'Tarts'); SELECT * FROM Include; -- 1. Простой запрос SELECT-FROM-WHERE к одной таблице -- Этот запрос выбирает названия кондитерских, города и их рейтинг для тех -- кондитерских, у которых рейтинг больше 4.5. SELECT title, city, rating FROM Confectionery WHERE rating > 4.5; -- 2. Простой запрос SELECT-FROM-WHERE к двум таблицам -- Этот запрос выбирает название и город кондитерской и дату заказа, -- связав таблицы Confectionery и Orders по определённым условиям. SELECT c.title, c.city, o.order_date FROM Confectionery c, Orders o WHERE c.title = 'Sweet Dreams' AND o.ID = 555; -- 3. Простой запрос SELECT-FROM-WHERE к трем таблицам: -- Этот запрос выводит информацию о кондитерской, используемых ингредиентах -- и их количестве для конкретной кондитерской в Санкт-Петербурге. SELECT c.title, c.city, i.kind, i.quantity FROM Confectionery c, Used u, Ingredient i WHERE c.title = 'Candy Land' AND c.city = 'Saint-Petersburg' AND u.title = c.title AND u.city = c.city AND u.kind = i.kind; -- 4. Запрос к одной таблице, умноженной на себя: -- Этот запрос выводит пары сотрудников, где первый сотрудник получает большую -- зарплату, чем второй. SELECT a.name, b.name FROM Employee a, Employee b WHERE a.salary > b.salary AND a.name != b.name; -- 5. SQL-запросы с подзапросами, использующие различные операторы, такие как -- EXISTS, IN, ALL, ANY ---- Запросы с подзапросами, использующие 2 таблицы (4 запроса): ---- 1. Этот запрос находит все кондитерские, которые ---- имели заказ с ID 555 на 23 февраля 2024 года. ------ Использование EXISTS: SELECT title, city FROM Confectionery c WHERE EXISTS ( SELECT 1 FROM Orders o WHERE o.ID = 555 AND o.order_date = '23-02-2024' ); ------ Использование IN: SELECT title, city FROM Confectionery c WHERE (c.title, c.city) IN ( SELECT title, city FROM Orders WHERE ID = 555 AND order_date = '23-02-2024' ); ---- 2. Этот запрос выводит имена и должности сотрудников, ---- чья зарплата больше, чем у всех менеджеров в компании. ------ Использование ALL: SELECT name, post FROM Employee WHERE salary > ALL ( SELECT salary FROM Employee WHERE post = 'Manager' ); ------ Использование ANY: SELECT name, post FROM Employee WHERE NOT salary < ANY ( SELECT salary FROM Employee WHERE post = 'Manager' ); ---- Запросы с подзапросами, использующие 3 таблицы (4 запроса): ---- 1. Этот запрос выводит кондитерские изделия, в которых используется мука ------ Использование EXISTS: SELECT title, city FROM Confectionery c WHERE EXISTS ( SELECT 1 FROM Used u WHERE u.title = c.title AND u.city = c.city AND u.kind = 'Flour' ); ------ Использование IN: SELECT title, city FROM Confectionery c WHERE (c.title, c.city) IN ( SELECT title, city FROM Used WHERE kind = 'Flour' ); ---- 2. Этот запрос выводит продукты, приготовленные из ингредиентов, поставляемых ---- конкретным поставщиком Quality Ingredients. ------ Использование ALL: SELECT p.appellation FROM Product p, Employ e, Deliver d WHERE p.appellation = e.appellation AND e.kind = d.kind AND d.manufacturer = 'Quality Ingredients' AND e.kind = ALL ( SELECT d.kind FROM Deliver d WHERE d.manufacturer = 'Quality Ingredients' ); ------ Использование ANY: SELECT p.appellation FROM Product p, Employ e, Deliver d WHERE p.appellation = e.appellation AND e.kind = d.kind AND d.manufacturer = 'Quality Ingredients' AND NOT e.kind < ANY ( SELECT d.kind FROM Deliver d WHERE d.manufacturer = 'Quality Ingredients' ); -- 6. Запросы теории множеств UNION, INTERSECT, EXCEPT(MINUS): ---- UNION: Этот запрос объединяет список всех клиентов и сотрудников в одну таблицу. SELECT name AS person_name, phoneNumber FROM Customer UNION SELECT name AS person_name, phoneNumber FROM Employee; ---- INTERSECT: Этот запрос выводит названия товаров, для которых используются ---- яйца и сахар. SELECT p.appellation FROM Product p WHERE p.appellation IN ( SELECT e.appellation FROM Employ e WHERE e.kind = 'Eggs' ) INTERSECT SELECT p.appellation FROM Product p WHERE p.appellation IN ( SELECT e.appellation FROM Employ e WHERE e.kind = 'Sugar' ); ---- EXCEPT: Этот запрос выводит названия товаров, для которых не используется ---- ваниль. SELECT p.appellation FROM Product p EXCEPT SELECT p.appellation FROM Product p WHERE p.appellation IN ( SELECT e.appellation FROM Employ e WHERE e.kind = 'Vanilla' ); -- 7. Пользовательское представление из 3 таблиц. ---- Пользовательское представление: CREATE VIEW OrderDetails AS SELECT o.ID AS OrderID, o.order_date AS OrderDate, o.cost AS OrderCost, c.name AS CustomerName, c.phoneNumber AS CustomerPhone, p.appellation AS ProductName, p.retailPrice AS ProductPrice FROM Orders o JOIN Make m ON m.ID = o.ID JOIN Customer c ON c.name = m.name AND c.phoneNumber = m.phoneNumber JOIN Include i ON i.ID = o.ID JOIN Product p ON p.appellation = i.appellation; ---- 1 запрос: Получить заказы, в которых заказан конкретный продукт "Brownie", ---- с указанием имени клиента и стоимости заказа. SELECT * FROM OrderDetails WHERE ProductName = 'Brownie'; ---- 2 запрос: Найти заказы, сделанные клиентами с определённым номером телефона, ---- "+7 (123) 456-78-90". SELECT * FROM OrderDetails WHERE CustomerPhone = '+7 (123) 456-78-90'; ---- 3 запрос: Получить заказы с продукцией, чья стоимость больше 120. SELECT * FROM OrderDetails WHERE ProductPrice > 120; -- 7. Внешние и внутренние соединения ---- JOIN ON возвращает только те строки, которые имеют совпадения в обеих ---- таблицах: Этот запрос вернет только те кондитерские и заказы, которые ---- присутствуют в обеих таблицах. SELECT c.title, c.city, o.order_date, o.cost FROM Confectionery c INNER JOIN Take t ON c.title = t.title AND c.city = t.city INNER JOIN Orders o ON t.ID = o.ID; ---- LEFT OUTER JOIN возвращает все строки из левой таблицы (в данном случае, ---- из таблицы Confectionery`), а если в правой таблице (например, `Orders`) ---- нет соответствующих строк, то будут возвращены `NULL значения для полей ---- правой таблицы: Этот запрос вернет все кондитерские, даже если они не имели ---- заказов, с полями заказов, заполненными NULL, если заказа не было. SELECT c.title, c.city, o.order_date, o.cost FROM Confectionery c LEFT OUTER JOIN Take t ON c.title = t.title AND c.city = t.city LEFT OUTER JOIN Orders o ON t.ID = o.ID; ---- RIGHT OUTER JOIN аналогичен LEFT OUTER JOIN, но возвращает все строки из ---- правой таблицы (`Orders`), и если в левой таблице (`Confectionery`) нет ---- совпадений, то поля левой таблицы будут содержать NULL: Этот запрос вернет ---- все заказы, даже если они не cвязаны с конкретной кондитерской. SELECT c.title, c.city, o.order_date, o.cost FROM Confectionery c RIGHT OUTER JOIN Take t ON c.title = t.title AND c.city = t.city RIGHT OUTER JOIN Orders o ON t.ID = o.ID; ---- FULL OUTER JOIN возвращает все строки из обеих таблиц. Если строка не имеет ---- соответствующего значения в другой таблице, то для столбцов другой таблицы ---- будут возвращены NULL: Этот запрос вернет все кондитерские и все заказы, даже ---- если они не связаны между собой. Если нет связи, то будут возвращены NULL значения. SELECT c.title, c.city, o.order_date, o.cost FROM Confectionery c FULL OUTER JOIN Take t ON c.title = t.title AND c.city = t.city FULL OUTER JOIN Orders o ON t.ID = o.ID; ---- CROSS JOIN возвращает декартово произведение двух таблиц, т.е. каждое ---- значение из первой таблицы соединяется с каждым значением из второй таблицы: ---- Получить все возможные комбинации кондитерских и продукции. Каждый продукт из ---- таблицы Product будет скомбинирован с каждой кондитерской из таблицы Confectionery, ---- создавая все возможные комбинации. SELECT Confectionery.title, Confectionery.city, Product.appellation FROM Confectionery CROSS JOIN Product; -- 9. Агрегация (SUM,COUNT,AVG,MIN,MAX) ---- SUM — сумма затрат на все ингредиенты в кондитерской: SELECT SUM(i.cost * i.quantity) AS total_cost FROM Ingredient i; ---- COUNT — количество сотрудников в каждой кондитерской: SELECT title, city, COUNT(*) AS employee_count FROM Cooperate GROUP BY title, city; ---- AVG — средняя зарплата сотрудников в каждой кондитерской: SELECT title, city, AVG(e.salary) AS average_salary FROM Cooperate c JOIN Employee e ON c.name = e.name AND c.post = e.post GROUP BY title, city; ---- MIN — минимальная стоимость продукта: SELECT MIN(retailPrice) AS min_retail_price FROM Product; ---- MAX — максимальная оценка кондитерской: SELECT MAX(rating) AS max_rating FROM Confectionery; -- 10. Агрегация и GROUP BY: Запрос для подсчета количества заказов каждого клиента: SELECT c.name, c.phoneNumber, COUNT(o.ID) AS total_orders FROM Make m JOIN Customer c ON m.name = c.name AND m.phoneNumber = c.phoneNumber JOIN Orders o ON m.ID = o.ID GROUP BY c.name, c.phoneNumber; -- 11. Агрегация, GROUP BY, HAVING: Запрос для подсчета количества продуктов, -- заказанных каждым клиентом, но только для тех клиентов, у которых количество -- заказов 2 и больше: SELECT c.name, c.phoneNumber, COUNT(o.ID) AS total_orders FROM Make m JOIN Customer c ON m.name = c.name AND m.phoneNumber = c.phoneNumber JOIN Orders o ON m.ID = o.ID GROUP BY c.name, c.phoneNumber HAVING COUNT(o.ID) > 1; -- 12. Коррелированные запросы разного типа: ---- Запрос для получения кондитерских с их рейтингами, где работают кондитеры, ---- у которых зарплата выше 600: SELECT c.title, c.city, c.rating FROM Confectionery c WHERE c.title IN ( SELECT co.title FROM Cooperate co JOIN Employee e ON co.name = e.name AND co.post = e.post WHERE co.city = c.city AND e.salary > 600 AND e.post = 'Confectioner' ); ---- Запрос для получения всех заказов с их датами, где клиенты заказывали чизкейк SELECT o.ID, o.order_date FROM Orders o WHERE EXISTS ( SELECT 1 FROM Include i JOIN Product p ON i.appellation = p.appellation WHERE i.ID = o.ID AND p.appellation = 'Cheesecake' ); CREATE OR REPLACE TRIGGER maintain_referential_integrity BEFORE DELETE ON Confectionery FOR EACH ROW BEGIN -- Удаление записей в связанных таблицах перед удалением кондитерской BEGIN DELETE FROM Visit WHERE title = :OLD.title AND city = :OLD.city; DBMS_OUTPUT.PUT_LINE('Deleted from Visit where title = ' || :OLD.title || ' and city = ' || :OLD.city); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error deleting from Visit: ' || SQLERRM); END; BEGIN DELETE FROM Cooperate WHERE title = :OLD.title AND city = :OLD.city; DBMS_OUTPUT.PUT_LINE('Deleted from Cooperate where title = ' || :OLD.title || ' and city = ' || :OLD.city); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error deleting from Cooperate: ' || SQLERRM); END; BEGIN DELETE FROM Used WHERE title = :OLD.title AND city = :OLD.city; DBMS_OUTPUT.PUT_LINE('Deleted from Used where title = ' || :OLD.title || ' and city = ' || :OLD.city); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error deleting from Used: ' || SQLERRM); END; BEGIN DELETE FROM Take WHERE title = :OLD.title AND city = :OLD.city; DBMS_OUTPUT.PUT_LINE('Deleted from Take where title = ' || :OLD.title || ' and city = ' || :OLD.city); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error deleting from Take: ' || SQLERRM); END; BEGIN DELETE FROM Supply WHERE title = :OLD.title AND city = :OLD.city; DBMS_OUTPUT.PUT_LINE('Deleted from Supply where title = ' || :OLD.title || ' and city = ' || :OLD.city); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error deleting from Supply: ' || SQLERRM); END; BEGIN DELETE FROM Fabricate WHERE title = :OLD.title AND city = :OLD.city; DBMS_OUTPUT.PUT_LINE('Deleted from Fabricate where title = ' || :OLD.title || ' and city = ' || :OLD.city); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error deleting from Fabricate: ' || SQLERRM); END; BEGIN DELETE FROM Make WHERE ID IN (SELECT ID FROM Orders WHERE title = :OLD.title AND city = :OLD.city); DBMS_OUTPUT.PUT_LINE('Deleted from Make where ID IN (SELECT ID FROM Orders WHERE title = ' || :OLD.title || ' and city = ' || :OLD.city || ')'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error deleting from Make: ' || SQLERRM); END; BEGIN DELETE FROM Include WHERE ID IN (SELECT ID FROM Orders WHERE title = :OLD.title AND city = :OLD.city); DBMS_OUTPUT.PUT_LINE('Deleted from Include where ID IN (SELECT ID FROM Orders WHERE title = ' || :OLD.title || ' and city = ' || :OLD.city || ')'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error deleting from Include: ' || SQLERRM); END; END;