---

Ниже подготовлены запросы для твоей базы данных на MySQL 8.0+. Для каждого запроса есть объяснение, что он делает и какой результат возвращает.

1. Запросы на добавление и удаление таблиц

Здесь будут 2 запроса на создание таблиц и 2 запроса на удаление таблиц. Выполнять запросы на удаление нужно осторожно: они удаляют саму таблицу вместе с данными.

1.1. Добавление таблицы преподавателей
CREATE TABLE IF NOT EXISTS teachers_backup (
    teacher_id INT AUTO_INCREMENT PRIMARY KEY,
    last_name VARCHAR(50) NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    specialization VARCHAR(100) NOT NULL
);

Объяснение: создаётся таблица teachers_backup, предназначенная для хранения фамилии, имени и специализации преподавателя. IF NOT EXISTS предотвращает ошибку, если таблица с таким названием уже существует.

1.2. Добавление таблицы для истории заявок
CREATE TABLE IF NOT EXISTS application_history (
    history_id INT AUTO_INCREMENT PRIMARY KEY,
    application_id INT NOT NULL,
    old_status VARCHAR(20),
    new_status VARCHAR(20),
    changed_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Объяснение: создаётся таблица application_history, в которой можно хранить сведения об изменении статусов заявок: прежний статус, новый статус и дату изменения.

Примечание: эти две таблицы вспомогательные и не входят в исходные семь таблиц базы данных.

1.3. Удаление таблицы преподавателей из резервной таблицы
DROP TABLE IF EXISTS teachers_backup;

Объяснение: удаляет таблицу teachers_backup, если она существует. Вместе с таблицей удаляются все находящиеся в ней данные.

1.4. Удаление таблицы истории заявок
DROP TABLE IF EXISTS application_history;

Объяснение: удаляет таблицу application_history, если она существует. Команда IF EXISTS позволяет избежать ошибки, если таблица уже была удалена.

2. Выборка данных с условием отбора — 5 запросов

Для этих запросов используется оператор SELECT, который выбирает данные, и WHERE, который задаёт условия отбора.

Запрос 1. Найти всех детей с определённой фамилией
SELECT *
FROM children
WHERE last_name = 'Иванов';

Объяснение: выводит все сведения о детях, фамилия которых — Иванов. Заменив фамилию в условии, можно найти других детей.

Запрос 2. Найти программы длительностью более 50 часов
SELECT name, duration_hours
FROM programs
WHERE duration_hours > 50;

Объяснение: выводит названия программ и их продолжительность, если программа рассчитана более чем на 50 часов. Оператор > означает «больше».

Запрос 3. Найти кружки, в которых есть свободные места
SELECT group_id, program_id, teacher_id, free_seats
FROM `groups`
WHERE free_seats > 0;

Объяснение: показывает кружки, в которых осталось хотя бы одно свободное место. Кавычки вокруг groups нужны для корректного использования имени таблицы.

Запрос 4. Найти заявки со статусом «новая»
SELECT application_id, child_id, group_id, submitted_at
FROM applications
WHERE status = 'новая';

Объяснение: выводит все новые заявки, которые ещё не перешли в другой статус. Это позволяет определить заявки, требующие рассмотрения.

Запрос 5. Найти преподавателей по специализации
SELECT last_name, first_name, specialization
FROM teachers
WHERE specialization LIKE '%программирование%';

Объяснение: находит преподавателей, в специализации которых содержится слово «программирование». Символ % означает любое количество любых символов, в том числе ноль.

3. Выборка с использованием группировки и фильтрации — 5 запросов

В этих запросах используются GROUP BY, COUNT(), AVG() и HAVING.

GROUP BY — объединяет записи в группы по заданному полю.

COUNT() — считает количество записей.

AVG() — вычисляет среднее значение.

HAVING — фильтрует уже сформированные группы.

Запрос 1. Подсчитать количество детей у каждого родителя
SELECT parent_id, COUNT(*) AS children_count
FROM children
GROUP BY parent_id;

Объяснение: группирует детей по идентификатору родителя и считает количество детей в каждой группе. Результат содержит идентификатор родителя и количество связанных с ним детей.

Запрос 2. Найти родителей, у которых больше одного ребёнка
SELECT parent_id, COUNT(*) AS children_count
FROM children
GROUP BY parent_id
HAVING COUNT(*) > 1;

Объяснение: сначала группирует детей по родителям, а затем оставляет только тех родителей, у которых больше одного ребёнка. Условие применяется через HAVING, потому что оно проверяет результат группировки.

Запрос 3. Подсчитать количество заявок каждого статуса
SELECT status, COUNT(*) AS applications_count
FROM applications
GROUP BY status;

Объяснение: объединяет заявки по статусам и подсчитывает их количество в каждой группе. Например, результат может показать, сколько заявок новых, одобренных или отклонённых.

Запрос 4. Найти программы, по которым открыто более двух кружков
SELECT program_id, COUNT(*) AS groups_count
FROM `groups`
GROUP BY program_id
HAVING COUNT(*) > 2;

Объяснение: группирует кружки по образовательным программам и считает количество кружков каждой программы. В результат попадают программы, для которых создано более двух кружков.

Запрос 5. Найти кружки со средним количеством свободных мест больше пяти
SELECT program_id, AVG(free_seats) AS average_free_seats
FROM `groups`
GROUP BY program_id
HAVING AVG(free_seats) > 5;

Объяснение: для каждой программы вычисляет среднее количество свободных мест в её кружках. Затем выводит только программы, у которых среднее значение превышает пять.

4. Выборка данных из нескольких таблиц — 5 запросов

Для получения связанных данных используются JOIN и LEFT JOIN. Они позволяют объединять сведения по внешним ключам.

Запрос 1. Вывести детей вместе с данными родителей
SELECT
    children.last_name AS child_last_name,
    children.first_name AS child_first_name,
    parents.last_name AS parent_last_name,
    parents.first_name AS parent_first_name,
    parents.phone
FROM children
JOIN parents
    ON children.parent_id = parents.parent_id;

Объяснение: объединяет таблицы children и parents по полю parent_id. Результат показывает имя ребёнка, имя родителя и его телефон.

Запрос 2. Вывести заявки с именами детей
SELECT
    applications.application_id,
    children.last_name,
    children.first_name,
    applications.status,
    applications.submitted_at
FROM applications
JOIN children
    ON applications.child_id = children.child_id;

Объяснение: объединяет заявки с таблицей детей. Вместо одного только идентификатора ребёнка можно увидеть его фамилию и имя, а также статус и дату подачи заявки.

Запрос 3. Вывести кружки, названия программ и преподавателей
SELECT
    `groups`.group_id,
    programs.name AS program_name,
    teachers.last_name AS teacher_last_name,
    teachers.first_name AS teacher_first_name,
    `groups`.room,
    `groups`.academic_year
FROM `groups`
JOIN programs
    ON `groups`.program_id = programs.program_id
JOIN teachers
    ON `groups`.teacher_id = teachers.teacher_id;

Объяснение: объединяет три таблицы: groups, programs и teachers. Запрос показывает, какая программа проводится в каждом кружке и какой преподаватель его ведёт.

Запрос 4. Вывести расписание занятий с названиями программ
SELECT
    schedule.weekday,
    schedule.start_time,
    schedule.end_time,
    `groups`.group_id,
    programs.name AS program_name
FROM schedule
JOIN `groups`
    ON schedule.group_id = `groups`.group_id
JOIN programs
    ON `groups`.program_id = programs.program_id
ORDER BY schedule.weekday, schedule.start_time;

Объяснение: объединяет расписание, кружки и программы. Результат показывает день недели, время занятия, номер кружка и название программы. ORDER BY сортирует записи по дню недели и времени начала.

Запрос 5. Вывести заявки, детей и назначенные кружки
SELECT
    applications.application_id,
    children.last_name,
    children.first_name,
    applications.status,
    programs.name AS program_name,
    applications.submitted_at
FROM applications
JOIN children
    ON applications.child_id = children.child_id
LEFT JOIN `groups`
    ON applications.group_id = `groups`.group_id
LEFT JOIN programs
    ON `groups`.program_id = programs.program_id;

Объяснение: объединяет заявки с детьми, кружками и образовательными программами.

Здесь используется LEFT JOIN, чтобы заявки без назначенного кружка тоже отображались. Если кружок ещё не назначен, поле program_name будет содержать NULL.

Итог

В работе представлены 17 запросов: 2 на создание таблиц, 2 на удаление таблиц и по 5 запросов для каждого из трёх видов выборки.

Раздел

Количество


Создание таблиц

2


Удаление таблиц

2


Выборка с условиями WHERE

5


Группировка и фильтрация GROUP BY, HAVING

5


Выборка из нескольких таблиц (JOIN)

5


Всего

19


Обрати внимание: итоговое количество — 19 запросов. Чтобы увидеть результаты запросов на выборку, сначала нужно добавить данные в таблицы. Если таблицы пустые, запросы выполнятся, но вернут пустые результаты.