Ts26Z1Yj

1DROP DATABASE IF EXISTS Db;
2
3CREATE DATABASE Db;
4USE Db;
5
6CREATE TABLE StudentsGroup (
7	Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
8    groupName VARCHAR(256) NOT NULL,
9    tCount INT default 0,
10    sCount INT default 0
11);
12
13CREATE TABLE Person (
14	Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
15    name VARCHAR(256),
16    groupId INT NOT NULL,
17    personType ENum ('student', 'teacher'),
18    FOREIGN KEY (groupId) REFERENCES StudentsGroup(Id)
19);
20
21DELIMITER //
22CREATE TRIGGER InsertT AFTER INSERT ON Person
23FOR EACH ROW
24BEGIN
25    UPDATE StudentsGroup AS g
26    SET sCount = CASE WHEN NEW.personType = 'student' THEN g.sCount + 1
27    ELSE g.sCount END,
28    tCount = CASE WHEN NEW.personType = 'teacher' THEN g.tCount + 1
29    ELSE g.tCount END
30    WHERE g.Id = NEW.groupId;
31   
32END//
33
34CREATE TRIGGER DeleteS AFTER DELETE ON Person
35FOR EACH ROW
36BEGIN
37    UPDATE StudentsGroup g
38	SET sCount = CASE WHEN old.personType = 'student' THEN g.sCount - 1
39    ELSE g.sCount END,
40    tCount = CASE WHEN old.personType = 'teacher' THEN g.tCount - 1
41    ELSE g.tCount END
42	WHERE g.Id = old.groupId; 
43END//
44
45DELIMITER ;
46
47INSERT INTO StudentsGroup 
48VALUES (1, 'Group 1', 0, 0);
49INSERT INTO StudentsGroup 
50VALUES (2, 'Group 2', 0, 0);
51INSERT INTO StudentsGroup 
52VALUES (3, 'Group 3', 0, 0);  
53INSERT INTO Person 
54Values (1, 'Zaprqnov', 2, 'teacher');
55INSERT INTO Person 
56Values (2, 'Toshko', 1, 'student');
57INSERT INTO Person 
58Values (3, 'Iliqn', 1, 'student');
59INSERT INTO Person 
60Values (4, 'Stela', 1, 'teacher');
61
62SELECT g.groupName, s.name, s.personType FROM Person s 
63LEFT JOIN StudentsGroup AS g on g.Id = s.groupId;
64
65SELECT g.groupName, SUM(g.tCount + g.sCount) FROM StudentsGroup AS g GROUP BY g.Id;
66SELECT g.groupName, g.tCount FROM StudentsGroup g;
67SELECT g.groupName, g.sCount FROM StudentsGroup g;