Задачи SQL на собеседовании: решения и объяснения
Задачи SQL на собеседовании проверяют не только знание ключевых слов. Нужно понять данные, определить нужные строки и объяснить, что произойдёт с отсутствующими связями. Разберём пять задач на учебном каталоге. Все запросы этого урока выполнены в SQLite 3.53.1 на приведённых данных; запуск в PostgreSQL здесь не заявлен.
Прежде чем читать решение, запишите ожидаемый результат вручную. Это особенно полезно для JOIN и группировки: ошибка часто начинается с неверного представления о строках после соединения. Наши таблицы маленькие, поэтому каждую строку можно проследить без дополнительного инструмента.
Подготавливаем данные
В каталоге есть четыре темы и шесть материалов. Некоторые материалы ещё не опубликованы, один не имеет темы. Это намеренные учебные случаи. Поле published хранит числа 0 и 1, чтобы пример одинаково описывался в выбранной среде SQLite.
CREATE TABLE topics (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE materials (
id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
topic_id INTEGER REFERENCES topics(id),
published INTEGER NOT NULL CHECK (published IN (0, 1))
);
INSERT INTO topics VALUES
(1, 'Frontend'), (2, 'Backend'), (3, 'QA'), (4, 'Design');
INSERT INTO materials VALUES
(10, 'JavaScript: массивы', 1, 1),
(11, 'CSS Grid', 1, 0),
(12, 'C#: LINQ', 2, 1),
(13, 'HTTP: основы', NULL, 1),
(14, 'SQL: соединения', 2, 1),
(15, 'QA: поиск', 3, 0);Внешний ключ описывает связь с темой, а NULL разрешён для ещё не назначенной темы. В файле запуска SQLite из архива включена проверка внешних ключей. Для упражнений создаётся новая база в памяти; повторное выполнение создания таблиц в той же базе потребует удалить прежние таблицы или начать заново.
На интервью уточняйте ограничения до запроса. Может ли тема отсутствовать? Может ли материал принадлежать нескольким темам? Наше решение предполагает не более одной темы. При связи многие-ко-многим появится другая таблица, и количество строк после соединения изменится.
Задача 1. Найти опубликованные материалы темы Frontend
Идентификатор выбранной темы известен: 1. Нужно вывести идентификатор и название опубликованных материалов, сохранив определённый порядок.
SELECT id, title
FROM materials
WHERE topic_id = 1 AND published = 1
ORDER BY id;| id | title |
|---|---|
| 10 | JavaScript: массивы |
CSS Grid относится к нужной теме, но остаётся черновиком. Условия соединены через AND: строка должна удовлетворять обоим. Если использовать OR, в результат попадут и другие опубликованные материалы, и любой черновик темы 1. Это уже другой вопрос к данным.
ORDER BY нужен, когда порядок является частью ожидаемого результата. Без него не следует обещать сортировку только потому, что таблица сейчас возвращается привычным образом. Для проверки измените параметр темы на 2: в результате должны оказаться два опубликованных материала. Так вы отделяете условие от конкретного набора названий.
Задача 2. Вывести все материалы вместе с темой
Ключевое слово «все» включает материал без назначенной темы. Поэтому начинаем с materials и сохраняем его строки через LEFT JOIN:
SELECT m.id, m.title, t.name AS topic
FROM materials AS m
LEFT JOIN topics AS t ON t.id = m.topic_id
ORDER BY m.id;Для HTTP: основы поле topic будет NULL, остальные строки получат название своей темы. Всего остаётся шесть строк. INNER JOIN вернул бы только пять: у материала 13 нет совпавшей строки справа.
Здесь полезно объяснить не только название соединения, но и выбранную левую сторону. Если начать с тем, сохранёнными будут темы, а материал без темы не появится таким же способом. Модель соединений описана в руководстве PostgreSQL; подробная практика на сайте есть в уроке о JOIN.
Для отображения можно использовать COALESCE(t.name, 'Без темы'). Это заменит значение в результате, но не назначит тему в исходной таблице. Различайте подготовку подписи интерфейса и изменение данных.
Задача 3. Посчитать материалы для каждой темы, включая пустые
Теперь сохранять нужно темы. Тема Design не имеет материалов, но должна получить ноль:
SELECT t.id, t.name, COUNT(m.id) AS total
FROM topics AS t
LEFT JOIN materials AS m ON m.topic_id = t.id
GROUP BY t.id, t.name
ORDER BY t.id;| name | total |
|---|---|
| Frontend | 2 |
| Backend | 2 |
| QA | 1 |
| Design | 0 |
Для темы без совпадений LEFT JOIN сохраняет строку, где поля материала отсутствуют. COUNT(m.id) считает только ненулевые значения этого поля, поэтому выдаёт ноль. COUNT(*) считал бы сохранённую строку и показал единицу для Design — неверный ответ на поставленную задачу. Разницу вариантов COUNT уточняет документация SQLite об агрегатах.
Обратите внимание на материал без темы: он не входит ни в одну из четырёх групп. Если нужен общий отчёт, в котором есть отдельная категория «Без темы», условия задачи придётся расширить. Не приписывайте такому отчёту свойство, которого запрос не реализует.
Задача 4. Найти темы с двумя или более опубликованными материалами
Сначала выбираем опубликованные строки, потом формируем группы и проверяем их размер:
SELECT t.id, t.name, COUNT(m.id) AS total
FROM topics AS t
JOIN materials AS m ON m.topic_id = t.id
WHERE m.published = 1
GROUP BY t.id, t.name
HAVING COUNT(m.id) >= 2
ORDER BY t.id;Результат — Backend с количеством 2. Frontend имеет два материала всего, но опубликован только один. QA не имеет опубликованных, Design вообще не имеет материалов. Поэтому условия не сводятся к подсчёту всех записей.
WHERE отбирает строки до группировки, HAVING задаёт условие для полученных групп. Это логическое объяснение результата; оно не обещает конкретный физический план выполнения движка. Модель запроса и её этапы описаны в документации SQLite о SELECT.
Если собеседник просит вместо количества материалы самой большой темы, одной группировки уже недостаточно: нужно определить способ выбора и поведение при равенстве. Уточняйте новый результат прежде, чем добавлять LIMIT наугад.
Задача 5. Посчитать опубликованные материалы и сохранить нулевые темы
Распространённая ошибка — взять запрос из задачи 3 и добавить WHERE m.published = 1. У сохранённой строки пустой темы значение справа отсутствует, условие её не пропускает. QA также исчезнет, хотя для отчёта нужен ноль.
Перенесём ограничение совпавших материалов в условие соединения:
SELECT t.id, t.name, COUNT(m.id) AS published_total
FROM topics AS t
LEFT JOIN materials AS m
ON m.topic_id = t.id AND m.published = 1
GROUP BY t.id, t.name
ORDER BY t.id;Получаем Frontend — 1, Backend — 2, QA — 0, Design — 0. Темы остаются слева, а справа присоединяются только опубликованные материалы. Размещение условия меняет смысл результата, поэтому это хороший вопрос для объяснения, а не просто синтаксическое упражнение.
Дополнительно найдите материал без темы: WHERE topic_id IS NULL. Сравнение topic_id = NULL не задаёт требуемую проверку отсутствия. В SQL неизвестное значение требует соответствующего условия, а не обычного равенства. После этого подумайте, где в отчёте следует показывать такую запись.
Как проверить решение самостоятельно
В архиве лежат схема, запросы и небольшой запуск через Python со стандартным модулем SQLite. Он создаёт базу в памяти и выводит результаты пяти задач; отдельный сервер не нужен. Сравните число строк и значения с таблицами урока. Это выполнение учебных запросов, а не измерение производительности базы.
Затем измените данные: добавьте опубликованный материал в Design, снимите публикацию с одного Backend и оставьте материал без темы. Предскажите новые ответы до запуска. Если результат неожиданен, вернитесь к строкам после JOIN и месту фильтрации.
Для PostgreSQL потребуется свой запуск и учёт выбранных типов и окружения. В этом уроке подтверждён SQLite, а последовательное изучение другого движка вынесено в серию PostgreSQL для веба. На интервью такая явная граница помогает говорить о выполненном решении точно.
Скачать схему, решения и запуск · Практикум по поиску работы