# SQL для QA · рабочая тетрадь

Редакция QA Orbit · версия 2026-10-28.1. Все данные синтетические.

## Договор SQLite-CORE-1

SQLite-CORE-1 — отдельный учебный контракт QA Orbit, не API и не производственная БД.
users(id,name): u1 Anna; u2 Boris; u3 Vera; u4 Gleb.
tasks(id,author_id,assignee_id,title,state,priority,estimate):
t1,u1,u2,Export,published,2,3
t2,u1,NULL,Draft,draft,1,NULL
t3,u2,NULL,Login,published,3,5
t4,u2,u1,Search,draft,2,0
t5,u1,u3,Profile,published,1,2
t6,u3,u1,Legacy,archived,3,NULL
comments(id,task_id,author_id): c1,t1,u2; c2,t1,u1; c3,t3,u1; c4,t3,u2; c5,t5,u3.
Данные синтетические. Одна строка tasks — одна задача. estimate — часы; NULL означает «оценка неизвестна», 0 означает «оценено в ноль». priority: 1 низкий, 2 обычный, 3 высокий. Отчёт об авторе использует author_id, об исполнителе — assignee_id. Ожидания относятся ровно к этому снимку; изменение fixture требует нового расчёта.

## Запуск

Скачайте в одну новую папку `01-fixture.sql`, `02-queries.sql`, `expected-results.json`, `03-transactions.sql`, `verify-sql.py`. Нужен Python 3 со встроенным sqlite3. Установка pip-пакетов не нужна.

```sh
python3 verify-sql.py
```

Скрипт создаёт только новую БД в памяти. Готовый пакет должен вывести 25 PASS-проверок и завершиться кодом 0. Он не открывает существующие файлы БД. Версия SQLite печатается в конце.

Для ручной практики откройте новую пустую БД в SQLite-клиенте, выполните `01-fixture.sql`, затем читающие запросы из `02-queries.sql`. На каждом новом соединении явно включайте `PRAGMA foreign_keys=ON` до транзакций. Fixture не содержит DROP: для повторного старта создайте другую пустую БД. `03-transactions.sql` выполняйте только в собственной учебной копии после fixture.

Ограничения: title длиной 1–80; три значения state; priority диапазон 1–3; estimate >=0 или NULL; ссылки на существующих пользователей и задачи. Обычная SQLite INTEGER-колонка не гарантирует строгую типизацию: случаи с иными типами не входят в оракул курса. Пробельный title разрешён схемой длины; публикационные права не моделируются.

## Задания и разборы

### SELECT, условия и смысл NULL

Коллега считает, что запрос с = NULL должен вернуть две задачи. Проверьте его объяснение по снимку.

1. Напишите Q1: опубликованные задачи с priority >= 2, колонки id и title.
2. Исправьте поиск задач без исполнителя и укажите точные id.
3. Отдельно найдите задачи с estimate = 0.
4. Объясните, почему строка NULL, SQL NULL и число 0 дают разные ожидания.

**Разбор:** Q1: SELECT id,title FROM tasks WHERE state='published' AND priority>=2 ORDER BY id; => (t1,Export),(t3,Login). Q2: SELECT id FROM tasks WHERE assignee_id IS NULL ORDER BY id; => t2,t3. Q11: SELECT id FROM tasks WHERE estimate=0 ORDER BY id; => t4. = NULL не выбирает строки; 'NULL' — текст, не отсутствие значения. estimate IS NULL дало бы t2,t6, поэтому это отдельная проверка, а не замена поиска нуля.

**Ошибки:** Фильтровать исполнителя, когда вопрос об авторе.; Сравнивать только число строк вместо идентификаторов.; Считать порядок без ORDER BY стабильным.

### Агрегации, GROUP BY и HAVING

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

1. Вычислите Q3 и запишите все четыре значения.
2. Постройте Q4: количество задач по состоянию.
3. Сравните Q5 и Q12, назовите входные идентификаторы каждой агрегации.
4. Объясните, почему у u4 нет строки при GROUP BY tasks.author_id.

**Разбор:** Q3=(6,4,10,2.5). Q4 ORDER BY state: archived=1,draft=2,published=3. Q5 по всем задачам: u1=3,u2=2; у u3 одна задача и HAVING её исключает. Q12 сначала оставляет t1,t3,t5; после группировки и HAVING остаётся u1=2. u4 не представлен ни одной строкой tasks, GROUP BY не создаёт отсутствующие сущности. Ноль для всех пользователей будет отдельной задачей LEFT JOIN.

**Ошибки:** Считать COUNT(estimate) количеством положительных оценок.; Применять фильтр публикации после подсчёта без изменения смысла.; Выбирать title без однозначного правила внутри группы.

### JOIN и кардинальность: как не потерять и не размножить

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

1. Разделите две независимые ошибки: потерю пользователей и рост счётчика.
2. Напишите Q6 без комментариев и Q7 с комментариями.
3. Покажите дочерние строки u1 до агрегации.
4. Получите Q10: число комментариев для всех шести задач.

**Разбор:** Неверный WHERE исключает u3/u4; фильтр задач переносится в ON. Соединение comments размножает t1 и t3; для количества задач нужен COUNT(DISTINCT t.id), либо предварительная агрегация. Q6 и Q7 оба: u1=2,u2=1,u3=0,u4=0. Развёртка u1: (u1,t1,c1),(u1,t1,c2),(u1,t5,c5). Q10 через tasks LEFT JOIN comments и COUNT(c.id): t1=2,t2=0,t3=2,t4=0,t5=1,t6=0. Неверный COUNT после присоединения комментариев дал бы 3,2,0,0 даже при исправленном ON.

**Ошибки:** Лечить потерю строки только COALESCE: он не возвращает удалённого пользователя.; Использовать COUNT(*) для нулевой дочерней связи.; Подменять количество задач количеством комментариев.

### Подзапросы и проверки целостности

Коллега получил пустой список пользователей без назначений и решил, что все заняты. В подзапросе встретился NULL.

1. Напишите Q8 и перечислите задачи без комментариев.
2. Исправьте поиск пользователей без назначений.
3. Объясните пустой результат неверного NOT IN.
4. Напишите проверку отсутствующих авторов Q13 и назовите ожидаемый набор.

**Разбор:** Q8 NOT EXISTS comments по task_id => t2,t4,t6. Q9 NOT EXISTS tasks по assignee_id=u.id => u4. NOT IN с NULL не даёт истинного отрицания для u4 и здесь возвращает пустой набор. Допустимый другой вариант: id NOT IN (SELECT assignee_id FROM tasks WHERE assignee_id IS NOT NULL). Q13: SELECT t.id FROM tasks t LEFT JOIN users u ON u.id=t.author_id WHERE u.id IS NULL ORDER BY t.id; => []. Ноль комментариев допустим; отсутствующий автор — нарушение ссылочной целостности, но активность и команда схемой не заданы.

**Ошибки:** Считать пустой результат ошибочного запроса доказательством отсутствия проблемы.; Забыть корреляцию внутреннего поиска с внешней строкой.; Выводить права пользователя из одного факта существования id.

### Ограничения, транзакции и безопасный откат

Проверьте ограничения на t2 и объясните, что останется после отката к savepoint.

1. Зафиксируйте начальную строку t2: title, estimate, priority.
2. Выполните 03-transactions.sql в отдельной копии.
3. Проверьте шесть независимых неверных изменений через verify-sql.py.
4. Объясните, почему NULL estimate допустим и почему ошибка UPDATE не доказывает откат всей операции.

**Разбор:** До опыта t2=(Draft,NULL,1). После изменения title внутри первой транзакции: (Temporary,NULL,1); после ROLLBACK: (Draft,NULL,1). Во второй транзакции после ROLLBACK TO/RELEASE: estimate=8,priority=1. После внешнего ROLLBACK: estimate=NULL,priority=1. Шесть отказов: FK missing, state deleted, estimate -1, title NULL, title empty, duplicate user u1. Каждый изолирован savepoint и сравнивает полный снимок сразу после отказа, затем после cleanup. estimate NULL разрешён схемой; CHECK(estimate>=0) не заменяет NOT NULL. Отказ отдельной команды и откат всей транзакции — разные события.

**Ошибки:** Включить foreign_keys уже внутри активной транзакции и считать это достаточным.; Проверить только текст ошибки, не проверяя данные.; Перепутать ROLLBACK TO с внешним ROLLBACK.; Объявить INTEGER строгой гарантией типа без отдельной проверки.

### Итоговый проект: аудит отчёта и целостности

Передайте пакет SQL-проверок так, чтобы коллега мог воспроизвести его в новой папке без доступа к вашей машине.

1. Составьте матрицу требований и Q1–Q13 с точными результатами.
2. Воспроизведите три неверных запроса и объясните расхождения.
3. Выполните изолированные проверки ограничений и отката.
4. Запишите версии, команду, фактические статусы и ограничения вывода.
5. Перезапустите пакет из новой папки и проверьте полноту файлов.

**Разбор:** Эталонная структура: 01-fixture.sql,02-queries.sql,expected-results.json,03-transactions.sql,verify-sql.py,report.md. В чистом запуске готового пакета 25 проверок. Q6/Q7: u1=2,u2=1,u3=0,u4=0; Q8:t2,t4,t6; Q9:u4; Q3:6,4,10,2.5; Q13:[]. Неверный WHERE теряет u3/u4; неверный COUNT после comments даёт 3,2,0,0; NOT IN с NULL даёт []. После сценария t2=(Draft,NULL,1). Собственный отчёт должен содержать ваш фактический вывод, а не скопированный статус. Ограничения: локальный снимок, одно соединение, учебная SQLite-схема; UI, HTTP, права и нагрузка не проверялись.

**Ошибки:** Выдать эталонный вывод за собственный запуск.; Приложить queries без исходной фикстуры и версии договора.; Скрыть неизвестные значения под нулём.; Назвать 25 учебных проверок полноценным регрессом продукта.

## Что проверяет запуск

13 точных запросов; 3 неверных запроса как контрпримеры; 6 независимых отказов ограничений; восстановление после транзакций; foreign_key_check и integrity_check. Это 25 проверок локального снимка. Тестов UI, HTTP, авторизации и нагрузки здесь нет. Не выдавайте эталонный вывод за свой запуск.

## Первичные источники

- [SQLite SELECT](https://www.sqlite.org/lang_select.html)
- [SQLite expressions](https://www.sqlite.org/lang_expr.html)
- [SQLite aggregates](https://www.sqlite.org/lang_aggfunc.html)
- [SQLite constraints](https://www.sqlite.org/lang_createtable.html)
- [SQLite foreign keys](https://www.sqlite.org/foreignkeys.html)
- [SQLite transactions](https://www.sqlite.org/lang_transaction.html)
- [SQLite savepoints](https://www.sqlite.org/lang_savepoint.html)
- [Python sqlite3](https://docs.python.org/3/library/sqlite3.html)