SQLize Online / PHPize Online  /  SQLtest Online

create table Store ( storeID INT, name varchar(100), location varchar(50), contactInfo varchar(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 INT, name VARCHAR(50), category VARCHAR(20), sizes VARCHAR(5), color VARCHAR(20), price INT, stockQuantity INT, 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 VARCHAR(50), email VARCHAR(100), phone VARCHAR(20), address VARCHAR(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 INT, 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 INT, 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; -- Создаем таблицу аудита CREATE TABLE AuditLog ( logID SERIAL PRIMARY KEY, tableName VARCHAR(50), operationType VARCHAR(10), -- INSERT, UPDATE, DELETE operationTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP, details TEXT ); -- Пример триггера для таблицы Store CREATE OR REPLACE FUNCTION log_store_changes() RETURNS TRIGGER AS $$ BEGIN IF (TG_OP = 'INSERT') THEN INSERT INTO AuditLog (tableName, operationType, details) VALUES ('Store', 'INSERT', 'New record: storeID=' || NEW.storeID || ', name=' || NEW.name); RETURN NEW; ELSIF (TG_OP = 'UPDATE') THEN INSERT INTO AuditLog (tableName, operationType, details) VALUES ('Store', 'UPDATE', 'Updated record: storeID=' || OLD.storeID || ', name=' || OLD.name || ' -> new values: name=' || NEW.name); RETURN NEW; ELSIF (TG_OP = 'DELETE') THEN INSERT INTO AuditLog (tableName, operationType, details) VALUES ('Store', 'DELETE', 'Deleted record: storeID=' || OLD.storeID || ', name=' || OLD.name); RETURN OLD; END IF; RETURN NULL; END; $$ LANGUAGE plpgsql; -- Привязываем триггер к таблице Store CREATE TRIGGER trigger_store_audit AFTER INSERT OR UPDATE OR DELETE ON Store FOR EACH ROW EXECUTE FUNCTION log_store_changes();