gCjX4C0y

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