SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
CREATE SCHEMA shop; --ddl-скрипты (пункт 3) CREATE TABLE shop.client ( client_id SERIAL PRIMARY KEY, first_nm VARCHAR(255) NOT NULL, middle_nm VARCHAR(255) NOT NULL, last_nm VARCHAR(255) NOT NULL, card_no VARCHAR(16) NOT NULL ); CREATE TABLE shop.product_type ( type_id SERIAL PRIMARY KEY, type_nm VARCHAR(255) NOT NULL ); CREATE TABLE shop.product ( product_id SERIAL PRIMARY KEY, type_id INTEGER NOT NULL, product_nm VARCHAR(256) NOT NULL, price_amt NUMERIC(10, 2) CHECK (price_amt > 0), mass_amt NUMERIC(5, 3) NOT NULL, maker_nm VARCHAR(256) NOT NULL, FOREIGN KEY (type_id) REFERENCES shop.product_type (type_id) ); CREATE TABLE shop.characteristic ( characteristic_id SERIAL PRIMARY KEY, type_id INTEGER NOT NULL, characteristic_nm VARCHAR(255) NOT NULL, FOREIGN KEY (type_id) REFERENCES shop.product_type (type_id) ); CREATE TABLE shop.product_description ( product_id INTEGER NOT NULL, characteristic_id INTEGER NOT NULL, characteristic_desc VARCHAR(255) NOT NULL, FOREIGN KEY (product_id) REFERENCES shop.product (product_id), FOREIGN KEY (characteristic_id) REFERENCES shop.characteristic (characteristic_id) ); CREATE TABLE shop.order ( order_id SERIAL PRIMARY KEY, client_id INTEGER NOT NULL, address VARCHAR(255) NOT NULL, FOREIGN KEY (client_id) REFERENCES shop.client (client_id) ); CREATE TABLE shop.pickup_point ( warehouse_id SERIAL PRIMARY KEY, address VARCHAR(255) NOT NULL ); CREATE TABLE shop.order_description ( order_id INTEGER NOT NULL, product_id INTEGER NOT NULL, product_cnt INTEGER NOT NULL, warehouse_id INTEGER NOT NULL, FOREIGN KEY (order_id) REFERENCES shop.order (order_id), FOREIGN KEY (product_id) REFERENCES shop.product (product_id), FOREIGN KEY (warehouse_id) REFERENCES shop.pickup_point (warehouse_id) ); CREATE TABLE shop.employee ( employee_id SERIAL PRIMARY KEY, warehouse_id INTEGER NOT NULL, first_nm VARCHAR(255) NOT NULL, middle_nm VARCHAR(255) NOT NULL, last_nm VARCHAR(255) NOT NULL, phone_no VARCHAR(255) NOT NULL UNIQUE, VALID_FROM_DTTM DATE default now():: DATE, VALID_TO_DTTM DATE default '9999-01-01', FOREIGN KEY (warehouse_id) REFERENCES shop.pickup_point (warehouse_id) ); CREATE TABLE shop.storage ( product_id INTEGER NOT NULL, warehouse_id INTEGER NOT NULL, product_cnt INTEGER CHECK (product_cnt >= 0), FOREIGN KEY (product_id) REFERENCES shop.product (product_id), FOREIGN KEY (warehouse_id) REFERENCES shop.pickup_point (warehouse_id) ); --Заполнение таблицы с помошью INSERT, DELETE, UPDATE (пункты 4,5) INSERT INTO shop.client(first_nm, middle_nm, last_nm, card_no) VALUES ('Осип', 'Герасимов', 'Валерьевич', '4594235202205726') , ('Соломон', 'Лобанов', 'Германович', '4594234826689324') , ('Герман', 'Русаков', 'Павлович', '4371009827743729') , ('Артур', 'Ларионов', 'Якунович', '4837975790499747') , ('Афанасий', 'Веселов', 'Ильяович', '4032856030357445') , ('Юрий', 'Александров', 'Иринеевич', '4594235377806480') , ('Максим', 'Доронин', 'Вениаминович', '4594238062796206') , ('Велорий', 'Туров', 'Кимович', '4837979650635234') , ('Алан', 'Захаров', 'Пантелеймонович', '4032855826467095') , ('Степан', 'Юдин', 'Лаврентьевич', '4371006399298413'); INSERT INTO shop.pickup_point(address) VALUES ('г. Москва ул.Пушкина д.1'), ('г. Москва ул.Пушкина д.2'), ('г. Москва ул.Пушкина д.3'), ('г. Москва ул.Пушкина д.4'), ('г. Москва ул.Пушкина д.5'), ('г. Москва ул.Пушкина д.6'), ('г. Москва ул.Пушкина д.7'), ('г. Москва ул.Пушкина д.8'), ('г. Москва ул.Пушкина д.9'), ('г. Москва ул.Пушкина д.10'); INSERT INTO shop.employee(warehouse_id, first_nm, middle_nm, last_nm, phone_no) VALUES ((SELECT warehouse_id FROM shop.pickup_point WHERE address = 'г. Москва ул.Пушкина д.1'), 'Осип', 'Герасимов', 'Валерьевич', '88005553535') , ((SELECT warehouse_id FROM shop.pickup_point WHERE address = 'г. Москва ул.Пушкина д.2'), 'Соломон', 'Лобанов', 'Германович', '88105553535') , ((SELECT warehouse_id FROM shop.pickup_point WHERE address = 'г. Москва ул.Пушкина д.3'), 'Герман', 'Русаков', 'Павлович', '88205553535') , ((SELECT warehouse_id FROM shop.pickup_point WHERE address = 'г. Москва ул.Пушкина д.4'), 'Артур', 'Ларионов', 'Якунович', '88305553535') , ((SELECT warehouse_id FROM shop.pickup_point WHERE address = 'г. Москва ул.Пушкина д.5'), 'Афанасий', 'Веселов', 'Ильяович', '88405553535') , ((SELECT warehouse_id FROM shop.pickup_point WHERE address = 'г. Москва ул.Пушкина д.6'), 'Юрий', 'Александров', 'Иринеевич', '88505553535') , ((SELECT warehouse_id FROM shop.pickup_point WHERE address = 'г. Москва ул.Пушкина д.7'), 'Максим', 'Доронин', 'Вениаминович', '88605553535') , ((SELECT warehouse_id FROM shop.pickup_point WHERE address = 'г. Москва ул.Пушкина д.8'), 'Велорий', 'Туров', 'Кимович', '88705553535') , ((SELECT warehouse_id FROM shop.pickup_point WHERE address = 'г. Москва ул.Пушкина д.9'), 'Алан', 'Захаров', 'Пантелеймонович', '88805553535') , ((SELECT warehouse_id FROM shop.pickup_point WHERE address = 'г. Москва ул.Пушкина д.10'), 'Степан', 'Юдин', 'Лаврентьевич', '88905553535'); INSERT INTO shop.employee(warehouse_id, first_nm, middle_nm, last_nm, phone_no, VALID_FROM_DTTM) VALUES ((SELECT warehouse_id FROM shop.pickup_point WHERE address = 'г. Москва ул.Пушкина д.1'), 'Иван', 'Иванов', 'Иванович', '88115553535', '2022-05-03'); INSERT INTO shop.product_type(type_nm) VALUES ('laptop'), ('pc'), ('phone'), ('printer'), ('bicycle'), ('tv'), ('fridge'), ('washing machine'), ('book'), ('headphones'); INSERT INTO shop.characteristic(type_id, characteristic_nm) VALUES ((SELECT type_id FROM shop.product_type WHERE type_nm = 'laptop'), 'тип'), ((SELECT type_id FROM shop.product_type WHERE type_nm = 'pc'), 'подсветка'), ((SELECT type_id FROM shop.product_type WHERE type_nm = 'phone'), 'цвет'), ((SELECT type_id FROM shop.product_type WHERE type_nm = 'printer'), 'тип печати'), ((SELECT type_id FROM shop.product_type WHERE type_nm = 'bicycle'), 'материал рамы'), ((SELECT type_id FROM shop.product_type WHERE type_nm = 'tv'), 'производитель'), ((SELECT type_id FROM shop.product_type WHERE type_nm = 'fridge'), 'класс энергопотребления'), ((SELECT type_id FROM shop.product_type WHERE type_nm = 'washing machine'), 'тип загрузки'), ((SELECT type_id FROM shop.product_type WHERE type_nm = 'book'), 'жанр'), ((SELECT type_id FROM shop.product_type WHERE type_nm = 'headphones'), 'тип излучателя'); INSERT INTO shop.product(type_id, product_nm, price_amt, mass_amt, maker_nm) VALUES ((SELECT type_id FROM shop.product_type WHERE type_nm = 'laptop'), 'lenovo yoga', 1.10, 0.1, 'lenovo'), ((SELECT type_id FROM shop.product_type WHERE type_nm = 'laptop'), 'macbook air 13', 2.90, 11.1, 'apple'), ((SELECT type_id FROM shop.product_type WHERE type_nm = 'pc'), 'dell alienware', 2.10, 10.1, 'dell'), ((SELECT type_id FROM shop.product_type WHERE type_nm = 'phone'), 'iphone 13 pro max ultra giga super', 3.10, 20.1, 'apple'), ((SELECT type_id FROM shop.product_type WHERE type_nm = 'printer'), 'hp deskjet', 4.10, 30.1, 'hp'), ((SELECT type_id FROM shop.product_type WHERE type_nm = 'bicycle'), 'stels a1', 5.10, 40.1, 'stels'), ((SELECT type_id FROM shop.product_type WHERE type_nm = 'tv'), 'lg 43lm', 6.10, 50.1, 'lg'), ((SELECT type_id FROM shop.product_type WHERE type_nm = 'fridge'), 'bosch serie 4', 7.10, 60.1, 'bosch'), ((SELECT type_id FROM shop.product_type WHERE type_nm = 'washing machine'), 'samsung wf861', 8.10, 70.1, 'samsung'), ((SELECT type_id FROM shop.product_type WHERE type_nm = 'book'), 'alice adventures in wonderland', 9.10, 80.1, 'просвещение'), ((SELECT type_id FROM shop.product_type WHERE type_nm = 'headphones'), 'sennheiser hd 280 pro', 10.10, 90.1, 'sennheiser'); INSERT INTO shop.product_description(product_id, characteristic_id, characteristic_desc) VALUES (1, (SELECT characteristic_id FROM shop.characteristic WHERE type_id = 1 AND characteristic_nm = 'тип'), 'ультрабук'), (2, (SELECT characteristic_id FROM shop.characteristic WHERE type_id = 2 AND characteristic_nm = 'подсветка'), 'rgb'), (3, (SELECT characteristic_id FROM shop.characteristic WHERE type_id = 3 AND characteristic_nm = 'цвет'), 'белый'), (4, (SELECT characteristic_id FROM shop.characteristic WHERE type_id = 4 AND characteristic_nm = 'тип печати'), 'струйный'), (5, (SELECT characteristic_id FROM shop.characteristic WHERE type_id = 5 AND characteristic_nm = 'материал рамы'), 'сталь'), (6, (SELECT characteristic_id FROM shop.characteristic WHERE type_id = 6 AND characteristic_nm = 'производитель'), 'LG'), (7, (SELECT characteristic_id FROM shop.characteristic WHERE type_id = 7 AND characteristic_nm = 'класс энергопотребления'), 'A'), (8, (SELECT characteristic_id FROM shop.characteristic WHERE type_id = 8 AND characteristic_nm = 'тип загрузки'), 'вертикальная'), (9, (SELECT characteristic_id FROM shop.characteristic WHERE type_id = 9 AND characteristic_nm = 'жанр'), 'детская'), (10, (SELECT characteristic_id FROM shop.characteristic WHERE type_id = 10 AND characteristic_nm = 'тип излучателя'), 'динамический'); INSERT INTO shop.storage(product_id, warehouse_id, product_cnt) VALUES (1, (SELECT warehouse_id FROM shop.pickup_point WHERE address = 'г. Москва ул.Пушкина д.1'), 100500), (2, (SELECT warehouse_id FROM shop.pickup_point WHERE address = 'г. Москва ул.Пушкина д.2'), 11), (3, (SELECT warehouse_id FROM shop.pickup_point WHERE address = 'г. Москва ул.Пушкина д.3'), 22), (4, (SELECT warehouse_id FROM shop.pickup_point WHERE address = 'г. Москва ул.Пушкина д.4'), 1), (5, (SELECT warehouse_id FROM shop.pickup_point WHERE address = 'г. Москва ул.Пушкина д.5'), 228), (6, (SELECT warehouse_id FROM shop.pickup_point WHERE address = 'г. Москва ул.Пушкина д.6'), 1), (7, (SELECT warehouse_id FROM shop.pickup_point WHERE address = 'г. Москва ул.Пушкина д.7'), 10), (8, (SELECT warehouse_id FROM shop.pickup_point WHERE address = 'г. Москва ул.Пушкина д.8'), 10), (9, (SELECT warehouse_id FROM shop.pickup_point WHERE address = 'г. Москва ул.Пушкина д.9'), 100), (10, (SELECT warehouse_id FROM shop.pickup_point WHERE address = 'г. Москва ул.Пушкина д.10'), 1000); INSERT INTO shop.order(client_id, address) VALUES (1, 'г.Москва ул.Тверская д.1'), (2, 'г.Москва ул.Тверская д.2'), (3, 'г.Москва ул.Тверская д.3'), (4, 'г.Москва ул.Тверская д.4'), (5, 'г.Москва ул.Тверская д.5'), (6, 'г.Москва ул.Тверская д.6'), (7, 'г.Москва ул.Тверская д.7'), (8, 'г.Москва ул.Тверская д.8'), (9, 'г.Москва ул.Тверская д.9'), (10, 'г.Москва ул.Тверская д.10'); INSERT INTO shop.order_description(order_id, product_id, warehouse_id, product_cnt) VALUES (1, 1, 1, 1), (2, 2, 2, 2), (3, 3, 3, 3), (4, 4, 4, 4), (5, 5, 5, 5), (6, 6, 6, 6), (7, 7, 7, 7), (8, 8, 8, 8), (9, 9, 9, 9), (10, 10, 10, 10); UPDATE shop.storage SET product_cnt = product_cnt - 1 WHERE warehouse_id = 5 AND product_id = 5; DELETE FROM shop.storage WHERE product_id = 6 AND product_id = 6; SELECT * FROM shop.client; SELECT * FROM shop.order; SELECT * FROM shop.order_description; SELECT * FROM shop.product; SELECT * FROM shop.product_type; SELECT * FROM shop.characteristic; SELECT * FROM shop.product_description; SELECT * FROM shop.storage; SELECT * FROM shop.pickup_point; SELECT * FROM shop.employee; CREATE INDEX ON shop.client(client_id); CREATE INDEX ON shop.product(product_id); CREATE INDEX ON shop.characteristic(characteristic_id); CREATE INDEX ON shop.product_description(product_id, characteristic_id); CREATE INDEX ON shop.order(order_id); CREATE INDEX ON shop.order_description(order_id,product_id); CREATE INDEX ON shop.pickup_point(warehouse_id); CREATE INDEX ON shop.employee(employee_id); CREATE INDEX ON shop.storage(product_id,warehouse_id); CREATE VIEW workers as SELECT first_nm, middle_nm, last_nm, CONCAT(SUBSTRING(phone_no, 1, 3), SUBSTRING('**************', 1, LENGTH(phone_no) - 6), SUBSTRING(phone_no, LENGTH(phone_no) - 2, LENGTH(phone_no))) as phone_no FROM shop.employee; CREATE VIEW users as SELECT first_nm, middle_nm, last_nm, CONCAT(SUBSTRING(card_no, 1, 4), '********', SUBSTRING(card_no, LENGTH(card_no) - 3, LENGTH(card_no))) as card_no FROM shop.client; CREATE VIEW order_address as SELECT first_nm, middle_nm, last_nm, CONCAT(SUBSTRING(address, 1, 3), '***', SUBSTRING(address, LENGTH(address) - 2, LENGTH(address))) as address FROM shop.client JOIN shop.order ON client.client_id = shop.order.client_id; CREATE VIEW attitude as SELECT DISTINCT type_nm, product_nm, CAST(product_sum as NUMERIC(10, 3)) / CAST(type_sum as NUMERIC(10, 3)) as attitude FROM (SELECT product_type.type_id, type_nm, product_nm, SUM(product_cnt) OVER (partition BY product.product_id) as product_sum, SUM(product_cnt) OVER (partition BY product_type.type_id) as type_sum FROM shop.order_description JOIN shop.product ON order_description.product_id = product.product_id JOIN shop.product_type ON product.type_id = product_type.type_id) as sales; SELECT * FROM attitude; CREATE VIEW pickup_points as SELECT count(*), address FROM shop.pickup_point JOIN shop.employee ON pickup_point.warehouse_id = employee.warehouse_id GROUP BY pickup_point.warehouse_id; SELECT * FROM pickup_points; CREATE VIEW average_cnt as SELECT product_nm, avg(product_cnt) FROM shop.order_description JOIN shop.product ON order_description.product_id = product.product_id GROUP BY product.product_id; SELECT * FROM average_cnt; CREATE OR REPLACE FUNCTION shop.func_salary(prod_id INT, percent FLOAT) RETURNS void AS $$ UPDATE shop.product SET price_amt = price_amt * (1 - percent) WHERE product.product_id = prod_id; $$ LANGUAGE SQL; SELECT shop.func_salary(4, 0.2); CREATE OR REPLACE FUNCTION shop.trigger_insert() RETURNS TRIGGER AS $$ BEGIN IF (TG_OP = 'INSERT') THEN UPDATE shop.storage SET product_cnt = product_cnt - NEW.product_cnt WHERE NEW.warehouse_id = warehouse_id; RETURN NEW; END IF; END; $$ LANGUAGE plpgsql; CREATE TRIGGER trigger_change_product_cnt AFTER INSERT ON shop.order_description FOR EACH ROW EXECUTE PROCEDURE shop.trigger_insert(); SELECT * FROM shop.storage; INSERT INTO shop.order_description(order_id, product_id, warehouse_id, product_cnt) VALUES (2, 2, 2, 5); SELECT * FROM shop.storage; CREATE OR REPLACE FUNCTION shop.trigger_set_date() RETURNS TRIGGER AS $$ BEGIN IF (TG_OP = 'DELETE') THEN INSERT INTO shop.employee (warehouse_id, first_nm, middle_nm, last_nm, phone_no, VALID_FROM_DTTM,VALID_TO_DTTM) VALUES (OLD.warehouse_id, OLD.first_nm, OLD.middle_nm, OLD.last_nm, OLD.phone_no, OLD.VALID_FROM_DTTM, now():: DATE); RETURN NEW; END IF; END; $$ LANGUAGE plpgsql; CREATE TRIGGER trigger_change_date AFTER DELETE ON shop.employee FOR EACH ROW EXECUTE PROCEDURE shop.trigger_set_date(); SELECT * FROM shop.employee; DELETE FROM shop.employee WHERE employee_id = 1; SELECT * FROM shop.employee;
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear