---

SELECT 
    cls.class_num,
    cls.class_letter,
    sub.subject_name,
    AVG(CAST(g.grade_value AS FLOAT)) AS avg_subject_grade
FROM Classes cls
JOIN Students st ON cls.class_id = st.class_id
JOIN Grades g ON st.student_id = g.student_id
JOIN Subjects sub ON g.subject_id = sub.subject_id
WHERE g.quarter = 3 -- Номер нужной четверти
GROUP BY cls.class_num, cls.class_letter, sub.subject_name
ORDER BY cls.class_num ASC, cls.class_letter ASC, sub.subject_name ASC;

-- Средний балл по каждому классу
SELECT 
    cls.class_num,
    cls.class_letter,
    AVG(CAST(g.grade_value AS FLOAT)) AS class_avg_grade
FROM Classes cls
JOIN Students st ON cls.class_id = st.class_id
JOIN Grades g ON st.student_id = g.student_id
GROUP BY cls.class_num, cls.class_letter
ORDER BY cls.class_num ASC, cls.class_letter ASC;

-- Общий средний балл по всей школе
SELECT 
    AVG(CAST(grade_value AS FLOAT)) AS school_overall_avg
FROM Grades;

SELECT 
    SUM(CASE WHEN min_grade = 5 AND max_grade = 5 THEN 1 ELSE 0 END) AS only_fives,
    SUM(CASE WHEN min_grade >= 4 THEN 1 ELSE 0 END) AS fours_and_fives_only,
    SUM(CASE WHEN has_three = 1 THEN 1 ELSE 0 END) AS has_at_least_one_three,
    SUM(CASE WHEN has_two = 1 THEN 1 ELSE 0 END) AS has_at_least_one_two
FROM (
    SELECT 
        st.student_id,
        MIN(g.grade_value) AS min_grade,
        MAX(g.grade_value) AS max_grade,
        MAX(CASE WHEN g.grade_value = 3 THEN 1 ELSE 0 END) AS has_three,
        MAX(CASE WHEN g.grade_value = 2 THEN 1 ELSE 0 END) AS has_two
    FROM Students st
    JOIN Grades g ON st.student_id = g.student_id
    GROUP BY st.student_id
) AS student_stats;

-- 1. Классный руководитель для каждого класса
SELECT 
    cls.class_num,
    cls.class_letter,
    t.full_name AS class_teacher
FROM Classes cls
JOIN Teachers t ON cls.class_teacher_id = t.teacher_id
ORDER BY cls.class_num ASC, cls.class_letter ASC;

-- 2. Количество учителей по каждому предмету
SELECT 
    sub.subject_name,
    COUNT(ts.teacher_id) AS teacher_count
FROM Subjects sub
LEFT JOIN Teacher_Subjects ts ON sub.subject_id = ts.subject_id
GROUP BY sub.subject_name;

-- 3. Общее количество кабинетов в школе
SELECT 
    COUNT(*) AS total_cabinets 
FROM Cabinets;