SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE if not exists "HOOKAH" ( hookah_id int NOT NULL, hookah_nm varchar(20) NOT NULL, hookah_price money NOT NULL, hookah_desc text NOT NULL, CONSTRAINT hookah_pk PRIMARY KEY (hookah_id), CONSTRAINT chk_hookah CHECK (hookah_id > 0 and hookah_price::numeric::integer > 0) ); CREATE TABLE if not exists "Costumer" ( costumer_id int NOT NULL, costumer_in timestamptz, costumer_out timestamptz, CONSTRAINT costumer_pk PRIMARY KEY (costumer_id) ); CREATE TABLE if not exists "Office" ( office_id int NOT NULL, location varchar(50) NOT NULL, phone_nb varchar(20) NOT NULL, CONSTRAINT office_pk PRIMARY KEY (office_id), CONSTRAINT chk_office CHECK (office_id > 0 and phone_nb not like '%[^0-9]%') ); CREATE TABLE if not exists "Employer" ( employer_id int NOT NULL, office_id int NOT NULL, employer_nm varchar(50) NOT NULL, salary int NOT NULL, phone_nb varchar(20) NOT NULL, experience int NOT NULL, CONSTRAINT employer_pk PRIMARY KEY (employer_id), CONSTRAINT office_employer FOREIGN KEY (office_id) REFERENCES "Office" (office_id), CONSTRAINT chk_employer CHECK (employer_id > 0 and phone_nb not like '%[^0-9]%' and salary > 10000 and experience >=0) ); CREATE TABLE if not exists "Order" ( order_id int NOT NULL, office_id int NOT NULL, costumer_id int NOT NULL, employer_id int NOT NULL, order_date date NOT NULL, order_time time NOT NULL, CONSTRAINT order_pk PRIMARY KEY (order_id), CONSTRAINT order_costumer FOREIGN KEY (costumer_id) REFERENCES "Costumer" (costumer_id), CONSTRAINT order_office FOREIGN KEY (office_id) REFERENCES "Office" (office_id), CONSTRAINT order_employer FOREIGN KEY (employer_id) REFERENCES "Employer" (employer_id), CONSTRAINT chk_order CHECK (order_id > 0) ); INSERT INTO "Office" (office_id, location, phone_nb) VALUES (11, 'Ivanovskaya', '9254567889'); INSERT INTO "Office" (office_id, location, phone_nb) VALUES (12, 'Lozjevaya', '9457369864'); INSERT INTO "Office" (office_id, location, phone_nb) VALUES (13, 'Dirijabelnata', '9754329880'); INSERT INTO "Office" (office_id, location, phone_nb) VALUES (14, 'Lubimaya', '9257612847'); INSERT INTO "HOOKAH" (hookah_id, hookah_nm, hookah_price, hookah_desc) VALUES (1, 'Light', '1000', 'Usual'); INSERT INTO "HOOKAH" (hookah_id, hookah_nm, hookah_price, hookah_desc) VALUES (2, 'Mean', '1200', 'Usual'); INSERT INTO "HOOKAH" (hookah_id, hookah_nm, hookah_price, hookah_desc) VALUES (3, 'Hard', '1200', 'Difficult'); INSERT INTO "HOOKAH" (hookah_id, hookah_nm, hookah_price, hookah_desc) VALUES (4, 'Yagodniyi', '1000', 'Usual'); INSERT INTO "HOOKAH" (hookah_id, hookah_nm, hookah_price, hookah_desc) VALUES (5, 'Platina', '2000', 'Difficult'); INSERT INTO "HOOKAH" (hookah_id, hookah_nm, hookah_price, hookah_desc) VALUES (6, 'Mary', '1500', 'Difficult'); INSERT INTO "Costumer" (costumer_id) VALUES (1); INSERT INTO "Costumer" (costumer_id) VALUES (2); INSERT INTO "Costumer" (costumer_id) VALUES (3); INSERT INTO "Costumer" (costumer_id) VALUES (4); INSERT INTO "Costumer" (costumer_id) VALUES (5); INSERT INTO "Employer" (employer_id, office_id, employer_nm, salary, phone_nb, experience) VALUES (1, 11, 'Andrey Kaznacheev', 18000, '9251285760', 3); INSERT INTO "Employer" (employer_id, office_id, employer_nm, salary, phone_nb, experience) VALUES (2, 12, 'Evgeny Lubin', 35000, '9653452678', 2); INSERT INTO "Employer" (employer_id, office_id, employer_nm, salary, phone_nb, experience) VALUES (3, 13, 'Vladislav Novikov', 17000, '9456789009', 0); INSERT INTO "Employer" (employer_id, office_id, employer_nm, salary, phone_nb, experience) VALUES (4, 12, 'Anya Ivanova', 23000, '9051345267', 6); INSERT INTO "Employer" (employer_id, office_id, employer_nm, salary, phone_nb, experience) VALUES (5, 14, 'Artur Eniseev', 100000, '9054789405', 8); INSERT INTO "Employer" (employer_id, office_id, employer_nm, salary, phone_nb, experience) VALUES (6, 11, 'Yulia Zverskaya', 150000, '9654321962', 5); INSERT INTO "Order" (order_id, office_id, costumer_id, employer_id, order_date, order_time) VALUES (1, 11, 2, 2, '2021-09-11', '21:21:21'); INSERT INTO "Order" (order_id, office_id, costumer_id, employer_id, order_date, order_time) VALUES (2, 13, 3, 4, '2021-09-11', '22:22:22'); INSERT INTO "Order" (order_id, office_id, costumer_id, employer_id, order_date, order_time) VALUES (3, 14, 4, 6, '2021-09-11', '23:23:23'); CREATE TABLE if not exists "Deleted_information" ( LIKE public."Order" INCLUDING ALL ); ALTER TABLE "Deleted_information" ADD column deleted timestamp default now(); CREATE OR REPLACE function save_deleted_information() returns TRIGGER AS $$ BEGIN execute 'INSERT INTO "Deleted_information" (order_id, office_id, costumer_id, employer_id, order_date, order_time) ' || 'VALUES ($1, $2, $3, $4, $5, $6)' USING old.order_id, old.office_id, old.costumer_id, old.employer_id, old.order_date, old.order_time; return new; END; $$ language plpgsql; drop TRIGGER if exists deleted_information_trigger ON public."Order"; CREATE TRIGGER deleted_information_trigger after DELETE ON public."Order" FOR each row execute procedure save_deleted_information(); INSERT INTO "Order" (order_id, office_id, costumer_id, employer_id, order_date, order_time) VALUES (5, 14, 4, 6, '2021-09-11', '23:23:23') ON conflict do nothing; DELETE FROM public."Order" WHERE order_id = 5; SELECT * FROM "Deleted_information"; SELECT * FROM public."Order" where order_id = 5;