· 7 years ago · Dec 10, 2018, 04:00 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
49INSERT INTO sports
50VALUES (NULL, 'Football') ,
51 (NULL, 'Volleyball'),
52 (NULL, 'Tennis');
53
54INSERT INTO coaches
55VALUES (NULL, 'Ivan Todorov Petkov', '7509041245') ,
56 (NULL, 'georgi Ivanov Todorov', '8010091245') ,
57 (NULL, 'Ilian Todorov Georgiev', '8407106352') ,
58 (NULL, 'Petar Slavkov Yordanov', '7010102045') ,
59 (NULL, 'Todor Ivanov Ivanov', '8302160980') ,
60 (NULL, 'Slavi Petkov Petkov', '7106041278');
61
62INSERT INTO students (name, egn, address, phone, class)
63VALUES ('Iliyan Ivanov', '9401150045', 'Sofia-Mladost 1', '0893452120', '10') ,
64 ('Ivan Iliev Georgiev', '9510104512', 'Sofia-Liylin', '0894123456', '11') ,
65 ('Elena Petrova Petrova', '9505052154', 'Sofia-Mladost 3', '0897852412', '11') ,
66 ('Ivan Iliev Iliev', '9510104542', 'Sofia-Mladost 3', '0894123457', '11') ,
67 ('Maria Hristova Dimova', '9510104547', 'Sofia-Mladost 4', '0894123442', '11') ,
68 ('Antoaneta Ivanova Georgieva', '9411104547', 'Sofia-Krasno selo', '0874526235', '10') ,
69 ('sadIliyan Ivanov', '9401250045', 'Sofdd-Mladost 1', '1193452120', '11');
70
71INSERT INTO sportGroups
72VALUES (NULL, 'Sofia-Mladost 1', 'Monday', '08:00:00', 1, 1 ) ,
73 (NULL, 'Sofia-Mladost 1', 'Monday', '09:30:00', 1, 2 ) ,
74 (NULL, 'Sofia-Liylin 7', 'Sunday', '08:00:00', 2, 1) ,
75 (NULL, 'Sofia-Liylin 7', 'Sunday', '09:30:00', 2, 2) ,
76 (NULL, 'Plovdiv', 'Monday', '12:00:00', 1, 1);
77
78SELECT
79 *
80FROM
81 students
82WHERE
83 id >= 2 AND id < 5
84ORDER BY id DESC;
85
86update sports
87set name ='Ch0rkata'
88where id=1;