SQLize Online / PHPize Online  /  SQLtest Online

create table Store ( storeID number, name varchar2(20), location varchar2(50), contactInfo varchar2(50) ); INSERT INTO Store VALUES (356, 'FashionHub', 'Moscow, Russia', '84951234567'); INSERT INTO Store VALUES (234, 'StyleSpot', 'Saint Petersburg, Russia', '88122345678'); INSERT INTO Store VALUES (321, 'TrendSetter', 'Novosibirsk, Russia', '83831234567'); INSERT INTO Store VALUES (245, 'ChicBoutique', 'Yekaterinburg, Russia', '83439876543'); INSERT INTO Store VALUES (267, 'EcoFashion', 'Kazan, Russia', '88431234567'); INSERT INTO Store VALUES (123, 'SportStyle', 'Nizhny Novgorod, Russia', '88312345678'); INSERT INTO Store VALUES (256, 'ModaCasa', 'Chelyabinsk, Russia', '83519876543'); INSERT INTO Store VALUES (743, 'GlamourSpot', 'Samara, Russia', '88461234567'); INSERT INTO Store VALUES (512, 'UrbanChic', 'Krasnodar, Russia', '84951237654'); INSERT INTO Store VALUES (678, 'VintageVogue', 'Sochi, Russia', '88641238765'); CREATE TABLE Product ( article NUMBER, name VARCHAR2(50), category VARCHAR2(20), sizes VARCHAR2(5), color VARCHAR2(20), price NUMBER, stockQuantity NUMBER ); INSERT INTO Product VALUES (82635, 'Classic T-Shirt', 'Tops', 'M', 'Black', 1500, 120); INSERT INTO Product VALUES (92147, 'Summer Dress', 'Dresses', 'S', 'Red', 3500, 45); INSERT INTO Product VALUES (34829, 'Hoodie', 'Outerwear', 'M', 'Gray', 3000, 60); INSERT INTO Product VALUES (65943, 'Leather Jacket', 'Outerwear', 'L', 'Black', 7000, 35); INSERT INTO Product VALUES (71582, 'Cargo Pants', 'Bottoms', 'M', 'Green', 2700, 100); INSERT INTO Product VALUES (23491, 'Maxi Skirt', 'Bottoms', 'S', 'Pink', 3200, 50); INSERT INTO Product VALUES (48236, 'Blazer', 'Outerwear', 'XL', 'Navy', 5000, 40); INSERT INTO Product VALUES (50984, 'Denim Jacket', 'Outerwear', 'M', 'Blue', 4500, 55); INSERT INTO Product VALUES (61473, 'Polo Shirt', 'Tops', 'L', 'White', 1800, 75); CREATE TABLE Customer ( name VARCHAR2(50), email VARCHAR2(100), phone VARCHAR2(20), address VARCHAR2(100) ); INSERT INTO Customer VALUES ('Alexei Petrov', 'alice.johnson@email.com', '+7 495 1111111', 'Moscow, Tverskaya St, 15'); INSERT INTO Customer VALUES ('Dmitry Ivanov', 'bob.smith@email.com', '+7 812 2222222', 'Saint Petersburg, Nevsky Ave, 45'); INSERT INTO Customer VALUES ('Natalia Sokolova', 'charlie.brown@email.com', '+7 383 3333333', 'Novosibirsk, Lenina St, 23'); INSERT INTO Customer VALUES ('Maria Novikova', 'diana.prince@email.com', '+7 343 4444444', 'Yekaterinburg, Malysheva St, 10'); INSERT INTO Customer VALUES ('Sergey Kuznetsov', 'edward.green@email.com', '+7 843 5555555', 'Kazan, Baumana St, 50'); INSERT INTO Customer VALUES ('Elena Smirnova', 'fiona.davis@email.com', '+7 831 6666666', 'Nizhny Novgorod, Gorkogo St, 12'); INSERT INTO Customer VALUES ('Viktor Orlov', 'george.white@email.com', '+7 351 7777777', 'Chelyabinsk, Kirova St, 7'); INSERT INTO Customer VALUES ('Anna Volkova', 'hannah.black@email.com', '+7 846 8888888', 'Samara, Kuybysheva St, 20'); INSERT INTO Customer VALUES ('Igor Fedorov', 'igor.fedorov@email.com', '+7 916 9999999', 'Moscow, Arbat St, 9'); INSERT INTO Customer VALUES ('Tatiana Morozova', 'tatiana.morozova@email.com', '+7 812 1234567', 'Saint Petersburg, Ligovsky Ave, 33'); CREATE TABLE Orders ( orderID INT, orderDate VARCHAR(10), -- Можно использовать строку формата 'MM/DD/YYYY' totalAmount DECIMAL(10, 2), placedIn VARCHAR(100), submittedBy VARCHAR(100), paidBy INT ); INSERT INTO Orders VALUES (51234, '09/01/2024', 8500, 'Moscow, Tverskaya St', 'Alexandra Gromova', 78342); INSERT INTO Orders VALUES (62345, '09/02/2024', 5200, 'Saint Petersburg, Nevsky Ave', 'Denis Lapin', 94321); INSERT INTO Orders VALUES (23154, '09/03/2024', 12000, 'Novosibirsk, Lenina St', 'Svetlana Berezina', 12365); INSERT INTO Orders VALUES (86453, '09/04/2024', 4600, 'Yekaterinburg, Malysheva St', 'Oleg Tikhonov', 67431); INSERT INTO Orders VALUES (13542, '09/05/2024', 7800, 'Kazan, Baumana St', 'Marina Zaitseva', 19843); INSERT INTO Orders VALUES (76321, '09/06/2024', 3900, 'Nizhny Novgorod, Gorkogo St', 'Maxim Solovyov', 32154); INSERT INTO Orders VALUES (90876, '09/07/2024', 6500, 'Chelyabinsk, Kirova St', 'Anastasia Kravtsova', 87534); INSERT INTO Orders VALUES (31425, '09/08/2024', 5200, 'Samara, Kuybysheva St', 'Vladimir Egorov', 54312); INSERT INTO Orders VALUES (78912, '09/09/2024', 7100, 'Moscow, Tverskaya St', 'Natalia Romanova', 93847); INSERT INTO Orders VALUES (45678, '09/10/2024', 9000, 'Saint Petersburg, Nevsky Ave', 'Igor Petrov', 74231); CREATE TABLE Payment ( paymentID INT, method VARCHAR(50), paymentDate VARCHAR(10), -- хранение даты в виде строки amountPaid DECIMAL(10, 2) ); INSERT INTO Payment VALUES (78342, 'Credit Card', '09/01/2024', 8500); INSERT INTO Payment VALUES (94321, 'PayPal', '09/02/2024', 5200); INSERT INTO Payment VALUES (12365, 'Credit Card', '09/03/2024', 12000); INSERT INTO Payment VALUES (67431, 'Cash', '09/04/2024', 4600); INSERT INTO Payment VALUES (19843, 'Bank Transfer', '09/05/2024', 7800); INSERT INTO Payment VALUES (32154, 'PayPal', '09/06/2024', 3900); INSERT INTO Payment VALUES (87534, 'Credit Card', '09/07/2024', 6500); INSERT INTO Payment VALUES (54312, 'Cash', '09/08/2024', 5200); INSERT INTO Payment VALUES (93847, 'Credit Card', '09/09/2024', 7100); INSERT INTO Payment VALUES (74231, 'Bank Transfer', '09/10/2024', 9000); CREATE TABLE Supplier ( name VARCHAR(100), phone VARCHAR(20), email VARCHAR(100) ); INSERT INTO Supplier VALUES ('Global Textiles', '8 (903) 567-8900', 'info@globaltextiles.com'); INSERT INTO Supplier VALUES ('Fashion Supplies Ltd', '8 (905) 1234-5678', 'contact@fashionsupplies.co.uk'); INSERT INTO Supplier VALUES ('Premium Clothing Co', '8 (914) 123-4567', 'sales@premiumclothing.de'); INSERT INTO Supplier VALUES ('Urban Wear Suppliers', '8 (912) 9876-5432', 'urban@wearsuppliers.fr'); INSERT INTO Supplier VALUES ('Classic Fabrics', '8 (920) 1234-5678', 'classic@fabrics.in'); INSERT INTO Supplier VALUES ('Elegant Apparel', '8 (916) 555-1234', 'service@elegantapparel.com'); INSERT INTO Supplier VALUES ('Modern Style Ltd', '8 (915) 1234-5678', 'support@modernstyle.jp'); INSERT INTO Supplier VALUES ('EuroFashion Imports', '8 (903) 9876-5432', 'sales@eurofashion.it'); INSERT INTO Supplier VALUES ('Smart Textile Co', '8 (917) 123-7654', 'info@smarttextile.com'); INSERT INTO Supplier VALUES ('Luxe Fabrics Inc', '8 (918) 432-9876', 'contact@luxefabricsinc.com'); CREATE TABLE Employee ( Name VARCHAR(100), Position VARCHAR(50), Salary DECIMAL(10, 2), HireDate VARCHAR(10), -- Дата как строка в формате 'MM/DD/YYYY' Store VARCHAR(100) ); INSERT INTO Employee VALUES ('Alice Ivanova', 'Sales Manager', 60000, '03/15/2022', 'Global Textiles'); INSERT INTO Employee VALUES ('Dmitry Petrov', 'Store Manager', 75000, '06/10/2021', 'Fashion Supplies Ltd'); INSERT INTO Employee VALUES ('Maria Sidorova', 'Cashier', 40000, '01/20/2023', 'Premium Clothing Co'); INSERT INTO Employee VALUES ('Igor Vasiliev', 'Stock Manager', 50000, '02/25/2022', 'Urban Wear Suppliers'); INSERT INTO Employee VALUES ('Elena Kuznetsova', 'Sales Associate', 45000, '05/12/2023', 'Classic Fabrics'); INSERT INTO Employee VALUES ('Sergey Mikhailov', 'Customer Service', 48000, '11/30/2021', 'Elegant Apparel'); INSERT INTO Employee VALUES ('Tatiana Romanova', 'Marketing Specialist', 55000, '09/15/2020', 'Modern Style Ltd'); INSERT INTO Employee VALUES ('Vladimir Nikolaev', 'Sales Associate', 42000, '04/05/2023', 'EuroFashion Imports'); INSERT INTO Employee VALUES ('Anna Volkova', 'Assistant Manager', 62000, '07/01/2022', 'Urban Wear Suppliers'); INSERT INTO Employee VALUES ('Alexey Smirnov', 'HR Specialist', 58000, '08/18/2021', 'Fashion Supplies Ltd'); CREATE TABLE processes ( storeID INT, name VARCHAR(100), orderID INT ); INSERT INTO processes VALUES (356, 'FashionHub', 51234); INSERT INTO processes VALUES (234, 'StyleSpot', 13256); INSERT INTO processes VALUES (234, 'StyleSpot', 45678); INSERT INTO processes VALUES (234, 'StyleSpot', 23678); INSERT INTO processes VALUES (234, 'StyleSpot', 62345); INSERT INTO processes VALUES (321, 'TrendSetter', 23154); INSERT INTO processes VALUES (245, 'ChicBoutique', 86453); INSERT INTO processes VALUES (267, 'EcoFashion', 13542); INSERT INTO processes VALUES (123, 'SportStyle', 76321); INSERT INTO processes VALUES (256, 'ModaCasa', 90876); INSERT INTO processes VALUES (743, 'GlamourSpot', 54312); INSERT INTO processes VALUES (512, 'UrbanChic', 93847); INSERT INTO processes VALUES (678, 'VintageVogue', 74231); CREATE TABLE provides ( storeID INT, name VARCHAR(100), article_name VARCHAR(100) ); INSERT INTO provides VALUES (356, 'FashionHub', 'Classic T-Shirt'); INSERT INTO provides VALUES (356, 'FashionHub', 'Skinny Jeans'); INSERT INTO provides VALUES (234, 'StyleSpot', 'Skinny Jeans'); INSERT INTO provides VALUES (234, 'StyleSpot', 'Summer Dress'); INSERT INTO provides VALUES (321, 'TrendSetter', 'Summer Dress'); INSERT INTO provides VALUES (321, 'TrendSetter', 'Hoodie'); INSERT INTO provides VALUES (245, 'ChicBoutique', 'Hoodie'); INSERT INTO provides VALUES (267, 'EcoFashion', 'Leather Jacket'); INSERT INTO provides VALUES (123, 'SportStyle', 'Cargo Pants'); INSERT INTO provides VALUES (256, 'ModaCasa', 'Maxi Skirt'); INSERT INTO provides VALUES (743, 'GlamourSpot', 'Blazer'); INSERT INTO provides VALUES (512, 'UrbanChic', 'Denim Jacket'); INSERT INTO provides VALUES (678, 'VintageVogue', 'Polo Shirt'); INSERT INTO provides VALUES (356, 'FashionHub', 'Leather Jacket'); INSERT INTO provides VALUES (678, 'VintageVogue', 'Classic T-Shirt'); INSERT INTO provides VALUES (256, 'ModaCasa', 'Denim Jacket'); CREATE TABLE contain ( orderID INT, article VARCHAR(100) ); INSERT INTO contain VALUES (51234, '82635 Classic T-Shirt'); INSERT INTO contain VALUES (62345, '57382 Skinny Jeans'); INSERT INTO contain VALUES (23154, '92147 Summer Dress'); INSERT INTO contain VALUES (86453, '34829 Hoodie'); INSERT INTO contain VALUES (13542, '65943 Leather Jacket'); INSERT INTO contain VALUES (76321, '71582 Cargo Pants'); INSERT INTO contain VALUES (90876, '23491 Maxi Skirt'); INSERT INTO contain VALUES (31425, '48236 Blazer'); INSERT INTO contain VALUES (78912, '50984 Denim Jacket'); INSERT INTO contain VALUES (45678, '61473 Polo Shirt'); INSERT INTO contain VALUES (51234, '57382 Skinny Jeans'); INSERT INTO contain VALUES (23154, '34829 Hoodie'); INSERT INTO contain VALUES (86453, '82635 Classic T-Shirt'); INSERT INTO contain VALUES (13542, '92147 Summer Dress'); CREATE TABLE work ( storeID INT, name VARCHAR(100), employee_name VARCHAR(100), position VARCHAR(100) ); INSERT INTO work VALUES (356, 'FashionHub', 'Alice Ivanova', 'Sales Manager'); INSERT INTO work VALUES (356, 'FashionHub', 'Ivan Petrov', 'Cashier'); INSERT INTO work VALUES (356, 'FashionHub', 'Maria Sidorova', 'Stock Manager'); INSERT INTO work VALUES (234, 'StyleSpot', 'Dmitry Petrov', 'Store Manager'); INSERT INTO work VALUES (234, 'StyleSpot', 'Elena Kuznetsova', 'Sales Associate'); INSERT INTO work VALUES (321, 'TrendSetter', 'Maria Sidorova', 'Cashier'); INSERT INTO work VALUES (321, 'TrendSetter', 'Igor Vasiliev', 'Stock Manager'); INSERT INTO work VALUES (245, 'ChicBoutique', 'Igor Vasiliev', 'Stock Manager'); INSERT INTO work VALUES (267, 'EcoFashion', 'Elena Kuznetsova', 'Sales Associate'); INSERT INTO work VALUES (123, 'SportStyle', 'Sergey Mikhailov', 'Customer Service'); INSERT INTO work VALUES (256, 'ModaCasa', 'Tatiana Romanova', 'Marketing Specialist'); INSERT INTO work VALUES (743, 'GlamourSpot', 'Vladimir Nikolaev', 'Sales Associate'); INSERT INTO work VALUES (512, 'UrbanChic', 'Anna Volkova', 'Assistant Manager'); INSERT INTO work VALUES (678, 'VintageVogue', 'Alexey Smirnov', 'HR Specialist'); INSERT INTO work VALUES (678, 'VintageVogue', 'Dmitry Petrov', 'Sales Associate'); CREATE TABLE supplied ( name VARCHAR(100), phone VARCHAR(20), article_name VARCHAR(100) ); INSERT INTO supplied VALUES ('Global Textiles', '8 (903) 567-8900', '82635 Classic T-Shirt'); INSERT INTO supplied VALUES ('Fashion Supplies Ltd', '8 (905) 1234-5678', '57382 Skinny Jeans'); INSERT INTO supplied VALUES ('Premium Clothing Co', '8 (914) 123-4567', '92147 Summer Dress'); INSERT INTO supplied VALUES ('Urban Wear Suppliers', '8 (912) 9876-5432', '34829 Hoodie'); INSERT INTO supplied VALUES ('Classic Fabrics', '8 (920) 1234-5678', '65943 Leather Jacket'); INSERT INTO supplied VALUES ('Elegant Apparel', '8 (916) 555-1234', '71582 Cargo Pants'); INSERT INTO supplied VALUES ('Modern Style Ltd', '8 (915) 1234-5678', '23491 Maxi Skirt'); INSERT INTO supplied VALUES ('EuroFashion Imports', '8 (903) 9876-5432', '48236 Blazer'); INSERT INTO supplied VALUES ('Smart Textile Co', '8 (917) 123-7654', '50984 Denim Jacket'); INSERT INTO supplied VALUES ('Luxe Fabrics Inc', '8 (918) 432-9876', '61473 Polo Shirt'); CREATE TABLE paid ( name VARCHAR(100), phone VARCHAR(20), paymentID INT, method VARCHAR(50) ); INSERT INTO paid VALUES ('Global Textiles', '8 (903) 567-8900', 78342, 'Credit Card'); INSERT INTO paid VALUES ('Fashion Supplies Ltd', '8 (905) 1234-5678', 94321, 'PayPal'); INSERT INTO paid VALUES ('Premium Clothing Co', '8 (914) 123-4567', 12365, 'Credit Card'); INSERT INTO paid VALUES ('Urban Wear Suppliers', '8 (912) 9876-5432', 67431, 'Cash'); INSERT INTO paid VALUES ('Classic Fabrics', '8 (920) 1234-5678', 19843, 'Bank Transfer'); INSERT INTO paid VALUES ('Elegant Apparel', '8 (916) 555-1234', 32154, 'PayPal'); INSERT INTO paid VALUES ('Modern Style Ltd', '8 (915) 1234-5678', 87534, 'Credit Card'); INSERT INTO paid VALUES ('EuroFashion Imports', '8 (903) 9876-5432', 54312, 'Cash'); INSERT INTO paid VALUES ('Smart Textile Co', '8 (917) 123-7654', 93847, 'Credit Card'); INSERT INTO paid VALUES ('Luxe Fabrics Inc', '8 (918) 432-9876', 74231, 'Bank Transfer'); CREATE TABLE submit ( name VARCHAR(100), phone VARCHAR(20), orderID INT ); INSERT INTO submit VALUES ('Alexei Petrov', '+7 495 1111111', 51234); INSERT INTO submit VALUES ('Alexei Petrov', '+7 495 1111111', 62345); INSERT INTO submit VALUES ('Dmitry Ivanov', '+7 812 2222222', 62346); INSERT INTO submit VALUES ('Natalia Sokolova', '+7 383 3333333', 23154); INSERT INTO submit VALUES ('Natalia Sokolova', '+7 383 3333333', 86453); INSERT INTO submit VALUES ('Maria Novikova', '+7 343 4444444', 86454); INSERT INTO submit VALUES ('Sergey Kuznetsov', '+7 843 5555555', 13542); INSERT INTO submit VALUES ('Elena Smirnova', '+7 831 6666666', 76321); INSERT INTO submit VALUES ('Viktor Orlov', '+7 351 7777777', 90876); INSERT INTO submit VALUES ('Anna Volkova', '+7 846 8888888', 31425); INSERT INTO submit VALUES ('Igor Fedorov', '+7 381 9999999', 78912); INSERT INTO submit VALUES ('Tatiana Morozova', '+7 863 1010101', 45678); INSERT INTO submit VALUES ('Alexei Petrov', '+7 495 1111111', 23155); INSERT INTO submit VALUES ('Dmitry Ivanov', '+7 812 2222222', 78913); INSERT INTO submit VALUES ('Maria Novikova', '+7 343 4444444', 45679); SELECT * FROM Store; SELECT * FROM Product; SELECT * FROM Customer; SELECT * FROM Orders; SELECT * FROM Payment; SELECT * FROM Supplier; SELECT * FROM Employee; SELECT * FROM processes; SELECT * FROM provides; SELECT * FROM contain; SELECT * FROM work; SELECT * FROM supplied; SELECT * FROM paid; SELECT * FROM submit; --простой запрос к 1 таблице SELECT storeID, name, location, contactInfo FROM Store WHERE location = 'Moscow, Russia'; --простой запрос к 2 таблицам SELECT o.orderID, o.totalAmount FROM Orders o, processes p WHERE p.name = 'StyleSpot' AND o.orderID = p.orderID; --простой запрос к 3 таблицам SELECT Store.name AS Store_Name, Product.name AS Product_Name, Customer.name AS Customer_Name FROM Store, Product, Customer WHERE Store.storeID = 234 AND Product.category = 'Outerwear' AND Customer.email = 'diana.prince@email.com'; --Запрос к одной таблице, умноженной на себя SELECT (SELECT name FROM Product A WHERE A.article = P.article) AS Product_A_Name, (SELECT name FROM Product B WHERE B.article = P.article) AS Product_B_Name FROM Product P; --2 таблицы, использующие EXISTS SELECT storeID, name FROM Store S WHERE EXISTS ( SELECT 1 FROM provides P WHERE P.storeID = S.storeID AND P.article_name = 'Classic T-Shirt' ); --2 таблицы, использующие IN SELECT storeID, name FROM Store WHERE storeID IN ( SELECT storeID FROM provides WHERE article_name = 'Leather Jacket' ); SELECT storeID, name FROM Store S WHERE 10000 > ALL ( SELECT price FROM Product P WHERE P.category = 'Outwear' ); --3 таблицы SELECT s.name FROM Store s WHERE EXISTS ( SELECT * FROM provides p, Product pr WHERE p.storeID = s.storeID AND p.article_name = pr.name AND pr.category = 'Tops' ); SELECT s.name FROM Store s WHERE s.storeID IN ( SELECT p.storeID FROM provides p, Product pr WHERE p.article_name = pr.name AND pr.category = 'Tops' ); SELECT s.name FROM Store s WHERE s.storeID IN (SELECT p.storeID FROM provides p, Product pr WHERE p.article_name = pr.name AND pr.price >= ALL (SELECT 2000 FROM dual)) AND EXISTS (SELECT 1 FROM provides p, Product pr WHERE p.storeID = s.storeID AND p.article_name = pr.name AND pr.price < 3000); SELECT s.name FROM Store s WHERE s.storeID IN (SELECT p.storeID FROM provides p, Product pr WHERE p.article_name = pr.name AND pr.price >= 2000) AND EXISTS (SELECT 1 FROM provides p, Product pr WHERE p.storeID = s.storeID AND p.article_name = pr.name AND pr.price < ANY (SELECT 3000 FROM dual)); --UNION (Объединение) --Найдем все уникальные названия товаров, которые продаются в магазинах, и все уникальные названия товаров в таблице Product, объединив результаты. SELECT name FROM Product UNION SELECT article_name FROM provides; --INTERSECT (Пересечение) --Найдем названия товаров, которые присутствуют как в таблице Product, так и в таблице provides. SELECT name FROM Product INTERSECT SELECT article_name FROM provides; --EXCEPT (MINUS): Product names in 'Tops' category but NOT in 'Outerwear' category SELECT name FROM Product WHERE category = 'Tops' EXCEPT SELECT name FROM Product WHERE category = 'Outerwear'; -- Создаем представление (view) CREATE OR REPLACE VIEW StoreProductView AS SELECT (SELECT name FROM Store WHERE storeID = pr.storeID) AS store_name, -- Подзапрос для получения имени магазина по storeID pr.article_name, -- Название товара из таблицы provides (SELECT name FROM Product WHERE name = pr.article_name) AS product_name, -- Подзапрос для получения имени товара из таблицы Product (SELECT category FROM Product WHERE name = pr.article_name) AS product_category -- Подзапрос для получения категории товара FROM provides pr; -- Основная таблица - provides -- Запрос к представлению (view) SELECT * FROM StoreProductView WHERE product_category = 'Tops'; -- 1. Внутреннее соединение (INNER JOIN) - неявный эквивалент NATURAL JOIN (менее предпочтительный) SELECT s.name, pr.article_name FROM Store s, provides pr WHERE s.storeID = pr.storeID; -- 2. Декартово произведение (CROSS JOIN) - все возможные комбинации SELECT s.name, pr.article_name FROM Store s CROSS JOIN provides pr; -- 3. Полное внешнее соединение (NATURAL FULL OUTER JOIN) - все строки из обеих таблиц, NULL там, где нет соответствия SELECT s.name, pr.article_name FROM Store s FULL OUTER JOIN provides pr USING (storeID); -- 4. Левое внешнее соединение (NATURAL LEFT OUTER JOIN) - все строки из левой таблицы (Store), NULL там, где нет соответствия в правой SELECT s.name, pr.article_name FROM Store s LEFT OUTER JOIN provides pr ON s.storeID = pr.storeID; -- 5. Правое внешнее соединение (NATURAL RIGHT OUTER JOIN) - все строки из правой таблицы (provides), NULL там, где нет соответствия в левой SELECT s.name, pr.article_name FROM Store s RIGHT OUTER JOIN provides pr ON s.storeID = pr.storeID; -- 6. Соединение с явным условием (JOIN ON) - предпочтительный метод SELECT s.name, pr.article_name FROM Store s JOIN provides pr ON s.storeID = pr.storeID; -- 1. SUM: Суммарная цена всех товаров SELECT SUM(price) AS total_price FROM Product; -- 2. COUNT: Количество товаров SELECT COUNT(*) AS total_products FROM Product; -- 3. AVG: Средняя цена товаров SELECT AVG(price) AS average_price FROM Product; -- 4. MIN: Минимальная цена товаров SELECT MIN(price) AS minimum_price FROM Product; -- 5. MAX: Максимальная цена товаров SELECT MAX(price) AS maximum_price FROM Product; --6. Среднее значение totalAmount из Orders SELECT AVG(totalAmount) AS average_order_amount FROM Orders; --7. Сумма totalAmount из Orders, где submittedBy = 'Alexandra Gromova' SELECT SUM(totalAmount) AS total_amount_alexandra FROM Orders WHERE submittedBy = 'Alexandra Gromova'; --8. Количество уникальных submittedBy из Orders SELECT COUNT(DISTINCT submittedBy) AS distinct_submitters FROM Orders; --9. Минимальное значение orderID из Orders SELECT MIN(orderID) AS minimum_order_id FROM Orders; --10 Максимальное значение orderID из Orders SELECT MAX(orderID) AS maximum_order_id FROM Orders; --Мы сгруппируем товары по категориям и рассчитаем общую стоимость товаров в каждой категории. SELECT category, SUM(price) AS total_category_price FROM Product GROUP BY category; --Воспользуемся таблицей Orders, чтобы продемонстрировать агрегацию с использованием GROUP BY и HAVING. Мы сгруппируем заказы по столбцу submittedBy, рассчитаем общую сумму для каждого отправителя и затем отфильтруем результаты, чтобы показать только тех отправителей, общая сумма заказов которых превышает определенный порог. SELECT submittedBy, SUM(totalAmount) AS total_amount FROM Orders GROUP BY submittedBy HAVING SUM(totalAmount) > 6000; -- Пример 1: Коррелированный подзапрос в предложении WHERE для поиска магазинов, -- которые продают все товары из категории 'Tops'. -- Магазин включается в результат только если он предоставляет ВСЕ товары из этой категории. SELECT s.name FROM Store s WHERE NOT EXISTS ( SELECT 1 FROM Product p WHERE p.category = 'Tops' AND NOT EXISTS ( SELECT 1 FROM provides pr WHERE pr.storeID = s.storeID AND pr.article_name = p.name ) ); -- Пример 2: Коррелированный подзапрос в предложении SELECT для вычисления общей суммы, -- потраченной каждым клиентом. SELECT c.name, (SELECT SUM(o.totalAmount) FROM Orders o JOIN submit su ON o.orderID = su.orderID WHERE su.phone = c.phone ) AS total_spent FROM Customer c; DELIMITER // CREATE OR REPLACE TRIGGER check_customer_before_order BEFORE INSERT ON Orders FOR EACH ROW BEGIN DECLARE customer_exists INT; SELECT COUNT(*) INTO customer_exists FROM Customer WHERE phone = NEW.submittedBy; IF customer_exists = 0 THEN -- More informative error message including the offending phone number SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = CONCAT('Customer with phone number "', NEW.submittedBy, '" does not exist.'); END IF; END; // DELIMITER ;