---

-- =====================================================
-- КУРСЫ ПОВЫШЕНИЯ КВАЛИФИКАЦИИ (CoursesDB)
-- Полный скрипт: DDL + DML + все 5 заданий
-- =====================================================

-- 1. УДАЛЕНИЕ ТАБЛИЦ (если существуют)
DROP TABLE IF EXISTS enrollments;
DROP TABLE IF EXISTS students;
DROP TABLE IF EXISTS courses;
DROP TABLE IF EXISTS instructors;

-- 2. СОЗДАНИЕ СТРУКТУРЫ (DDL)
CREATE TABLE instructors (
    id SERIAL PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    rating NUMERIC(3,2) CHECK (rating BETWEEN 0 AND 5)
);

CREATE TABLE courses (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    instructor_id INT NOT NULL REFERENCES instructors(id) ON DELETE CASCADE,
    price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
    duration_hours INT NOT NULL CHECK (duration_hours > 0)
);

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL
);

CREATE TABLE enrollments (
    id SERIAL PRIMARY KEY,
    student_id INT NOT NULL REFERENCES students(id) ON DELETE CASCADE,
    course_id INT NOT NULL REFERENCES courses(id) ON DELETE CASCADE,
    enroll_date DATE NOT NULL DEFAULT CURRENT_DATE,
    final_grade INT CHECK (final_grade BETWEEN 1 AND 100)
);

-- 3. НАПОЛНЕНИЕ ТЕСТОВЫМИ ДАННЫМИ (DML)

-- Инструкторы
INSERT INTO instructors (full_name, rating) VALUES
('Анна Иванова', 4.8),
('Борис Петров', 4.2),
('Виктор Смирнов', 4.9);

-- Курсы
INSERT INTO courses (title, instructor_id, price, duration_hours) VALUES
('Основы SQL', 1, 5000.00, 20),
('Продвинутый Python', 2, 12000.00, 40),
('Анализ данных в Excel', 1, 3500.00, 15),
('Машинное обучение', 3, 25000.00, 60);

-- Студенты
INSERT INTO students (full_name, email) VALUES
('Иван Иванов', 'ivan@example.com'),
('Мария Петрова', 'maria@example.com'),
('Сергей Сидоров', 'sergey@example.com'),
('Елена Смирнова', 'elena@example.com'),
('Дмитрий Козлов', 'dmitry@example.com');

-- Записи на курсы
INSERT INTO enrollments (student_id, course_id, enroll_date, final_grade) VALUES
(1, 1, '2024-01-15', 85),
(1, 2, '2024-03-10', NULL),
(2, 1, '2024-01-20', 92),
(2, 3, '2024-02-01', 78),
(3, 2, '2024-04-01', NULL),
(4, 4, '2024-02-15', NULL),
(5, 1, '2024-01-25', 65);

-- =====================================================
-- ВЫПОЛНЕНИЕ ЗАДАНИЙ
-- =====================================================

-- ЗАДАНИЕ 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)
SELECT s.id, c.id, CURRENT_DATE
FROM students s
CROSS JOIN courses c
WHERE s.email IN ('ivan@example.com', 'elena@example.com')
  AND c.title = 'Введение в Data Science';

-- Проверка результата задания 1
SELECT 'Задание 1: Новый курс и записи' AS task;
SELECT * FROM courses WHERE title = 'Введение в Data Science';
SELECT s.full_name, c.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;

-- =====================================================

-- ЗАДАНИЕ 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;

-- =====================================================

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

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

-- =====================================================

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

SELECT 'Задание 5: Инструкторы по рейтингу' AS task;
SELECT
    full_name,
    rating
FROM instructors
ORDER BY rating DESC;

-- =====================================================
-- КОНЕЦ СКРИПТА
-- =====================================================