---

CREATE DATABASE IF NOT EXISTS ExamUniversity;
USE ExamUniversity;

-- Таблица Дисциплины
CREATE TABLE Дисциплины (
    id_дисциплины INT PRIMARY KEY AUTO_INCREMENT,
    название      VARCHAR(100) NOT NULL
);

-- Таблица Вопросы
CREATE TABLE Вопросы (
    id_вопроса    INT PRIMARY KEY AUTO_INCREMENT,
    вопрос        TEXT NOT NULL,
    id_дисциплины INT NOT NULL,
    раздел        CHAR(1),
    FOREIGN KEY (id_дисциплины) REFERENCES Дисциплины(id_дисциплины)
);

-- Таблица Ответы
CREATE TABLE Ответы (
    id_ответа   INT PRIMARY KEY AUTO_INCREMENT,
    описание    TEXT NOT NULL,
    верный      TINYINT(1) NOT NULL,
    id_вопроса  INT NOT NULL,
    FOREIGN KEY (id_вопроса) REFERENCES Вопросы(id_вопроса)
);

-- Таблица Варианты
CREATE TABLE Варианты (
    id_варианта   INT PRIMARY KEY AUTO_INCREMENT,
    номер_варианта INT NOT NULL,
    id_вопроса    INT NOT NULL,
    id_дисциплины INT NOT NULL,
    FOREIGN KEY (id_вопроса)    REFERENCES Вопросы(id_вопроса),
    FOREIGN KEY (id_дисциплины) REFERENCES Дисциплины(id_дисциплины)
);

-- Таблица Тема
CREATE TABLE Тема (
    id_темы       INT PRIMARY KEY AUTO_INCREMENT,
    id_дисциплины INT NOT NULL,
    номер_темы    INT NOT NULL,
    id_вопроса    INT NOT NULL,
    FOREIGN KEY (id_дисциплины) REFERENCES Дисциплины(id_дисциплины),
    FOREIGN KEY (id_вопроса)    REFERENCES Вопросы(id_вопроса)
);

-- Таблица Студенты (нужна для заданий 2-4, 6-7, 10-11)
CREATE TABLE Студенты (
    id_студента INT PRIMARY KEY AUTO_INCREMENT,
    фамилия     VARCHAR(50) NOT NULL,
    имя         VARCHAR(50) NOT NULL,
    курс        INT NOT NULL
);

-- Таблица Экзамены (нужна для заданий 8-9)
CREATE TABLE Экзамены (
    id_экзамена   INT PRIMARY KEY AUTO_INCREMENT,
    id_студента   INT NOT NULL,
    id_дисциплины INT NOT NULL,
    оценка        TINYINT NOT NULL,
    FOREIGN KEY (id_студента)   REFERENCES Студенты(id_студента),
    FOREIGN KEY (id_дисциплины) REFERENCES Дисциплины(id_дисциплины)
);

-- Дисциплины
INSERT INTO Дисциплины (название) VALUES
('Математика'),
('Физика'),
('Информатика'),
('История'),
('Химия');

-- Вопросы
INSERT INTO Вопросы (вопрос, id_дисциплины, раздел) VALUES
('Что такое производная?',            1, 'A'),
('Второй закон Ньютона?',             2, 'A'),
('Что такое переменная в программе?', 3, 'B'),
('Год основания Москвы?',             4, 'A'),
('Формула воды?',                     5, 'B');

-- Ответы
INSERT INTO Ответы (описание, верный, id_вопроса) VALUES
('Предел отношения приращения функции', 1, 1),
('F = ma',                              1, 2),
('Именованная область памяти',          1, 3),
('1147 год',                            1, 4),
('H2O',                                 1, 5);

-- Варианты
INSERT INTO Варианты (номер_варианта, id_вопроса, id_дисциплины) VALUES
(1, 1, 1),
(1, 2, 2),
(2, 3, 3),
(2, 4, 4),
(3, 5, 5);

-- Тема
INSERT INTO Тема (id_дисциплины, номер_темы, id_вопроса) VALUES
(1, 1, 1),
(2, 1, 2),
(3, 2, 3),
(4, 1, 4),
(5, 3, 5);

-- Студенты
INSERT INTO Студенты (фамилия, имя, курс) VALUES
('Иванов',   'Алексей',   1),
('Петрова',  'Мария',     2),
('Сидоров',  'Дмитрий',   2),
('Козлова',  'Ольга',     3),
('Новиков',  'Иван',      4);

-- Экзамены
INSERT INTO Экзамены (id_студента, id_дисциплины, оценка) VALUES
(1, 1, 4),
(2, 1, 5),
(3, 2, 3),
(4, 3, 5),
(5, 4, 4);

SELECT * FROM Дисциплины;

SELECT *
FROM Студенты
WHERE курс = 2;

SELECT *
FROM Студенты
LIMIT 3;

SELECT *
FROM Студенты
WHERE курс BETWEEN 1 AND 4;

INSERT INTO Дисциплины (название)
VALUES ('Биология');

SELECT COUNT(*) AS всего_студентов
FROM Студенты;

SELECT COUNT(*) AS студентов_на_2_курсе
FROM Студенты
WHERE курс = 2;

SELECT MAX(оценка) AS максимальная_оценка
FROM Экзамены;

SELECT AVG(оценка) AS средняя_оценка
FROM Экзамены
WHERE id_дисциплины = 1;

SELECT курс,
       COUNT(*) AS количество_студентов
FROM Студенты
GROUP BY курс
ORDER BY курс ASC;


DELETE FROM Студенты
WHERE id_студента = 5;