· 6 months ago · Apr 10, 2025, 06:45 AM
1CREATE DATABASE _93aa_ksig;
2USE _93aa_ksig;
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', '3456789012');
39
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 NOT NULL,
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, 1),
54('Sofia-Mladost1', 'Mon', '9:00:00', 1, 2),
55('Sofia-Lulin', 'Sun', '8:00:00', 2, 3),
56('Sofia-Lulin', 'Sun', '9:30:00', 2, 2),
57('Plovdiv', 'Mon', '12:00:00', 1, 1);
58
59CREATE TABLE sportgr_student(
60student_id INT NOT NULL,
61CONSTRAINT FOREIGN KEY (student_id) REFERENCES students(id),
62sportgr_id INT NOT NULL,
63CONSTRAINT FOREIGN KEY (sportgr_id) REFERENCES sportgroups(id),
64PRIMARY KEY (student_id, sportgr_id)
65);
66INSERT INTO sportgr_student
67VALUES
68(1,1),
69(2,1),
70(3,2),
71(4,2),
72(5,1),
73(6,2);
74
75CREATE TABLE programmers(
76id INT AUTO_INCREMENT PRIMARY KEY,
77 name VARCHAR(255) NOT NULL,
78 address VARCHAR(255) NOT NULL,
79 startWorkingDate DATE,/**YYYY-MM-DD**/
80 teamLead_id INT NULL DEFAULT NULL,
81 CONSTRAINT FOREIGN KEY (teamLead_id) REFERENCES programmers(id)
82);
83
84INSERT INTO programmers (name, address, startWorkingDate, teamLead_id)
85VALUES
86('Ivan Ivanov', 'Sofia', '1999-05-25', NULL),
87('Georgi Petkov Todorov', 'Bulgaria - Sofia Nadezhda, bl. 35', '2002-12-01', 1),
88('Todor Petkov', 'Sofia - Liylin 7', '2009-11-01', 1),
89('Sofiq Dimitrova Petrova', 'Sofia - Mladost 4, bl. 7', '2010-01-01', 1),
90('Teodor Ivanov Stoyanov', 'Sofia - Obelya, bl. 48', '2011-10-01', NULL),
91('Iliya Stoynov Todorov', 'Sofia Nadezhda, bl. 28', '2000-02-01', 5),
92('Mariela Dimitrova Yordanova', 'Sofia - Knyajevo, bl. 17', '2005-05-01', 5),
93('Elena Miroslavova Georgieva', 'Sofia - Krasno Selo, bl. 27', '2008-04-01', 5),
94('Teodor Milanov Milanov', 'Sofia - Lozenetz', '2012-04-01', 5);
95
96SELECT progr.name as ProgramerName, progr.address as ProgramerAddress, teamLeads.name as TeamLeadName
97FROM programmers as progr JOIN programmers as teamLeads
98WHERE progr.teamLead_id = teamLeads.id;
99
100SELECT sportGroups.location,
101sportGroups.dayOfWeek,
102sportGroups.hourOfTraining,
103sports.name
104from sportGroups inner join sports
105on sportGroups.sport_id = sports.id;
106
107SELECT coaches.name, sports.name
108FROM coaches JOIN sports
109on coaches.id IN(
110SELECT coach_id
111from sportGroups
112WHERE sportGroups.sport_id = sports.id
113);
114
115SELECT distinct coaches.name, sports.name
116from coaches JOIN sportGroups
117on coach_id = sportGroups.coach_id
118JOIN sports
119on sportGroups.sport_id = sports.id;
120
121
122 delimiter |
123 create procedure getAllSportGroupsWithSports()
124 begin
125 select sg.location as locationOfGroup,
126 sg.DayOfWeek as trainigDay,
127 sg.hourOfTraining as trainingHour,
128 sp.name as sportName
129 from sportgroups as sg join sports as sp
130 on sg.sport_id = sp.id;
131 end
132 |
133 delimiter ;
134
135 call getAllSportGroupsWithSports();
136delimiter |
137create procedure proc_in (in var varchar(50))
138begin
139set @coach_name = var;
140end;
141|
142delimiter ;
143call proc_in ('Ivan Petrov');
144select * from coaches
145where name = @coach_name;
146
147 DELIMITER $$
148CREATE procedure checkMonthTax(IN studId INT, IN groupId INT, IN paymentMonth INT, IN paymentYear INT)
149BEGIN
150DECLARE result char(1);
151SET result = 0;
152 IF( (SELECT paymentAmount
153 FROM taxespayment
154 WHERE student_id = studId
155 AND group_id = groupId
156 AND month = paymentMonth
157 AND year = paymentYear) IS NOT NULL)
158 THEN
159 SET result = 1;
160 ELSE
161 SET result = 0;
162 END IF;
163SELECT result AS IsTaxPayed;
164END$$;
165DELIMITER ;
166CALL checkMonthTax(1,1,1,2023);
167
168DELIMITER $$
169CREATE PROCEDURE getPaymentPeriod(IN studId INT, IN groupId INT, IN paymentYear INT)
170BEGIN
171DECLARE countOfMonths int;
172DECLARE monthStr VARCHAR(10);
173DECLARE yearStr VARCHAR(10);
174SET monthStr = 'MONTH';
175SET yearStr = 'YEAR';
176 SELECT COUNT(*)
177 INTO countOfMonths
178 FROM taxespayment
179 WHERE student_id = studId
180 AND group_id = groupId
181 AND year = paymentYear;
182
183CASE countOfMonths
184 WHEN 0 THEN SELECT 'This student has not paid for this group/year!' as PAYMENT_PERIOD;
185 WHEN 1 THEN SELECT CONCAT('ONE_',monthStr) as PAYMENT_PERIOD;
186 WHEN 3 THEN SELECT CONCAT('THREE_',monthStr,'S') as PAYMENT_PERIOD;
187 WHEN 6 THEN SELECT CONCAT('SIX_',monthStr,'S') as PAYMENT_PERIOD;
188 WHEN 12 THEN SELECT yearStr as PAYMENT_PERIOD;
189 ELSE
190 SELECT CONCAT(countOfMonths, monthStr, 'S') as PAYMENT_PERIOD;
191 END CASE;
192END$$;
193DELIMITER ;
194CALL getPaymentPeriod(4,2,2022);
195
196
197DROP PROCEDURE IF EXISTS getAllPaymentsAmount;
198DELIMITER |
199CREATE PROCEDURE getAllPaymentsAmount(
200 IN firstMonth INT,
201 IN secMonth INT,
202 IN paymentYear INT,
203 IN studId INT
204)
205BEGIN
206 DECLARE totalAmount DECIMAL(10,2) DEFAULT 0;
207 DECLARE monthCount INT;
208
209 IF(firstMonth > secMonth) THEN
210 SELECT 'Please enter correct months!' AS RESULT;
211 ELSEIF((SELECT COUNT(*) FROM taxesPayment WHERE student_id = studId) = 0) THEN
212 SELECT 'Please enter correct student_id!' AS RESULT;
213 ELSE
214
215 SET monthCount = secMonth - firstMonth + 1;
216
217
218 SELECT IFNULL(SUM(paymentAmount), 0) INTO totalAmount
219 FROM taxesPayment
220 WHERE student_id = studId AND year = paymentYear AND month BETWEEN firstMonth AND secMonth;
221
222
223 SELECT
224 student_id,
225 group_id,
226 paymentAmount,
227 month,
228 monthCount AS months_in_period,
229 totalAmount AS total_paid
230 FROM taxesPayment
231 WHERE student_id = studId AND year = paymentYear AND month BETWEEN firstMonth AND secMonth;
232 END IF;
233END;
234|
235DELIMITER ;
236
237CALL getAllPaymentsAmount(1,6,2021,1);