· 8 years ago · Nov 29, 2017, 07:02 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
70INSERT INTO sportGroups
71VALUES (NULL, 'Sofia-Mladost 1', 'Monday', '08:00:00', 1, 1 ) ,
72 (NULL, 'Sofia-Mladost 1', 'Monday', '09:30:00', 1, 1 ) ,
73 (NULL, 'Sofia-Liylin 7', 'Sunday', '09:00:00', 2, 2) ;
74
75INSERT INTO student_sport
76VALUES (1, 1),
77 (2, 1),
78 (3, 1),
79 (4, 2),
80 (5, 2),
81 (6, 2);
82
83select * from sports;
84select * from coaches;
85select * from students;
86select * from students where name like 'I%';
87select * from students where egn like '9401150045';
88select * from students where egn = '9401150045';
89select * from students ORDER BY id DESC limit 3;
90
91
92
93
94
95INSERT INTO sports(name)
96 values('karate');
97
98INSERT INTO sports
99 values(null, 'karate');
100
101INSERT INTO coaches
102 values(null, 'Stoyan Petrov Ivanov', '0012345678');
103
104INSERT INTO students
105 values(null, 'Ivan Petrov', '0123456779', 'Georgi Rakovski', '0878656565', 11);
106
107UPDATE coaches
108 set name = 'Ivan Petrov Ivanov'
109 where id=7;