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 FUNCTION car_dealership.Consultant_sales() RETURNS TABLE ( full_name_nm VARCHAR(128), price_sum NUMERIC(9, 2) ) AS $$ SELECT full_name_nm, SUM(price_amt) AS price_sum FROM car_dealership.Consultant JOIN car_dealership.Purchase_and_sale_agreement ON Consultant.consultant_id = Purchase_and_sale_agreement.consultant_id GROUP BY full_name_nm $$ LANGUAGE SQL; SELECT * FROM car_dealership.Consultant_sales(); -- Для каждого года показывает количество проданных машин и их суммарную стоимость CREATE FUNCTION car_dealership.Sales_per_year() RETURNS TABLE ( year_nm INTEGER, price_sum NUMERIC(9, 2), sales_count BIGINT ) AS $$ SELECT cast(DATE_PART('year', cast(contract_date_dttm AS DATE)) AS INTEGER) AS year_nm, SUM(price_amt) AS price_sum, COUNT(price_amt) AS sales_count FROM car_dealership.Purchase_and_sale_agreement GROUP BY year_nm ORDER BY year_nm; $$ LANGUAGE SQL; SELECT * FROM car_dealership.Sales_per_year();
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear