SQL для тестировщика: запросы, проверки базы и задачи с собеседований

1. Прочитай схему до написания SQL

Название таблицы не описывает данные полностью. Уточни первичный ключ, внешние ключи, допустимость NULL, словарь статусов и ограничения уникальности. Без этого правдоподобный запрос может отвечать совсем не на тот вопрос.

В нашей схеме customers.id — ключ клиента; orders.customer_id и activities.customer_id ссылаются на него; payments.order_id ссылается на orders.id. У одного клиента бывает много заказов и активностей, а у заказа — несколько попыток оплаты. Эта кардинальность объясняет, почему JOIN иногда размножает строки.

• Перед JOIN опиши связь словами.
• Отличай отсутствующую строку от строки с NULL в поле.
• Уточняй timezone и правила soft delete для временных и удалённых данных.

2. SELECT и WHERE: точная проверка одной операции

После ответа 201 от POST /orders не ищи запись только по статусу. Сохрани order id из ответа и запроси именно эту строку. Сравни customer_id, status, total и время создания с запросом и контрактом. Явный список колонок делает доказательство читаемым и не вытаскивает лишние данные.

Временной диапазон помогает, если id недоступен, но он слабее: параллельный тест может создать похожую строку. ORDER BY не фильтрует записи, а LIMIT без однозначной сортировки способен вернуть разные строки в двух запусках.

SELECT id, customer_id, status, total, created_at
FROM orders
WHERE id = 1842 AND customer_id = 77;

3. JOIN: сравнение связанных бизнес-объектов

INNER JOIN возвращает только пары, найденные с обеих сторон. LEFT JOIN сохраняет каждую строку левой таблицы, а отсутствующую правую часть заполняет NULL. Это главное различие для отрицательных проверок: клиентов без заказов или заказов без платежей.

Фильтр по правой таблице в WHERE может удалить NULL-строки и фактически превратить LEFT JOIN в INNER JOIN. Для вопроса «клиенты без оплаченных заказов» условие paid ставится в ON, после чего проверяется NULL у id присоединённого заказа.

SELECT c.id, c.email
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.id AND o.status = 'paid'
WHERE o.id IS NULL
ORDER BY c.id;

4. NULL, пустая строка и отсутствие данных — разные случаи

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

Из-за трёхзначной логики сравнение с NULL даёт unknown и условие WHERE не проходит. COALESCE удобен в отчётах, но преждевременная подстановка значения по умолчанию может замаскировать дефект.

SELECT id, email,
  CASE
    WHEN email IS NULL THEN 'null'
    WHEN TRIM(email) = '' THEN 'blank'
    ELSE 'present'
  END AS email_state
FROM customers;

5. COUNT, GROUP BY и HAVING: дубли и неверные суммы

GROUP BY задаёт группы, COUNT, SUM, MIN и MAX считают значения внутри них, HAVING фильтрует готовые группы. WHERE отбирает исходные строки до группировки. Смешение этих этапов — частая ошибка на собеседованиях.

COUNT(*) считает строки, а COUNT(column) пропускает NULL в выбранной колонке. Одновременный JOIN нескольких связей one-to-many размножает строки и завышает суммы. Лучше агрегировать дочерние таблицы заранее, а не лечить результат необъяснимым DISTINCT.

SELECT LOWER(TRIM(email)) AS normalized_email, COUNT(*) AS customer_count
FROM customers
WHERE email IS NOT NULL
GROUP BY LOWER(TRIM(email))
HAVING COUNT(*) > 1;

SELECT external_id, COUNT(*) AS attempts, SUM(amount) AS charged
FROM payments
WHERE status = 'succeeded'
GROUP BY external_id
HAVING COUNT(*) > 1;

6. Проверяй целостность, а не только ответ API

Успешный ответ не доказывает согласованность связанных данных. Заказ может сохраниться без позиций, платёж — ссылаться на несуществующий заказ, а два активных клиента — иметь email, который должен быть уникальным. Foreign key, UNIQUE и CHECK защищают от классов дефектов, но приложение обязано корректно обработать отказ ограничения.

Поиск orphan-строк полезен для диагностики, но не разрешает читать лишние production-данные. Используй синтетические записи, выбирай минимум колонок и прикладывай к багу идентификаторы, а не полные персональные данные.

SELECT p.id, p.order_id
FROM payments p
LEFT JOIN orders o ON o.id = p.order_id
WHERE o.id IS NULL;

SELECT id, order_id, amount
FROM payments
WHERE status = 'succeeded' AND amount <= 0;

7. Транзакции, повторы и конкуренция

Транзакция объединяет изменения: они фиксируются вместе либо откатываются вместе. Проверь сбой между связанными записями: если payment успешен, а обновление order упало, какое состояние останется и как сработает reconciliation? Один успешный прогон не доказывает атомарность.

Параллельные запросы находят дефекты, невидимые последовательно. Две конвертации лида или повтор после timeout могут одновременно пройти проверку «запись уже существует?». Бизнес-инвариант должны удержать unique constraint, idempotency key или согласованная блокировка.

SELECT order_id, external_id, status, amount, created_at
FROM payments
WHERE order_id = 1842
ORDER BY created_at, id;

8. Как решать SQL-задачу на собеседовании

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

Ответ Middle дополнительно называет ограничения: дубли исходных данных, границы timezone, soft delete, isolation и допустимость прямого доступа к БД. Просить схему нормально; молча выдумывать колонки и кардинальность хуже одного точного вопроса.

• Объясни каждое условие JOIN.
• Скажи, возможны ли дубли строк результата.
• Проверь пустой набор и несколько дочерних строк.
• Отделяй диагностический SELECT от cleanup-скрипта.
• Не запускай UPDATE или DELETE ради демонстрации уверенности.