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'; ----------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------- -- чаще всего мы обращаемся к customer_id в join CREATE INDEX customer_id_index ON car_dealership.Customer (customer_id); -- часто обращаемся к consultant_id CREATE INDEX consultant_id_index ON car_dealership.Consultant (consultant_id); -- часто обращаемся к car_id CREATE INDEX cars_id_index ON car_dealership.Cars (car_id); -- часто обращаемся к account_id CREATE INDEX account_id_index ON car_dealership.Account (account_id); -- часто обращаемся к delivery_id CREATE INDEX delivery_id_index ON car_dealership.Delivery (delivery_id); -- часто обращаемся к supply_id CREATE INDEX supply_id_index ON car_dealership.Supply (supply_id); -- часто обращаемся к названию поставщика CREATE INDEX supplier_name_index ON car_dealership.Suppliers (supplier_name_nm); ------------------------------------------------ -- Показывает ФИО покупателей, номер телефона с закрытыми последними цифрами и адрес без указания дома CREATE VIEW car_dealership.customer_info_view AS ( SELECT full_name_nm, SUBSTRING(phone_number_no FOR 8) || '****' AS phone_number, REGEXP_REPLACE(address_desc, '[^[:alpha:]\s]', '', 'g') AS address FROM car_dealership.Customer ); -- Показывает ФИО консультанта, номер телефона с закрытыми последними цифрами и адрес без указания дома и дату рождения CREATE VIEW car_dealership.consultant_info_view AS ( SELECT full_name_nm, SUBSTRING(phone_number_no FOR 8) || '****' AS phone_number, REGEXP_REPLACE(address_desc, '[^[:alpha:]\s]', '', 'g') AS address, birth_date_dt FROM car_dealership.Consultant ); CREATE VIEW car_dealership.supplier_info_view AS ( SELECT supplier_name_nm, SUBSTRING(phone_number_no FOR 12) || '****' AS phone_number FROM car_dealership.Suppliers ); SELECT * FROM car_dealership.supplier_info_view; -- Объединяет договор купли-продажи, счет к оплате и доставку. В таблице показана сумма по всем счетам, самая ранняя дата вывоза и поздняя дата доставки CREATE VIEW car_dealership.Purchase_and_sale_agreement_info_view AS ( SELECT price_amt, SUM(total_price_amt) AS total_price, contract_date_dttm, MIN(dispatch_date_dt) AS first_dispatch_date, MAX(delivery_date_dt) AS last_delivery_date FROM car_dealership.Purchase_and_sale_agreement JOIN car_dealership.Account ON Purchase_and_sale_agreement.account_id = Account.account_id JOIN car_dealership.Delivery ON Purchase_and_sale_agreement.delivery_id = Delivery.delivery_id GROUP BY contract_id ); -- Для каждой машины показывает её поставщика CREATE VIEW car_dealership.car_info_view AS ( SELECT Cars.brand_nm, max_speed_amt, supplier_name_nm FROM car_dealership.Cars JOIN car_dealership.Supply ON Cars.supply_id = Supply.supply_id JOIN car_dealership.Suppliers ON Supply.supplier_id = Suppliers.supplier_id ); -- Для каждой купленной машины показывает её цвет при покупке и стоимость CREATE VIEW car_dealership.car_purchase_view AS ( SELECT brand_nm, color_desc, price_amt FROM car_dealership.Purchase_and_sale_agreement JOIN car_dealership.Cars ON Purchase_and_sale_agreement.car_id = Cars.car_id ); SELECT * FROM car_dealership.car_color_view
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear