GtAE7FvY

1DROP DATABASE IF EXISTS school;
2CREATE DATABASE school;
3USE school;
4
5CREATE TABLE groups (
6	id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
7    name VARCHAR(100) NOT NULL,
8    stu_count INTEGER DEFAULT 0,
9    tec_count INTEGER DEFAULT 0
10);
11
12CREATE TABLE users (
13	id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
14    name VARCHAR(100) NOT NULL,
15    group_id INTEGER NOT NULL,
16    type enum('teacher','student'),
17    
18    FOREIGN KEY (group_id)  REFERENCES groups(id)
19);
20
21DELIMITER $
22
23CREATE TRIGGER students_insert AFTER INSERT ON users 
24FOR EACH ROW
25BEGIN
26-- Created by Alex
27	IF (NEW.type = 'student') THEN
28		UPDATE groups SET stu_count = stu_count + 1
29		WHERE id = NEW.group_id;
30	ELSE 
31		UPDATE groups SET tec_count = tec_count + 1
32		WHERE id = NEW.group_id;
33	END IF;
34    
35END$
36
37CREATE TRIGGER users_delete AFTER DELETE ON users 
38FOR EACH ROW
39BEGIN
40-- Created by Boian
41	UPDATE groups SET stu_count = stu_count - 1
42    WHERE id = OLD.group_id AND OLD.type = 'student';
43    
44    
45    UPDATE groups SET tec_count = tec_count - 1
46    WHERE id = OLD.group_id AND OLD.type = 'teacher';
47END$
48
49DELIMITER ;
50
51INSERT INTO groups(name) VALUES('Group one');
52INSERT INTO groups(name) VALUES('Group two');
53INSERT INTO groups(name) VALUES('Group three');
54
55INSERT INTO users(name,group_id,type) VALUES('Alex',1,'student');
56INSERT INTO users(name,group_id,type) VALUES('Boian',1,'student');
57INSERT INTO users(name,group_id,type) VALUES('Mihail',1,'teacher');
58
59INSERT INTO users(name,group_id,type) VALUES('Shterev',2,'teacher');
60
61
62SELECT gr.name, u.name, u.type FROM groups gr
63LEFT JOIN users u
64ON gr.id = u.group_id;
65
66SELECT gr.name, COUNT(u.id) FROM groups gr
67LEFT JOIN users u
68ON gr.id = u.group_id
69GROUP BY gr.id;
70
71SELECT gr.name, COUNT(u.id) FROM groups gr
72LEFT JOIN users u
73ON gr.id = u.group_id AND u.type = 'student'
74GROUP BY gr.id;
75
76SELECT gr.name, COUNT(u.id) FROM groups gr
77LEFT JOIN users u
78ON gr.id = u.group_id AND u.type = 'teacher'
79GROUP BY gr.id;
80
81DELETE FROM users WHERE id = 1;
82
83SELECT * FROM groups;