3CXWCwc3

1DROP DATABASE IF EXISTS db;
2CREATE DATABASE db;
3USE db;
4 
5CREATE TABLE Participant(
6	id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
7	p_name VARCHAR(256) NOT NULL,
8    p_type ENUM('daskal', 'ne-daskal')
9);
10 
11CREATE TABLE Groups(
12	id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
13	g_name VARCHAR(256) NOT NULL    
14);
15
16CREATE TABLE ParticipantsInGroup(
17	id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
18    group_id INT NOT NULL,
19    participant_id INT NOT NULL,
20    FOREIGN KEY (group_id) REFERENCES Groups(id),
21    FOREIGN KEY (participant_id) REFERENCES Participant(id)
22);
23
24#1
25INSERT INTO Participant(p_name, p_type) VALUE ('Aleksandrof', 'daskal');
26INSERT INTO Participant(p_name, p_type) VALUE ('Freneca', 'daskal');
27INSERT INTO Participant(p_name, p_type) VALUE ('Az', 'ne-daskal');
28INSERT INTO Participant(p_name, p_type) VALUE ('Ti', 'ne-daskal');
29
30INSERT INTO Groups(g_name) VALUES ('purvi klas');
31INSERT INTO Groups(g_name) VALUES ('vtori klas');
32INSERT INTO Groups(g_name) VALUES ('treti klas');
33
34INSERT INTO ParticipantsInGroup(group_id, participant_id) VALUES (1, 1);
35INSERT INTO ParticipantsInGroup(group_id, participant_id) VALUES (1, 3);
36INSERT INTO ParticipantsInGroup(group_id, participant_id) VALUES (1, 4);
37
38INSERT INTO ParticipantsInGroup(group_id, participant_id) VALUES (2, 2);
39
40#2
41SELECT g.g_name, p.p_name, p.p_type FROM Groups g
42LEFT JOIN ParticipantsInGroup pg ON g.id = pg.group_id
43LEFT JOIN Participant p ON pg.participant_id = p.id;
44
45#3
46SELECT g.g_name, COUNT(pg.participant_id) FROM Groups g
47LEFT JOIN ParticipantsInGroup pg ON g.id = pg.group_id
48GROUP BY g.g_name;
49
50#4
51SELECT g.g_name, COUNT(p.p_type = 'ne-daskal') FROM Groups g
52LEFT JOIN ParticipantsInGroup pg ON g.id = pg.group_id
53LEFT JOIN Participant p ON pg.participant_id = p.id AND p.p_type = 'ne-daskal'
54GROUP BY g.g_name;
55
56#5
57SELECT g.g_name, COUNT(p.p_type = 'daskal') FROM Groups g
58LEFT JOIN ParticipantsInGroup pg ON g.id = pg.group_id
59LEFT JOIN Participant p ON pg.participant_id = p.id AND p.p_type = 'daskal'
60GROUP BY g.g_name;