SQL-практикум: реестр обращений

Как пользоваться

1. Нажмите кнопку выше, введите логин и пароль.
2. Слева список таблиц. Щёлкните по таблице, чтобы увидеть её строки и столбцы.
3. Вкладка Query сверху: вставьте запрос и нажмите Run Query (или Ctrl+Enter).
4. Результат появится под запросом. Кнопка CSV скачает его файлом.

Учебный реестр обращений, данные вымышленные. Откройте sqliteonline.com, загрузите setup.sql (File → Open SQL) и нажмите Run; или откройте registry.sqlite в DBeaver. Формат: тема на экране — выполняете сами — сверяем с эталоном. Ожидаемые числа посчитаны на этой базе; расхождение — повод спросить «почему».

Таблицы

Таблица	Строк	Столбцы
cases	92	case_id, person_id, district_code, topic_code, channel, opened_at, closed_at, days_to_close, status
persons	31	person_id, iin, name, birth_date
districts	5	district_code, title, valid_from
districts_history	6	district_code, title, valid_from, valid_to
topics	6	topic_code, title

Задание 0: проверка подключения

SELECT COUNT(*) AS cases_total FROM cases;

Ожидается: 92.

Задание 1: пять последних обращений

• Покажите номер обращения, код района и дату регистрации
• Только пять строк, самые свежие сверху
• При одинаковой дате выше должно быть обращение с большим номером
• Подсказка: два столбца в ORDER BY через запятую, у каждого своё направление
• Ожидается: 5 строк, первая — обращение 1033 от 28 августа

Задание 2: обращения района R-2 за второй квартал

• Покажите номер, код темы, дату регистрации и канал
• Только район R-2 и только даты с 1 апреля по 30 июня 2026 включительно
• Упорядочьте по дате регистрации, старые сверху
• Подсказка: правая граница — «раньше 1 июля»
• Ожидается: 6 строк, первая — обращение 1072 от 22 апреля

Задание 3: сколько открытых обращений пришло с портала и по телефону

• Посчитайте число обращений, у которых нет даты закрытия
• Только каналы «портал» и «телефон»
• Результат — одно число; назовите столбец open_cases
• Подсказка: COUNT(*) в SELECT и два условия в WHERE
• Ожидается: 12

Задание 4: сводка по всему реестру

• Одной строкой: всего обращений, закрытых обращений, средний срок закрытия, максимальный срок
• Средний срок округлите до одного знака после запятой
• Назовите столбцы cases_total, cases_closed, avg_days, max_days
• Подсказка: закрытые — COUNT по столбцу closed_at; средний — ROUND(AVG(...), 1)
• Ожидается: 92, 68, 13.2, 28

Задание 5: районы с 15 и более обращениями

• По каждому району: код района, всего обращений, закрытых обращений, средний срок с одним знаком
• Оставьте только районы, где обращений 15 и больше
• Упорядочьте по среднему сроку, самые долгие сверху
• Подсказка: условие на число обращений — в HAVING, а не в WHERE
• Ожидается: 4 строки, первая — R-2 со сроком 17.3

Задание 6: сколько обращений регистрировали каждый месяц

• Сгруппируйте обращения по месяцу регистрации; покажите месяц и число обращений
• Месяц — первые семь символов даты: SUBSTR(opened_at, 1, 7) даёт «2026-04»
• Упорядочьте по месяцу
• Подсказка: то же выражение и в SELECT, и в GROUP BY
• Ожидается: 8 строк, январь 14, август 13

Задание 7: обращения по названиям районов

• К каждому обращению подтяните название района из справочника districts
• Сгруппируйте по названию, посчитайте обращения, самые крупные сверху
• Обращения, у которых район не нашёлся в справочнике, потерять нельзя
• Подсказка: LEFT JOIN, группировка по d.title
• Ожидается: 6 строк, одна из них с пустым названием

Задание 8: тема «Дороги и транспорт» по районам с названиями

• Покажите название темы, название района и число обращений
• Только тема с кодом T-2; два объединения: со справочником тем и со справочником районов
• Упорядочьте по числу обращений, затем по названию района
• Подсказка: второй LEFT JOIN пишется сразу после первого, у каждого своё условие ON
• Ожидается: 5 строк, Есиль — 7

Задание 9: доля закрытых в срок по темам

• По каждой теме: название темы, закрытых обращений, из них закрытых за 15 дней и меньше, доля в процентах
• Долю округлите до одного знака; упорядочьте по доле, лучшие сверху
• Подсказка: LEFT JOIN topics, GROUP BY t.title, CASE внутри SUM
• Ожидается: 6 строк, «Социальная поддержка» — 92.3

Задание 10: районы, где срок выше среднего по реестру

• Через WITH посчитайте средний срок закрытия по районам с одним знаком
• Оставьте районы, где он выше среднего по всем закрытым обращениям
• Упорядочьте по сроку, самые долгие сверху
• Подсказка: общее среднее — подзапрос в скобках без группировки
• Ожидается: 3 строки: R-2, R-3, R-1

Задание 11: один человек заведён дважды и код района, которого нет

• Первый запрос: найдите ИИН, которые встречаются в таблице persons больше одного раза; покажите ИИН и число строк
• Второй запрос: найдите обращения, чей код района отсутствует в справочнике districts; покажите номер и код
• Подсказка к первому: GROUP BY iin HAVING COUNT(*) > 1
• Подсказка ко второму: LEFT JOIN districts и условие d.district_code IS NULL
• Ожидается: один ИИН с двумя строками; три обращения с кодом R-6

Задание 12: объедините правильно

• Запустите запрос с прошлого слайда и запишите число строк
• Исправьте объединение так, чтобы бралась только актуальная версия района: у неё valid_to пустое
• Подсказка: второе условие в ON через AND, или условие в WHERE
• Сравните число строк с 92 и объясните разницу
• Ожидается: 107 до исправления, 89 после

Задание 13: двойная регистрация обращения

• Найдите обращения, зарегистрированные дважды: тот же заявитель, та же тема, та же дата регистрации
• Покажите заявителя, тему, дату и число копий
• Подсказка: GROUP BY по трём столбцам, HAVING COUNT(*) > 1
• Ожидается: 2 строки, по две копии в каждой

Задание 14, бонус: последнее обращение каждого заявителя

• Пронумеруйте обращения каждого заявителя от новых к старым через ROW_NUMBER
• Оставьте только строки с номером 1; покажите номер обращения, заявителя и дату
• Упорядочьте по заявителю, покажите первые пять строк
• Подсказка: нумерацию сделать в CTE, фильтр rn = 1 — во внешнем запросе; WHERE не видит окно
• Ожидается: 5 строк, первая — обращение 1081 заявителя 1