TE05P0Na

1#12B class, Ivan Viyachki, 8, Kaloyan Madjunov 11
2DROP DATABASE IF EXISTS Class7;
3CREATE DATABASE Class7;
4USE Class7;
5
6CREATE TABLE sellers(
7	id INT PRIMARY KEY AUTO_INCREMENT,
8    name VARCHAR(30) NOT NULL,
9    age INT NOT NULL    
10);
11
12CREATE TABLE cars(
13	id INT PRIMARY KEY AUTO_INCREMENT,
14    brand VARCHAR(20) NOT NULL,
15	model VARCHAR(20) NOT NULL,
16    color VARCHAR(20) NOT NULL,
17    price INT NOT NULL,
18    date_of_purchase DATE,
19    sellerId INT NOT NULL,
20
21	#Task 1
22	FOREIGN KEY (sellerId) REFERENCES sellers(id)
23    
24);
25
26CREATE TABLE buyers(
27	id INT PRIMARY KEY AUTO_INCREMENT,
28    name VARCHAR(30) NOT NULL,
29    age INT NOT NULL
30
31);
32
33#Task 2
34CREATE TABLE cars_buyers(
35	id INT PRIMARY KEY AUTO_INCREMENT,
36	buyerId INT NOT NULL,
37    carId INT NOT NULL,
38    
39    FOREIGN KEY (buyerId) REFERENCES buyers(id),
40	FOREIGN KEY (carId) REFERENCES cars(id)
41);
42
43#Task 3
44INSERT INTO sellers (name, age) VALUES ('DJ Drag', 20);
45INSERT INTO sellers (name, age) VALUES ('Simo', 23);
46
47INSERT INTO buyers (name, age) VALUES ('Kaloyan', 19);
48INSERT INTO buyers (name, age) VALUES ('Ivan', 19);
49
50INSERT INTO cars(brand, model, color, price, sellerId) VALUES ('BMW','120d', 'Black', 6000, 1);
51INSERT INTO cars(brand, model, color, price, date_of_purchase, sellerId) VALUES ('Audi','A6' ,'Red' ,5000 ,now(),2);
52INSERT INTO cars(brand, model, color, price, date_of_purchase, sellerId) VALUES ('Ford','Mustang' ,'Yellow' ,13000 ,now(),1);
53INSERT INTO cars(brand, model, color, price, date_of_purchase, sellerId) VALUES ('Mercedes','S63' ,'Gray' ,50000,now(),2);
54INSERT INTO cars(brand, model, color, price, sellerId) VALUES ('Ferrari','F40' ,'Red' ,100000,2);
55
56INSERT INTO cars_buyers(buyerId, carId) VALUES(1, 3);
57INSERT INTO cars_buyers(buyerId, carId) VALUES(2, 4);
58INSERT INTO cars_buyers(buyerId, carId) VALUES(2, 2);
59INSERT INTO cars_buyers(buyerId, carId) VALUES(1, 2);
60
61#Task 4
62SELECT s.name, SUM(c.price) AS 'Sum'
63FROM sellers AS s
64LEFT JOIN cars AS c
65ON s.id = c.sellerId
66GROUP BY s.id, s.name;
67
68#Task 5
69SELECT b.name, SUM(c.price) AS 'Sum'
70FROM buyers AS b
71LEFT JOIN cars_buyers AS cb
72ON b.id = cb.buyerId
73LEFT JOIN cars AS c
74ON c.id = cb.carId
75GROUP BY b.id, b.name;
76
77#Task 6
78SELECT b.name, b.age, c.brand, c.model, c.color, c.date_of_purchase
79FROM buyers AS b
80LEFT JOIN cars_buyers AS cb
81ON b.id = cb.buyerId
82LEFT JOIN cars AS c
83ON c.id = cb.carId;