By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table studs(
st_id int primary key auto_increment,
st_name varchar(30),
st_surname varchar(30),
st_course int,
st_speciality enum('km', 'webs', 'mobile', 'mech'),
st_form enum('free', 'not free'),
st_value float
);
create table activities(
activity_id int primary key auto_increment,
activity_name varchar(50),
activity_description varchar(200),
activity_date date,
activity_place varchar(50),
activity_organizer varchar(30)
);
create table participation(
participation_id int primary key auto_increment,
student_id int,
activity_id int,
participation_role varchar(20),
participation_result varchar(50),
constraint ref_studs_to_participation foreign key (student_id) references studs (st_id),
constraint ref_activities_to_participation foreign key (activity_id) references activities (activity_id)
);
INSERT INTO studs VALUES(NULL ,'testA','testB',1,'km','free', 1.6);
INSERT INTO studs VALUES(NULL ,'testA1','testB1',2,'km','free', 1.1);
INSERT INTO studs VALUES(NULL ,'testA2','testB2',3,'km','free', 1.1);
INSERT INTO activities VALUEs (NULL,'test1','test2', NOW(),'test4','test5');
INSERT INTO activities VALUEs (NULL,'test11','test21', NOW(),'test41','test51');
INSERT INTO activities VALUEs (NULL,'test12','test22', NOW(),'test42','test52');
INSERT INTO participation VALUES (NULL,1,1,'test','test');
INSERT INTO participation VALUES (NULL,1,2,'test','test');
INSERT INTO participation VALUES (NULL,1,3,'test','test');
INSERT INTO participation VALUES (NULL,2,1,'test','test');
INSERT INTO participation VALUES (NULL,2,2,'test','test');
INSERT INTO participation VALUES (NULL,3,3,'test','test');
INSERT INTO participation VALUES (NULL,3,2,'test','test');
INSERT INTO participation VALUES (NULL,3,3,'test','test');
create procedure update_st_value_by_participation(in p_how_much int)
begin
declare p_student_id, p_value_multiplier int;
declare is_end int default 0;
declare students_cursor cursor for
select student_id, count(student_id) as st_participations
from participation group by student_id;
declare continue handler for not found set is_end=1;
open students_cursor;
curs: Loop
fetch students_cursor into p_student_id, p_value_multiplier;
if is_end then
leave curs;
end if;
update studs
set st_value = st_value + p_how_much * (p_value_multiplier - 1)
where st_id = p_student_id;
end loop curs;
close students_cursor;
end
CALL update_st_value_by_participation(21)
SELECT * FROM studs
| st_id | st_name | st_surname | st_course | st_speciality | st_form | st_value |
|---|---|---|---|---|---|---|
| 1 | testA | testB | 1 | km | free | 43.6 |
| 2 | testA1 | testB1 | 2 | km | free | 22.1 |
| 3 | testA2 | testB2 | 3 | km | free | 43.1 |