· 7 years ago · Feb 28, 2019, 07:56 AM
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 studentClass 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
49INSERT INTO sports
50VALUES (NULL, 'Football') ,
51 (NULL, 'Volleyball') ,
52 (NULL, 'Tennis');
53
54INSERT INTO coaches
55VALUES (NULL, 'Ivan Todorov Petkov', '8302160980') ,
56 (NULL, 'Georgi Ivanov Todorov', '7010102045') ,
57 (NULL, 'Ilian Todorov Georgiev', '7509041245') ,
58 (NULL, 'Petar Slavkov Yordanov', '8010091245') ,
59 (NULL, 'Todor Ivanov Ivanov', '8407106352') ,
60 (NULL, 'Slavi Petkov Petkov', '7106041278');
61
62INSERT INTO students
63VALUES (NULL,'Iliyan Ivanov', '9401150045', 'Sofia-Mladost 1', '0893452120', '10') ,
64 (NULL,'Ivan Iliev Georgiev', '9510104512', 'Sofia-Liylin', '0894123456', '11') ,
65 (NULL,'Elena Petrova Petrova', '9505052154', 'Sofia-Mladost 3', '0897852412', '11') ,
66 (NULL,'Ivan Iliev Iliev', '9510104542', 'Sofia-Mladost 3', '0894123457', '11') ,
67 (NULL,'Maria Hristova Dimova', '9510104547', 'Sofia-Mladost 4', '0894123442', '11') ,
68 (NULL,'Antoaneta Ivanova Georgieva', '9411104547', 'Sofia-Krasno selo', '0874526235', '10');
69
70INSERT INTO sportGroups
71VALUES (NULL, 'Sofia-Mladost 1', 'Monday', '08:00:00', 1, 1 ) ,
72 (NULL, 'Sofia-Mladost 1', 'Monday', '09:30:00', 1, 2 ) ,
73 (NULL, 'Sofia-Liylin 7', 'Sunday', '08:00:00', 2, 1) ,
74 (NULL, 'Sofia-Liylin 7', 'Sunday', '09:30:00', 2, 2) ,
75 (NULL, 'Plovdiv', 'Monday', '12:00:00', 1, 1);
76
77INSERT INTO student_sport
78VALUES (1, 1) ,
79 (2, 1) ,
80 (3, 1) ,
81 (4, 2) ,
82 (5, 2) ,
83 (6, 2) ,
84 (1, 3) ,
85 (2, 3) ,
86 (3, 3);
87INSERT INTO coaches
88VALUES (NULL, 'Borislav Ivanov Arangelov', '9812187300');
89
90UPDATE coaches
91set name = 'Ivan Ivanov Ivanov'
92WHERE id=7;
93
94DELETE FROM coaches
95WHERE id=7;
96
97
98SELECT sports.name, sportGroups.location
99FROM sports RIGHT JOIN sportGroups
100ON sports.id = sportGroups.sport_id;
101
102SELECT sports.name, coaches.name
103FROM sports JOIN coaches
104ON sports.id IN(
105SELECT sport_id
106FROM sportgroups
107WHERE sportGroups.coach_id = coaches.id
108);
109
110SELECT DISTINCT sports.name, coaches.name
111FROM sports JOIN sportGroups
112ON sports.id = sportGroups.sport_id
113JOIN coaches
114ON coaches.id = sportGroups.coach_id;