SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
CREATE SCHEMA IF NOT EXISTS car_dealership; CREATE TABLE IF NOT EXISTS car_dealership.Suppliers ( supplier_id SERIAL PRIMARY KEY, supplier_name_nm VARCHAR(128) NOT NULL, phone_number_no VARCHAR(32) NOT NULL, address_desc TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS car_dealership.Supply ( supply_id SERIAL PRIMARY KEY, supplier_id INTEGER NOT NULL, brand_nm VARCHAR(128) NOT NULL, number_of_cars_cnt INTEGER NOT NULL DEFAULT 1 CHECK (number_of_cars_cnt > 0), price_amt NUMERIC(9, 2) NOT NULL CHECK (price_amt > 0), date_dt DATE NOT NULL, FOREIGN KEY (supplier_id) REFERENCES car_dealership.Suppliers (supplier_id) ); CREATE TABLE IF NOT EXISTS car_dealership.Cars ( car_id SERIAL PRIMARY KEY, brand_nm VARCHAR(128) NOT NULL, max_speed_amt NUMERIC(5, 1) NOT NULL CHECK (max_speed_amt > 0), supply_id INTEGER NOT NULL, FOREIGN KEY (supply_id) REFERENCES car_dealership.Supply (supply_id) ); CREATE TABLE IF NOT EXISTS car_dealership.Customer ( customer_id SERIAL PRIMARY KEY, full_name_nm VARCHAR(128) NOT NULL, phone_number_no VARCHAR(32) NOT NULL, address_desc TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS car_dealership.Consultant ( consultant_id SERIAL PRIMARY KEY, full_name_nm VARCHAR(128) NOT NULL, phone_number_no VARCHAR(32) NOT NULL, address_desc TEXT NOT NULL, birth_date_dt DATE NOT NULL, hire_date_dt DATE NOT NULL, dismissal_date_dt DATE DEFAULT '2999-01-01' ); CREATE TABLE IF NOT EXISTS car_dealership.Account ( account_id SERIAL PRIMARY KEY, total_price_amt NUMERIC(9, 2) NOT NULL CHECK (total_price_amt > 0), payment_date_dttm TIMESTAMP NOT NULL ); CREATE TABLE IF NOT EXISTS car_dealership.Delivery ( delivery_id SERIAL PRIMARY KEY, dispatch_date_dt DATE NOT NULL, delivery_date_dt DATE NOT NULL, CHECK (dispatch_date_dt <= delivery_date_dt) ); CREATE TABLE IF NOT EXISTS car_dealership.Purchase_and_sale_agreement ( contract_id SERIAL PRIMARY KEY, car_id INTEGER NOT NULL, color_desc VARCHAR(32) NOT NULL, price_amt NUMERIC(9, 2) NOT NULL CHECK (price_amt > 0), customer_id INTEGER NOT NULL, consultant_id INTEGER NOT NULL, account_id INTEGER NOT NULL, delivery_id INTEGER NOT NULL, contract_date_dttm TIMESTAMP NOT NULL, FOREIGN KEY (car_id) REFERENCES car_dealership.Cars (car_id), FOREIGN KEY (customer_id) REFERENCES car_dealership.Customer (customer_id), FOREIGN KEY (consultant_id) REFERENCES car_dealership.Consultant (consultant_id), FOREIGN KEY (account_id) REFERENCES car_dealership.Account (account_id), FOREIGN KEY (consultant_id) REFERENCES car_dealership.Delivery (delivery_id) ); INSERT INTO car_dealership.Customer (full_name_nm, phone_number_no, address_desc) VALUES ('Архипов Абрам Константинович', '+79295344433', 'ул. Житная, 12'), ('Власов Илларион Протасьевич', '+71295458681', 'ул. Охотный ряд, 33'), ('Молчанов Роберт Святославович', '+71212051732', 'ул. Софийская, 90'), ('Дроздов Май Ильяович', '+74214485477', 'ул. Пречистенская, 54'), ('Корнилов Мирон Михаилович', '+76491615182', 'ул. Яузский, 47'), ('Тихонов Рубен Максович', '+74483311123', 'ул. Рождественская, 88'), ('Туров Корней Робертович', '+78545617823', 'ул. Макаренко, 57'), ('Тихонов Архип Игоревич', '+79458066081', 'ул. Валовая, 32'), ('Владимиров Петр Фролович', '+75691217647', 'ул. Кудринская, 4'), ('Мясников Виктор Валерьянович', '+77543642721', 'ул. Житная, 22'); INSERT INTO car_dealership.Consultant (full_name_nm, phone_number_no, address_desc, birth_date_dt, hire_date_dt, dismissal_date_dt) VALUES ('Романов Клим Серапионович', '+79995554433', 'ул. Первомайская, 23', '1990-08-23', '2015-02-23', DEFAULT), ('Меркушев Адольф Романович', '+79773352233', 'ул. Пушкина, 16', '1985-03-03', '2015-03-05', DEFAULT), ('Беспалов Геннадий Германнович', '+76691215687', 'ул. Веткина, 22А', '1994-01-30', '2015-03-05', DEFAULT), ('Щербаков Ким Григорьевич', '+78573647723', 'ул. Елецкая, 13', '1990-11-24', '2015-09-10', '2015-11-10'), ('Устинов Виталий Максович', '+71214445467', 'ул. Славы, 111', '1988-05-06', '2017-08-11', DEFAULT), ('Мишин Анатолий Романович', '+79098008081', 'ул. Богданова, 20Б', '1991-10-15', '2017-12-11', DEFAULT), ('Мамонтов Артур Яковович', '+79555758057', 'ул. Шухова, 54', '1989-03-23', '2018-10-22', '2019-03-22'), ('Щербаков Ким Григорьевич', '+78573647723', 'ул. Елецкая, 13', '1990-11-24', '2018-12-02', DEFAULT), ('Гуляев Максимилиан Артемович', '+74483233223', 'ул. Цандера, 32', '1994-09-29', '2019-10-15', DEFAULT), ('Власов Гордей Вениаминович', '+71112001232', 'ул. Сосновая, 99', '1990-09-11', '2019-10-16', DEFAULT); INSERT INTO car_dealership.Suppliers (supplier_name_nm, phone_number_no, address_desc) VALUES ('Hyundai Motor Company', '8 800 333-71-67', '123112, г. Москва, ул. Тестовская, д. 10'), ('BMW', '+7 495 500-50-00', 'Шмитовский проезд 24A 123100 Москва'), ('Nissan', '+7 (495) 152-02-86', 'Москва, Локомотивный проезд, 19'), ('HONDA', '+7 (495) 363-43-33', 'Волгоградский просп., 18, стр. 1, Москва, Россия'), ('Fiat Chrysler', '+7 (495) 308-81-75', 'Цветочный пр., 17, Москва, Россия'), ('Ford Motors', '+7 (495) 786-25-25', 'ул. Клары Цеткин, 24, Москва, Россия'), ('General Motors', '+7 (999) 996-60-95', 'Лихачёвский просп., 42, Долгопрудный, Россия'), ('Daimler AG', '+7 (495) 797-53-70', 'ул. Панфилова, 19, стр. 4, Химки, Россия'), ('Volkswagen AG', '+7 (495) 995-12-20', 'Нагатинская ул., 16, корп. 1, стр. 5, Москва, Россия'), ('Toyota Motor Corporation', '+7 (916) 329-81-41', 'Электродная ул., 2, стр. 32, Москва, Россия'); INSERT INTO car_dealership.Supply (supplier_id, brand_nm, number_of_cars_cnt, price_amt, date_dt) VALUES (1, 'Hyundai', 1, 20000, '2015-04-16'), (2, 'BMW', 2, 25000, '2015-09-29'), (2, 'BMW', 2, 40000, '2015-09-30'), (3, 'Nissan', 1, 15000, '2015-10-10'), (4, 'HONDA', 1, 20000, '2015-10-15'), (6, 'Ford', 3, 16000, '2015-11-30'), (9, 'Volkswagen', 1, 10000, '2016-01-27'), (10, 'Toyota', 2, 30000, '2018-09-29'); INSERT INTO car_dealership.Cars (brand_nm, max_speed_amt, supply_id) VALUES ('Hyundai', 170, 1), ('BMW', 175, 2), ('BMW', 180, 2), ('BMW', 195, 3), ('BMW', 200, 3), ('Nissan', 160, 4), ('HONDA', 170, 5), ('Ford', 160, 6), ('Ford', 160, 6), ('Ford', 160, 6), ('Volkswagen', 150, 7), ('Toyota', 180, 8), ('Toyota', 180, 8); INSERT INTO car_dealership.Account (total_price_amt, payment_date_dttm) VALUES (20050, '2015-04-16 13:00:00'), (25050, '2015-04-23 16:30:00'), (40010, '2015-05-12 13:00:00'), (40020, '2015-10-26 12:00:00'), (15050, '2018-11-05 12:30:00'), (20050, '2018-11-29 18:30:00'), (16020, '2018-12-29 14:30:00'), (16020, '2019-01-15 14:30:00'), (10020, '2019-11-03 16:30:00'), (30010, '2019-11-16 16:30:00'); INSERT INTO car_dealership.Delivery (dispatch_date_dt, delivery_date_dt) VALUES ('2015-04-17', '2015-04-20'), ('2015-04-24', '2015-04-27'), ('2015-05-13', '2015-05-16'), ('2015-10-27', '2015-10-30'), ('2018-11-06', '2018-11-09'), ('2018-11-30', '2018-12-02'), ('2018-12-30', '2019-01-03'), ('2019-01-16', '2019-01-19'), ('2019-11-04', '2019-11-07'), ('2019-11-17', '2019-11-20'); INSERT INTO car_dealership.Purchase_and_sale_agreement (car_id, color_desc, price_amt, customer_id, consultant_id, account_id, delivery_id, contract_date_dttm) VALUES (1, 'Black', 20000, 1, 1, 1, 1, '2015-04-16 13:00:00'), (2, 'Gray', 25000, 2, 1, 2, 2, '2015-04-23 16:30:00'), (4, 'Black', 40000, 3, 3, 3, 3, '2015-05-12 13:00:00'), (5, 'White', 40000, 4, 4, 4, 4, '2015-10-26 12:00:00'), (6, 'Black', 15000, 5, 5, 5, 5, '2018-11-05 12:30:00'), (7, 'Red', 20000, 6, 6, 6, 6, '2018-11-29 18:30:00'), (8, 'White', 16000, 7, 6, 7, 7, '2018-12-29 14:30:00'), (9, 'White', 16000, 8, 8, 8, 8, '2019-01-15 14:30:00'), (11, 'Gray', 10000, 9, 8, 9, 9, '2019-11-03 16:30:00'), (12, 'Gray', 30000, 10, 9, 10, 10, '2019-11-16 16:30:00'); SELECT * FROM car_dealership.Customer; SELECT * FROM car_dealership.Consultant; SELECT * FROM car_dealership.Suppliers; SELECT * FROM car_dealership.Supply; SELECT * FROM car_dealership.Cars; SELECT * FROM car_dealership.Account; SELECT * FROM car_dealership.Delivery; SELECT * FROM car_dealership.Purchase_and_sale_agreement; SELECT DISTINCT brand_nm FROM car_dealership.Cars; INSERT INTO car_dealership.Consultant (full_name_nm, phone_number_no, address_desc, birth_date_dt, hire_date_dt, dismissal_date_dt) VALUES ('Сергеев Андрей Артемович', '+79451524643', 'ул. Золотова, 23', '1990-08-23', '2023-04-23', DEFAULT); DELETE FROM car_dealership.Consultant WHERE hire_date_dt > '2022-05-08'; DELETE FROM car_dealership.Consultant WHERE full_name_nm = 'Власов Гордей Вениаминович'; UPDATE car_dealership.Customer SET phone_number_no = '+75691217647' WHERE full_name_nm = 'Владимиров Петр Фролович'; UPDATE car_dealership.Purchase_and_sale_agreement SET color_desc = 'Gray' WHERE car_id = 6; INSERT INTO car_dealership.Suppliers (supplier_name_nm, phone_number_no, address_desc) VALUES ('Volvo', '+7 (495) 266-55-87', '2-я Магистральная ул., 18, стр. 9, Москва, Россия'); UPDATE car_dealership.Suppliers SET supplier_name_nm = 'Inchcape Volvo' WHERE supplier_name_nm = 'Volvo'; DELETE FROM car_dealership.Suppliers WHERE supplier_name_nm = 'Fiat Chrysler'; UPDATE car_dealership.Suppliers SET phone_number_no = '+7 (499) 957-00-00', address_desc = 'ул. Обручева, 30/1с2, Москва, Россия' WHERE supplier_name_nm = 'Volkswagen AG'; ----------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------- CREATE TABLE car_dealership.changes_info ( operation VARCHAR(1) NOT NULL, date_dttm TIMESTAMP NOT NULL, user_id TEXT NOT NULL, car_id INTEGER NOT NULL, price_amt NUMERIC(9, 2) NOT NULL ); CREATE FUNCTION log_purchase_and_sales_agreement_changes() RETURNS TRIGGER AS $$ BEGIN IF (TG_OP = 'DELETE') THEN INSERT INTO car_dealership.changes_info SELECT 'D', now(), user AS user_id, OLD.car_id, OLD.price_amt; ELSIF (TG_OP = 'UPDATE') THEN INSERT INTO car_dealership.changes_info SELECT 'U', now(), user AS user_id, NEW.car_id, NEW.price_amt; ELSIF (TG_OP = 'INSERT') THEN INSERT INTO car_dealership.changes_info SELECT 'I', now(), user AS user_id, NEW.car_id, NEW.price_amt; END IF; RETURN NULL; END; $$ LANGUAGE plpgsql; CREATE TRIGGER record_changes AFTER INSERT OR UPDATE OR DELETE ON car_dealership.Purchase_and_sale_agreement FOR EACH ROW EXECUTE FUNCTION log_purchase_and_sales_agreement_changes(); INSERT INTO car_dealership.Purchase_and_sale_agreement (car_id, color_desc, price_amt, customer_id, consultant_id, account_id, delivery_id, contract_date_dttm) VALUES (1, 'Black', 20000, 1, 1, 1, 1, '2015-04-16 13:00:00'); SELECT * FROM car_dealership.changes_info; INSERT INTO car_dealership.Purchase_and_sale_agreement (car_id, color_desc, price_amt, customer_id, consultant_id, account_id, delivery_id, contract_date_dttm) VALUES (1, 'Black', 20000, 1, 1, 1, 1, '2015-04-16 13:00:00'); SELECT * FROM car_dealership.changes_info; CREATE FUNCTION check_cars_correctness() RETURNS TRIGGER AS $$ BEGIN IF LENGTH(NEW.brand_nm) <= 1 THEN RAISE EXCEPTION 'Incorrect car name'; END IF; IF NEW.max_speed_amt < 25 THEN RAISE EXCEPTION 'Incorrect car speed'; END IF; RETURN NULL; END; $$ LANGUAGE plpgsql; CREATE TRIGGER check_correct BEFORE INSERT OR UPDATE ON car_dealership.Cars FOR EACH ROW EXECUTE FUNCTION check_cars_correctness(); INSERT INTO car_dealership.Cars (brand_nm, max_speed_amt, supply_id) VALUES ('H', 100, 1);
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear