· 8 years ago · Jan 10, 2018, 06:12 PM
1DROP DATABASE IF EXISTS Biba_e_qka;
2CREATE DATABASE Biba_e_qka;
3USE Biba_e_qka;
4
5CREATE TABLE students(
6id INT AUTO_INCREMENT PRIMARY KEY,
7name VARCHAR(255) NOT NULL,
8egn VARCHAR(10) NOT NULL UNIQUE,
9address VARCHAR(255) NOT NULL,
10phone VARCHAR(20) NULL DEFAULT NULL,
11class VARCHAR(10) NULL DEFAULT NULL
12);
13
14CREATE TABLE sportGroups(
15id INT AUTO_INCREMENT PRIMARY KEY,
16location VARCHAR(255) NOT NULL,
17dayOfWeek ENUM('Monday','Tuesday','Wednesday','Thursday','Friday','Saturday','Sunday'),
18hourOfTraining TIME NOT NULL
19);
20
21CREATE TABLE student_sport(
22student_id int not null,
23sportGroup_id int not null,
24PRIMARY KEY(student_id,sportGroup_id)
25);
26
27CREATE TABLE sports(
28id INT AUTO_INCREMENT PRIMARY KEY,
29name VARCHAR(255) NOT NULL
30);
31
32ALTER TABLE sportGroups ADD sport_id INT NOT NULL;
33
34
35CREATE TABLE coaches(
36id INT AUTO_INCREMENT PRIMARY KEY,
37name VARCHAR(255) NOT NULL,
38egn VARCHAR(10) NOT NULL UNIQUE
39);
40
41ALTER TABLE sportGroups ADD coach_id INT;
42
43CREATE TABLE programmers(
44 id INT AUTO_INCREMENT PRIMARY KEY,
45 name VARCHAR(255) NOT NULL,
46 address VARCHAR(255) NOT NULL,
47 teamLead_id INT NULL DEFAULT NULL
48);
49
50CREATE TABLE taxesPayments(
51id INT AUTO_INCREMENT PRIMARY KEY,
52student_id INT NOT NULL,
53group_id INT NOT NULL,
54paymentAmount double NOT NULL,
55month TINYINT,
56year YEAR,
57dateOfPayment datetime not null
58);
59
60CREATE TABLE salaryPayments(
61id INT AUTO_INCREMENT PRIMARY KEY,
62coach_id INT NOT NULL,
63month TINYINT,
64year YEAR,
65salaryAmount double,
66dateOfPayment datetime not null,
67UNIQUE KEY(coach_id,month,year)
68);
69
70INSERT INTO sports (name) VALUES ('Football');
71INSERT INTO sports (name) VALUES ('Volleyball');
72
73INSERT INTO coaches (name, egn)
74VALUES ('Ilian', '7509041245'),
75('Georgi', '8010091245'),
76('Ivan', '8407106352');
77
78INSERT INTO coaches (name, egn) VALUES ('Petur', '7010102045');
79INSERT INTO coaches (name, egn) VALUES ('Slavi', '7106041278');
80
81INSERT INTO students (name, egn, address, phone, class)
82VALUES ('Iliyan Ivanov', '9401150045', 'Sofia-Mladost 1', '0893452120', '10'),
83('Ivan Iliev Georgiev', '9510104512', 'Sofia-Liylin', '0894123456', '11'),
84('Elena Petrova Petrova', '9505052154', 'Sofia-Mladost 3', '0897852412', '11'),
85('Ivan Iliev Iliev', '9510104542', 'Sofia-Mladost 3', '0894123457', '11'),
86('Maria Hristova Dimova', '9510104547', 'Sofia-Mladost 4', '0894123442', '11'),
87('Antoaneta Ivanova Georgieva', '9411104547', 'Sofia-Krasno selo', '0874526235', '10');
88
89INSERT INTO student_sport (student_id, sportGroup_id) VALUES
90 (1, 1),
91 (2, 1),
92 (3, 1),
93 (4, 2),
94 (5, 2),
95 (6, 2);
96
97 INSERT INTO sportGroups (location, dayOfWeek, hourOfTraining, sport_id, coach_id)
98 VALUES ('Sofia-Mladost 1', 'Monday', '8:00:00', 1, 1),
99 ('Sofia-Mladost 1', 'Monday', '9:30:00', 1, 1),
100 ('Sofia- Liylin 7','Sunday','09:00:00',2,2),
101 ('Sofia-Nadezhda', 'Sunday', '08:00', 1,NULL);
102
103INSERT INTO programmers (name, address, teamLead_id)
104VALUES ('Ivan Ivanov', 'Sofia', NULL),
105 ('Georgi Petkov Todorov', 'Bulgaria- Sofia Nadezhda, bl. 35', '1'),
106 ('Todor Petkov', 'Sofia - Liylin 7', 1),
107 ('Sofiq Dimitrova Petrova', 'Sofia - Mladost 4, bl. 7', 1),
108 ('Teodor Ivanov Stoyanov', 'Sofia - Obelya, bl. 48', NULL),
109 ('Iliya Stoynov Todorov', 'Sofia - Nadezhda, bl. 28', 5),
110 ('Mariela Dimitrova Yordanova', 'Sofia - Knyajevo, bl. 17', 5),
111 ('Elena Miroslavova Georgieva', 'Sofia - Krasno Selo, bl. 27', 5),
112 ('Teodor Milanov Milanov', 'Sofia - Lozenetz', 5);
113
114
115INSERT INTO taxesPayments (student_id, group_id, paymentAmount, month, year, dateOfPayment) VALUES
116 ('1', '1', '200', '1', 2010, now()),
117 ('1', '1', '200', '2', 2014, now()),
118 ('1', '1', '200', '3', 2011, now()),
119 ('1', '1', '200', '4', 2017, now()),
120 ('1', '1', '200', '5', 2018, now()),
121 ('1', '1', '200', '6', 2014, now()),
122 ('1', '1', '200', '7', 2013, now()),
123 ('1', '1', '200', '8', 2017, now()),
124 ('1', '1', '200', '9', 2017, now()),
125 ('1', '1', '200', '10', 2015, now()),
126 ('1', '1', '200', '11', 2015, now()),
127 ('1', '1', '200', '12', 2016, now()),
128 ('2', '1', '250', '1', 2002, now()),
129 ('2', '1', '250', '2', 2005, now()),
130 ('2', '1', '250', '3', 2004, now()),
131 ('2', '1', '250', '4', 2006, now()),
132 ('2', '1', '250', '5', 2013, now()),
133 ('2', '1', '250', '6', 2015, now()),
134 ('2', '1', '250', '7', 2005, now()),
135 ('2', '1', '250', '8', 2007, now()),
136 ('2', '1', '250', '9', 2008, now()),
137 ('2', '1', '250', '10', 2009, now()),
138 ('2', '1', '250', '11', 2010, now()),
139 ('2', '1', '250', '12', 2011, now()),
140 ('3', '1', '250', '1', 2015, now()),
141 ('3', '1', '250', '2', 2015, now()),
142 ('3', '1', '250', '3', 2015, now()),
143 ('3', '1', '250', '4', 2015, now()),
144 ('3', '1', '250', '5', 2015, now()),
145 ('3', '1', '250', '6', 2015, now()),
146 ('3', '1', '250', '7', 2015, now()),
147 ('3', '1', '250', '8', 2015, now()),
148 ('3', '1', '250', '9', 2015, now()),
149 ('3', '1', '250', '10', 2015, now()),
150 ('3', '1', '250', '11', 2015, now()),
151 ('3', '1', '250', '12', 2015, now()),
152 ('1', '2', '200', '1', 2015, now()),
153 ('1', '2', '200', '2', 2015, now()),
154 ('1', '2', '200', '3', 2015, now()),
155 ('1', '2', '200', '4', 2015, now()),
156 ('1', '2', '200', '5', 2015, now()),
157 ('1', '2', '200', '6', 2015, now()),
158 ('1', '2', '200', '7', 2015, now()),
159 ('1', '2', '200', '8', 2015, now()),
160 ('1', '2', '200', '9', 2015, now()),
161 ('1', '2', '200', '10', 2015, now()),
162 ('1', '2', '200', '11', 2015, now()),
163 ('1', '2', '200', '12', 2015, now()),
164 ('4', '2', '200', '1', 2015, now()),
165 ('4', '2', '200', '2', 2015, now()),
166 ('4', '2', '200', '3', 2015, now()),
167 ('4', '2', '200', '4', 2015, now()),
168 ('4', '2', '200', '5', 2015, now()),
169 ('4', '2', '200', '6', 2015, now()),
170('4', '2', '200', '7', 2015, now()),
171('4', '2', '200', '8', 2015, now()),
172('4', '2', '200', '9', 2015, now()),
173('4', '2', '200', '10', 2015, now()),
174('4', '2', '200', '11', 2015, now()),
175('4', '2', '200', '12', 2015, now()),
176('1', '1', '200', '1', 2014, now()),
177('1', '1', '200', '2', 2014, now()),
178('1', '1', '200', '3', 2014, now()),
179('1', '1', '200', '4', 2014, now()),
180('1', '1', '200', '5', 2014, now()),
181('1', '1', '200', '6', 2014, now()),
182('1', '1', '200', '7', 2014, now()),
183('1', '1', '200', '8', 2014, now()),
184('1', '1', '200', '9', 2014, now()),
185('1', '1', '200', '10', 2014, now()),
186('1', '1', '200', '11', 2014, now()),
187('1', '1', '200', '12', 2014, now()),
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 ('1', '2', '200', '1', 2014, now()),
214 ('1', '2', '200', '2', 2014, now()),
215 ('1', '2', '200', '3', 2014, now()),
216 ('1', '2', '200', '4', 2014, now()),
217 ('1', '2', '200', '5', 2014, now()),
218 ('1', '2', '200', '6', 2014, now()),
219('1', '2', '200', '7', 2014, now()),
220('1', '2', '200', '8', 2014, now()),
221('1', '2', '200', '9', 2014, now()),
222('1', '2', '200', '10', 2014, now()),
223('1', '2', '200', '11', 2014, now()),
224('1', '2', '200', '12', 2014, now()),
225('4', '2', '200', '1', 2014, now()),
226('4', '2', '200', '2', 2014, now()),
227('4', '2', '200', '3', 2014, now()),
228('4', '2', '200', '4', 2014, now()),
229('4', '2', '200', '5', 2014, now()),
230('4', '2', '200', '6', 2014, now()),
231('4', '2', '200', '7', 2014, now()),
232('4', '2', '200', '8', 2014, now()),
233('4', '2', '200', '9', 2014, now()),
234('4', '2', '200', '10', 2014, now()),
235('4', '2', '200', '11', 2014, now()),
236 ('4', '2', '200', '12', 2014, now()),
237 ('1', '1', '200', '1', 2016, now()),
238 ('1', '1', '200', '2', 2016, now()),
239 ('1', '1', '200', '3', 2016, now()),
240 ('2', '1', '250', '1', 2016, now()),
241 ('3', '1', '250', '1', 2016, now()),
242 ('3', '1', '250', '2', 2016, now()),
243 ('1', '2', '200', '1', 2016, now()),
244 ('1', '2', '200', '2', 2016, now()),
245 ('1', '2', '200', '3', 2016, now()),
246 ('4', '2', '200', '1', 2016, now()),
247 ('4', '2', '200', '2', 2016, now());