· 6 years ago · Mar 20, 2019, 06:20 PM
1DROP DATABASE IF EXISTS school_sport_clubs;
2CREATE DATABASE school_sport_clubs;
3USE school_sport_clubs;
4
5CREATE TABLE school_sport_clubs.sports (
6 id INT AUTO_INCREMENT PRIMARY KEY,
7 name VARCHAR(255) NOT NULL
8);
9
10CREATE TABLE school_sport_clubs.coaches (
11 id INT AUTO_INCREMENT PRIMARY KEY,
12 name VARCHAR(255) NOT NULL,
13 egn VARCHAR(10) NOT NULL UNIQUE
14);
15
16CREATE TABLE school_sport_clubs.students (
17 id INT AUTO_INCREMENT PRIMARY KEY,
18 name 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 class VARCHAR(10) NULL DEFAULT NULL
23);
24
25CREATE TABLE school_sport_clubs.sportGroups (
26 id INT AUTO_INCREMENT PRIMARY KEY,
27 location VARCHAR(255) NOT NULL,
28 dayOfWeek ENUM('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday'),
29 hourOfTraining TIME NOT NULL,
30 sport_id INT NOT NULL,
31 coach_id INT NOT NULL,
32 UNIQUE KEY (location , dayOfWeek , hourOfTraining),
33 CONSTRAINT FOREIGN KEY (sport_id)
34 REFERENCES sports (id),
35 CONSTRAINT FOREIGN KEY (coach_id)
36 REFERENCES coaches (id)
37);
38
39CREATE TABLE school_sport_clubs.student_sport (
40 student_id INT NOT NULL,
41 sportGroup_id INT NOT NULL,
42 CONSTRAINT FOREIGN KEY (student_id)
43 REFERENCES students (id),
44 CONSTRAINT FOREIGN KEY (sportGroup_id)
45 REFERENCES sportGroups (id),
46 PRIMARY KEY (student_id , sportGroup_id)
47);
48
49CREATE TABLE taxesPayments (
50 id INT AUTO_INCREMENT PRIMARY KEY,
51 student_id INT NOT NULL,
52 group_id INT NOT NULL,
53 paymentAmount DOUBLE NOT NULL,
54 month TINYINT,
55 year YEAR,
56 dateOfPayment DATETIME NOT NULL,
57 CONSTRAINT FOREIGN KEY (student_id)
58 REFERENCES students (id),
59 CONSTRAINT FOREIGN KEY (group_id)
60 REFERENCES sportgroups (id)
61);
62
63CREATE TABLE salaryPayments (
64 id INT AUTO_INCREMENT PRIMARY KEY,
65 coach_id INT NOT NULL,
66 month TINYINT,
67 year YEAR,
68 salaryAmount DOUBLE,
69 dateOfPayment DATETIME NOT NULL,
70 CONSTRAINT FOREIGN KEY (coach_id)
71 REFERENCES coaches (id),
72 UNIQUE KEY (coach_id , `month` , `year`)
73);
74
75INSERT INTO sports
76VALUES (NULL, 'Football') ,
77 (NULL, 'Volleyball'),
78 (NULL, 'Tennis');
79
80INSERT INTO coaches
81VALUES (NULL, 'Ivan Todorov Petkov', '7509041245') ,
82 (NULL, 'georgi Ivanov Todorov', '8010091245') ,
83 (NULL, 'Ilian Todorov Georgiev', '8407106352') ,
84 (NULL, 'Petar Slavkov Yordanov', '7010102045') ,
85 (NULL, 'Todor Ivanov Ivanov', '8302160980') ,
86 (NULL, 'Slavi Petkov Petkov', '7106041278');
87
88INSERT INTO students (name, egn, address, phone, class)
89VALUES ('Iliyan Ivanov', '9401150045', 'Sofia-Mladost 1', '0893452120', '10') ,
90 ('Ivan Iliev Georgiev', '9510104512', 'Sofia-Liylin', '0894123456', '11') ,
91 ('Elena Petrova Petrova', '9505052154', 'Sofia-Mladost 3', '0897852412', '11') ,
92 ('Ivan Iliev Iliev', '9510104542', 'Sofia-Mladost 3', '0894123457', '11') ,
93 ('Maria Hristova Dimova', '9510104547', 'Sofia-Mladost 4', '0894123442', '11') ,
94 ('Antoaneta Ivanova Georgieva', '9411104547', 'Sofia-Krasno selo', '0874526235', '10');
95
96INSERT INTO sportGroups
97VALUES (NULL, 'Sofia-Mladost 1', 'Monday', '08:00:00', 1, 1 ) ,
98 (NULL, 'Sofia-Mladost 1', 'Monday', '09:30:00', 1, 2 ) ,
99 (NULL, 'Sofia-Liylin 7', 'Sunday', '08:00:00', 2, 1) ,
100 (NULL, 'Sofia-Liylin 7', 'Sunday', '09:30:00', 2, 2) ,
101 (NULL, 'Plovdiv', 'Monday', '12:00:00', '1', '1');
102
103INSERT INTO student_sport
104VALUES (1, 1),
105 (2, 1),
106 (3, 1),
107 (4, 2),
108 (5, 2),
109 (6, 2),
110 (1, 3),
111 (2, 3),
112 (3, 3);
113
114INSERT INTO school_sport_clubs.`taxespayments`
115VALUES (NULL, '1', '1', '200', '1', 2015, now()),
116 (NULL, '1', '1', '200', '2', 2015, now()),
117 (NULL, '1', '1', '200', '3', 2015, now()),
118 (NULL, '1', '1', '200', '4', 2015, now()),
119 (NULL, '1', '1', '200', '5', 2015, now()),
120 (NULL, '1', '1', '200', '6', 2015, now()),
121 (NULL, '1', '1', '200', '7', 2015, now()),
122 (NULL, '1', '1', '200', '8', 2015, now()),
123 (NULL, '1', '1', '200', '9', 2015, now()),
124 (NULL, '1', '1', '200', '10', 2015, now()),
125 (NULL, '1', '1', '200', '11', 2015, now()),
126 (NULL, '1', '1', '200', '12', 2015, now()),
127 (NULL, '2', '1', '250', '1', 2015, now()),
128 (NULL, '2', '1', '250', '2', 2015, now()),
129 (NULL, '2', '1', '250', '3', 2015, now()),
130 (NULL, '2', '1', '250', '4', 2015, now()),
131 (NULL, '2', '1', '250', '5', 2015, now()),
132 (NULL, '2', '1', '250', '6', 2015, now()),
133 (NULL, '2', '1', '250', '7', 2015, now()),
134 (NULL, '2', '1', '250', '8', 2015, now()),
135 (NULL, '2', '1', '250', '9', 2015, now()),
136 (NULL, '2', '1', '250', '10', 2015, now()),
137 (NULL, '2', '1', '250', '11', 2015, now()),
138 (NULL, '2', '1', '250', '12', 2015, now()),
139 (NULL, '3', '1', '250', '1', 2015, now()),
140 (NULL, '3', '1', '250', '2', 2015, now()),
141 (NULL, '3', '1', '250', '3', 2015, now()),
142 (NULL, '3', '1', '250', '4', 2015, now()),
143 (NULL, '3', '1', '250', '5', 2015, now()),
144 (NULL, '3', '1', '250', '6', 2015, now()),
145 (NULL, '3', '1', '250', '7', 2015, now()),
146 (NULL, '3', '1', '250', '8', 2015, now()),
147 (NULL, '3', '1', '250', '9', 2015, now()),
148 (NULL, '3', '1', '250', '10', 2015, now()),
149 (NULL, '3', '1', '250', '11', 2015, now()),
150 (NULL, '3', '1', '250', '12', 2015, now()),
151 (NULL, '1', '2', '200', '1', 2015, now()),
152 (NULL, '1', '2', '200', '2', 2015, now()),
153 (NULL, '1', '2', '200', '3', 2015, now()),
154 (NULL, '1', '2', '200', '4', 2015, now()),
155 (NULL, '1', '2', '200', '5', 2015, now()),
156 (NULL, '1', '2', '200', '6', 2015, now()),
157 (NULL, '1', '2', '200', '7', 2015, now()),
158 (NULL, '1', '2', '200', '8', 2015, now()),
159 (NULL, '1', '2', '200', '9', 2015, now()),
160 (NULL, '1', '2', '200', '10', 2015, now()),
161 (NULL, '1', '2', '200', '11', 2015, now()),
162 (NULL, '1', '2', '200', '12', 2015, now()),
163 (NULL, '4', '2', '200', '1', 2015, now()),
164 (NULL, '4', '2', '200', '2', 2015, now()),
165 (NULL, '4', '2', '200', '3', 2015, now()),
166 (NULL, '4', '2', '200', '4', 2015, now()),
167 (NULL, '4', '2', '200', '5', 2015, now()),
168 (NULL, '4', '2', '200', '6', 2015, now()),
169 (NULL, '4', '2', '200', '7', 2015, now()),
170 (NULL, '4', '2', '200', '8', 2015, now()),
171 (NULL, '4', '2', '200', '9', 2015, now()),
172 (NULL, '4', '2', '200', '10', 2015, now()),
173 (NULL, '4', '2', '200', '11', 2015, now()),
174 (NULL, '4', '2', '200', '12', 2015, now()),
175 /**2014**/
176 (NULL, '1', '1', '200', '1', 2014, now()),
177 (NULL, '1', '1', '200', '2', 2014, now()),
178 (NULL, '1', '1', '200', '3', 2014, now()),
179 (NULL, '1', '1', '200', '4', 2014, now()),
180 (NULL, '1', '1', '200', '5', 2014, now()),
181 (NULL, '1', '1', '200', '6', 2014, now()),
182 (NULL, '1', '1', '200', '7', 2014, now()),
183 (NULL, '1', '1', '200', '8', 2014, now()),
184 (NULL, '1', '1', '200', '9', 2014, now()),
185 (NULL, '1', '1', '200', '10', 2014, now()),
186 (NULL, '1', '1', '200', '11', 2014, now()),
187 (NULL, '1', '1', '200', '12', 2014, now()),
188 (NULL, '2', '1', '250', '1', 2014, now()),
189 (NULL, '2', '1', '250', '2', 2014, now()),
190 (NULL, '2', '1', '250', '3', 2014, now()),
191 (NULL, '2', '1', '250', '4', 2014, now()),
192 (NULL, '2', '1', '250', '5', 2014, now()),
193 (NULL, '2', '1', '250', '6', 2014, now()),
194 (NULL, '2', '1', '250', '7', 2014, now()),
195 (NULL, '2', '1', '250', '8', 2014, now()),
196 (NULL, '2', '1', '250', '9', 2014, now()),
197 (NULL, '2', '1', '250', '10', 2014, now()),
198 (NULL, '2', '1', '250', '11', 2014, now()),
199 (NULL, '2', '1', '250', '12', 2014, now()),
200 (NULL, '3', '1', '250', '1', 2014, now()),
201 (NULL, '3', '1', '250', '2', 2014, now()),
202 (NULL, '3', '1', '250', '3', 2014, now()),
203 (NULL, '3', '1', '250', '4', 2014, now()),
204 (NULL, '3', '1', '250', '5', 2014, now()),
205 (NULL, '3', '1', '250', '6', 2014, now()),
206 (NULL, '3', '1', '250', '7', 2014, now()),
207 (NULL, '3', '1', '250', '8', 2014, now()),
208 (NULL, '3', '1', '250', '9', 2014, now()),
209 (NULL, '3', '1', '250', '10', 2014, now()),
210 (NULL, '3', '1', '250', '11', 2014, now()),
211 (NULL, '3', '1', '250', '12', 2014, now()),
212 (NULL, '1', '2', '200', '1', 2014, now()),
213 (NULL, '1', '2', '200', '2', 2014, now()),
214 (NULL, '1', '2', '200', '3', 2014, now()),
215 (NULL, '1', '2', '200', '4', 2014, now()),
216 (NULL, '1', '2', '200', '5', 2014, now()),
217 (NULL, '1', '2', '200', '6', 2014, now()),
218 (NULL, '1', '2', '200', '7', 2014, now()),
219 (NULL, '1', '2', '200', '8', 2014, now()),
220 (NULL, '1', '2', '200', '9', 2014, now()),
221 (NULL, '1', '2', '200', '10', 2014, now()),
222 (NULL, '1', '2', '200', '11', 2014, now()),
223 (NULL, '1', '2', '200', '12', 2014, now()),
224 (NULL, '4', '2', '200', '1', 2014, now()),
225 (NULL, '4', '2', '200', '2', 2014, now()),
226 (NULL, '4', '2', '200', '3', 2014, now()),
227 (NULL, '4', '2', '200', '4', 2014, now()),
228 (NULL, '4', '2', '200', '5', 2014, now()),
229 (NULL, '4', '2', '200', '6', 2014, now()),
230 (NULL, '4', '2', '200', '7', 2014, now()),
231 (NULL, '4', '2', '200', '8', 2014, now()),
232 (NULL, '4', '2', '200', '9', 2014, now()),
233 (NULL, '4', '2', '200', '10', 2014, now()),
234 (NULL, '4', '2', '200', '11', 2014, now()),
235 (NULL, '4', '2', '200', '12', 2014, now()),
236 /**2016**/
237 (NULL, '1', '1', '200', '1', 2016, now()),
238 (NULL, '1', '1', '200', '2', 2016, now()),
239 (NULL, '1', '1', '200', '3', 2016, now()),
240 (NULL, '2', '1', '250', '1', 2016, now()),
241 (NULL, '3', '1', '250', '1', 2016, now()),
242 (NULL, '3', '1', '250', '2', 2016, now()),
243 (NULL, '1', '2', '200', '1', 2016, now()),
244 (NULL, '1', '2', '200', '2', 2016, now()),
245 (NULL, '1', '2', '200', '3', 2016, now()),
246 (NULL, '4', '2', '200', '1', 2016, now()),
247 (NULL, '4', '2', '200', '2', 2016, now());
248
249
250
251
252SELECT
253 st.name, sp.name
254FROM
255 students AS st
256 JOIN
257 sports AS sp ON st.id IN (SELECT
258 ss.student_id
259 FROM
260 student_sport AS ss
261 WHERE
262 ss.sportGroup_id IN (SELECT
263 sg.id
264 FROM
265 sportgroups AS sg
266 WHERE
267 sg.sport_id = sp.id));
268
269SELECT
270 students.name, sports.name
271FROM
272 students
273 JOIN
274 student_sport AS ss ON students.id = ss.student_id
275 JOIN
276 sportgroups ON sportgroups.id = ss.sportGroup_id
277 JOIN
278 sports ON sports.id = sportgroups.sport_id;
279
280
281SELECT
282 st.name, c.name, c.egn
283FROM
284 students AS st
285 JOIN
286 coaches AS c ON st.id IN (SELECT
287 tax.student_id
288 FROM
289 taxespayments AS tax
290 WHERE
291 tax.group_id IN (SELECT
292 sg.id
293 FROM
294 sportgroups AS sg
295 WHERE
296 sg.coach_id = c.id));
297
298
299SELECT DISTINCT
300 st.name, SUM(tax.paymentAmount)
301FROM
302 students AS st
303 JOIN
304 taxespayments AS tax ON st.id = tax.student_id
305WHERE
306 st.id = 1
307ORDER BY tax.student_id;
308
309SELECT
310 s.name, SUM(paymentAmount)
311FROM
312 sports AS s
313 JOIN
314 taxespayments AS tp ON s.id IN (SELECT
315 sg.sport_id
316 FROM
317 sportgroups AS sg
318 WHERE
319 sg.id = tp.group_id);
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339SELECT
340 st1.name AS Student1, st2.name AS Student2, sp.name AS Sport
341FROM
342 students AS st1
343 JOIN
344 students AS st2 ON st1.id > st2.id
345 JOIN
346 sports AS sp ON (st1.id IN (SELECT
347 ss.student_id
348 FROM
349 student_sport AS ss
350 WHERE
351 ss.sportGroup_id IN (SELECT
352 sg.id
353 FROM
354 sportgroups AS sg
355 WHERE
356 sg.sport_id = sp.id))
357 AND (st2.id IN (SELECT
358 ss.student_id
359 FROM
360 student_sport AS ss
361 WHERE
362 ss.sportGroup_id IN (SELECT
363 sg.id
364 FROM
365 sportgroups AS sg
366 WHERE
367 sg.sport_id = sp.id))))
368WHERE
369 st1.id IN (SELECT
370 student_id
371 FROM
372 student_sport
373 WHERE
374 sportGroup_id IN (SELECT
375 sportGroup_id
376 FROM
377 student_sport
378 WHERE
379 student_id = st2.id))
380ORDER BY Sport;
381
382
383
384SELECT
385 st1.name AS Student1, st2.name AS Student2, sp.name AS Sport
386FROM
387 students AS st1
388 JOIN
389 students AS st2 ON st1.id > st2.id
390 JOIN
391 sports AS sp ON (st1.id IN (SELECT
392 ss.student_id
393 FROM
394 student_sport AS ss
395 WHERE
396 ss.sportGroup_id IN (SELECT
397 sg.id
398 FROM
399 sportgroups AS sg
400 WHERE
401 sg.sport_id = sp.id))
402 AND (st2.id IN (SELECT
403 ss.student_id
404 FROM
405 student_sport AS ss
406 WHERE
407 ss.sportGroup_id IN (SELECT
408 sg.id
409 FROM
410 sportgroups AS sg
411 WHERE
412 sg.sport_id = sp.id))));
413
414
415SELECT
416 st.name, sp.name
417FROM
418 students AS st
419 JOIN
420 sports AS sp ON st.id IN (SELECT
421 ss.student_id
422 FROM
423 student_sport AS ss
424 WHERE
425 ss.sportGroup_id IN (SELECT
426 sg.id
427 FROM
428 sportgroups AS sg
429 WHERE
430 sg.sport_id = sp.id));
431
432SELECT
433 sg.location, sp.name
434FROM
435 sportgroups AS sg
436 LEFT JOIN
437 sports AS sp ON sg.sport_id = sp.id
438UNION (SELECT
439 sg.location, sp.name
440FROM
441 sportgroups AS sg
442 RIGHT JOIN
443 sports AS sp ON sg.sport_id = sp.id);
444
445
446
447
448SELECT
449 students.name, sports.name, coaches.name
450FROM
451 students
452 JOIN
453 sports ON students.id IN (SELECT
454 student_sport.student_id
455 FROM
456 student_sport
457 WHERE
458 student_sport.sportGroup_id IN (SELECT
459 sportgroups.id
460 FROM
461 sportgroups
462 WHERE
463 sportgroups.sport_id = sports.id))
464 JOIN
465 coaches ON coaches.id = students.id
466WHERE
467 coaches.name LIKE '%Ivan%'
468 AND students.class = '11';
469
470SELECT
471 students.name, sports.name
472FROM
473 students
474 JOIN
475 sports ON students.id IN (SELECT
476 student_sport.student_id
477 FROM
478 student_sport
479 WHERE
480 student_sport.sportGroup_id IN (SELECT
481 sportgroups.id
482 FROM
483 sportgroups
484 WHERE
485 sportgroups.sport_id = sports.id))
486WHERE
487 students.class = 11
488 AND sports.name = 'Football';
489
490SELECT
491 st.name, sp.name, tax.year
492FROM
493 students AS st
494 JOIN
495 student_sport AS ss ON st.id = ss.student_id
496 JOIN
497 sportgroups AS sg ON sg.id = ss.sportGroup_id
498 JOIN
499 sports AS sp ON sp.id = sg.sport_id
500 JOIN
501 taxespayments AS tax ON st.id = tax.student_id
502WHERE
503 st.class = '11' AND sp.name = 'Football';
504
505
506
507SELECT
508 st.name, sg.hourOfTraining
509FROM
510 students AS st
511 JOIN
512 sportgroups AS sg ON st.id IN (SELECT
513 tax.student_id
514 FROM
515 taxespayments AS tax
516 WHERE
517 tax.group_id = sg.id);
518
519
520
521SELECT
522 c.name, sp.name
523FROM
524 coaches AS c
525 JOIN
526 sports AS sp ON c.id IN (SELECT
527 sg.coach_id
528 FROM
529 sportgroups AS sg
530 WHERE
531 sg.sport_id = sp.id);
532
533
534
535SELECT
536 st.name, st.address, SUM(paymentAmount)
537FROM
538 students AS st
539 JOIN
540 taxespayments AS tax ON st.id = tax.student_id
541WHERE
542 st.id = 1 OR st.id = 2 OR st.id = 3
543 OR st.id = 4
544 OR st.id = 5
545 OR st.id = 6
546GROUP BY paymentAmount
547LIMIT 6;
548
549
550SELECT
551 group_id, SUM(paymentAmount)
552FROM
553 taxespayments
554GROUP BY SUM(paymentAmount);
555
556UPDATE taxespayments
557SET
558 group_id = '3'
559WHERE
560 id = 15;
561
562
563
564
565SELECT
566 st.name, sp.name
567FROM
568 students AS st
569 JOIN
570 sports AS sp ON st.id IN (SELECT
571 ss.student_id
572 FROM
573 student_sport AS ss
574 WHERE
575 ss.sportGroup_id IN (SELECT
576 sg.id
577 FROM
578 sportgroups AS sg
579 WHERE
580 sg.sport_id = sp.id));
581
582
583select st.name,sp.name
584from students as st
585join (sports as sp )
586on st.id in(
587 select tax.student_id
588 from taxespayments as tax
589 where tax.group_id in(
590 select sg.id
591 from sportgroups as sg
592 where sg.id=sp.id))
593 where sp.name like 'football';