---

-- =====================================================
-- 1. СОЗДАНИЕ ТАБЛИЦ (SQLite / стандартный SQL)
-- =====================================================

CREATE TABLE departments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE
);

CREATE TABLE subjects (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    department_id INTEGER NOT NULL,
    FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE RESTRICT
);

CREATE TABLE teachers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    full_name TEXT NOT NULL,
    department_id INTEGER NOT NULL,
    FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE RESTRICT
);

CREATE TABLE students (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    full_name TEXT NOT NULL,
    group_name TEXT NOT NULL,
    department_id INTEGER NOT NULL,
    FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE RESTRICT
);

CREATE TABLE grade_reports (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    student_id INTEGER NOT NULL,
    subject_id INTEGER NOT NULL,
    teacher_id INTEGER NOT NULL,
    grade INTEGER NOT NULL CHECK (grade BETWEEN 2 AND 5),
    exam_date TEXT NOT NULL DEFAULT CURRENT_DATE,
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
    FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE,
    FOREIGN KEY (teacher_id) REFERENCES teachers(id)
);

-- =====================================================
-- 2. ТЕСТОВЫЕ ДАННЫЕ
-- =====================================================

INSERT INTO departments (name) VALUES 
    ('Кафедра информационных систем'),
    ('Кафедра сетей и телекоммуникаций');

INSERT INTO subjects (name, department_id) VALUES 
    ('Базы данных', 1),
    ('Программирование', 1),
    ('Сетевые технологии', 2);

INSERT INTO teachers (full_name, department_id) VALUES 
    ('Ефимова А.А.', 1),
    ('Иванов И.И.', 1),
    ('Петров П.П.', 2);

INSERT INTO students (full_name, group_name, department_id) VALUES 
    ('Сидоров С.С.', 'ИС-21', 1),
    ('Кузнецова К.К.', 'ИС-21', 1),
    ('Смирнов С.С.', 'СТ-31', 2),
    ('Васильева В.В.', 'СТ-31', 2);

INSERT INTO grade_reports (student_id, subject_id, teacher_id, grade, exam_date) VALUES 
    (1, 1, 1, 5, '2025-01-10'),
    (1, 2, 2, 4, '2025-01-15'),
    (2, 1, 1, 3, '2025-01-10'),
    (2, 2, 2, 5, '2025-01-15'),
    (3, 3, 3, 4, '2025-01-20'),
    (4, 3, 3, 5, '2025-01-20'),
    (1, 1, 1, 5, '2025-06-05');

-- =====================================================
-- 3. ЗАПРОСЫ ПО ЗАДАНИЮ
-- =====================================================

-- 3.1 Простой запрос с условием
-- Студенты, сдавшие 'Базы данных' на 5
SELECT s.full_name, g.grade
FROM students s
JOIN grade_reports g ON s.id = g.student_id
JOIN subjects sub ON g.subject_id = sub.id
WHERE sub.name = 'Базы данных' AND g.grade = 5;

-- 3.2 Запрос с соединением таблиц (JOIN)
-- Студент, дисциплина, оценка, преподаватель
SELECT 
    st.full_name AS student,
    sub.name AS subject,
    gr.grade,
    t.full_name AS teacher
FROM grade_reports gr
JOIN students st ON gr.student_id = st.id
JOIN subjects sub ON gr.subject_id = sub.id
JOIN teachers t ON gr.teacher_id = t.id
ORDER BY st.full_name;

-- 3.3 Группировка + агрегатная функция
-- Средний балл по каждой дисциплине
SELECT 
    sub.name AS subject,
    ROUND(AVG(gr.grade), 2) AS avg_grade
FROM grade_reports gr
JOIN subjects sub ON gr.subject_id = sub.id
GROUP BY sub.id, sub.name;

-- 3.4 Подзапрос
-- Студенты со средним баллом выше общего среднего
SELECT 
    s.full_name,
    (SELECT ROUND(AVG(grade), 2) 
     FROM grade_reports 
     WHERE student_id = s.id) AS student_avg
FROM students s
WHERE (SELECT AVG(grade) FROM grade_reports WHERE student_id = s.id) > (
    SELECT AVG(grade) FROM grade_reports
);

-- 3.5 Обновление данных
-- У Сидорова по Программированию оценку 4 → 5
UPDATE grade_reports
SET grade = 5
WHERE student_id = (SELECT id FROM students WHERE full_name = 'Сидоров С.С.')
  AND subject_id = (SELECT id FROM subjects WHERE name = 'Программирование');

-- Проверка обновления
SELECT 
    s.full_name,
    sub.name,
    gr.grade
FROM grade_reports gr
JOIN students s ON gr.student_id = s.id
JOIN subjects sub ON gr.subject_id = sub.id
WHERE s.full_name = 'Сидоров С.С.' AND sub.name = 'Программирование';