9x19xHMN

· 8 years ago · Apr 14, 2018, 07:50 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);
51INSERT INTO coaches (name, egn) 
52VALUES ('Ð˜Ð²Ð°Ð½ ÐŸÐµÑ‚ÐºÐ¾Ð²', '7509041245'),
53('Ð“ÐµÐ¾Ñ€Ð³Ð¸ Ð˜Ð²Ð°Ð½Ð¾Ð² Ð¢Ð¾Ð´Ð¾Ñ€Ð¾Ð²', '8010091245'),
54('Ð˜Ð»Ð¸ÑÐ½ Ð¢Ð¾Ð´Ð¾Ñ€Ð¾Ð² Ð“ÐµÐ¾Ñ€Ð³Ð¸ÐµÐ²', '8407106352');
55
56USE school_sport_clubs;
57INSERT INTO `sports` (`name`) VALUES ('Football');
58INSERT INTO `sports` (`name`) VALUES ('Volleyball');
59
60INSERT INTO `coaches` (`name`, `egn`) 
61VALUES ('Ð˜Ð²Ð°Ð½ ÐŸÐµÑ‚ÐºÐ¾Ð²', '7509041245'),
62('Ð“ÐµÐ¾Ñ€Ð³Ð¸ Ð˜Ð²Ð°Ð½Ð¾Ð² Ð¢Ð¾Ð´Ð¾Ñ€Ð¾Ð²', '8010091245'),
63('Ð˜Ð»Ð¸ÑÐ½ Ð¢Ð¾Ð´Ð¾Ñ€Ð¾Ð² Ð“ÐµÐ¾Ñ€Ð³Ð¸ÐµÐ²', '8407106352');
64
65USE school_sport_clubs;
66
67DROP TABLE IF EXISTS taxesPayments;
68CREATE TABLE taxesPayments(
69id INT AUTO_INCREMENT PRIMARY KEY,
70student_id INT NOT NULL,
71group_id INT NOT NULL,
72paymentAmount double NOT NULL,
73month TINYINT,
74year YEAR,
75dateOfPayment datetime not null,
76CONSTRAINT FOREIGN KEY (student_id) references students(id),
77CONSTRAINT FOREIGN KEY (group_id) references sportgroups(id)
78);
79
80
81
82
83DROP TABLE IF EXISTS salaryPayments;
84CREATE TABLE salaryPayments(
85id INT AUTO_INCREMENT PRIMARY KEY,
86coach_id INT NOT NULL,
87month TINYINT,
88year YEAR,
89salaryAmount double,
90dateOfPayment datetime not null,
91CONSTRAINT FOREIGN KEY (coach_id) references coaches(id),
92UNIQUE KEY(`coach_id`,`month`,`year`)
93);
94
95truncate table `school_sport_clubs`.`taxespayments`;
96/**2015**/
97INSERT INTO `school_sport_clubs`.`taxespayments` (`student_id`, `group_id`, `paymentAmount`, `month`, `year`,`dateOfPayment`) VALUES ('1', '1', '200', '1', 2015, now()),
98('1', '1', '200', '2', 2015, now()),
99('1', '1', '200', '3', 2015, now()),
100('1', '1', '200', '4', 2015, now()),
101('1', '1', '200', '5', 2015, now()),
102('1', '1', '200', '6', 2015, now()),
103('1', '1', '200', '7', 2015, now()),
104('1', '1', '200', '8', 2015, now()),
105('1', '1', '200', '9', 2015, now()),
106('1', '1', '200', '10', 2015, now()),
107('1', '1', '200', '11', 2015, now()),
108('1', '1', '200', '12', 2015, now()),
109
110('2', '1', '250', '1', 2015, now()),
111('2', '1', '250', '2', 2015, now()),
112('2', '1', '250', '3', 2015, now()),
113('2', '1', '250', '4', 2015, now()),
114('2', '1', '250', '5', 2015, now()),
115('2', '1', '250', '6', 2015, now()),
116('2', '1', '250', '7', 2015, now()),
117('2', '1', '250', '8', 2015, now()),
118('2', '1', '250', '9', 2015, now()),
119('2', '1', '250', '10', 2015, now()),
120('2', '1', '250', '11', 2015, now()),
121('2', '1', '250', '12', 2015, now()),
122
123('3', '1', '250', '1', 2015, now()),
124('3', '1', '250', '2', 2015, now()),
125('3', '1', '250', '3', 2015, now()),
126('3', '1', '250', '4', 2015, now()),
127('3', '1', '250', '5', 2015, now()),
128('3', '1', '250', '6', 2015, now()),
129('3', '1', '250', '7', 2015, now()),
130('3', '1', '250', '8', 2015, now()),
131('3', '1', '250', '9', 2015, now()),
132('3', '1', '250', '10', 2015, now()),
133('3', '1', '250', '11', 2015, now()),
134('3', '1', '250', '12', 2015, now()),
135
136
137('1', '2', '200', '1', 2015, now()),
138('1', '2', '200', '2', 2015, now()),
139('1', '2', '200', '3', 2015, now()),
140('1', '2', '200', '4', 2015, now()),
141('1', '2', '200', '5', 2015, now()),
142('1', '2', '200', '6', 2015, now()),
143('1', '2', '200', '7', 2015, now()),
144('1', '2', '200', '8', 2015, now()),
145('1', '2', '200', '9', 2015, now()),
146('1', '2', '200', '10', 2015, now()),
147('1', '2', '200', '11', 2015, now()),
148('1', '2', '200', '12', 2015, now()),
149
150('4', '2', '200', '1', 2015, now()),
151('4', '2', '200', '2', 2015, now()),
152('4', '2', '200', '3', 2015, now()),
153('4', '2', '200', '4', 2015, now()),
154('4', '2', '200', '5', 2015, now()),
155('4', '2', '200', '6', 2015, now()),
156('4', '2', '200', '7', 2015, now()),
157('4', '2', '200', '8', 2015, now()),
158('4', '2', '200', '9', 2015, now()),
159('4', '2', '200', '10', 2015, now()),
160('4', '2', '200', '11', 2015, now()),
161('4', '2', '200', '12', 2015, now()),
162/**2014**/
163('1', '1', '200', '1', 2014, now()),
164('1', '1', '200', '2', 2014, now()),
165('1', '1', '200', '3', 2014, now()),
166('1', '1', '200', '4', 2014, now()),
167('1', '1', '200', '5', 2014, now()),
168('1', '1', '200', '6', 2014, now()),
169('1', '1', '200', '7', 2014, now()),
170('1', '1', '200', '8', 2014, now()),
171('1', '1', '200', '9', 2014, now()),
172('1', '1', '200', '10', 2014, now()),
173('1', '1', '200', '11', 2014, now()),
174('1', '1', '200', '12', 2014, now()),
175
176('2', '1', '250', '1', 2014, now()),
177('2', '1', '250', '2', 2014, now()),
178('2', '1', '250', '3', 2014, now()),
179('2', '1', '250', '4', 2014, now()),
180('2', '1', '250', '5', 2014, now()),
181('2', '1', '250', '6', 2014, now()),
182('2', '1', '250', '7', 2014, now()),
183('2', '1', '250', '8', 2014, now()),
184('2', '1', '250', '9', 2014, now()),
185('2', '1', '250', '10', 2014, now()),
186('2', '1', '250', '11', 2014, now()),
187('2', '1', '250', '12', 2014, now()),
188
189('3', '1', '250', '1', 2014, now()),
190('3', '1', '250', '2', 2014, now()),
191('3', '1', '250', '3', 2014, now()),
192('3', '1', '250', '4', 2014, now()),
193('3', '1', '250', '5', 2014, now()),
194('3', '1', '250', '6', 2014, now()),
195('3', '1', '250', '7', 2014, now()),
196('3', '1', '250', '8', 2014, now()),
197('3', '1', '250', '9', 2014, now()),
198('3', '1', '250', '10', 2014, now()),
199('3', '1', '250', '11', 2014, now()),
200('3', '1', '250', '12', 2014, now()),
201
202
203('1', '2', '200', '1', 2014, now()),
204('1', '2', '200', '2', 2014, now()),
205('1', '2', '200', '3', 2014, now()),
206('1', '2', '200', '4', 2014, now()),
207('1', '2', '200', '5', 2014, now()),
208('1', '2', '200', '6', 2014, now()),
209('1', '2', '200', '7', 2014, now()),
210('1', '2', '200', '8', 2014, now()),
211('1', '2', '200', '9', 2014, now()),
212('1', '2', '200', '10', 2014, now()),
213('1', '2', '200', '11', 2014, now()),
214('1', '2', '200', '12', 2014, now()),
215
216('4', '2', '200', '1', 2014, now()),
217('4', '2', '200', '2', 2014, now()),
218('4', '2', '200', '3', 2014, now()),
219('4', '2', '200', '4', 2014, now()),
220('4', '2', '200', '5', 2014, now()),
221('4', '2', '200', '6', 2014, now()),
222('4', '2', '200', '7', 2014, now()),
223('4', '2', '200', '8', 2014, now()),
224('4', '2', '200', '9', 2014, now()),
225('4', '2', '200', '10', 2014, now()),
226('4', '2', '200', '11', 2014, now()),
227('4', '2', '200', '12', 2014, now()),
228
229
230/**2016**/
231('1', '1', '200', '1', 2016, now()),
232('1', '1', '200', '2', 2016, now()),
233('1', '1', '200', '3', 2016, now()),
234
235
236('2', '1', '250', '1', 2016, now()),
237
238('3', '1', '250', '1', 2016, now()),
239('3', '1', '250', '2', 2016, now()),
240
241
242
243('1', '2', '200', '1', 2016, now()),
244('1', '2', '200', '2', 2016, now()),
245('1', '2', '200', '3', 2016, now()),
246
247
248('4', '2', '200', '1', 2016, now()),
249('4', '2', '200', '2', 2016, now());