DROP DATABASE IF EXISTS db;
CREATE DATABASE db;
USE db;

CREATE TABLE Participant(
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    p_name VARCHAR(256) NOT NULL,
    p_type ENUM('daskal', 'ne-daskal')
);

CREATE TABLE Groups(
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    g_name VARCHAR(256) NOT NULL
);

CREATE TABLE ParticipantsInGroup(
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    group_id INT NOT NULL,
    participant_id INT NOT NULL,
    FOREIGN KEY (group_id) REFERENCES Groups(id),
    FOREIGN KEY (participant_id) REFERENCES Participant(id)
);

#1
INSERT INTO Participant(p_name, p_type) VALUE ('Aleksandrof', 'daskal');
INSERT INTO Participant(p_name, p_type) VALUE ('Freneca', 'daskal');
INSERT INTO Participant(p_name, p_type) VALUE ('Az', 'ne-daskal');
INSERT INTO Participant(p_name, p_type) VALUE ('Ti', 'ne-daskal');

INSERT INTO Groups(g_name) VALUES ('purvi klas');
INSERT INTO Groups(g_name) VALUES ('vtori klas');
INSERT INTO Groups(g_name) VALUES ('treti klas');

INSERT INTO ParticipantsInGroup(group_id, participant_id) VALUES (1, 1);
INSERT INTO ParticipantsInGroup(group_id, participant_id) VALUES (1, 3);
INSERT INTO ParticipantsInGroup(group_id, participant_id) VALUES (1, 4);

INSERT INTO ParticipantsInGroup(group_id, participant_id) VALUES (2, 2);

#2
SELECT g.g_name, p.p_name, p.p_type FROM Groups g
LEFT JOIN ParticipantsInGroup pg ON g.id = pg.group_id
LEFT JOIN Participant p ON pg.participant_id = p.id;

#3
SELECT g.g_name, COUNT(pg.participant_id) FROM Groups g
LEFT JOIN ParticipantsInGroup pg ON g.id = pg.group_id
GROUP BY g.g_name;

#4
SELECT g.g_name, COUNT(p.p_type = 'ne-daskal') FROM Groups g
LEFT JOIN ParticipantsInGroup pg ON g.id = pg.group_id
LEFT JOIN Participant p ON pg.participant_id = p.id AND p.p_type = 'ne-daskal'
GROUP BY g.g_name;

#5
SELECT g.g_name, COUNT(p.p_type = 'daskal') FROM Groups g
LEFT JOIN ParticipantsInGroup pg ON g.id = pg.group_id
LEFT JOIN Participant p ON pg.participant_id = p.id AND p.p_type = 'daskal'
GROUP BY g.g_name;