· 8 years ago · May 17, 2018, 03:24 PM
1DROP DATABASE IF EXISTS KAT;
2CREATE DATABASE KAT;
3USE KAT;
4
5CREATE TABLE towns(
6 id INT PRIMARY KEY AUTO_INCREMENT,
7 name VARCHAR(100) NOT NULL
8);
9
10CREATE TABLE owners(
11 id INT PRIMARY KEY AUTO_INCREMENT,
12 first_name VARCHAR(100) NOT NULL,
13 middle_name VARCHAR(100) NOT NULL,
14 last_name VARCHAR(100) NOT NULL,
15 egn VARCHAR(10) NOT NULL,
16 date_of_birth DATE NOT NULL,
17 sex ENUM('m', 'f'),
18 height INT,
19 town_id INT NOT NULL,
20 CONSTRAINT fk_owners_towns FOREIGN KEY(town_id) REFERENCES towns(id)
21);
22
23CREATE TABLE insurers(
24 id INT PRIMARY KEY AUTO_INCREMENT,
25 name VARCHAR(100) NOT NULL,
26 town_id INT NOT NULL,
27 CONSTRAINT fk_insurers_towns FOREIGN KEY(town_id) REFERENCES towns(id)
28);
29
30CREATE TABLE cars(
31 id INT PRIMARY KEY AUTO_INCREMENT,
32 make VARCHAR(100) NOT NULL,
33 model VARCHAR(100) NOT NULL,
34 registration_number VARCHAR(8) NOT NULL,
35 date_of_production DATE NOT NULL,
36 date_of_registration DATE NOT NULL,
37 owner_id INT NOT NULL,
38 insurer_id INT NOT NULL,
39 CONSTRAINT fk_cars_owners FOREIGN KEY(owner_id) REFERENCES owners(id),
40 CONSTRAINT fk_cars_insurers FOREIGN KEY(insurer_id) REFERENCES insurers(id)
41);
42
43CREATE TABLE laws(
44 id INT PRIMARY KEY AUTO_INCREMENT,
45 name VARCHAR(100) NOT NULL UNIQUE
46);
47
48CREATE TABLE fines(
49 id INT PRIMARY KEY AUTO_INCREMENT,
50 name VARCHAR(100) NOT NULL UNIQUE,
51 amount DECIMAL(19,2) NOT NULL,
52 law_id INT NOT NULL,
53 CONSTRAINT fk_fines_laws FOREIGN KEY (law_id) REFERENCES laws(id)
54);
55
56CREATE TABLE owners_fines(
57 owner_id INT NOT NULL,
58 fine_id INT NOT NULL,
59 PRIMARY KEY(owner_id, fine_id),
60 CONSTRAINT fk_owners_fines_owners FOREIGN KEY (owner_id) REFERENCES owners(id),
61 CONSTRAINT fk_owners_fines_fines FOREIGN KEY (fine_id) REFERENCES fines(id)
62);
63
64CREATE TABLE payed_fines(
65 owner_id INT NOT NULL,
66 fine_id INT NOT NULL,
67 PRIMARY KEY(owner_id, fine_id),
68 CONSTRAINT fk_payed_fines_owners FOREIGN KEY (owner_id) REFERENCES owners(id),
69 CONSTRAINT fk_payed_fines_fines FOREIGN KEY (fine_id) REFERENCES fines(id)
70);
71
72INSERT INTO towns(name)
73VALUES ('Pernik'), ('Plovdiv'), ('Varna'), ('Sofia'), ('Burgas'), ('Pazardzhik'), ('Pirdop'), ('Ruse'), ('Sliven');
74
75INSERT INTO owners(first_name, middle_name, last_name, egn, date_of_birth, sex, height, town_id)
76VALUES ('Georgi', 'Malinov', 'Demirov', '9811113498', '1998-11-11', 'm', 190, 1),
77('Petko', 'Zaprianov', 'Kolarov', '9812124498', '1998-12-12', 'm', 190, 2),
78('Pavel', 'Dematov', 'Dimitrov', '9610101415', '1996-10-10', 'm', 190, 3),
79('Ivan', 'Yoradnov', 'Poiukov', '9005053498', '1990-05-05', 'm', 190, 4),
80('Maslin', 'Kiparov', 'Iskrov', '9711113498', '1997-11-11', 'm', 190, 5),
81('Lukcho', 'Johnson', 'Peterson', '1001013498', '1910-01-01', 'm', 190, 6);
82
83INSERT INTO insurers(name, town_id)
84VALUES ('Raifaizen bank', 1), ('Good company', 3), ('SDI', 5), ('Bad company', 4), ('Armeec', 3), ('VNER', 6);
85
86INSERT INTO cars (make, model, registration_number, date_of_production, date_of_registration, owner_id, insurer_id)
87VALUES ('Peugeot', '405', 'PA6478AK', '1995-01-01', '1995-01-02', 4, 2),
88('Peugeot', '406', 'CO1919AM', '2000-01-01', '2000-01-02', 1, 1),
89('BMW', 'X6', 'CH3013PA', '1998-01-02', '2000-01-02', 2, 3),
90('BMW', '320i', 'K3421MP', '2010-01-01', '2011-01-02',3, 4),
91('Mercedes', 'E220', 'BH1919KA', '2015-01-01', '2016-01-02', 4, 5),
92('Renault', 'Fluence', 'P1241KM', '2018-01-01', '2018-01-02', 5, 6),
93('Mazda', 'RX-8', 'A4141KM', '2003-01-01', '2004-01-02', 6, 4);
94
95INSERT INTO laws(name)
96VALUES('Law for driving'), ('Law for punishment'), ('Law for judgement');
97
98INSERT INTO fines(name, amount, law_id)
99VALUES('To high speed', 1000, 1), ('To low speed', 414, 1), ('Drunk driving', 5000, 2), ('Catastrophe', 1313, 3), ('Hit a person', 414, 3),
100('Hit another vehicle', 80, 1), ('Not follow the rules', 10, 2);
101
102INSERT INTO owners_fines(owner_id, fine_id)
103VALUES (1, 1), (1, 2), (2, 2), (2, 3), (3, 1), (3, 5), (5, 5), (3, 6), (6, 3);
104
105INSERT INTO payed_fines(owner_id, fine_id)
106VALUES (1, 1), (3, 1), (5, 5);
107
108SELECT #2 Cars made before 2000 with their owners names
109 c.make,
110 c.model,
111 c.registration_number,
112 c.date_of_production,
113 c.date_of_registration,
114 CONCAT(o.first_name, ' ', o.last_name) AS `owner_full_name`
115FROM
116 cars AS c
117 JOIN
118 owners AS o ON o.id = c.owner_id
119WHERE
120 YEAR(date_of_production) < 2000
121ORDER BY `owner_full_name` ASC;
122
123
124SELECT #3 Owners and their total sum of their fines
125 CONCAT(o.first_name, ' ', o.last_name) AS `owner_full_name`,
126 SUM(f.amount) AS `owed_amount_for_fines`
127FROM
128 owners AS o
129 JOIN
130 owners_fines AS of ON of.owner_id = o.id
131 JOIN
132 fines AS f ON f.id = of.fine_id
133GROUP BY o.id
134HAVING `owed_amount_for_fines` > 1000
135ORDER BY `owed_amount_for_fines` DESC;
136
137
138SELECT #4 Owner, their cars and their violations
139 CONCAT(o.first_name, ' ', o.last_name) AS `owner_full_name`,
140 c.registration_number,
141 i.name,
142 f.name
143FROM
144 cars AS c
145 LEFT OUTER JOIN
146 owners AS o ON o.id = c.owner_id
147 LEFT OUTER JOIN
148 insurers AS i ON i.id = c.insurer_id
149 INNER JOIN
150 owners_fines AS of ON of.owner_id = o.id
151 INNER JOIN
152 fines AS f ON f.id = of.fine_id;
153
154SELECT #5 Owners, count of their cars and owed money for their fines
155 CONCAT(o.first_name, ' ', o.last_name) AS `owner_full_name`,
156 COUNT(c.id) AS `count_of_cars`,
157 IFNULL((SELECT
158 sum(f.amount)
159 FROM
160 owners_fines AS of
161 JOIN
162 fines AS f ON f.id = of.fine_id
163 WHERE
164 of.owner_id = o.id), 0) as `owed_money`
165FROM
166 owners AS o
167 LEFT OUTER JOIN
168 cars AS c ON c.owner_id = o.id
169GROUP BY o.id
170ORDER BY `count_of_cars` DESC;
171
172#6
173DELIMITER $$
174CREATE PROCEDURE usp_show_drivers_with_owed_money_more_than(IN owed_money DECIMAL)
175BEGIN
176 DECLARE owner_full_name VARCHAR(200);
177 DECLARE current_owed_money DECIMAL(19, 2);
178 DECLARE finished INT;
179
180 DECLARE cursor_for_owners CURSOR FOR
181 SELECT first_name,
182 IFNULL((SELECT
183 sum(f.amount)
184 FROM
185 owners_fines AS of
186 JOIN
187 fines AS f ON f.id = of.fine_id
188 WHERE
189 of.owner_id = o.id), 0)
190 FROM owners as o
191 WHERE IFNULL((SELECT
192 sum(f.amount)
193 FROM
194 owners_fines AS of
195 JOIN
196 fines AS f ON f.id = of.fine_id
197 WHERE
198 of.owner_id = o.id), 0) > owed_money;
199
200 DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
201
202 SET finished = 0;
203
204 CREATE TEMPORARY TABLE owners_with_this_amounts (
205 owner_full_name_temp VARCHAR(200),
206 current_owed_money_temp DECIMAL(19, 2)
207 ) ENGINE = MEMORY;
208
209 OPEN cursor_for_owners;
210 show_drivers: WHILE (finished = 0)
211 DO
212 FETCH cursor_for_owners INTO owner_full_name, current_owed_money;
213 IF (finished = 1)
214 THEN
215 LEAVE show_drivers;
216 END IF;
217
218 INSERT INTO owners_with_this_amounts(owner_full_name_temp, current_owed_money_temp)
219 VALUES (owner_full_name, current_owed_money);
220 END WHILE;
221
222SELECT
223 *
224FROM
225 owners_with_this_amounts
226ORDER BY current_owed_money_temp ASC;
227 CLOSE cursor_for_owners;
228 DROP TABLE owners_with_this_amounts;
229END $$
230DELIMITER ;
231
232DROP PROCEDURE usp_show_drivers_with_owed_money_more_than;
233CALL usp_show_drivers_with_owed_money_more_than(1);