-- Список студентов указанной группы
SELECT s.last_name, s.first_name, g.group_name
FROM students s
JOIN groups g ON s.group_id = g.group_id
WHERE g.group_name = 'ИВТ-21';

-- Количество студентов в каждой группе
SELECT g.group_name, COUNT(*) AS student_count
FROM students s
JOIN groups g ON s.group_id = g.group_id
GROUP BY g.group_name;

-- Список преподавателей кафедры
SELECT t.last_name, t.first_name, d.department_name
FROM teachers t
JOIN departments d ON t.department_id = d.department_id
WHERE d.department_name = 'Кафедра программирования';

-- Преподаватели с зарплатой выше 100000
SELECT last_name, first_name, salary
FROM teachers
WHERE salary > 100000;

-- Список студентов, сдавших экзамен на отлично
SELECT s.last_name, s.first_name, d.discipline_name
FROM exams e
JOIN students s ON s.student_id = e.student_id
JOIN disciplines d ON d.discipline_id = e.discipline_id
WHERE e.grade = 5;

-- Средний балл студентов
SELECT
    s.last_name,
    s.first_name,
    ROUND(AVG(e.grade), 2) AS average_grade
FROM students s
JOIN exams e ON s.student_id = e.student_id
GROUP BY s.student_id;

-- Студенты, не сдавшие экзамен
SELECT DISTINCT
    s.last_name,
    s.first_name
FROM students s
JOIN exams e ON s.student_id = e.student_id
WHERE e.grade = 2;

-- Количество экзаменов по дисциплинам
SELECT
    d.discipline_name,
    COUNT(*) AS exams_count
FROM exams e
JOIN disciplines d ON d.discipline_id = e.discipline_id
GROUP BY d.discipline_name;

-- Список дисциплин по семестрам
SELECT d.discipline_name, s.semester_number
FROM disciplines d
JOIN teaching_assignments ta
ON d.discipline_id = ta.discipline_id
JOIN semesters s
ON s.semester_id = ta.semester_id;

-- Общая нагрузка преподавателей
SELECT
    t.last_name,
    t.first_name,
    SUM(
        ta.lecture_hours +
        ta.practice_hours +
        ta.lab_hours
    ) AS total_hours
FROM teachers t
JOIN teaching_assignments ta
ON t.teacher_id = ta.teacher_id
GROUP BY t.teacher_id;

-- Преподаватели, ведущие более одной дисциплины
SELECT
    t.last_name,
    t.first_name,
    COUNT(DISTINCT ta.discipline_id) AS disciplines_count
FROM teachers t
JOIN teaching_assignments ta
ON t.teacher_id = ta.teacher_id
GROUP BY t.teacher_id
HAVING COUNT(DISTINCT ta.discipline_id) > 1;

-- Количество студентов по факультетам
SELECT
    f.faculty_name,
    COUNT(s.student_id) AS student_count
FROM faculties f
JOIN groups g ON f.faculty_id = g.faculty_id
JOIN students s ON g.group_id = s.group_id
GROUP BY f.faculty_name;

-- Студенты с повышенной стипендией
SELECT
    last_name,
    first_name,
    scholarship
FROM students
WHERE scholarship >= 15000
ORDER BY scholarship DESC;