[sql] Cars
Viewer
- CREATE DATABASE `commission_car_shop`;
- USE `commission_car_shop`;
- -- create
- CREATE TABLE contracts (
- id INT AUTO_INCREMENT PRIMARY KEY,
- customer VARCHAR(255) NOT NULL,
- dealer VARCHAR(255) NOT NULL,
- contract_date DATE NOT NULL,
- car_brand VARCHAR(255) NOT NULL,
- release_date DATE NOT NULL,
- mileage INT NOT NULL,
- sale_date DATE NOT NULL,
- sale_price INT NOT NULL,
- commission_amount INT NOT NULL,
- cust_id INT,
- deal_id INT
- );
- CREATE TABLE customers (
- id INT AUTO_INCREMENT PRIMARY KEY,
- customer_full_name VARCHAR(255) NOT NULL,
- city VARCHAR(255) NOT NULL,
- address VARCHAR(255) NOT NULL,
- telephone_number INT NOT NULL
- );
- CREATE TABLE dealers (
- id INT AUTO_INCREMENT PRIMARY KEY,
- dealer_full_name VARCHAR(255) NOT NULL,
- photo BLOB,
- address VARCHAR(255) NOT NULL,
- telephone_number INT NOT NULL
- );
- -- insert
- INSERT INTO contracts (customer, dealer, contract_date, car_brand, release_date, mileage, sale_date, sale_price, commission_amount, cust_id, deal_id)
- VALUES
- ("Bohdan", "Dmytro", "2021.03.18", "Toyota", "2018.01.01", 5250, "2022.03.17", 74480, 6520, 1, 1),
- ("Ivan", "Oleksandr", "2023.09.07", "Porsche", "2019.01.01", 6100, "2023.09.12", 140000, 15000, 2, 3),
- ("Ihor", "Maksym", "2022.05.22", "Tesla", "2017.01.01", 795, "2022.12.28", 51605, 4500, 3, 2),
- ("Juliia", "Dmytro", "2022.08.04", "BMW", "2020.01.01", 3790, "2023.06.21", 133000, 12560, 4, 1),
- ("Mykyta", "Dmytro", "2023.08.10", "Audi", "2019.01.01", 4855, "2023.08.31", 166950, 17700, 5, 1),
- ("Stepan", "Oleksandr", "2022.12.31", "Porsche", "2017.01.01", 7921, "2023.01.07", 135660, 13670, 6, 3),
- ("Daria", "Dmytro", "2022.11.11", "Mercedes-Benz", "2021.01.01", 5920, "2022.12.22", 119899, 8230, 7, 1),
- ("Oleh", "Maksym", "2023.07.15", "Mustang", "2020.01.01", 7700, "2023.09.01", 125000, 9000, 8, 2),
- ("Volodymyr", "Maksym", "2022.02.23", "Dodge", "2021.01.01", 1175, "2022.07.06", 85830, 7330, 9, 2),
- ("Danylo", "Oleksandr", "2023.08.01", "Porsche", "2016.01.01", 2115, "2023.09.18", 82450, 7100, 10, 3);
- INSERT INTO customers (customer_full_name, city, address, telephone_number)
- VALUES
- ('Voronin Bohdan Oleksandrovych', 'Mykolaiv', 'st. Okeanivska 36', 0632345072),
- ('Hoba Ivan Ivanovych', 'Mykolaiv', 'st. Novozavodska 111', 0991236036),
- ('Li Ihor Volodymyrovych', 'Kyiv', 'st. Kyrylivska 99', 0931906783),
- ('Vorkinh Juliia Yevhenivna', 'Lviv', 'st. Teatralna 8', 0666134777),
- ('Molchanov Mykyta Bohdanovych', 'Mykolaiv', 'st. Sadova 11/13', 0988765415),
- ('Lobov Stepan Mychailovych', 'Odesa', 'st. Seredna 18', 0992384740),
- ('Tsvihun Daria Pavlivna', 'Mykolaiv', 'st. Shevchenka 69A', 0652349730),
- ('Holub Oleh Vladyslavovych', 'Uman', 'st. Hrushevskoho 46', 0669762629),
- ('Boiko Volodymyr Petrovych', 'Dnipro', 'st. Marshala Malynovskoho 36', 0982357854),
- ('Pyzhyk Danylo Valeriiovych', 'Lutsk', 'st. Kopernyka 34', 0999487510);
- INSERT INTO dealers (dealer_full_name, address, telephone_number)
- VALUES
- ('Holovko Dmytro Serhiiovych', 'Zaliznychnyi district, st. Lyubinska 162', 999856785),
- ('Vdovychenko Maksym Dmytrovych', 'Halytskyi district, st. Drahomanova 50', 932411691),
- ('Shevchenko Oleksandr Oleksandrovych', 'Lychakyvskyi district, st. General Tarnavskoho 107', 932411660);
- -- alter
- -- ALTER TABLE dealers ADD CONSTRAINT fk_dt FOREING KEY (id) REFERENCES contracts(deal_id) ON DELETE CASCADE ON UPDATE CASCADE;
- -- ALTER TABLE customers ADD CONSTRAINT fk_ct FOREING KEY (id) REFERENCES contracts(cust_id) ON DELETE CASCADE ON UPDATE CASCADE;
- -- fetch
- -- SELECT * FROM contracts;
- -- SELECT * FROM customers;
- -- SELECT * FROM dealers;
- -- Створюємо нову таблицю для інформації про марки автомобілів
- CREATE TABLE car_brands (
- car_brand_id INT PRIMARY KEY,
- car_brand VARCHAR(255) NOT NULL
- );
- -- Заповнюємо нову таблицю
- INSERT INTO car_brands (car_brand_id, car_brand)
- VALUES
- (1, 'Toyota'),
- (2, 'Porsche'),
- (3, 'Tesla'),
- (4, 'BMW'),
- (5, 'Audi'),
- (6, 'Mercedes-Benz'),
- (7, 'Mustang'),
- (8, 'Dodge');
- -- Видаляємо стовпець car_brand з таблиці contracts
- ALTER TABLE contracts
- DROP COLUMN car_brand;
- -- Додаємо до таблиці contracts зовнішній ключ car_brand_id
- ALTER TABLE contracts
- ADD COLUMN car_brand_id INT,
- ADD CONSTRAINT fk_car_brand
- FOREIGN KEY (car_brand_id)
- REFERENCES car_brands(car_brand_id);
- UPDATE contracts
- SET car_brand_id = 1 WHERE id = 1;
- UPDATE contracts
- SET car_brand_id = 2 WHERE id = 2;
- UPDATE contracts
- SET car_brand_id = 2 WHERE id = 6;
- UPDATE contracts
- SET car_brand_id = 2 WHERE id = 10;
- UPDATE contracts
- SET car_brand_id = 3 WHERE id = 3;
- UPDATE contracts
- SET car_brand_id = 4 WHERE id = 4;
- UPDATE contracts
- SET car_brand_id = 5 WHERE id = 5;
- UPDATE contracts
- SET car_brand_id = 6 WHERE id = 7;
- UPDATE contracts
- SET car_brand_id = 7 WHERE id = 8;
- UPDATE contracts
- SET car_brand_id = 8 WHERE id = 9;
- -- SELECT * FROM contracts;
- SELECT * FROM car_brands;
- -- DESC customers;
Editor
You can edit this paste and save as new: