---

-- Создание БД
CREATE DATABASE StudentsDB;
USE StudentsDB;

-- Кафедры
CREATE TABLE Departments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL
);

-- Дисциплины
CREATE TABLE Subjects (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL
);

-- Преподаватели
CREATE TABLE Teachers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    full_name VARCHAR(100) NOT NULL,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES Departments(id)
);

-- Студенты
CREATE TABLE Students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    full_name VARCHAR(100) NOT NULL,
    group_name VARCHAR(20)
);

-- Ведомость успеваемости
CREATE TABLE Grades (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT,
    subject_id INT,
    grade INT CHECK (grade BETWEEN 2 AND 5),
    FOREIGN KEY (student_id) REFERENCES Students(id),
    FOREIGN KEY (subject_id) REFERENCES Subjects(id)
);

--------------------------------------------------
-- 1. Простая выборка с условием
SELECT *
FROM Students
WHERE group_name = 'ИС-21';

--------------------------------------------------
-- 2. Соединение таблиц
SELECT Students.full_name, Subjects.name, Grades.grade
FROM Grades
JOIN Students ON Grades.student_id = Students.id
JOIN Subjects ON Grades.subject_id = Subjects.id;

--------------------------------------------------
-- 3. Группировка и агрегатная функция
SELECT subject_id, AVG(grade) AS average_grade
FROM Grades
GROUP BY subject_id;

--------------------------------------------------
-- 4. Подзапрос
SELECT *
FROM Students
WHERE id IN (
    SELECT student_id
    FROM Grades
    WHERE grade = 5
);

--------------------------------------------------
-- 5. Обновление данных
UPDATE Grades
SET grade = 5
WHERE id = 1;