SQLize | PHPize | SQLtest

create table Store ( storeID INT, name varchar(100), location varchar2(50), contactInfo varchar2(50), primary key (storeID, name) ); 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, primary key (article, name) ); 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); INSERT INTO Product VALUES (82635, 'Skinny Jeans', 'Bottoms', 'M', 'Black', 1500, 120); INSERT INTO Product VALUES (57382, 'Skinny Jeans', 'Bottoms', 'S', 'Blue', 1500, 120); INSERT INTO Product VALUES (57382, 'Summer Dress', 'Dresses', 'S', 'White', 3500, 45); INSERT INTO Product VALUES (92147, 'Hoodie', 'Outerwear', 'S', 'Red', 3500, 45); INSERT INTO Product VALUES (82635, 'Leather Jacket', 'Outerwear', 'M', 'Black', 1500, 120); INSERT INTO Product VALUES (61473, 'Classic T-Shirt', 'Tops', 'L', 'Blue', 1500, 120); CREATE TABLE Customer ( name VARCHAR2(50), email VARCHAR2(100), phone VARCHAR2(20), address VARCHAR2(100), primary key (name, phone) ); 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'); INSERT INTO Customer VALUES ('Global Textiles', 'glob@email.com', '8 (903) 567-8900', 'Saint Petersburg, Ligovsky Ave, 33'); INSERT INTO Customer VALUES ('Igor Fedorov', 'igor.fedorov@email.com', '+7 381 9999999', 'Moscow, Arbat St, 9'); INSERT INTO Customer VALUES ('Tatiana Morozova', 'tatiana.morozova@email.com', '+7 863 1010101', '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, primary key (orderID) ); 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); INSERT INTO Orders VALUES (13256, '09/09/2023', 7100, 'Moscow, Tverskaya St', 'Natalia Romanova', 93847); INSERT INTO Orders VALUES (23678, '09/09/2024', 2300, 'Moscow, Tverskaya St', 'Natalia Romanova', 93847); INSERT INTO Orders VALUES (54312, '09/04/2024', 4600, 'Yekaterinburg, Malysheva St', 'Oleg Tikhonov', 67431); INSERT INTO Orders VALUES (93847, '09/01/2024', 8500, 'Moscow, Tverskaya St', 'Alexandra Gromova', 78342); INSERT INTO Orders VALUES (74231, '09/02/2024', 5200, 'Saint Petersburg, Nevsky Ave', 'Denis Lapin', 94321); INSERT INTO Orders VALUES (45679, '09/09/2024', 2300, 'Moscow, Tverskaya St', 'Natalia Romanova', 93847); INSERT INTO Orders VALUES (62346, '09/07/2024', 6500, 'Chelyabinsk, Kirova St', 'Anastasia Kravtsova', 87534); INSERT INTO Orders VALUES (86454, '09/08/2024', 5200, 'Samara, Kuybysheva St', 'Vladimir Egorov', 54312); INSERT INTO Orders VALUES (23155, '09/08/2024', 5200, 'Samara, Kuybysheva St', 'Vladimir Egorov', 54312); INSERT INTO Orders VALUES (78913, '09/02/2024', 5200, 'Saint Petersburg, Nevsky Ave', 'Denis Lapin', 94321); CREATE TABLE Payment ( paymentID INT, method VARCHAR(50), paymentDate VARCHAR(10), -- хранение даты в виде строки amountPaid DECIMAL(10, 2), primary key (paymentID, method) ); 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', 3400); INSERT INTO Payment VALUES (74231, 'Bank Transfer', '09/10/2024', 9000); CREATE TABLE Supplier ( name VARCHAR(100), phone VARCHAR(20), email VARCHAR(100), primary key (name, phone) ); 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), primary key (name, position) ); 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'); INSERT INTO Employee VALUES ('Ivan Petrov', 'Cashier', 60000, '03/15/2022', 'Global Textiles'); INSERT INTO Employee VALUES ('Maria Sidorova', 'Stock Manager', 48000, '11/30/2021', 'Elegant Apparel'); INSERT INTO Employee VALUES ('Dmitry Petrov', 'Sales Associate', 55000, '09/15/2020', 'Modern Style Ltd'); --связь между Store и Order CREATE TABLE Processes ( storeID INT, name VARCHAR(100), orderID INT, PRIMARY KEY (storeID, name, orderID), FOREIGN KEY (storeID, name) REFERENCES Store (storeID, name), FOREIGN KEY (orderID) REFERENCES Orders (orderID) ); 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, storeName VARCHAR(100), article INT, productName VARCHAR(50), PRIMARY KEY (storeID, storeName, article, productName), FOREIGN KEY (storeID, storeName) REFERENCES Store (storeID, name), FOREIGN KEY (article, productName) REFERENCES Product (article, name) ); INSERT INTO Provides VALUES (356, 'FashionHub', 82635, 'Classic T-Shirt'); INSERT INTO Provides VALUES (356, 'FashionHub', 82635, 'Skinny Jeans'); INSERT INTO Provides VALUES (234, 'StyleSpot', 57382, 'Skinny Jeans'); INSERT INTO Provides VALUES (234, 'StyleSpot', 57382, 'Summer Dress'); INSERT INTO Provides VALUES (321, 'TrendSetter', 92147, 'Summer Dress'); INSERT INTO Provides VALUES (321, 'TrendSetter', 92147, 'Hoodie'); INSERT INTO Provides VALUES (245, 'ChicBoutique', 34829, 'Hoodie'); INSERT INTO Provides VALUES (267, 'EcoFashion', 65943, 'Leather Jacket'); INSERT INTO Provides VALUES (123, 'SportStyle', 71582, 'Cargo Pants'); INSERT INTO Provides VALUES (256, 'ModaCasa', 23491, 'Maxi Skirt'); INSERT INTO Provides VALUES (743, 'GlamourSpot', 48236, 'Blazer'); INSERT INTO Provides VALUES (512, 'UrbanChic', 50984, 'Denim Jacket'); INSERT INTO Provides VALUES (678, 'VintageVogue', 61473, 'Polo Shirt'); INSERT INTO Provides VALUES (356, 'FashionHub', 82635, 'Leather Jacket'); INSERT INTO Provides VALUES (678, 'VintageVogue', 61473, 'Classic T-Shirt'); INSERT INTO Provides VALUES (256, 'ModaCasa', 50984, 'Denim Jacket'); --связь Order и Product CREATE TABLE Contain ( orderID INT, article NUMBER, name VARCHAR(50), PRIMARY KEY (orderID, article, name), FOREIGN KEY (orderID) REFERENCES Orders (OrderID), FOREIGN KEY (article, name) REFERENCES Product (article, name) ); 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'); --связь Store и Employee CREATE TABLE Work ( storeID INT, name VARCHAR(100), employee_name VARCHAR(100), position VARCHAR(100), PRIMARY KEY (storeID, name, employee_name, position), FOREIGN KEY (storeID, name) REFERENCES Store (storeID, name), FOREIGN KEY (employee_name, position) REFERENCES Employee (name, position) ); 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'); --связь Supplier и Product CREATE TABLE Supplied ( name VARCHAR(100), phone VARCHAR(20), article NUMBER, productName VARCHAR(50), PRIMARY KEY (name, phone, article, productName), FOREIGN KEY (name, phone) REFERENCES Supplier (name, phone), FOREIGN KEY (article, productName) REFERENCES Product (article, name) ); 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'); --Payment Customer CREATE TABLE Paid ( name VARCHAR(100), phone VARCHAR(20), paymentID INT, method VARCHAR(50), PRIMARY KEY (name, phone, paymentID, method), FOREIGN KEY (name, phone) REFERENCES Customer (name, phone), FOREIGN KEY (paymentID, method) REFERENCES Payment (paymentID, method) ); -- Заполнение таблицы paid INSERT INTO paid VALUES ('Alexei Petrov', '+7 495 1111111', 78342, 'Credit Card'); INSERT INTO paid VALUES ('Dmitry Ivanov', '+7 812 2222222', 94321, 'PayPal'); INSERT INTO paid VALUES ('Natalia Sokolova', '+7 383 3333333', 12365, 'Credit Card'); INSERT INTO paid VALUES ('Maria Novikova', '+7 343 4444444', 67431, 'Cash'); INSERT INTO paid VALUES ('Sergey Kuznetsov', '+7 843 5555555', 19843, 'Bank Transfer'); INSERT INTO paid VALUES ('Elena Smirnova', '+7 831 6666666', 32154, 'PayPal'); INSERT INTO paid VALUES ('Viktor Orlov', '+7 351 7777777', 87534, 'Credit Card'); INSERT INTO paid VALUES ('Anna Volkova', '+7 846 8888888', 54312, 'Cash'); INSERT INTO paid VALUES ('Igor Fedorov', '+7 916 9999999', 93847, 'Credit Card'); INSERT INTO paid VALUES ('Tatiana Morozova', '+7 812 1234567', 74231, 'Bank Transfer'); --customer order CREATE TABLE Submit ( name VARCHAR(100), phone VARCHAR(20), orderID INT, PRIMARY KEY (name, phone, orderID), FOREIGN KEY (name, phone) REFERENCES Customer (name, phone), FOREIGN KEY (orderID) REFERENCES Orders (orderID) ); 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 o.orderID, o.orderDate, o.totalAmount, (SELECT c.name FROM Customer c WHERE c.phone = '+7 495 1111111') AS customer_name, (SELECT s.name FROM Store s WHERE s.storeID = 356) AS store_name FROM Orders o WHERE o.totalAmount > 5000; --Запрос к одной таблице, умноженной на себя --Найти пары магазинов, которые находятся в разных городах. SELECT S1.storeID AS Store1_ID, S1.name AS Store1_Name, S2.storeID AS Store2_ID, S2.name AS Store2_Name FROM Store S1, Store S2 WHERE S1.location <> S2.location AND S1.storeID < S2.storeID; --2 таблицы, использующие EXISTS SELECT storeID, name FROM Store S WHERE EXISTS ( SELECT 1 FROM provides P WHERE P.storeID = S.storeID AND P.article = 61473 ); --2 таблицы, использующие IN SELECT storeID, name FROM Store WHERE storeID IN ( SELECT storeID FROM provides WHERE article = 61473 ); SELECT storeID, name FROM Store S WHERE 10000 > ALL ( SELECT price FROM Product P WHERE P.category = 'Outwear' ); SELECT storeID, name FROM Store S WHERE NOT 10000 <= ANY ( 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.productName = 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.productName = 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.productName = 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.productName = 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.productName = pr.name AND pr.price >= 2000) AND EXISTS (SELECT 1 FROM provides p, Product pr WHERE p.storeID = s.storeID AND p.productName = pr.name AND pr.price < ANY (SELECT 3000 FROM dual)); --UNION (Объединение) --Найдем все уникальные названия товаров, которые продаются в магазинах, и все уникальные названия товаров в таблице Product, объединив результаты. SELECT name FROM Product UNION SELECT productName FROM provides; --INTERSECT (Пересечение) --Найдем названия товаров, которые присутствуют как в таблице Product, так и в таблице provides. SELECT name FROM Product INTERSECT SELECT productName 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 StoreProducts AS SELECT s.storeID, s.name AS store_name, (SELECT MAX(p.productName) FROM provides p WHERE p.storeID = s.storeID) AS productName, (SELECT MAX(pr.category) FROM Product pr WHERE pr.name = (SELECT MAX(p.productName) FROM provides p WHERE p.storeID = s.storeID)) AS category FROM Store s; --запрос для получения списка магазинов, продающих товары категории 'Tops': SELECT DISTINCT store_name FROM StoreProducts WHERE category = 'Tops'; -- 1. Внутреннее соединение (INNER JOIN) - неявный эквивалент NATURAL JOIN (менее предпочтительный) SELECT s.name, pr.productName FROM Store s, provides pr WHERE s.storeID = pr.storeID; -- 2. Декартово произведение (CROSS JOIN) - все возможные комбинации SELECT s.name, pr.productName FROM Store s CROSS JOIN provides pr; -- 3. Полное внешнее соединение (NATURAL FULL OUTER JOIN) - все строки из обеих таблиц, NULL там, где нет соответствия SELECT s.name, pr.productName FROM Store s FULL OUTER JOIN provides pr USING (storeID); -- 4. Левое внешнее соединение (NATURAL LEFT OUTER JOIN) - все строки из левой таблицы (Store), NULL там, где нет соответствия в правой SELECT s.name, pr.productName FROM Store s LEFT OUTER JOIN provides pr ON s.storeID = pr.storeID; -- 5. Правое внешнее соединение (NATURAL RIGHT OUTER JOIN) - все строки из правой таблицы (provides), NULL там, где нет соответствия в левой SELECT s.name, pr.productName FROM Store s RIGHT OUTER JOIN provides pr ON s.storeID = pr.storeID; -- 6. Соединение с явным условием (JOIN ON) - предпочтительный метод SELECT s.name, pr.productName 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.productName = 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;