CREATE DATABASE School;

---------------------------------------------------------------------------------------------------------------------------------------
USE School;

CREATE TABLE Timepair (
    id INT IDENTITY(1,1) PRIMARY KEY,
    start_pair TIME NOT NULL,
    end_pair TIME NOT NULL
);

CREATE TABLE Class (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name VARCHAR(200) NOT NULL
);

CREATE TABLE Student (
    id INT IDENTITY(1,1) PRIMARY KEY,
    first_name VARCHAR(200) NOT NULL,
    middle_name VARCHAR(200),
    last_name VARCHAR(200) NOT NULL,
    birthday DATETIME NOT NULL,
    address VARCHAR(200)
);

CREATE TABLE Student_in_class (
    class INT REFERENCES Class(id),
    student INT REFERENCES Student(id),
    PRIMARY KEY (class, student)
);

CREATE TABLE Teacher (
    id INT IDENTITY(1,1) PRIMARY KEY,
    first_name VARCHAR(200) NOT NULL,
    middle_name VARCHAR(200),
    last_name VARCHAR(200) NOT NULL
);

CREATE TABLE Subject (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name VARCHAR(200) NOT NULL
);


CREATE TABLE Schedule (
    id INT IDENTITY(1,1) PRIMARY KEY,
    DATE DATE NOT NULL,
    class INT REFERENCES Class(id),
    number_pair INT REFERENCES Timepair(id),
    teacher INT REFERENCES Teacher(id),
    subject INT REFERENCES Subject(id),
    classroom INT NOT NULL
);

---------------------------------------------------------------------------------------------------------------------------------------
USE School;

INSERT INTO Class (name)
VALUES
('9A'),
('10B'),
('11A'),
('11B'),
('12A');

INSERT INTO Student (first_name, middle_name, last_name, birthday, address)
VALUES
('Иван', 'Иванович', 'Иванов', '2005-01-01', 'ул. Ленина, 1'),
('Петр', 'Петрович', 'Петров', '2005-02-02', 'ул. Пушкина, 2'),
('Мария', 'Сергеевна', 'Сидорова', '2005-03-03', 'ул. Гоголя, 3'),
('Анна', 'Владимировна', 'Кузнецова', '2005-04-04', 'ул. Лермонтова, 4'),
('Дмитрий', 'Александрович', 'Волков', '2005-05-05', 'ул. Толстого, 5');

INSERT INTO Student_in_class (class, student)
VALUES
(1, 1),
(1, 2),
(2, 3),
(2, 4),
(3, 5);

INSERT INTO Teacher (first_name, middle_name, last_name)
VALUES
('Павел', 'Петрович', 'Ромашкин'),
('Василий', 'Антонович', 'Колесников'),
('Мария', 'Степанова', 'Ваулина'),
('Таисия', 'Тимуровна', 'Жедринская'),
('Марина', 'Геннадьевна', 'Сосновская');

INSERT INTO Subject (name)
VALUES
('Математика'),
('Русский язык'),
('Литература'),
('Физика'),
('Физкультура');

INSERT INTO Schedule (DATE, class, number_pair, teacher, subject, classroom)
VALUES
('2019-09-02', 1, 1, 1, 1, 101),
('2019-09-02', 2, 2, 2, 2, 202),
('2023-10-27', 3, 3, 3, 3, 303),
('2023-10-27', 4, 4, 4, 4, 404),
('2023-10-28', 5, 5, 5, 5, 505);

INSERT INTO Timepair (start_pair, end_pair)
VALUES
('08:00', '09:30'),
('09:40', '11:10'),
('11:20', '12:50'),
('13:00', '14:30'),
('14:40', '16:10');

---------------------------------------------------------------------------------------------------------------------------------------
#1) Сколько всего 10-ых классов?
USE School;

SELECT COUNT(*) AS '10-е классы'
FROM Class
WHERE name LIKE '10%';

#2) Сколько различных кабинетов школы использовались 2.09.2019 в образовательных целях ?
USE School;

SELECT COUNT(*) AS 'Кол-во классов'
FROM Schedule
WHERE DATE = '2019-09-02';

#3) Выведите информацию об обучающихся живущих на улице Пушкина (ul. Pushkina)?
SELECT *
FROM Student
WHERE address LIKE N'%ул. Пушкина%';

#4) Сколько лет самому молодому обучающемуся ?
SELECT MIN(DATEDIFF(YEAR, birthday, GETDATE ())) AS 'Самый молодой ученик'
FROM Student;

#5) Выведите название предметов, которые преподает Ромашкин П.П. (Romashkin P.P.) ?
SELECT name
FROM Subject
WHERE id = 1

#6) Во сколько начинается 4-ый учебный предмет по расписанию ?
SELECT start_pair
FROM Timepair
WHERE id = 4

#7) Сколько времени обучающийся будет находиться в школе, учась со 2-го по 4-ый уч. предмет ?
SELECT
    CAST(SUM(DATEDIFF(MINUTE, Timepair.start_pair, Timepair.end_pair)) AS FLOAT) / 60 'Кол-во часов'
FROM Schedule
INNER JOIN Timepair ON Schedule.number_pair = Timepair.id
WHERE Schedule.number_pair BETWEEN 2 AND 4;

#8) Выведите фамилии преподавателей, которые ведут физическую культуру (Physical Culture). Отсортируйте преподавателей по фамилии.
USE School;
SELECT Teacher.last_name AS 'Фамилия учителя'
FROM Teacher
JOIN Schedule ON Teacher.id = Schedule.teacher
JOIN Subject ON Schedule.subject = Subject.id
WHERE Subject.name = 'Физкультура'
ORDER BY Teacher.last_name;

#9) Найдите максимальный возраст (колич. лет) среди обучающихся 10 классов ?
SELECT MAX(DATEDIFF(YEAR, birthday, GETDATE())) AS 'Возраст'
FROM Student
WHERE id IN (
    SELECT student
    FROM Student_in_class
    WHERE class IN (
        SELECT id
        FROM Class
        WHERE name LIKE '10%'
    )
);

#10) В каких классах ведет занятия преподаватель "Krauze" ?
SELECT Class.name AS 'Класс'
FROM Class
WHERE Class.id IN (
    SELECT Schedule.class
    FROM Schedule
    WHERE Schedule.teacher = (
        SELECT id
        FROM Teacher
        WHERE Teacher.last_name = 'Круз'
    )
);
---------------------------------------------------------------------------------------------------------------------------------------