Ответы к заданиям практикума

Эталонные запросы и результаты на учебной базе стенда. Раскрывайте по одному после того, как попробовали сами.

Открыть редактор запросов ↗ Задания Таблицы и схема Шпаргалка SQL Ответы

Эталонные запросы и результаты на учебной базе registry.sqlite. Запросы работают в SQLite, PostgreSQL и MySQL 8.

0. Проверка подключения — показать решение

SELECT COUNT(*) AS cases_total FROM cases;

Результат, строк: 1

cases_total
92

1. SELECT, ORDER BY, LIMIT: пять последних обращений — показать решение

SELECT case_id, district_code, opened_at
FROM cases
ORDER BY opened_at DESC, case_id DESC
LIMIT 5;

Результат, строк: 5

case_id	district_code	opened_at
1034	R-2	2026-08-28
1024	R-3	2026-08-27
1017	R-2	2026-08-27
1068	R-6	2026-08-24
1051	R-5	2026-08-23

2. WHERE: район R-2, второй квартал — показать решение

SELECT case_id, topic_code, opened_at, channel
FROM cases
WHERE district_code = 'R-2'
  AND opened_at >= '2026-04-01'
  AND opened_at < '2026-07-01'
ORDER BY opened_at;

Результат, строк: 6

case_id	topic_code	opened_at	channel
1072	T-2	2026-04-22	телефон
1033	T-2	2026-04-25	телефон
1056	T-4	2026-05-31	портал
1064	T-6	2026-06-13	телефон
1031	T-5	2026-06-27	письмо
1050	T-4	2026-06-29	письмо

3. WHERE с IS NULL и IN: открытые обращения с портала и по телефону — показать решение

SELECT COUNT(*) AS open_cases
FROM cases
WHERE closed_at IS NULL
  AND channel IN ('портал', 'телефон');

Результат, строк: 1

open_cases
12

4. Агрегаты: всего, закрыто, средний и максимальный срок — показать решение

SELECT COUNT(*) AS cases_total,
       COUNT(closed_at) AS cases_closed,
       ROUND(AVG(days_to_close), 1) AS avg_days,
       MAX(days_to_close) AS max_days
FROM cases;

Результат, строк: 1

cases_total	cases_closed	avg_days	max_days
92	68	13.2	28

5. GROUP BY и HAVING: районы с 15 и более обращениями — показать решение

SELECT district_code,
       COUNT(*) AS cases_total,
       COUNT(closed_at) AS cases_closed,
       ROUND(AVG(days_to_close), 1) AS avg_days
FROM cases
GROUP BY district_code
HAVING COUNT(*) >= 15
ORDER BY avg_days DESC;

Результат, строк: 4

district_code	cases_total	cases_closed	avg_days
R-2	18	10	17.3
R-3	18	15	14.7
R-4	19	14	11.6
R-5	21	16	10.4

6. GROUP BY по месяцу: динамика регистрации — показать решение

SELECT SUBSTR(opened_at, 1, 7) AS month,
       COUNT(*) AS cases_total
FROM cases
GROUP BY SUBSTR(opened_at, 1, 7)
ORDER BY month;

Результат, строк: 8

month	cases_total
2026-01	14
2026-02	9
2026-03	7
2026-04	13
2026-05	15
2026-06	12
2026-07	9
2026-08	13

7. LEFT JOIN: обращения по названиям районов — показать решение

SELECT d.title AS district,
       COUNT(c.case_id) AS cases_total
FROM cases AS c
LEFT JOIN districts AS d
  ON c.district_code = d.district_code
GROUP BY d.title
ORDER BY cases_total DESC, district;

Результат, строк: 6

district	cases_total
Нура	21
Байконур	19
Есиль	18
Сарыарка	18
Алматы	13
	3

8. Два объединения: тема и район в одном отчёте — показать решение

SELECT t.title AS topic,
       d.title AS district,
       COUNT(*) AS cases_total
FROM cases AS c
LEFT JOIN topics AS t ON c.topic_code = t.topic_code
LEFT JOIN districts AS d ON c.district_code = d.district_code
WHERE c.topic_code = 'T-2'
GROUP BY t.title, d.title
ORDER BY cases_total DESC, district;

Результат, строк: 5

topic	district	cases_total
Дороги и транспорт	Есиль	7
Дороги и транспорт	Байконур	5
Дороги и транспорт	Алматы	3
Дороги и транспорт	Нура	3
Дороги и транспорт	Сарыарка	2

9. CASE и доля: закрыто в срок (15 дней) по темам — показать решение

SELECT t.title AS topic,
       COUNT(c.closed_at) AS closed_total,
       SUM(CASE WHEN c.days_to_close <= 15 THEN 1 ELSE 0 END) AS closed_on_time,
       ROUND(100.0 * SUM(CASE WHEN c.days_to_close <= 15 THEN 1 ELSE 0 END) / COUNT(c.closed_at), 1) AS on_time_pct
FROM cases AS c
LEFT JOIN topics AS t ON c.topic_code = t.topic_code
GROUP BY t.title
ORDER BY on_time_pct DESC;

Результат, строк: 6

topic	closed_total	closed_on_time	on_time_pct
Социальная поддержка	13	12	92.3
Дороги и транспорт	13	8	61.5
ЖКХ и коммунальные услуги	10	6	60.0
Образование	11	6	54.5
Здравоохранение	13	7	53.8
Благоустройство	8	4	50.0

10. Подзапрос и CTE: районы со сроком выше среднего — показать решение

WITH by_district AS (
  SELECT district_code, ROUND(AVG(days_to_close), 1) AS avg_days
  FROM cases
  GROUP BY district_code
)
SELECT district_code, avg_days
FROM by_district
WHERE avg_days > (SELECT AVG(days_to_close) FROM cases)
ORDER BY avg_days DESC;

Результат, строк: 3

district_code	avg_days
R-2	17.3
R-3	14.7
R-1	14.0

11. Проверка качества: повтор ИИН в таблице заявителей — показать решение

SELECT iin, COUNT(*) AS persons_with_iin
FROM persons
GROUP BY iin
HAVING COUNT(*) > 1;

Результат, строк: 1

iin	persons_with_iin
630926300079	2

12. Проверка качества: код района, которого нет в справочнике — показать решение

SELECT c.case_id, c.district_code
FROM cases AS c
LEFT JOIN districts AS d ON c.district_code = d.district_code
WHERE d.district_code IS NULL
ORDER BY c.case_id;

Результат, строк: 3

case_id	district_code
1008	R-6
1038	R-6
1068	R-6

13. Ловушка объединения: справочник с историей без фильтра версии — показать решение

SELECT COUNT(*) AS rows_after_join
FROM cases AS c
JOIN districts_history AS h ON c.district_code = h.district_code;

Результат, строк: 1

rows_after_join
107

14. Ловушка объединения: то же с фильтром актуальной версии — показать решение

SELECT COUNT(*) AS rows_after_join
FROM cases AS c
JOIN districts_history AS h
  ON c.district_code = h.district_code
 AND h.valid_to IS NULL;

Результат, строк: 1

rows_after_join
89

15. Проверка качества: двойная регистрация обращения — показать решение

SELECT person_id, topic_code, opened_at, COUNT(*) AS copies
FROM cases
GROUP BY person_id, topic_code, opened_at
HAVING COUNT(*) > 1
ORDER BY opened_at;

Результат, строк: 2

person_id	topic_code	opened_at	copies
24	T-2	2026-02-14	2
18	T-1	2026-06-15	2