eZUzeWYZ

· 8 years ago · Apr 14, 2018, 08:12 AM
1CREATE DATABASE SCHOOL_SPORT_CLUBS;
2USE SCHOOL_SPORT_CLUBS;
3 
4CREATE TABLE students(
5id INT AUTO_INCREMENT PRIMARY KEY,
6name VARCHAR(255) NOT NULL,
7egn VARCHAR(10) NOT NULL UNIQUE,
8address VARCHAR(255) NOT NULL,
9phone VARCHAR(20) NULL DEFAULT NULL,
10class VARCHAR(10) NULL DEFAULT NULL  
11);
12 
13CREATE TABLE sportGroups(
14id INT AUTO_INCREMENT PRIMARY KEY,
15location VARCHAR(255) NOT NULL,
16dayOfWeek ENUM('Monday','Tuesday','Wednesday','Thursday','Friday','Saturday','Sunday'),
17hourOfTraining TIME NOT NULL,
18UNIQUE KEY(location,dayOfWeek,hourOfTraining)
19);
20 
21CREATE TABLE student_sport(
22student_id int not null,  
23CONSTRAINT FOREIGN KEY (student_id) REFERENCES students(id),
24sportGroup_id int not null,
25CONSTRAINT FOREIGN KEY (sportGroup_id) REFERENCES sportGroups(id),
26PRIMARY KEY(student_id,sportGroup_id)
27);
28 
29CREATE TABLE sports(
30id INT AUTO_INCREMENT PRIMARY KEY,
31name VARCHAR(255) NOT NULL
32);
33 
34ALTER TABLE sportGroups
35ADD sport_id INT NOT NULL;
36 
37ALTER TABLE sportGroups
38ADD CONSTRAINT FOREIGN KEY(sport_id) REFERENCES sports(id);
39 
40CREATE TABLE coaches(
41id INT AUTO_INCREMENT PRIMARY KEY,
42name VARCHAR(255) NOT NULL,
43egn VARCHAR(10) NOT NULL UNIQUE
44);
45 
46ALTER TABLE sportGroups
47ADD coach_id INT;
48 
49ALTER TABLE sportGroups
50ADD CONSTRAINT FOREIGN KEY (coach_id) REFERENCES coaches(id);
51
52USE school_sport_clubs;
53 
54DROP TABLE IF EXISTS taxesPayments;
55CREATE TABLE taxesPayments(
56id INT AUTO_INCREMENT PRIMARY KEY,
57student_id INT NOT NULL,
58group_id INT NOT NULL,
59paymentAmount double NOT NULL,
60month TINYINT,
61year YEAR,
62dateOfPayment datetime not null,
63CONSTRAINT FOREIGN KEY (student_id) references students(id),
64CONSTRAINT FOREIGN KEY (group_id) references sportgroups(id)
65);
66 
67 
68 
69 
70DROP TABLE IF EXISTS salaryPayments;
71CREATE TABLE salaryPayments(
72id INT AUTO_INCREMENT PRIMARY KEY,
73coach_id INT NOT NULL,
74month TINYINT,
75year YEAR,
76salaryAmount double,
77dateOfPayment datetime not null,
78CONSTRAINT FOREIGN KEY (coach_id) references coaches(id),
79UNIQUE KEY(`coach_id`,`month`,`year`)
80);
81
82INSERT INTO sports (`name`) VALUES ('Football');
83INSERT INTO sports (`name`) VALUES ('Volleyball');
84INSERT INTO sports (name) VALUES (BASKETBALL);
85
86INSERT INTO `coaches` (`name`, `egn`)
87VALUES ('Ð˜Ð²Ð°Ð½ ÐŸÐµÑ‚ÐºÐ¾Ð²', '7509041245'),
88('Ð“ÐµÐ¾Ñ€Ð³Ð¸ Ð˜Ð²Ð°Ð½Ð¾Ð² Ð¢Ð¾Ð´Ð¾Ñ€Ð¾Ð²', '8010091245'),
89('Ð˜Ð»Ð¸ÑÐ½ Ð¢Ð¾Ð´Ð¾Ñ€Ð¾Ð² Ð“ÐµÐ¾Ñ€Ð³Ð¸ÐµÐ²', '8407106352');
90(Ð˜Ð’ÐÐ Ð¢ÐžÐ”ÐžÐ ÐžÐ’ ÐŸÐ•Ð¢Ð ÐžÐ’, 7869854750);
91
92INSERT INTO students (name, egn, address, phone, class) 
93VALUES ('Iliyan Ivanov', '9401150045', 'Sofia-Mladost 1', '0893452120', '10'),
94('Ivan Iliev Georgiev', '9510104512', 'Sofia-Liylin', '0894123456', '11'),
95('Elena Petrova Petrova', '9505052154', 'Sofia-Mladost 3', '0897852412', '11'),
96('Ivan Iliev Iliev', '9510104542', 'Sofia-Mladost 3', '0894123457', '11'),
97('Maria Hristova Dimova', '9510104547', 'Sofia-Mladost 4', '0894123442', '11'),
98('Antoaneta Ivanova Georgieva', '9411104547', 'Sofia-Krasno selo', '0874526235', '10');
99
100INSERT INTO school_sport_clubs.`student_sport` (student_id, sportGroup_id) VALUES (1, 1),
101 (2, 1),
102 (3, 1),
103 (4, 2),
104 (5, 2),
105 (6, 2);
106
107truncate table `school_sport_clubs`.`taxespayments`;
108/**2015**/
109INSERT INTO `school_sport_clubs`.`taxespayments` (`student_id`, `group_id`, `paymentAmount`, `month`, `year`,`dateOfPayment`) VALUES ('1', '1', '200', '1', 2015, now()),
110('1', '1', '200', '2', 2015, now()),
111('1', '1', '200', '3', 2015, now()),
112('1', '1', '200', '4', 2015, now()),
113('1', '1', '200', '5', 2015, now()),
114('1', '1', '200', '6', 2015, now()),
115('1', '1', '200', '7', 2015, now()),
116('1', '1', '200', '8', 2015, now()),
117('1', '1', '200', '9', 2015, now()),
118('1', '1', '200', '10', 2015, now()),
119('1', '1', '200', '11', 2015, now()),
120('1', '1', '200', '12', 2015, now()),
121
122('2', '1', '250', '1', 2015, now()),
123('2', '1', '250', '2', 2015, now()),
124('2', '1', '250', '3', 2015, now()),
125('2', '1', '250', '4', 2015, now()),
126('2', '1', '250', '5', 2015, now()),
127('2', '1', '250', '6', 2015, now()),
128('2', '1', '250', '7', 2015, now()),
129('2', '1', '250', '8', 2015, now()),
130('2', '1', '250', '9', 2015, now()),
131('2', '1', '250', '10', 2015, now()),
132('2', '1', '250', '11', 2015, now()),
133('2', '1', '250', '12', 2015, now()),
134
135('3', '1', '250', '1', 2015, now()),
136('3', '1', '250', '2', 2015, now()),
137('3', '1', '250', '3', 2015, now()),
138('3', '1', '250', '4', 2015, now()),
139('3', '1', '250', '5', 2015, now()),
140('3', '1', '250', '6', 2015, now()),
141('3', '1', '250', '7', 2015, now()),
142('3', '1', '250', '8', 2015, now()),
143('3', '1', '250', '9', 2015, now()),
144('3', '1', '250', '10', 2015, now()),
145('3', '1', '250', '11', 2015, now()),
146('3', '1', '250', '12', 2015, now()),
147
148
149('1', '2', '200', '1', 2015, now()),
150('1', '2', '200', '2', 2015, now()),
151('1', '2', '200', '3', 2015, now()),
152('1', '2', '200', '4', 2015, now()),
153('1', '2', '200', '5', 2015, now()),
154('1', '2', '200', '6', 2015, now()),
155('1', '2', '200', '7', 2015, now()),
156('1', '2', '200', '8', 2015, now()),
157('1', '2', '200', '9', 2015, now()),
158('1', '2', '200', '10', 2015, now()),
159('1', '2', '200', '11', 2015, now()),
160('1', '2', '200', '12', 2015, now()),
161
162('4', '2', '200', '1', 2015, now()),
163('4', '2', '200', '2', 2015, now()),
164('4', '2', '200', '3', 2015, now()),
165('4', '2', '200', '4', 2015, now()),
166('4', '2', '200', '5', 2015, now()),
167('4', '2', '200', '6', 2015, now()),
168('4', '2', '200', '7', 2015, now()),
169('4', '2', '200', '8', 2015, now()),
170('4', '2', '200', '9', 2015, now()),
171('4', '2', '200', '10', 2015, now()),
172('4', '2', '200', '11', 2015, now()),
173('4', '2', '200', '12', 2015, now()),
174/**2014**/
175('1', '1', '200', '1', 2014, now()),
176('1', '1', '200', '2', 2014, now()),
177('1', '1', '200', '3', 2014, now()),
178('1', '1', '200', '4', 2014, now()),
179('1', '1', '200', '5', 2014, now()),
180('1', '1', '200', '6', 2014, now()),
181('1', '1', '200', '7', 2014, now()),
182('1', '1', '200', '8', 2014, now()),
183('1', '1', '200', '9', 2014, now()),
184('1', '1', '200', '10', 2014, now()),
185('1', '1', '200', '11', 2014, now()),
186('1', '1', '200', '12', 2014, now()),
187
188('2', '1', '250', '1', 2014, now()),
189('2', '1', '250', '2', 2014, now()),
190('2', '1', '250', '3', 2014, now()),
191('2', '1', '250', '4', 2014, now()),
192('2', '1', '250', '5', 2014, now()),
193('2', '1', '250', '6', 2014, now()),
194('2', '1', '250', '7', 2014, now()),
195('2', '1', '250', '8', 2014, now()),
196('2', '1', '250', '9', 2014, now()),
197('2', '1', '250', '10', 2014, now()),
198('2', '1', '250', '11', 2014, now()),
199('2', '1', '250', '12', 2014, now()),
200
201('3', '1', '250', '1', 2014, now()),
202('3', '1', '250', '2', 2014, now()),
203('3', '1', '250', '3', 2014, now()),
204('3', '1', '250', '4', 2014, now()),
205('3', '1', '250', '5', 2014, now()),
206('3', '1', '250', '6', 2014, now()),
207('3', '1', '250', '7', 2014, now()),
208('3', '1', '250', '8', 2014, now()),
209('3', '1', '250', '9', 2014, now()),
210('3', '1', '250', '10', 2014, now()),
211('3', '1', '250', '11', 2014, now()),
212('3', '1', '250', '12', 2014, now()),
213
214
215('1', '2', '200', '1', 2014, now()),
216('1', '2', '200', '2', 2014, now()),
217('1', '2', '200', '3', 2014, now()),
218('1', '2', '200', '4', 2014, now()),
219('1', '2', '200', '5', 2014, now()),
220('1', '2', '200', '6', 2014, now()),
221('1', '2', '200', '7', 2014, now()),
222('1', '2', '200', '8', 2014, now()),
223('1', '2', '200', '9', 2014, now()),
224('1', '2', '200', '10', 2014, now()),
225('1', '2', '200', '11', 2014, now()),
226('1', '2', '200', '12', 2014, now()),
227
228('4', '2', '200', '1', 2014, now()),
229('4', '2', '200', '2', 2014, now()),
230('4', '2', '200', '3', 2014, now()),
231('4', '2', '200', '4', 2014, now()),
232('4', '2', '200', '5', 2014, now()),
233('4', '2', '200', '6', 2014, now()),
234('4', '2', '200', '7', 2014, now()),
235('4', '2', '200', '8', 2014, now()),
236('4', '2', '200', '9', 2014, now()),
237('4', '2', '200', '10', 2014, now()),
238('4', '2', '200', '11', 2014, now()),
239('4', '2', '200', '12', 2014, now()),
240
241
242/**2016**/
243('1', '1', '200', '1', 2016, now()),
244('1', '1', '200', '2', 2016, now()),
245('1', '1', '200', '3', 2016, now()),
246
247
248('2', '1', '250', '1', 2016, now()),
249
250('3', '1', '250', '1', 2016, now()),
251('3', '1', '250', '2', 2016, now()),
252
253
254
255('1', '2', '200', '1', 2016, now()),
256('1', '2', '200', '2', 2016, now()),
257('1', '2', '200', '3', 2016, now()),
258
259
260('4', '2', '200', '1', 2016, now()),
261('4', '2', '200', '2', 2016, now());