PNU7bSn2

1DROP DATABASE IF EXISTS grades;
2
3CREATE DATABASE grades;
4USE grades;
5
6CREATE TABLE students(
7	id INTEGER AUTO_INCREMENT PRIMARY KEY,
8    name VARCHAR(50) NOT NULL,
9    average_grade DOUBLE DEFAULT 0,
10    age INTEGER NOT NULL
11);
12
13CREATE TABLE grades(
14	id INTEGER AUTO_INCREMENT PRIMARY KEY,
15	student_id INTEGER,
16    FOREIGN KEY (student_id) REFERENCES students(id),
17    grade_name ENUM('BEL', 'MATH', 'SUBD'),
18    grade INTEGER DEFAULT 2
19);
20
21
22DELIMITER $$
23CREATE TRIGGER calculate_avg_on_create 
24	AFTER INSERT ON grades
25	FOR EACH ROW
26BEGIN
27	UPDATE students
28    SET average_grade = (SELECT AVG(grade) FROM grades
29						  WHERE students.id = grades.student_id)
30    WHERE id = New.student_id;
31END$$
32DELIMITER ;
33
34DELIMITER $$
35CREATE TRIGGER calculate_avg_on_update 
36	AFTER UPDATE ON grades
37	FOR EACH ROW
38BEGIN
39	UPDATE students
40    SET average_grade = (SELECT AVG(grade) FROM grades
41						  WHERE students.id = grades.student_id)
42    WHERE id = New.student_id;
43END$$
44DELIMITER ;
45
46DELIMITER $$
47CREATE TRIGGER calculate_avg_on_delete 
48	AFTER DELETE ON grades
49	FOR EACH ROW
50BEGIN
51	UPDATE students
52    SET average_grade = (SELECT AVG(grade) FROM grades
53						  WHERE students.id = grades.student_id)
54    WHERE id = Old.student_id;
55END$$
56DELIMITER ;
57
58INSERT INTO students(name, age) VALUES('Ivancho', 16);
59INSERT INTO students(name, age) VALUES('Mariika', 18);
60
61INSERT INTO grades(student_id, grade_name, grade) VALUES(1, 'BEL', 6);
62INSERT INTO grades(student_id, grade_name, grade) VALUES(1, 'BEL', 6);
63INSERT INTO grades(student_id, grade_name, grade) VALUES(1, 'BEL', 6);
64
65INSERT INTO grades(student_id, grade_name, grade) VALUES(1, 'MATH', 6);
66INSERT INTO grades(student_id, grade_name, grade) VALUES(1, 'MATH', 4);
67INSERT INTO grades(student_id, grade_name, grade) VALUES(1, 'MATH', 5);
68
69INSERT INTO grades(student_id, grade_name, grade) VALUES(1, 'SUBD', 6);
70INSERT INTO grades(student_id, grade_name, grade) VALUES(1, 'SUBD', 6);
71INSERT INTO grades(student_id, grade_name, grade) VALUES(1, 'SUBD', 4);
72
73SELECT st.name, g.grade_name, g.grade
74FROM students st
75LEFT JOIN grades g
76ON g.student_id = st.id;
77
78SELECT AVG(st.average_grade)
79FROM students st
80WHERE st.id = 1;
81
82SELECT st.name, st.age, g.grade_name, g.grade
83FROM students st
84LEFT JOIN grades g
85ON g.student_id = st.id
86WHERE g.grade > 4;