Cm7LQfxz

1DROP DATABASE IF EXISTS SchoolBook;
2CREATE DATABASE SchoolBook CHARSET 'utf8';
3USE SchoolBook;
4
5#Task 1
6CREATE TABLE student(
7	id INT PRIMARY KEY AUTO_INCREMENT,
8    name VARCHAR(30) NOT NULL,
9    age INT NOT NULL,
10    average_grade NUMERIC(5, 2) DEFAULT 2 NOT NULL
11);
12
13CREATE TABLE grades(
14	id INT PRIMARY KEY AUTO_INCREMENT,
15    student_id INT NOT NULL,
16    subject VARCHAR(30) NOT NULL, #added 'subject' field
17    grade_name ENUM('Слаб', 'Среден', 'Добър', 'Много добър', 'Отличен') NOT NULL,
18    grade INT DEFAULT 2 NOT NULL,
19    
20    FOREIGN KEY (student_id) REFERENCES student(id)
21);
22
23#Task 6
24DROP TRIGGER IF EXISTS calculate_average_grade_on_insert;
25DROP TRIGGER IF EXISTS calculate_average_grade_on_update;
26DROP TRIGGER IF EXISTS calculate_average_grade_on_delete;
27
28DELIMITER $$
29
30CREATE TRIGGER calculate_average_grade_on_insert AFTER INSERT
31ON grades
32FOR EACH ROW
33BEGIN
34	UPDATE student AS s
35    SET s.average_grade = (
36		SELECT AVG(g.grade)
37        FROM grades AS g
38        WHERE g.student_id = NEW.student_id
39    )
40    WHERE s.id = NEW.student_id;
41END$$
42
43CREATE TRIGGER calculate_average_grade_on_update AFTER UPDATE
44ON grades
45FOR EACH ROW
46BEGIN
47	UPDATE student AS s
48    SET s.average_grade = (
49		SELECT AVG(g.grade)
50        FROM grades AS g
51        WHERE g.student_id = NEW.student_id
52    )
53    WHERE s.id = NEW.student_id;
54END$$
55
56CREATE TRIGGER calculate_average_grade_on_delete AFTER DELETE
57ON grades
58FOR EACH ROW
59BEGIN
60	UPDATE student AS s
61    SET s.average_grade = (
62		SELECT AVG(g.grade)
63        FROM grades AS g
64        WHERE g.student_id = OLD.student_id
65    )
66    WHERE s.id = OLD.student_id;
67END$$
68
69DELIMITER ;
70
71#Task 2
72INSERT INTO student(name, age) VALUES('Albert Einstein', 14);
73INSERT INTO student(name, age) VALUES('Frankenstein', 12);
74
75INSERT INTO grades(student_id, subject, grade_name, grade) VALUES(1, 'Physics and Astronomy', 5, 6);
76INSERT INTO grades(student_id, subject, grade_name, grade) VALUES(1, 'Physics and Astronomy', 5, 6);
77INSERT INTO grades(student_id, subject, grade_name, grade) VALUES(1, 'Math', 5, 6);
78INSERT INTO grades(student_id, subject, grade_name, grade) VALUES(1, 'Math', 5, 6);
79INSERT INTO grades(student_id, subject, grade_name, grade) VALUES(1, 'Nazi Policy', 2, 3);
80INSERT INTO grades(student_id, subject, grade_name, grade) VALUES(1, 'Nazi Policy', 1, 2);
81
82INSERT INTO grades(student_id, subject, grade_name, grade) VALUES(2, 'Biology', 4, 5);
83INSERT INTO grades(student_id, subject, grade_name, grade) VALUES(2, 'Biology', 5, 6);
84INSERT INTO grades(student_id, subject, grade_name, grade) VALUES(2, 'Math', 1, 2);
85INSERT INTO grades(student_id, subject, grade_name, grade) VALUES(2, 'Math', 2, 3);
86INSERT INTO grades(student_id, subject, grade_name, grade) VALUES(2, 'PE', 3, 4);
87INSERT INTO grades(student_id, subject, grade_name, grade) VALUES(2, 'PE', 4, 5);
88
89UPDATE grades SET grade = 2 WHERE id = 5;
90
91DELETE FROM grades WHERE id = 5;
92
93#Task 3
94SELECT s.name, g.subject, g.grade_name, g.grade
95FROM student AS s
96LEFT JOIN grades AS g
97ON s.id = g.student_id;
98
99#Task 4
100SELECT s.average_grade AS AVG
101FROM student AS s
102WHERE s.id = 1;
103
104#Task 5
105SELECT s.name, s.age, g.subject, g.grade_name, g.grade
106FROM student AS s
107LEFT JOIN grades AS g
108ON s.id = g.student_id
109WHERE g.grade > 4;