SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
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 "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 PROCEDURE add_message(order_id integer, text text) LANGUAGE SQL AS $$ INSERT INTO "Order"(order_id, office_id, costumer_id, employer_id, order_date, order_time) VALUES (9, 11, 2, 2, '2022-08-09', '01:25:43'); $$; CALL add_message(9, 'payment error'); SELECT * FROM public."Order" WHERE order_id = 1 ORDER BY order_date desc;
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear