---

-- =============================================
-- Шаг 1. Создание базы данных (выполняется отдельно)
-- =============================================
CREATE DATABASE university_system;

-- =============================================
-- Шаг 2. Подключение к базе (в pgAdmin выберите БД)
-- =============================================
-- USE university_system; -- в pgAdmin не нужен, просто выберите БД

-- =============================================
-- Шаг 3. Создание таблицы факультетов
-- =============================================
CREATE TABLE faculties (
    id SERIAL PRIMARY KEY,
    faculty_name VARCHAR(100) NOT NULL
);

-- =============================================
-- Шаг 4. Создание таблицы групп (связь с факультетом)
-- =============================================
CREATE TABLE groups (
    id SERIAL PRIMARY KEY,
    group_name VARCHAR(20) NOT NULL,
    faculty_id INTEGER REFERENCES faculties(id) ON DELETE SET NULL
);

-- =============================================
-- Шаг 5. Создание таблицы студентов (связь с группой)
-- =============================================
CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    group_id INTEGER REFERENCES groups(id) ON DELETE SET NULL
);

-- =============================================
-- Шаг 6. Создание таблицы преподавателей (связь с факультетом)
-- =============================================
CREATE TABLE teachers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    faculty_id INTEGER REFERENCES faculties(id) ON DELETE SET NULL
);

-- =============================================
-- Шаг 7. Создание таблицы курсов (связь с преподавателем)
-- =============================================
CREATE TABLE courses (
    id SERIAL PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL,
    teacher_id INTEGER REFERENCES teachers(id) ON DELETE SET NULL
);

-- =============================================
-- Шаг 8. Добавление данных (faculties, groups, students, teachers, courses)
-- =============================================
INSERT INTO faculties (faculty_name) VALUES
('Computer Science'),
('Mathematics'),
('Physics');

INSERT INTO groups (group_name, faculty_id) VALUES
('CS-101', 1),
('CS-102', 1),
('MATH-201', 2),
('PHY-301', 3);

INSERT INTO students (name, group_id) VALUES
('Alex Johnson', 1),
('Maria Smith', 1),
('John Doe', 2),
('Kate Brown', 3),
('Peter Lee', 4);

INSERT INTO teachers (name, faculty_id) VALUES
('Ivan Petrov', 1),
('Anna Smirnova', 2),
('Sergey Ivanov', 1),
('Elena Sidorova', 3);

INSERT INTO courses (course_name, teacher_id) VALUES
('Databases', 1),
('Algebra', 2),
('Programming', 3),
('Quantum Mechanics', 4);

-- Для демонстрации оценок добавим таблицу "Оценки" (связь студент-курс)
-- Это пятая таблица (у нас уже есть 5: faculties, groups, students, teachers, courses)
-- Но для агрегатов нужны оценки. Добавим шестую? По условию "не менее пяти" – можно и шесть.
-- Я добавлю enrollment как необходимую связующую таблицу (тогда будет 6 таблиц – это допустимо).
-- Или можно добавить столбец grade в students, но тогда не связь с курсом. Лучше сделаем enrollment.
CREATE TABLE enrollments (
    id SERIAL PRIMARY KEY,
    student_id INTEGER REFERENCES students(id) ON DELETE CASCADE,
    course_id INTEGER REFERENCES courses(id) ON DELETE CASCADE,
    grade INTEGER CHECK (grade >= 0 AND grade <= 100)
);

-- Данные об успеваемости
INSERT INTO enrollments (student_id, course_id, grade) VALUES
(1, 1, 85),
(1, 2, 78),
(2, 1, 90),
(2, 3, 88),
(3, 2, 75),
(4, 4, 92),
(5, 3, 80);

-- =============================================
-- Шаг 9. JOIN нескольких таблиц (студент → группа → факультет + курс + преподаватель)
-- =============================================
SELECT 
    s.name AS student_name,
    g.group_name,
    f.faculty_name,
    c.course_name,
    t.name AS teacher_name,
    e.grade
FROM students s
JOIN groups g ON s.group_id = g.id
JOIN faculties f ON g.faculty_id = f.id
JOIN enrollments e ON s.id = e.student_id
JOIN courses c ON e.course_id = c.id
JOIN teachers t ON c.teacher_id = t.id;

-- =============================================
-- Шаг 10. Агрегатный запрос + GROUP BY (средний балл по курсам)
-- =============================================
SELECT 
    c.course_name,
    AVG(e.grade) AS avg_grade
FROM enrollments e
JOIN courses c ON e.course_id = c.id
GROUP BY c.course_name;

-- =============================================
-- Шаг 11. HAVING (курсы со средним баллом >= 80)
-- =============================================
SELECT 
    c.course_name,
    AVG(e.grade) AS avg_grade
FROM enrollments e
JOIN courses c ON e.course_id = c.id
GROUP BY c.course_name
HAVING AVG(e.grade) >= 80;

-- =============================================
-- Шаг 12. Подзапрос (студенты с баллом выше среднего по всем оценкам)
-- =============================================
SELECT DISTINCT s.name, e.grade
FROM students s
JOIN enrollments e ON s.id = e.student_id
WHERE e.grade > (SELECT AVG(grade) FROM enrollments);

-- =============================================
-- Шаг 13. Создание представления (студенты-отличники, grade >= 85)
-- =============================================
CREATE VIEW excellent_students AS
SELECT DISTINCT s.id, s.name, e.grade
FROM students s
JOIN enrollments e ON s.id = e.student_id
WHERE e.grade >= 85;

-- =============================================
-- Шаг 14. Использование представления
-- =============================================
SELECT * FROM excellent_students ORDER BY grade DESC;