kxFGvefF

1DROP DATABASE IF EXISTS school;
2CREATE DATABASE school;
3USE school;
4
5CREATE TABLE seller(
6	id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
7    name VARCHAR(100) NOT NULL,
8    age INTEGER NOT NULL
9);
10
11CREATE TABLE car(
12	id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
13    brand VARCHAR(100) NOT NULL,
14    model VARCHAR(100) NOT NULL,
15    price INTEGER NOT NULL,
16    sell_date DATE DEFAULT NULL,
17    seller_id INTEGER NOT NULL,
18    
19    FOREIGN KEY (seller_id) REFERENCES seller(id)
20);
21
22CREATE TABLE buyer(
23	id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
24    name VARCHAR(100) NOT NULL,
25    age INTEGER NOT NULL
26);
27
28CREATE TABLE purchase(
29	id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
30    car_id INTEGER NOT NULL,
31    buyer_id INTEGER NOT NULL,
32    
33    FOREIGN KEY (car_id) REFERENCES car(id),
34	FOREIGN KEY (buyer_id) REFERENCES buyer(id)
35);
36
37DELIMITER //
38
39CREATE TRIGGER sell_date_on_purchase_insert AFTER INSERT ON purchase
40FOR EACH ROW
41BEGIN
42	UPDATE car SET sell_date = NOW() WHERE car.id = NEW.car_id;
43END//
44DELIMITER ;
45
46
47INSERT INTO seller(name,age) VALUES('Alex', 18);
48INSERT INTO seller(name,age) VALUES('Boian', 25);
49
50INSERT INTO car(brand,model,price,seller_id) VALUES('Mercedes','CLK',1500,2);
51INSERT INTO car(brand,model,price,seller_id) VALUES('Renault','19',400,1);
52INSERT INTO car(brand,model,price,seller_id) VALUES('Ferari','1',100000,2);
53INSERT INTO car(brand,model,price,seller_id) VALUES('Skoda','Octavia',3000,1);
54INSERT INTO car(brand,model,price,seller_id) VALUES('Peugeot','306',1000,2);
55
56INSERT INTO buyer(name,age) VALUES('AlexBuyer', 35);
57INSERT INTO buyer(name,age) VALUES('BoianBuyer', 48);
58
59INSERT INTO purchase(car_id,buyer_id) VALUES(1,1);
60INSERT INTO purchase(car_id,buyer_id) VALUES(1,2);
61INSERT INTO purchase(car_id,buyer_id) VALUES(3,2);
62INSERT INTO purchase(car_id,buyer_id) VALUES(5,1);
63
64SELECT s.name,SUM(c.price) AS total FROM car c
65INNER JOIN seller s 
66ON s.id = c.seller_id
67WHERE c.sell_date IS NULL
68GROUP BY seller_id;
69
70SELECT b.name, SUM(c.price) FROM purchase p
71INNER JOIN buyer b ON b.id = p.buyer_id
72INNER JOIN car c ON c.id = p.car_id
73GROUP BY p.buyer_id;
74
75SELECT b.name, b.age, c.brand, c.model, c.price, c.sell_date FROM purchase p
76INNER JOIN buyer b ON b.id = p.buyer_id
77INNER JOIN car c ON c.id = p.car_id;