---

-- =====================================================
-- ЗАДАНИЯ 1-5 (единый скрипт)
-- =====================================================

-- ЗАДАНИЕ 1: Добавление нового курса и запись студентов (CRUD)
-- =====================================================

-- Добавление нового курса
INSERT INTO courses (title, instructor_id, price, duration_hours)
VALUES ('Введение в Data Science', 3, 15000.00, 30);

-- Запись первого студента
INSERT INTO enrollments (student_id, course_id, enroll_date)
VALUES (
    (SELECT id FROM students WHERE email = 'ivan@example.com'),
    (SELECT id FROM courses WHERE title = 'Введение в Data Science'),
    CURRENT_DATE
);

-- Запись второго студента
INSERT INTO enrollments (student_id, course_id, enroll_date)
VALUES (
    (SELECT id FROM students WHERE email = 'elena@example.com'),
    (SELECT id FROM courses WHERE title = 'Введение в Data Science'),
    CURRENT_DATE
);

-- Проверка задания 1
SELECT 'ЗАДАНИЕ 1: Проверка добавленного курса' AS task;
SELECT * FROM courses WHERE title = 'Введение в Data Science';

SELECT 'ЗАДАНИЕ 1: Проверка записей студентов на новый курс' AS task;
SELECT 
    s.full_name AS student_name,
    c.title AS course_title,
    e.enroll_date
FROM enrollments e
JOIN students s ON e.student_id = s.id
JOIN courses c ON e.course_id = c.id
WHERE c.title = 'Введение в Data Science';

-- =====================================================
-- ЗАДАНИЕ 2: Список студентов и их курсов (JOIN)
-- =====================================================

SELECT 'ЗАДАНИЕ 2: Студенты и курсы, на которые они записаны' AS task;
SELECT
    s.full_name AS student_name,
    c.title AS course_title,
    e.enroll_date
FROM enrollments e
JOIN students s ON e.student_id = s.id
JOIN courses c ON e.course_id = c.id
ORDER BY s.full_name, e.enroll_date;

-- Проверка задания 2
SELECT 'ЗАДАНИЕ 2: Проверка - общее количество записей' AS task;
SELECT COUNT(*) AS total_enrollments FROM enrollments;

SELECT 'ЗАДАНИЕ 2: Проверка - количество студентов с курсами' AS task;
SELECT COUNT(DISTINCT student_id) AS students_with_courses FROM enrollments;

-- =====================================================
-- ЗАДАНИЕ 3: Средняя оценка по курсам (Агрегация)
-- =====================================================

SELECT 'ЗАДАНИЕ 3: Средняя оценка по курсам (только завершённые)' AS task;
SELECT
    c.title AS course_title,
    ROUND(AVG(e.final_grade), 2) AS average_grade,
    COUNT(e.id) AS completed_students
FROM enrollments e
JOIN courses c ON e.course_id = c.id
WHERE e.final_grade IS NOT NULL
GROUP BY c.id, c.title
ORDER BY average_grade DESC;

-- Проверка задания 3
SELECT 'ЗАДАНИЕ 3: Проверка - оценки по курсу Основы SQL' AS task;
SELECT 
    s.full_name,
    e.final_grade
FROM enrollments e
JOIN students s ON e.student_id = s.id
JOIN courses c ON e.course_id = c.id
WHERE c.title = 'Основы SQL' 
  AND e.final_grade IS NOT NULL;

SELECT 'ЗАДАНИЕ 3: Проверка - оценки по курсу Анализ данных в Excel' AS task;
SELECT 
    s.full_name,
    e.final_grade
FROM enrollments e
JOIN students s ON e.student_id = s.id
JOIN courses c ON e.course_id = c.id
WHERE c.title = 'Анализ данных в Excel' 
  AND e.final_grade IS NOT NULL;

-- =====================================================
-- ЗАДАНИЕ 4: Курсы дороже среднего (Фильтрация с подзапросом)
-- =====================================================

SELECT 'ЗАДАНИЕ 4: Курсы дороже среднего' AS task;
SELECT
    title,
    price,
    duration_hours
FROM courses
WHERE price > (SELECT AVG(price) FROM courses)
ORDER BY price DESC;

-- Проверка задания 4
SELECT 'ЗАДАНИЕ 4: Проверка - средняя цена всех курсов' AS task;
SELECT AVG(price) AS average_price FROM courses;

SELECT 'ЗАДАНИЕ 4: Проверка - все курсы с ценами' AS task;
SELECT title, price FROM courses ORDER BY price DESC;

-- =====================================================
-- ЗАДАНИЕ 5: Сортировка инструкторов по рейтингу
-- =====================================================

SELECT 'ЗАДАНИЕ 5: Инструкторы по убыванию рейтинга' AS task;
SELECT
    full_name,
    rating
FROM instructors
ORDER BY rating DESC;

-- Проверка задания 5
SELECT 'ЗАДАНИЕ 5: Проверка - инструктор с максимальным рейтингом' AS task;
SELECT full_name, rating 
FROM instructors 
WHERE rating = (SELECT MAX(rating) FROM instructors);

SELECT 'ЗАДАНИЕ 5: Проверка - инструктор с минимальным рейтингом' AS task;
SELECT full_name, rating 
FROM instructors 
WHERE rating = (SELECT MIN(rating) FROM instructors);