MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table student(
Sno char(3) not null primary key,
Sname char(8) not null,
Scount int null);

insert student(Sno, Sname, Scount)
values
('108', 'Tom', 40),
('105', 'John', 30),
('107', 'Mary', 23),
('101', 'Jake', 15),
('109', 'Lily', 10),
('103', 'James', 21);

SELECT * FROM student;


Sno
Sname
Scount


101
Jake
15

103
James
21

105
John
30

107
Mary
23

108
Tom
40

109
Lily
10


create table score(
Sno Char(3) not null,
foreign key (Sno) references student(Sno) on update cascade,
Cno char(5) not null,
Degree decimal(4,1) null);
CREATE TRIGGER AddCount 
AFTER INSERT 
ON score 
FOR EACH ROW 
UPDATE student 
SET Scount = Scount + 1 
WHERE Sno = NEW.Sno;
insert score(Sno, Cno, Degree)
values
('103', '3-245', 86),
('105', '3-245', 75),
('109', '3-245', 68),
('103', '3-105', 92),
('105', '3-105', 88),
('109', '3-105', 76),
('101', '3-105', 64),
('107', '3-105', 91),
('107', '6-166', 79),
('108', '6-166', 81);
SELECT * FROM student;


Sno
Sname
Scount


101
Jake
16

103
James
23

105
John
32

107
Mary
25

108
Tom
41

109
Lily
12