/* 1 */
DROP VIEW IF EXISTS coach_salary_info;
CREATE VIEW coach_salary_info AS
SELECT DISTINCT
    c.name AS coach_name,
    CONCAT(sg.id, ' - ', sg.location) AS group_info,
    s.name AS sport_name,
    YEAR(sp.dateOfPayment) AS year,
    MONTH(sp.dateOfPayment) AS month,
    sp.salaryAmount
FROM
    salaryPayments sp
JOIN
    coaches c ON sp.coach_id = c.id
JOIN
    coach_work cw ON sp.coach_id = cw.coach_id
JOIN
    sportGroups sg ON cw.group_id = sg.id
JOIN
    sports s ON sg.sport_id = s.id
WHERE
    YEAR(sp.dateOfPayment) = YEAR(NOW()) AND MONTH(sp.dateOfPayment) = MONTH(NOW());


/* 2 */
DELIMITER //
DROP PROCEDURE IF EXISTS students_multiple_groups;
CREATE PROCEDURE students_multiple_groups()
BEGIN
    SELECT
        s.name AS student
    FROM
        students s
    JOIN
        student_sport ss ON s.id = ss.student_id
    GROUP BY
        ss.student_id
    HAVING
        COUNT(DISTINCT ss.sportGroup_id) > 1;
END//

DELIMITER ;
CALL students_multiple_groups();

/* 3 */
DELIMITER //
DROP PROCEDURE IF EXISTS coaches_no_groups;
CREATE PROCEDURE coaches_no_groups()
BEGIN
    SELECT
        c.name AS coach_name
    FROM
        coaches c
    LEFT JOIN
        coach_work cw ON c.id = cw.coach_id
    WHERE
        cw.coach_id IS NULL;
END//

DELIMITER ;

CALL coaches_no_groups();