CEMRawRj

1#12B class, Kaloyan Madjunov, Simeon Chakarov
2DROP DATABASE IF EXISTS Class6;
3CREATE DATABASE Class6;
4USE Class6;
5
6CREATE TABLE groupWithParticipants(
7	id INT PRIMARY KEY AUTO_INCREMENT,
8    name VARCHAR(50) NOT NULL
9);
10
11CREATE TABLE participants(
12	id INT PRIMARY KEY AUTO_INCREMENT,
13    name VARCHAR(30) NOT NULL,
14    type ENUM('student', 'teacher') NOT NULL,
15    group_id INT NOT NULL,
16    
17    FOREIGN KEY (group_id) REFERENCES groupWithParticipants(id)
18);
19
20#Task 1
21INSERT INTO groupWithParticipants(name) VALUES ('PowerRangers');
22INSERT INTO groupWithParticipants(name) VALUES ('Queen');
23INSERT INTO groupWithParticipants(name) VALUES ('Stark');
24
25INSERT INTO participants(name, type, group_id) VALUES ('Red', 1, 1);
26INSERT INTO participants(name, type, group_id) VALUES ('Blue', 1, 1);
27INSERT INTO participants(name, type, group_id) VALUES ('Rita Repulsa', 2, 1);
28
29INSERT INTO participants(name, type, group_id) VALUES ('Freddie Mercury', 2, 2);
30
31#Task 2
32SELECT g.name AS 'Group', p.name AS 'Participant', p.type AS 'Type'
33FROM participants AS p
34LEFT JOIN groupWithParticipants AS g
35ON p.group_id = g.id;
36
37#Task 3
38SELECT g.name AS 'Group', COUNT(p.id) AS 'Participants'
39FROM groupWithParticipants AS g
40LEFT JOIN participants AS p
41ON g.id = p.group_id
42GROUP BY g.id;
43
44#Task 4
45SELECT g.name AS 'Group', COUNT(p.id) AS 'Participants'
46FROM groupWithParticipants AS g
47LEFT JOIN participants AS p
48ON g.id = p.group_id && p.type = 1
49GROUP BY g.id;
50
51#Task 4
52SELECT g.name AS 'Group', COUNT(p.id) AS 'Participants'
53FROM groupWithParticipants AS g
54LEFT JOIN participants AS p
55ON g.id = p.group_id && p.type = 2
56GROUP BY g.id;