ppf5z3J2

1DROP DATABASE IF EXISTS school_12b;
2CREATE DATABASE school_12b;
3
4USE school_12b;
5
6CREATE TABLE Students(
7	id INTEGER PRIMARY KEY AUTO_INCREMENT,
8	Name VARCHAR(150) NOT NULL,
9    Age INTEGER NOT NULL,
10    GPA NUMERIC(3, 2) DEFAULT 2.00
11);
12
13CREATE TABLE Teachers_book (
14	id INTEGER PRIMARY KEY AUTO_INCREMENT,
15	name ENUM('matematika', 'bel', 'subd'),
16    StudentId INTEGER,
17    mark INTEGER,
18	FOREIGN KEY (StudentId)
19		REFERENCES Students(id)
20        ON UPDATE CASCADE 
21        ON DELETE CASCADE
22	
23);
24
25DROP TRIGGER IF EXISTS after_insert_tb;
26DROP TRIGGER IF EXISTS after_update_tb;
27
28DELIMITER $$
29CREATE TRIGGER after_insert_tb AFTER INSERT
30ON Teachers_book
31FOR EACH ROW
32BEGIN
33    UPDATE Students st
34	SET st.GPA = (SELECT AVG(tb.mark) FROM Teachers_book tb
35		WHERE tb.StudentId = New.StudentId)
36	WHERE st.id = New.StudentId;
37END$$
38DELIMITER ;
39
40DELIMITER $$
41CREATE TRIGGER after_update_tb AFTER UPDATE
42ON Teachers_book
43FOR EACH ROW
44BEGIN
45    UPDATE Students st
46	SET st.GPA = (SELECT AVG(tb.mark) FROM Teachers_book tb
47		WHERE tb.StudentId = New.StudentId)
48	WHERE st.id = New.StudentId;
49END$$
50DELIMITER ;
51
52INSERT INTO Students(Name, Age)
53VALUES ('Aleksandur Aleksandrov', 20),
54('iliqn tuchev', 12),
55('simo dimitrov', 32),
56('roberta netsova', 12),
57('Pesho peshev', 39); 
58
59INSERT INTO Teachers_book(name, StudentId, mark)
60VALUES (1, 1, 6),
61(2, 1, 5),
62(3, 1, 4);
63SELECT * FROM Students;
64UPDATE Teachers_book
65SET mark = 6
66WHERE StudentId = 1;
67SELECT * FROM Students;