· 8 years ago · Mar 27, 2018, 05:46 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 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 (NULL, 'Karate') ,
80 (NULL, 'Taekwon-do');
81
82INSERT INTO coaches
83VALUES (NULL, 'Ivan Todorov Petkov', '7509041245') ,
84 (NULL, 'georgi Ivanov Todorov', '8010091245') ,
85 (NULL, 'Ilian Todorov Georgiev', '8407106352') ,
86 (NULL, 'Petar Slavkov Yordanov', '7010102045') ,
87 (NULL, 'Todor Ivanov Ivanov', '8302160980') ,
88 (NULL, 'Slavi Petkov Petkov', '7106041278');
89
90INSERT INTO students (name, egn, address, phone, class)
91VALUES ('Iliyan Ivanov', '9401150045', 'Sofia-Mladost 1', '0893452120', '10') ,
92 ('Ivan Iliev Georgiev', '9510104512', 'Sofia-Liylin', '0894123456', '11') ,
93 ('Elena Petrova Petrova', '9505052154', 'Sofia-Mladost 3', '0897852412', '11') ,
94 ('Ivan Iliev Iliev', '9510104542', 'Sofia-Mladost 3', '0894123457', '11') ,
95 ('Maria Hristova Dimova', '9510104547', 'Sofia-Mladost 4', '0894123442', '11') ,
96 ('Antoaneta Ivanova Georgieva', '9411104547', 'Sofia-Krasno selo', '0874526235', '10');
97
98INSERT INTO sportGroups
99VALUES (NULL, 'Sofia-Mladost 1', 'Monday', '08:00:00', 1, 1 ) ,
100 (NULL, 'Sofia-Mladost 1', 'Monday', '09:30:00', 1, 2 ) ,
101 (NULL, 'Sofia-Liylin 7', 'Sunday', '08:00:00', 2, 1) ,
102 (NULL, 'Sofia-Liylin 7', 'Sunday', '09:30:00', 2, 2) ,
103 (NULL, 'Plovdiv', 'Monday', '12:00:00', 1, 1);
104
105INSERT INTO student_sport
106VALUES (1, 1),
107 (2, 1),
108 (3, 1),
109 (4, 2),
110 (5, 2),
111 (6, 2),
112 (1, 3),
113 (2, 3),
114 (3, 3);
115
116INSERT INTO taxesPayments
117VALUES (NULL, '1', '1', '200', '1', 2015, now()),
118 (NULL, '1', '1', '200', '2', 2015, now()),
119 (NULL, '1', '1', '200', '3', 2015, now()),
120 (NULL, '1', '1', '200', '4', 2015, now()),
121 (NULL, '1', '1', '200', '5', 2015, now()),
122 (NULL, '1', '1', '200', '6', 2015, now()),
123 (NULL, '1', '1', '200', '7', 2015, now()),
124 (NULL, '1', '1', '200', '8', 2015, now()),
125 (NULL, '1', '1', '200', '9', 2015, now()),
126 (NULL, '1', '1', '200', '10', 2015, now()),
127 (NULL, '1', '1', '200', '11', 2015, now()),
128 (NULL, '1', '1', '200', '12', 2015, now()),
129 (NULL, '2', '1', '250', '1', 2015, now()),
130 (NULL, '2', '1', '250', '2', 2015, now()),
131 (NULL, '2', '1', '250', '3', 2015, now()),
132 (NULL, '2', '1', '250', '4', 2015, now()),
133 (NULL, '2', '1', '250', '5', 2015, now()),
134 (NULL, '2', '1', '250', '6', 2015, now()),
135 (NULL, '2', '1', '250', '7', 2015, now()),
136 (NULL, '2', '1', '250', '8', 2015, now()),
137 (NULL, '2', '1', '250', '9', 2015, now()),
138 (NULL, '2', '1', '250', '10', 2015, now()),
139 (NULL, '2', '1', '250', '11', 2015, now()),
140 (NULL, '2', '1', '250', '12', 2015, now()),
141 (NULL, '3', '1', '250', '1', 2015, now()),
142 (NULL, '3', '1', '250', '2', 2015, now()),
143 (NULL, '3', '1', '250', '3', 2015, now()),
144 (NULL, '3', '1', '250', '4', 2015, now()),
145 (NULL, '3', '1', '250', '5', 2015, now()),
146 (NULL, '3', '1', '250', '6', 2015, now()),
147 (NULL, '3', '1', '250', '7', 2015, now()),
148 (NULL, '3', '1', '250', '8', 2015, now()),
149 (NULL, '3', '1', '250', '9', 2015, now()),
150 (NULL, '3', '1', '250', '10', 2015, now()),
151 (NULL, '3', '1', '250', '11', 2015, now()),
152 (NULL, '3', '1', '250', '12', 2015, now()),
153 (NULL, '1', '2', '200', '1', 2015, now()),
154 (NULL, '1', '2', '200', '2', 2015, now()),
155 (NULL, '1', '2', '200', '3', 2015, now()),
156 (NULL, '1', '2', '200', '4', 2015, now()),
157 (NULL, '1', '2', '200', '5', 2015, now()),
158 (NULL, '1', '2', '200', '6', 2015, now()),
159 (NULL, '1', '2', '200', '7', 2015, now()),
160 (NULL, '1', '2', '200', '8', 2015, now()),
161 (NULL, '1', '2', '200', '9', 2015, now()),
162 (NULL, '1', '2', '200', '10', 2015, now()),
163 (NULL, '1', '2', '200', '11', 2015, now()),
164 (NULL, '1', '2', '200', '12', 2015, now()),
165 (NULL, '4', '2', '200', '1', 2015, now()),
166 (NULL, '4', '2', '200', '2', 2015, now()),
167 (NULL, '4', '2', '200', '3', 2015, now()),
168 (NULL, '4', '2', '200', '4', 2015, now()),
169 (NULL, '4', '2', '200', '5', 2015, now()),
170 (NULL, '4', '2', '200', '6', 2015, now()),
171 (NULL, '4', '2', '200', '7', 2015, now()),
172 (NULL, '4', '2', '200', '8', 2015, now()),
173 (NULL, '4', '2', '200', '9', 2015, now()),
174 (NULL, '4', '2', '200', '10', 2015, now()),
175 (NULL, '4', '2', '200', '11', 2015, now()),
176 (NULL, '4', '2', '200', '12', 2015, now()),
177 /**2014**/
178 (NULL, '1', '1', '200', '1', 2014, now()),
179 (NULL, '1', '1', '200', '2', 2014, now()),
180 (NULL, '1', '1', '200', '3', 2014, now()),
181 (NULL, '1', '1', '200', '4', 2014, now()),
182 (NULL, '1', '1', '200', '5', 2014, now()),
183 (NULL, '1', '1', '200', '6', 2014, now()),
184 (NULL, '1', '1', '200', '7', 2014, now()),
185 (NULL, '1', '1', '200', '8', 2014, now()),
186 (NULL, '1', '1', '200', '9', 2014, now()),
187 (NULL, '1', '1', '200', '10', 2014, now()),
188 (NULL, '1', '1', '200', '11', 2014, now()),
189 (NULL, '1', '1', '200', '12', 2014, now()),
190 (NULL, '2', '1', '250', '1', 2014, now()),
191 (NULL, '2', '1', '250', '2', 2014, now()),
192 (NULL, '2', '1', '250', '3', 2014, now()),
193 (NULL, '2', '1', '250', '4', 2014, now()),
194 (NULL, '2', '1', '250', '5', 2014, now()),
195 (NULL, '2', '1', '250', '6', 2014, now()),
196 (NULL, '2', '1', '250', '7', 2014, now()),
197 (NULL, '2', '1', '250', '8', 2014, now()),
198 (NULL, '2', '1', '250', '9', 2014, now()),
199 (NULL, '2', '1', '250', '10', 2014, now()),
200 (NULL, '2', '1', '250', '11', 2014, now()),
201 (NULL, '2', '1', '250', '12', 2014, now()),
202 (NULL, '3', '1', '250', '1', 2014, now()),
203 (NULL, '3', '1', '250', '2', 2014, now()),
204 (NULL, '3', '1', '250', '3', 2014, now()),
205 (NULL, '3', '1', '250', '4', 2014, now()),
206 (NULL, '3', '1', '250', '5', 2014, now()),
207 (NULL, '3', '1', '250', '6', 2014, now()),
208 (NULL, '3', '1', '250', '7', 2014, now()),
209 (NULL, '3', '1', '250', '8', 2014, now()),
210 (NULL, '3', '1', '250', '9', 2014, now()),
211 (NULL, '3', '1', '250', '10', 2014, now()),
212 (NULL, '3', '1', '250', '11', 2014, now()),
213 (NULL, '3', '1', '250', '12', 2014, now()),
214 (NULL, '1', '2', '200', '1', 2014, now()),
215 (NULL, '1', '2', '200', '2', 2014, now()),
216 (NULL, '1', '2', '200', '3', 2014, now()),
217 (NULL, '1', '2', '200', '4', 2014, now()),
218 (NULL, '1', '2', '200', '5', 2014, now()),
219 (NULL, '1', '2', '200', '6', 2014, now()),
220 (NULL, '1', '2', '200', '7', 2014, now()),
221 (NULL, '1', '2', '200', '8', 2014, now()),
222 (NULL, '1', '2', '200', '9', 2014, now()),
223 (NULL, '1', '2', '200', '10', 2014, now()),
224 (NULL, '1', '2', '200', '11', 2014, now()),
225 (NULL, '1', '2', '200', '12', 2014, now()),
226 (NULL, '4', '2', '200', '1', 2014, now()),
227 (NULL, '4', '2', '200', '2', 2014, now()),
228 (NULL, '4', '2', '200', '3', 2014, now()),
229 (NULL, '4', '2', '200', '4', 2014, now()),
230 (NULL, '4', '2', '200', '5', 2014, now()),
231 (NULL, '4', '2', '200', '6', 2014, now()),
232 (NULL, '4', '2', '200', '7', 2014, now()),
233 (NULL, '4', '2', '200', '8', 2014, now()),
234 (NULL, '4', '2', '200', '9', 2014, now()),
235 (NULL, '4', '2', '200', '10', 2014, now()),
236 (NULL, '4', '2', '200', '11', 2014, now()),
237 (NULL, '4', '2', '200', '12', 2014, now()),
238 /**2016**/
239 (NULL, '1', '1', '200', '1', 2016, now()),
240 (NULL, '1', '1', '200', '2', 2016, now()),
241 (NULL, '1', '1', '200', '3', 2016, now()),
242 (NULL, '2', '1', '250', '1', 2016, now()),
243 (NULL, '3', '1', '250', '1', 2016, now()),
244 (NULL, '3', '1', '250', '2', 2016, now()),
245 (NULL, '1', '2', '200', '1', 2016, now()),
246 (NULL, '1', '2', '200', '2', 2016, now()),
247 (NULL, '1', '2', '200', '3', 2016, now()),
248 (NULL, '4', '2', '200', '1', 2016, now()),
249 (NULL, '4', '2', '200', '2', 2016, now());
250
251SELECT students.name, groups.dayofWeek, groups.location
252FROM school_sport_clubs.students AS students
253JOIN school_sport_clubs.student_sport AS student_groups
254ON student_groups.student_id = students.id
255JOIN school_sport_clubs.sportGroups AS groups
256ON student_groups.sportGroup_id = groups.id
257WHERE groups.location LIKE '%Liylin 7';
258
259SELECT COUNT(students.name) AS students, sports.name AS name_of_sport
260FROM school_sport_clubs.students AS students
261JOIN school_sport_clubs.student_sport AS student_sports
262ON student_sports.student_id = students.id
263JOIN school_sport_clubs.sportGroups AS sg
264ON student_sports.sportGroup_id = sg.id
265RIGHT JOIN school_sport_clubs.sports AS sports
266ON sg.sport_id = sports.id
267GROUP BY sports.name
268ORDER BY MIN(students.id);
269
270SELECT students.name AS students, sports.name AS name_of_sport
271FROM school_sport_clubs.students AS students
272JOIN school_sport_clubs.student_sport AS student_sports
273ON student_sports.student_id = students.id
274JOIN school_sport_clubs.sportGroups AS sg
275ON student_sports.sportGroup_id = sg.id
276RIGHT JOIN school_sport_clubs.sports AS sports
277ON sg.sport_id = sports.id;
278
279SELECT students.name, sportGroups.location
280FROM students
281JOIN sportGroups
282ON sportGroups.id IN (
283SELECT student_sport.sportGroup_id
284FROM student_sport
285WHERE student_sport.student_id = students.id);
286
287SELECT COUNT(sports.name), sportGroups.dayOfWeek
288FROM sports
289RIGHT JOIN sportGroups
290ON sportGroups.sport_id = sports.id
291GROUP BY sportGroups.dayOfWeek;
292
293SELECT COUNT(students.class), sportGroups.location
294FROM students
295JOIN student_sport
296ON student_sport.student_id = students.id
297JOIN sportGroups
298ON student_sport.sportGroup_id = sportGroups.id
299GROUP BY sportGroups.location;
300
301SELECT AVG(taxesPayments.paymentAmount) AS payment, sportGroups.location
302FROM taxesPayments
303JOIN sportGroups
304ON taxesPayments.group_id = sportGroups.id
305GROUP BY sportGroups.location
306ORDER BY payment;
307
308SELECT AVG(taxesPayments.paymentAmount) AS class_payment, students.class
309FROM taxesPayments
310RIGHT JOIN students
311ON taxesPayments.student_id = students.id
312GROUP BY students.class;
313
314SELECT coaches.name, coaches.egn
315FROM coaches
316JOIN sportGroups
317ON sportGroups.coach_id = coaches.id
318WHERE sportGroups.dayOfWeek = 'Monday';
319
320SELECT coaches.name, coaches.egn
321FROM coaches
322WHERE coaches.id IN (
323SELECT sportGroups.coach_id
324FROM sportGroups
325WHERE sportGroups.dayOfWeek = 'Monday');