pE7u4p4y

1DROP DATABASE IF EXISTS groups;
2
3CREATE DATABASE groups;
4USE groups;
5
6CREATE TABLE groups(
7	id INTEGER AUTO_INCREMENT PRIMARY KEY,
8    name VARCHAR(50) NOT NULL,
9    students_count INTEGER DEFAULT 0,
10    teachers_count INTEGER DEFAULT 0,
11    members_count INTEGER DEFAULT 0
12);
13
14CREATE TABLE participants(
15	id INTEGER AUTO_INCREMENT PRIMARY KEY,
16    name VARCHAR(50) NOT NULL,
17    type ENUM('student', 'teacher'),
18    group_id INTEGER NOT NULL,
19    FOREIGN KEY(group_id) REFERENCES groups(id)
20);
21
22CREATE TRIGGER after_student_insert
23AFTER INSERT ON participants
24FOR EACH ROW
25UPDATE groups
26SET students_count = students_count + 1
27WHERE id = NEW.group_id 
28AND NEW.type = 'student';
29    
30CREATE TRIGGER after_teacher_insert
31AFTER INSERT ON participants
32FOR EACH ROW
33UPDATE groups
34SET teachers_count = teachers_count + 1
35WHERE id = NEW.group_id
36AND NEW.type = 'teacher';
37
38CREATE TRIGGER count_members_after_insert
39AFTER INSERT ON participants
40FOR EACH ROW
41UPDATE groups
42SET members_count = members_count + 1
43WHERE id = NEW.group_id;
44
45CREATE TRIGGER after_student_delete
46AFTER DELETE ON participants
47FOR EACH ROW
48UPDATE groups
49SET students_count = students_count - 1
50WHERE id = OLD.group_id
51AND OLD.type = 'student';
52
53CREATE TRIGGER after_teacher_delete
54AFTER DELETE ON participants
55FOR EACH ROW
56UPDATE groups
57SET teachers_count = teachers_count - 1
58WHERE id = OLD.group_id
59AND OLD.type = 'teacher';
60
61CREATE TRIGGER count_members_after_delete
62AFTER DELETE ON participants
63FOR EACH ROW
64UPDATE groups
65SET members_count = members_count - 1
66WHERE id = OLD.group_id;
67
68INSERT INTO groups(name) VALUES('first gruop');
69INSERT INTO participants(name, type, group_id) VALUES('Ivan', 'student', 1);
70INSERT INTO participants(name, type, group_id) VALUES('Georgi', 'student', 1);
71INSERT INTO participants(name, type, group_id) VALUES('Bobinata', 'teacher', 1);
72
73INSERT INTO groups(name) VALUES('second gruop');
74INSERT INTO participants(name, type, group_id) VALUES('Stela', 'teacher', 2);
75
76INSERT INTO groups(name) VALUES('third gruop');
77
78-- DELETE FROM `participants`
79-- WHERE `id` = 1;
80
81SELECT gr.name as 'Group', p.name as 'Participant', p.type as 'Type'
82FROM groups gr
83LEFT JOIN participants p
84ON gr.id = p.group_id;
85
86SELECT gr.name as 'Group', COUNT(p.id) as 'Count of participants'
87FROM groups gr
88LEFT JOIN participants p
89ON gr.id = p.group_id
90GROUP BY gr.id; 
91
92SELECT gr.name as 'Group', COUNT(p.id) as 'Count of students'
93FROM groups gr
94LEFT JOIN participants p
95ON gr.id = p.group_id 
96AND p.type = 'student'
97GROUP BY gr.id;
98
99SELECT gr.name as 'Group', COUNT(p.id) as 'Count of teachers'
100FROM groups gr
101LEFT JOIN participants p
102ON gr.id = p.group_id 
103AND p.type = 'teacher'
104GROUP BY gr.id;