F51uUvSd

1DROP DATABASE IF EXISTS snow_safety;
2CREATE DATABASE snow_safety;
3USE snow_safety;
4
5CREATE TABLE snowplows(
6	id INT AUTO_INCREMENT PRIMARY KEY,
7    manufactureYear YEAR NOT NULL,
8    make VARCHAR(255) NOT NULL,
9    model VARCHAR(255) NOT NULL,
10    regNumber VARCHAR(255) NOT NULL UNIQUE,
11    size VARCHAR(255) NULL DEFAULT NULL,
12    color VARCHAR(255) NULL DEFAULT NULL
13);
14
15CREATE TABLE drivers(
16	id INT AUTO_INCREMENT PRIMARY KEY,
17	name VARCHAR(255) NOT NULL,
18    categories VARCHAR(255) NOT NULL,
19    egn VARCHAR(10) NOT NULL UNIQUE,
20    address VARCHAR(255) NOT NULL, 
21    phone VARCHAR(20) NULL DEFAULT NULL
22);
23
24CREATE TABLE snowfalls(
25	id INT AUTO_INCREMENT PRIMARY KEY,
26    date DATETIME NOT NULL,
27    location VARCHAR(255) NOT NULL,
28    cleaningHours INT NOT NULL,
29    notes VARCHAR(1000) NULL DEFAULT NULL
30);
31
32CREATE TABLE snow_plow(
33	snowplow_id INT NOT NULL,
34    snowfall_id INT NOT NULL,
35    CONSTRAINT FOREIGN KEY(snowplow_id) REFERENCES snowplows(id),
36    CONSTRAINT FOREIGN KEY(snowfall_id) REFERENCES snowfalls(id),
37    PRIMARY KEY(snowplow_id, snowfall_id)
38);
39
40CREATE TABLE snow_driver(
41	driver_id INT NOT NULL,
42    snowfall_id INT NOT NULL,
43    CONSTRAINT FOREIGN KEY(driver_id) REFERENCES drivers(id),
44    CONSTRAINT FOREIGN KEY(snowfall_id) REFERENCES snowfalls(id),
45    PRIMARY KEY(driver_id, snowfall_id)
46);
47
48INSERT INTO snowplows
49VALUES (NULL, 1999, 'Wolfmann', 'CXA20', 'СА 5220 АР', NULL, 'red'),
50		(NULL, 2008, 'Mercedes', 'Sprinter', 'СА 2010 АХ', NULL, 'blue'),
51        (NULL, 2006, 'Mercedes', 'Sprinter', 'СА 8542 АР', NULL, 'white'),
52        (NULL, 1996, 'Scania', 'Tiger', 'СА 2635 АН', NULL, 'green'),
53        (NULL, 1996, 'Scania', 'Tiger', 'СА 1180 АМ', NULL, 'green'),
54        (NULL, 1999, 'Scania', 'Tiger', 'СА 4896 АР', NULL, 'black');
55
56INSERT INTO drivers
57VALUES (NULL, 'Ivan Sotirov Nikolov', 'B-Tkt', '9702022829', 'Sofia Student city 12-207', NULL),
58		(NULL, 'Martin Nevenov Mutafchev', 'B', '9703032829', 'Sofia Student city 12-207', NULL);
59        
60INSERT INTO snowfalls
61VALUES (NULL, '2018-01-01 04:00:00', 'Student city', 3, NULL),
62		(NULL, '2018-01-01 04:00:00', 'Mladost 4', 2, NULL),
63        (NULL, '2018-02-22 05:00:00', 'Mladost 4', 4, NULL),
64        (NULL, '2018-02-18 04:00:00', 'Mladost 4', 2, NULL),
65        (NULL, '2018-03-01 07:00:00', 'Mladost 4', 1, NULL);
66
67INSERT INTO snow_plow
68VALUES  (1,1),
69		(2,1),
70		(2,2),
71        (2,3),
72        (4,5),
73        (5,4);
74
75INSERT INTO snow_driver
76VALUES  (1,2),
77		(2,1);
78        
79SELECT regNumber
80FROM snowplows
81WHERE make = 'Scania';
82
83SELECT make AS Manufacturer, AVG(manufactureYear) AS Average
84FROM snowplows
85GROUP BY make;
86
87SELECT drivers.name, drivers.egn, drivers.address, snowfalls.location
88FROM drivers LEFT JOIN snowfalls
89ON drivers.id IN(
90	SELECT driver_id
91    FROM snow_driver
92    WHERE snow_driver.snowfall_id = snowfalls.id
93);
94
95SELECT snowplows.make, SUM(snowfalls.cleaningHours) AS TotalWorkHours
96FROM snowplows JOIN snowfalls
97ON snowplows.id IN(
98	SELECT snowplow_id
99	FROM snow_plow
100    WHERE snowfall_id = snowfalls.id
101)
102GROUP BY make;