· 5 months ago · Apr 15, 2025, 04:46 PM
1CREATE DATABASE 42_Iva;
2USE 42_Iva;
3CREATE TABLE students(
4id INT AUTO_INCREMENT PRIMARY KEY,
5name VARCHAR(50) NOT NULL,
6address VARCHAR(50) NOT NULL,
7egn VARCHAR (10) NOT NULL UNIQUE,
8phone VARCHAR(10) NULL DEFAULT NULL,
9class VARCHAR(5) NULL DEFAULT NULL
10);
11INSERT INTO students(name, egn, address, phone, class)
12VALUES
13('Ilyan Ivanov', '9401150045', 'Sofia-Mladost', '0895345123', '10'),
14('Ivan Iliev', '9510104512', 'Sofia-Lulin', '0896786453', '11'),
15('Elena Petrovs', '9505052154', 'Sofia-Mladost2', '0899123456', '11'),
16('Ivan Iliev', '9510104542', 'Sofia-Mladost3', '0897123456', '11'),
17('Maria Dimova', '9510104547', 'Sofia-Mladost4', '088898654', '11'),
18('Antoaneta Ivanova', '9411104547', 'Sofia-Krasno selo', '0877123456', '10');
19
20CREATE TABLE sports(
21id INT AUTO_INCREMENT PRIMARY KEY,
22name VARCHAR(50) NOT NULL
23);
24INSERT INTO sports
25VALUES
26(NULL, 'football'),
27(NULL, 'voleyball');
28
29CREATE TABLE coaches(
30id INT AUTO_INCREMENT PRIMARY KEY,
31name VARCHAR(50) NOT NULL,
32egn VARCHAR (10) NOT NULL UNIQUE
33);
34INSERT INTO coaches(name, egn)
35VALUES
36('Ivan Petrov', '75090412'),
37('Petyr Dimitrov', '2345678901'),
38('Kaloyan Kolev', '3456789112');
39drop table sportGroups;
40CREATE TABLE sportGroups(
41id INT AUTO_INCREMENT PRIMARY KEY,
42location VARCHAR(50) NOT NULL,
43dayOfWeek ENUM('Mon', 'Tue', 'Wed', 'Thur', 'Fri', 'Sat', 'Sun'),
44hourOfTraining TIME NOT NULL,
45sport_id INT NOT NULL,
46CONSTRAINT FOREIGN KEY (sport_id) REFERENCES sports(id),
47coach_id INT ,
48CONSTRAINT FOREIGN KEY(coach_id) REFERENCES coaches(id),
49UNIQUE KEY (location, dayOfWeek, hourOfTraining)
50);
51INSERT INTO sportGroups(location, dayOfWeek, hourOfTraining, sport_id, coach_id)
52VALUES
53('Sofia-Mladost1', 'Mon', '8:00:00', 1, 350),
54('Sofia-Mladost1', 'Fri', '9:00:00', 1, 354),
55('Sofia-Lulin', 'Fri', '8:00:00', 2, 354),
56('Sofia-Lulin', 'Fri', '9:30:00', 2, 355),
57('Plovdiv', 'Tue', '12:00:00', 1, 354);
58INSERT INTO sportGroups(location, dayOfWeek, hourOfTraining, sport_id, coach_id)
59VALUES
60('Plovdiv', 'Fri', '12:00:00', 1, null);
61drop table sportgr_student;
62
63CREATE TABLE sportgr_student(
64student_id INT NOT NULL,
65CONSTRAINT FOREIGN KEY (student_id) REFERENCES students(id),
66sportgr_id INT NOT NULL,
67CONSTRAINT FOREIGN KEY (sportgr_id) REFERENCES sportgroups(id),
68PRIMARY KEY (student_id, sportgr_id)
69);
70INSERT INTO sportgr_student
71VALUES
72(1,31),
73(2,31),
74(3,32),
75(4,32),
76(5,31),
77(6,32);
78
79CREATE TABLE programmers(
80id INT AUTO_INCREMENT PRIMARY KEY,
81 name VARCHAR(255) NOT NULL,
82 address VARCHAR(255) NOT NULL,
83 startWorkingDate DATE,/**YYYY-MM-DD**/
84 teamLead_id INT NULL DEFAULT NULL,
85 CONSTRAINT FOREIGN KEY (teamLead_id) REFERENCES programmers(id)
86);
87
88INSERT INTO programmers (name, address, startWorkingDate, teamLead_id)
89VALUES
90('Ivan Ivanov', 'Sofia', '1999-05-25', NULL),
91('Georgi Petkov Todorov', 'Bulgaria - Sofia Nadezhda, bl. 35', '2002-12-01', 1),
92('Todor Petkov', 'Sofia - Liylin 7', '2009-11-01', 1),
93('Sofiq Dimitrova Petrova', 'Sofia - Mladost 4, bl. 7', '2010-01-01', 1),
94('Teodor Ivanov Stoyanov', 'Sofia - Obelya, bl. 48', '2011-10-01', NULL),
95('Iliya Stoynov Todorov', 'Sofia Nadezhda, bl. 28', '2000-02-01', 5),
96('Mariela Dimitrova Yordanova', 'Sofia - Knyajevo, bl. 17', '2005-05-01', 5),
97('Elena Miroslavova Georgieva', 'Sofia - Krasno Selo, bl. 27', '2008-04-01', 5),
98('Teodor Milanov Milanov', 'Sofia - Lozenetz', '2012-04-01', 5);
99drop table taxesPayment;
100
101CREATE TABLE taxesPayment(
102id int auto_increment primary key,
103student_id int not null,
104group_id int not null,
105paymentAmount double not null,
106month int,
107year int,
108dateOfPayment datetime not null,
109constraint foreign key (student_id) references students(id),
110constraint foreign key (group_id) references sportgroups(id)
111);
112INSERT INTO taxesPayment (student_id, group_id, paymentAmount, month, year, dateOfPayment)
113VALUES
114(1,11, 200, 3, 2022, now()),
115(1,30, 200, 4, 2022, now()),
116(1,33, 200, 5, 2022, now()),
117(1,11, 200, 6, 2022, now()),
118(1,11, 200, 7, 2022, now()),
119(1,11, 200, 8, 2022, now()),
120(1,30, 200, 9, 2022, now()),
121(1,33, 200, 10, 2022, now()),
122(1,33, 200, 11, 2022, now()),
123(1,33, 200, 12, 2022, now()),
124(4,11, 200, 1, 2022, now()),
125(4,49, 200, 2, 2022, now()),
126(4,49, 200, 3, 2022, now()),
127(4,49, 200, 4, 2022, now()),
128(4,49, 200, 5, 2022, now()),
129(4,11, 200, 6, 2022, now());
130
131select count(coaches_id) as CountOfGroupWithCoach from sportgroups;
132select sum(paymentAmount) as Allpayment
133from taxespayment
134where student_id=4;
135select max(paymentAmount) as Maxpayment
136from taxespayment
137where student_id=4;
138select group_id as GroupId, AVG(paymentAmount) as AvgOfAllPaymentPerGroup
139from taxespayment
140group by group_id;
141select students.id,students.name as StudentNam, sum(tp.paymentAmount) as SumOfAllPaymentPerGroup ,tp.month as Month
142from taxespayment as tp
143join students on tp.student_id= students.id
144GROUP BY month, student_id
145having SumOfAllPaymentPerGroup >100
146LIMIT 3;
147CREATE VIEW student_classes
148 AS SELECT students.name, students.class, taxespayment.group_id
149 FROM students
150 LEFT JOIN taxespayment
151 on students.id = taxespayment.student_id
152 JOIN sportgr_student
153 on students.id=sportgr_student.student_id
154 JOIN sportgroups
155 on sportgr_student.sportgr_id = sportgroups.sport_id
156 join coaches
157 on sportgroups.coach_id = coaches.id
158 join sports
159 on sportgroups.sport_id= sports.id
160 where sportgroups.dayOfWeek='Mon' AND sportgroups.hourOfTraining='8:00:00'
161 and coaches.name='Ivan Petrov'
162 and sports.name='Football';
163
164
165
166-- EX1 - Da se izvedat vsichki sportove sus sportnite grupi, koito sa SAMO za Sofia izpolzvaiki psevdonimi na koloni
167SELECT
168 sp.name AS sport_name,
169 sg.location AS group_location,
170 sg.dayOfWeek AS group_day,
171 sg.hourOfTraining AS training_hour,
172 c.name AS coach_name
173FROM
174 sportGroups sg
175JOIN
176 sports sp ON sg.sport_id = sp.id
177JOIN
178 coaches c ON sg.coach_id = c.id
179WHERE
180 sg.location LIKE 'Sofia%';
181
182-- EX2.1 - Da se sformirat dvoiki uchenici na baza na sportna grupa v koqto trenirat
183SELECT
184 s1.name AS student_1,
185 s2.name AS student_2,
186 sg.location AS sport_group_location,
187 sg.dayOfWeek AS sport_group_day,
188 sg.hourOfTraining AS sport_group_time
189FROM sportgr_student sg1
190JOIN sportgr_student sg2 ON sg1.sportgr_id = sg2.sportgr_id
191JOIN students s1 ON sg1.student_id = s1.id
192JOIN students s2 ON sg2.student_id = s2.id
193JOIN sportGroups sg ON sg.id = sg1.sportgr_id
194WHERE sg1.student_id < sg2.student_id;
195
196-- EX2.2 - Da se sformirat dvoiki uchenici na baza na sportna grupa v koqto trenirat
197SELECT DISTINCT
198 s1.name AS student1,
199 s2.name AS student2
200FROM sportgr_student sg1
201JOIN sportgr_student sg2 ON sg1.sportgr_id = sg2.sportgr_id AND sg1.student_id < sg2.student_id
202JOIN students s1 ON sg1.student_id = s1.id
203JOIN students s2 ON sg2.student_id = s2.id
204JOIN sportgroups sg ON sg.id = sg1.sportgr_id;
205
206
207-- Using union all
208SELECT name, egn, address
209FROM students
210WHERE address LIKE '%mladost%'
211
212UNION
213
214SELECT name, egn, address
215FROM students
216WHERE address LIKE '%mladost%';
217
218delete from coaches where id=1;
219
220ALTER TABLE sportgroups
221DROP FOREIGN KEY sportgroups_ibfk_2;
222
223ALTER TABLE sportgroups
224ADD CONSTRAINT FOREIGN KEY coach_id_key (coach_id) REFERENCES coaches(id)
225ON DELETE SET NULL ON UPDATE CASCADE;
226
227delete from coaches
228where id=2;
229update coaches
230set id=350 where id=3;
231
232DELETE FROM sportgroups
233WHERE id=32;
234
235DELIMITER $$
236CREATE TRIGGER before_sportgr_delete
237BEFORE DELETE ON sportgroups
238FOR EACH ROW
239BEGIN
240 DECLARE new_sportgr_id INT;
241
242 SELECT id INTO new_sportgr_id
243 FROM sportgroups
244 WHERE location = OLD.location AND id != OLD.id
245 LIMIT 1;
246
247 IF new_sportgr_id IS NOT NULL THEN
248 UPDATE sportgr_student
249 SET sportgr_id = new_sportgr_id
250 WHERE sportgr_id = OLD.id;
251 END IF;
252
253END$$
254DELIMITER ;
255
256
257DELIMITER $$
258CREATE procedure checkMonthTax(IN studId INT, IN groupId INT, IN paymentMonth INT, IN paymentYear INT)
259BEGIN
260DECLARE result char(1);
261SET result = 0;
262 IF( (SELECT paymentAmount
263 FROM taxespayment
264 WHERE student_id = studId
265 AND group_id = groupId
266 AND month = paymentMonth
267 AND year = paymentYear) IS NOT NULL)
268 THEN
269 SET result = 1;
270 ELSE
271 SET result = 0;
272 END IF;
273SELECT result AS IsTaxPayed;
274END$$;
275DELIMITER ;
276CALL checkMonthTax(1,1,1,2022);
277
278SELECT * FROM taxespayment;
279
280DELIMITER $$
281CREATE procedure checkPaymentPeriod(IN studId INT, IN groupId INT, IN paymentYear INT)
282BEGIN
283DECLARE countOfMonths int;
284DECLARE monthStr VARCHAR(10);
285DECLARE yearStr VARCHAR(10);
286SET monthStr = 'MONTH';
287SET yearStr = 'YEAR';
288 SELECT COUNT(*)
289 INTO countOfMonths
290 FROM taxespayment
291 WHERE student_id = studId
292 AND group_id = groupId
293 AND year = paymentYear;
294
295CASE countOfMonths
296 WHEN 0 THEN SELECT 'This student has not paid for this group/year!' as PAYMENT_PERIOD;
297 WHEN 1 THEN SELECT CONCAT('ONE_',monthStr) as PAYMENT_PERIOD;
298 WHEN 3 THEN SELECT CONCAT('THREE_',monthStr,'S') as PAYMENT_PERIOD;
299 WHEN 6 THEN SELECT CONCAT('SIX_',monthStr,'S') as PAYMENT_PERIOD;
300 WHEN 12 THEN SELECT yearStr as PAYMENT_PERIOD;
301 ELSE
302 SELECT CONCAT(countOfMonths, monthStr, 'S') as PAYMENT_PERIOD;
303 END CASE;
304END$$;
305DELIMITER ;
306
307
308DROP PROCEDURE getAllPaymentsAmount;
309DELIMITER $$
310CREATE PROCEDURE getAllPaymentsAmount(IN firstMonth INT, IN secMonth INT, IN paymentYear INT, IN studld INT)
311BEGIN
312DECLARE iterator int;
313DECLARE sum int;
314DECLARE paymentAmount_result int;
315SET sum = 0;
316IF (firstMonth >= secMonth)
317 THEN
318 SELECT 'Please enter correct months!' as RESULT;
319 ELSE IF( (SELECT COUNT(*)
320 FROM taxesPayment
321 WHERE student_id = studld) = 0) THEN SELECT 'Please enter correct student_id!' as RESULT;
322 ELSE
323 SET iterator = firstMonth;
324 WHILE(iterator >= firstMonth AND iterator <= secMonth)
325 DO
326 SELECT paymentAmount
327 INTO paymentAmount_result
328 FROM taxespayment
329 WHERE student_id = studld AND year = paymentYear AND month = iterator;
330 SET sum = sum + paymentAmount_result;
331 SET iterator = iterator + 1;
332 END WHILE;
333 END IF;
334 END IF;
335 SELECT sum AS result;
336END;
337DELIMITER ;
338
339
340
341
342DROP PROCEDURE IF EXISTS getAllPaymentsAmount;
343DELIMITER $$
344
345CREATE PROCEDURE getAllPaymentsAmount(IN firstMonth INT, IN secMonth INT, IN paymentYear INT, IN studld INT)
346BEGIN
347 DECLARE iterator INT;
348 DECLARE sum INT DEFAULT 0;
349 DECLARE paymentAmount_result INT;
350
351 CREATE TEMPORARY TABLE IF NOT EXISTS temp_result (
352 result INT
353 );
354
355 TRUNCATE TABLE temp_result;
356
357 IF (firstMonth >= secMonth) THEN
358 INSERT INTO temp_result (result) VALUES ('Please enter correct months!');
359 ELSEIF ( (SELECT COUNT(*) FROM taxesPayment WHERE student_id = studld) = 0) THEN
360 INSERT INTO temp_result (result) VALUES ('Please enter correct student_id!');
361 ELSE
362 SET iterator = firstMonth;
363 WHILE (iterator >= firstMonth AND iterator <= secMonth) DO
364 SELECT paymentAmount
365 INTO paymentAmount_result
366 FROM taxesPayment
367 WHERE student_id = studld AND year = paymentYear AND month = iterator;
368
369 IF paymentAmount_result IS NOT NULL THEN
370 SET sum = sum + paymentAmount_result;
371 END IF;
372
373 SET iterator = iterator + 1;
374 END WHILE;
375
376 INSERT INTO temp_result (result) VALUES (sum);
377 END IF;
378
379 SELECT * FROM temp_result;
380
381 DROP TEMPORARY TABLE IF EXISTS temp_result;
382END;
383$$
384DELIMITER ;
385