Задание к контрольной работе № 4

Исходные данные:
В школе № 321 проходили предметные олимпиады. В них успешно выступили ученики 9А, 9Б, 10А и 10Б классов. Классный руководитель 9А класса – учитель физики Лутченко Н.А. Классный руководитель 9Б класса – учитель математики Лифшиц И.И. Классный руководитель 10А класса – учитель химии Рогулина Р.П. Классный руководитель 10Б класса – учитель математики Шеина Т.Ю. В соревновании по истории медаль завоевал ученик 9А класса Петр Мешков; грамоту получил ученик 9А класса Иван Голубев; почетный приз – ученица 10Б класса Света Дубинина. В соревновании по математике медаль завоевала ученица 9А класса Людмила Першина; грамоту получила ученица 10А класса Анна Рогова; почетный приз – ученица 10А класса Римма Первина. В соревновании по физике медали получили ученик 9Б класса Алексей Яшин и ученица 10Б класса Воронова Мария. В соревновании по химии медаль получил ученик 9А класса Кирилл Антонов; приз подучил ученик 9А класса Семен Лобов. Возраст победителей: Мешков, Яшин и Лобов – 15 лет; Антонов и Першина – 16 лет; Воронова – 18 лет; остальным ребятам – по 17 лет. По итогам олимпиады за успехи своих воспитанников дипломами были награждены учителя Лутченко, Рогулина и Шеина.

Требуется выполнить следующие действия.
1. Спроектировать, используя нормализацию, и создать базу данных с информацией о результатах олимпиады.
2. Обратиться к БД со следующими за сохранить результаты:
За Получить список всех ребят, награжденных медалями. В списке указать: фамилию, имя, класс, предмет. Упорядочить список в алфавитном порядке по фамилиям.
За Получить список всех награжденных десятиклассников. В списке указать: фамилию, класс, классного руководителя, предмет, награду, возраст. Упорядочить по убыванию возраста.
За Получить список всех награжденных, классным руководителем которых является учитель математики. В списке указать: фамилию, класс, предмет, награда. Упорядочить по фамилиям.
За Получить список всех награжденных девятиклассников, классные руководители которых получили дипломы, и возраст которых не превышает 16 лет. В список включить: фамилию, имя, возраст. Упорядочить по фамилиям в алфавитном порядке.

Добрый день! Сегодня я буду играть роль школьного учителя и помогу вам решить задание к контрольной работе №4.

1. Начнем с первого задания: спроектировать и создать базу данных с информацией о результатах олимпиады, используя нормализацию.

Для этого создадим таблицы с соответствующими полями:

Таблица "Ученики":
- ID (идентификатор);
- Фамилия;
- Имя;
- Класс (9А, 9Б, 10А, 10Б).

Таблица "Предметы":
- ID (идентификатор);
- Название предмета (Физика, Математика, Химия, История).

Таблица "Учителя":
- ID (идентификатор);
- Фамилия;
- Имя;
- Предмет (Физика, Математика, Химия).

Таблица "Результаты":
- ID (идентификатор);
- ID_ученика (связь с таблицей "Ученики");
- ID_предмета (связь с таблицей "Предметы");
- ID_учителя (связь с таблицей "Учителя");
- Медаль (Да/Нет);
- Грамота (Да/Нет);
- Почетный_приз (Да/Нет).

Таблица "Возраст":
- ID (идентификатор);
- ID_ученика (связь с таблицей "Ученики");
- Возраст.

Таблица "Дипломы":
- ID (идентификатор);
- ID_учителя (связь с таблицей "Учителя").


2. Теперь перейдем ко второму заданию: обратиться к БД с результатами олимпиады и выполнить несколько запросов.

- Запрос 1: Получить список всех ребят, награжденных медалями, упорядоченный в алфавитном порядке по фамилиям.

Решение:
```sql
SELECT Ученики.Фамилия, Ученики.Имя, Ученики.Класс, Предметы.Название_предмета
FROM Ученики
JOIN Результаты ON Ученики.ID = Результаты.ID_ученика
JOIN Предметы ON Результаты.ID_предмета = Предметы.ID
WHERE Результаты.Медаль = 'Да'
ORDER BY Ученики.Фамилия;
```

- Запрос 2: Получить список всех награжденных десятиклассников, упорядоченный по убыванию возраста.

Решение:
```sql
SELECT Ученики.Фамилия, Ученики.Класс, Учителя.Фамилия AS Классный_руководитель, Предметы.Название_предмета,
CASE
WHEN Результаты.Медаль = 'Да' THEN 'Медаль'
WHEN Результаты.Грамота = 'Да' THEN 'Грамота'
WHEN Результаты.Почетный_приз = 'Да' THEN 'Почетный приз'
END AS Награда,
Возраст.Возраст
FROM Ученики
JOIN Результаты ON Ученики.ID = Результаты.ID_ученика
JOIN Предметы ON Результаты.ID_предмета = Предметы.ID
JOIN Учителя ON Результаты.ID_учителя = Учителя.ID
JOIN Возраст ON Ученики.ID = Возраст.ID_ученика
WHERE Ученики.Класс LIKE '10%'
ORDER BY Возраст.Возраст DESC;
```

- Запрос 3: Получить список всех награжденных, классным руководителем которых является учитель математики, упорядоченный по фамилиям.

Решение:
```sql
SELECT Ученики.Фамилия, Ученики.Класс, Предметы.Название_предмета,
CASE
WHEN Результаты.Медаль = 'Да' THEN 'Медаль'
WHEN Результаты.Грамота = 'Да' THEN 'Грамота'
WHEN Результаты.Почетный_приз = 'Да' THEN 'Почетный приз'
END AS Награда
FROM Ученики
JOIN Результаты ON Ученики.ID = Результаты.ID_ученика
JOIN Предметы ON Результаты.ID_предмета = Предметы.ID
JOIN Учителя ON Результаты.ID_учителя = Учителя.ID
WHERE Учителя.Предмет = 'Математика'
ORDER BY Ученики.Фамилия;
```

- Запрос 4: Получить список всех награжденных девятиклассников, классные руководители которых получили дипломы, и возраст которых не превышает 16 лет, упорядоченный по фамилиям в алфавитном порядке.

Решение:
```sql
SELECT Ученики.Фамилия, Ученики.Имя, Возраст.Возраст
FROM Ученики
JOIN Результаты ON Ученики.ID = Результаты.ID_ученика
JOIN Возраст ON Ученики.ID = Возраст.ID_ученика
JOIN Учителя ON Результаты.ID_учителя = Учителя.ID
JOIN Дипломы ON Учителя.ID = Дипломы.ID_учителя
WHERE Учителя.Предмет = 'Математика' AND Ученики.Класс LIKE '9%' AND Возраст.Возраст <= 16
ORDER BY Ученики.Фамилия;
```

Это были пошаговые решения для каждого из запросов. Если у вас возникнут какие-либо дополнительные вопросы или нужна помощь с другими заданиями, обязательно спрашивайте!