CNZNeZ61

1DROP DATABASE IF EXISTS DA;
2
3CREATE DATABASE DA;
4USE DA;
5
6CREATE TABLE GroupStudentTeacher (
7	Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
8    GroupName VARCHAR(256) NOT NULL,
9    TeachersCount INT default 0,
10    StudentsCount INT default 0
11);
12
13CREATE TABLE Person (
14	Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
15    PName VARCHAR(256),
16    GroupId INT NOT NULL,
17    PType ENum ('student', 'talant'),
18    FOREIGN KEY (GroupId) REFERENCES GroupStudentTeacher(Id)
19);
20
21DELIMITER //
22
23CREATE TRIGGER TeacherInsert
24AFTER INSERT
25ON Person
26FOR EACH ROW
27Begin
28	UPDATE GroupStudentTeacher g
29	SET StudentsCount = case 
30    when new.PType = 'student' then g.StudentsCount + 1
31    else g.StudentsCount
32    end,
33    TeachersCount = case 
34    when new.PType = 'talant' then g.TeachersCount + 1
35    else g.TeachersCount
36    end
37	WHERE g.Id = new.GroupId; 
38	
39End//
40
41CREATE TRIGGER StudentDelete
42AFTER DELETE
43ON Person
44FOR EACH ROW
45Begin
46    UPDATE GroupStudentTeacher g
47	SET StudentsCount = case 
48    when old.PType = 'student' then g.StudentsCount - 1
49    else g.StudentsCount
50    end,
51    TeachersCount = case 
52    when old.PType = 'talant' then g.TeachersCount - 1
53    else g.TeachersCount
54    end
55	WHERE g.Id = old.GroupId; 
56End//
57
58Delimiter ;
59
60
61INSERT INTO GroupStudentTeacher VALUES (1, 'K', 0, 0);
62INSERT INTO GroupStudentTeacher VALUES (2, 'KFF', 0, 0);  
63INSERT INTO Person Values (1, 'FSDGDGF', 1, 'student');
64INSERT INTO Person Values (2, 'ZDFSGSAG', 1, 'student');
65INSERT INTO Person Values (3, 'QRTQ', 1, 'talant');
66
67select g.GroupName, p.PName, p.PType from Person p 
68left join GroupStudentTeacher g on g.Id = p.GroupId;
69
70select g.GroupName, SUM(g.TeachersCount + g.StudentsCount) from GroupStudentTeacher g group by g.Id;
71
72select g.GroupName, g.TeachersCount from GroupStudentTeacher g;
73select g.GroupName, g.StudentsCount from GroupStudentTeacher g;