{"draftJsState":{"blocks":[{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SQL — один из самых полезных навыков для тестировщика. Даже если вы не работаете напрямую с базой данных каждый день, умение читать и писать SQL-запросы помогает:","type":"unstyled","key":"84rtp"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"проверять, что данные корректно сохранились после действий в UI или API;","type":"unordered-list-item","key":"ebq7e"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"находить дефекты в бизнес-логике;","type":"unordered-list-item","key":"toir"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"готовить тестовые данные;","type":"unordered-list-item","key":"4hlom"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"сравнивать данные между таблицами, системами и окружениями;","type":"unordered-list-item","key":"69pus"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"проверять миграции БД;","type":"unordered-list-item","key":"eske8"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"быстрее разбираться в инцидентах;","type":"unordered-list-item","key":"fcj5g"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"не зависеть полностью от разработчиков и аналитиков.","type":"unordered-list-item","key":"8aj1q"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Эта статья ориентирована на QA-инженеров: от начинающих до тех, кому нужно систематизировать знания.","type":"unstyled","key":"4v4k3"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Примеры преимущественно совместимы с PostgreSQL, MySQL и MS SQL Server. Однако синтаксис некоторых функций может различаться. Всегда уточняйте, какая СУБД используется в проекте.","type":"blockquote","key":"3760l"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"1. Что такое SQL","type":"header-two","key":"d78hi"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":3,"offset":0,"style":"BOLD"}],"text":"SQL (Structured Query Language) — язык структурированных запросов для работы с реляционными базами данных.","type":"unstyled","key":"55i2q"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"С помощью SQL можно:","type":"unstyled","key":"clfo8"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"получать данные;","type":"unordered-list-item","key":"big7l"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"добавлять данные;","type":"unordered-list-item","key":"4hnos"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"изменять данные;","type":"unordered-list-item","key":"r14o"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"удалять данные;","type":"unordered-list-item","key":"6srkh"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"создавать и изменять таблицы;","type":"unordered-list-item","key":"3h46e"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"управлять транзакциями;","type":"unordered-list-item","key":"acsf8"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"выдавать права доступа.","type":"unordered-list-item","key":"2ohcm"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":30,"offset":64,"style":"BOLD"}],"text":"SQL — декларативный язык. Это значит, что обычно мы описываем, какой результат хотим получить, а не пошаговый алгоритм его получения.","type":"unstyled","key":"3g3u4"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Например:","type":"unstyled","key":"51i57"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT name, email","type":"blockquote","key":"eopl9"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM users","type":"blockquote","key":"b0k21"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"WHERE status = 'ACTIVE';","type":"blockquote","key":"f3tgo"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Этот запрос говорит базе данных:","type":"unstyled","key":"9dss3"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"«Верни имена и email всех активных пользователей».","type":"blockquote","key":"br43a"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Как именно база найдёт эти записи — решает оптимизатор СУБД.","type":"unstyled","key":"43mp8"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"2. Немного истории SQL","type":"header-two","key":"dismg"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"История SQL начинается в 1970-х годах.","type":"unstyled","key":"fveps"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":9,"offset":3,"style":"BOLD"}],"text":"В 1970 году Эдгар Кодд, сотрудник IBM, предложил реляционную модель данных.","type":"unordered-list-item","key":"bpc3g"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":9,"offset":3,"style":"BOLD"}],"text":"В 1974 году в IBM появился язык SEQUEL — Structured English Query Language.","type":"unordered-list-item","key":"52bfq"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":3,"offset":33,"style":"BOLD"}],"text":"Позже SEQUEL был переименован в SQL.","type":"unordered-list-item","key":"36rkf"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":9,"offset":3,"style":"BOLD"}],"text":"В 1986 году SQL стал стандартом ANSI.","type":"unordered-list-item","key":"at2p3"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":9,"offset":3,"style":"BOLD"}],"text":"В 1987 году — стандартом ISO.","type":"unordered-list-item","key":"2kair"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Сегодня SQL поддерживают почти все популярные реляционные СУБД:","type":"unstyled","key":"dmirn"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"PostgreSQL;","type":"unordered-list-item","key":"8do1u"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"MySQL;","type":"unordered-list-item","key":"4no28"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"MariaDB;","type":"unordered-list-item","key":"2iege"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Microsoft SQL Server;","type":"unordered-list-item","key":"e0bp1"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Oracle Database;","type":"unordered-list-item","key":"6942k"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SQLite;","type":"unordered-list-item","key":"edjvj"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"IBM Db2.","type":"unordered-list-item","key":"6q91e"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Хотя существует стандарт SQL, у каждой СУБД есть собственные расширения и особенности.","type":"unstyled","key":"9lclu"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Например:","type":"unstyled","key":"8ck8l"},{"data":{"image":{"id":"6a8462ab1b1d0f51c821b4e5"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"atomic:image","key":"78sns"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"3. Реляционные базы данных: минимальная теория","type":"header-two","key":"90p1f"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":8,"offset":38,"style":"BOLD"}],"text":"В реляционной базе данные хранятся в таблицах.","type":"unstyled","key":"ecl7j"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Пример таблицы users:","type":"unstyled","key":"5d1kv"},{"data":{"image":{"id":"6a8462cb075d785b99b5f11a"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"atomic:image","key":"2m9p9"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Основные термины","type":"header-two","key":"6ib6"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":7,"offset":0,"style":"BOLD"}],"text":"Таблица — набор однотипных данных, например users, orders, products.","type":"unordered-list-item","key":"3b0qg"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":6,"offset":0,"style":"BOLD"}],"text":"Строка (row, record) — одна запись в таблице.","type":"unordered-list-item","key":"7lbdh"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":7,"offset":0,"style":"BOLD"}],"text":"Столбец (column, field) — характеристика сущности: email, status, price.","type":"unordered-list-item","key":"1d5b3"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":14,"offset":0,"style":"BOLD"}],"text":"Первичный ключ (Primary Key, PK) — уникальный идентификатор строки.","type":"unordered-list-item","key":"auppu"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":12,"offset":0,"style":"BOLD"}],"text":"Внешний ключ (Foreign Key, FK) — ссылка на запись в другой таблице.","type":"unordered-list-item","key":"5c31i"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":6,"offset":0,"style":"BOLD"}],"text":"Индекс — структура, ускоряющая поиск и сортировку.","type":"unordered-list-item","key":"7sktf"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":5,"offset":0,"style":"BOLD"}],"text":"Схема (schema) — логическая группа таблиц. Например: public.users, sales.orders.","type":"unordered-list-item","key":"eds72"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"4. Пример предметной области для практики","type":"header-two","key":"1n8v1"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Представим интернет-магазин с таблицами:","type":"unstyled","key":"b81q"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"users","type":"blockquote","key":"fi5pk"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"orders","type":"blockquote","key":"d96qn"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"order_items","type":"blockquote","key":"5a74"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"products","type":"blockquote","key":"a2ufr"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"payments","type":"blockquote","key":"eibir"},{"data":{"image":{"id":"6a846544aeb2af3a0c50940d"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"atomic:image","key":"9p9qc"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"5. Виды SQL-команд","type":"header-two","key":"5b24q"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SQL обычно разделяют на несколько групп.","type":"unstyled","key":"8uulu"},{"data":{"image":{"id":"6a846599cce6ed21c393452b"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"atomic:image","key":"6249p"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Для тестировщика в первую очередь важны:","type":"unstyled","key":"d0nvo"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT;","type":"ordered-list-item","key":"e892q"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"JOIN;","type":"ordered-list-item","key":"3fgbe"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"WHERE;","type":"ordered-list-item","key":"99k0t"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"агрегатные функции;","type":"ordered-list-item","key":"9ap6i"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"INSERT, UPDATE, DELETE — только на тестовых окружениях и с осторожностью;","type":"ordered-list-item","key":"fa70n"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"транзакции;","type":"ordered-list-item","key":"5b5qh"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"проверка ограничений и целостности данных.","type":"ordered-list-item","key":"4rqlb"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"6. SELECT: получение данных","type":"header-two","key":"d43s1"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Базовый запрос","type":"header-three","key":"ac651"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT *","type":"blockquote","key":"c7cdl"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM users;","type":"blockquote","key":"7kcn4"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"* означает «все столбцы».","type":"unstyled","key":"4a5kj"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Но в реальной работе лучше указывать нужные поля явно:","type":"unstyled","key":"6trgc"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT id, name, email, status","type":"blockquote","key":"c8vod"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM users;","type":"blockquote","key":"5tckj"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Почему это лучше:","type":"unstyled","key":"74n2i"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"запрос понятнее;","type":"unordered-list-item","key":"9b0vr"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"меньше данных передаётся из БД;","type":"unordered-list-item","key":"afkjd"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"снижается риск случайно увидеть чувствительные данные;","type":"unordered-list-item","key":"cn8nm"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Псевдонимы столбцов: AS","type":"header-three","key":"31sk7"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT","type":"blockquote","key":"f8u8t"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" id AS user_id,","type":"blockquote","key":"di8nc"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" name AS user_name,","type":"blockquote","key":"lpo0"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" email AS user_email","type":"blockquote","key":"6f76"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM users;","type":"blockquote","key":"ck8k0"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"AS задаёт понятное имя поля в результирующем наборе.","type":"unstyled","key":"cktrq"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Для таблиц псевдонимы особенно полезны в JOIN:","type":"unstyled","key":"fu8qc"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT","type":"blockquote","key":"81nt0"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" u.id,","type":"blockquote","key":"2qgg6"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" u.name,","type":"blockquote","key":"jfac"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" o.id AS order_id","type":"blockquote","key":"8nrs1"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM users AS u","type":"blockquote","key":"8bf8i"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"JOIN orders AS o ON o.user_id = u.id;","type":"blockquote","key":"1fibk"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"DISTINCT: уникальные значения","type":"header-three","key":"8uegg"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT DISTINCT status","type":"blockquote","key":"a73gi"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM orders;","type":"blockquote","key":"bcb6r"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Результат может быть таким:","type":"unstyled","key":"1a4tf"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"NEW","type":"blockquote","key":"o450"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"PAID","type":"blockquote","key":"48agp"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"CANCELLED","type":"blockquote","key":"97or3"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"DELIVERED","type":"blockquote","key":"fbu3j"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"7. Фильтрация: WHERE","type":"header-two","key":"4j6rd"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"WHERE отбирает только те строки, которые соответствуют условию.","type":"unstyled","key":"3irg2"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT id, name, email","type":"blockquote","key":"d0613"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM users","type":"blockquote","key":"e6f7v"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"WHERE status = 'ACTIVE';","type":"blockquote","key":"6ijou"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Операторы сравнения","type":"header-three","key":"c3uvt"},{"data":{"image":{"id":"6a8467b986529f1c6519ecb5"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"atomic:image","key":"a4f8g"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Пример:","type":"unstyled","key":"e0h32"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT *","type":"blockquote","key":"fprp4"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM orders","type":"blockquote","key":"fah4v"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"WHERE total_amount >= 5000;","type":"blockquote","key":"for9t"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"AND, OR, NOT","type":"header-three","key":"6hvg7"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Пример использования AND:","type":"unstyled","key":"9sj9m"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT *","type":"blockquote","key":"2n7ju"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM users","type":"blockquote","key":"vka2"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"WHERE status = 'ACTIVE'","type":"blockquote","key":"6m2ja"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"AND email IS NOT NULL;","type":"blockquote","key":"3gjst"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Пример использования OR:","type":"unstyled","key":"6musf"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT *","type":"blockquote","key":"92kjh"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM orders","type":"blockquote","key":"3dut0"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"WHERE status = 'PAID'","type":"blockquote","key":"7o8ed"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"OR status = 'DELIVERED';","type":"blockquote","key":"2clca"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Пример использования NOT:","type":"unstyled","key":"80drk"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT *","type":"blockquote","key":"2c8ha"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM products","type":"blockquote","key":"9ktpg"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"WHERE NOT is_active;","type":"blockquote","key":"dbl78"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"IN","type":"header-three","key":"6keup"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"IN удобно использовать, когда нужно сравнить значение с набором вариантов.","type":"unstyled","key":"97kc5"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT *","type":"blockquote","key":"9vb9q"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM orders","type":"blockquote","key":"9u7ji"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"WHERE status IN ('PAID', 'DELIVERED', 'SHIPPED');","type":"blockquote","key":"670qt"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Эквивалентно:","type":"unstyled","key":"eucar"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT *","type":"blockquote","key":"avohj"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM orders","type":"blockquote","key":"6es5h"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"WHERE status = 'PAID'","type":"blockquote","key":"5up6j"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" OR status = 'DELIVERED'","type":"blockquote","key":"2mpdo"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" OR status = 'SHIPPED';","type":"blockquote","key":"50iec"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"BETWEEN","type":"header-three","key":"9s4c1"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT *","type":"blockquote","key":"8qu2u"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM products","type":"blockquote","key":"1vu2k"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"WHERE price BETWEEN 1000 AND 5000;","type":"blockquote","key":"dfqlk"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":6,"offset":0,"style":"BOLD"}],"text":"Важно: BETWEEN включает обе границы.","type":"unstyled","key":"3ddjo"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"price BETWEEN 1000 AND 5000","type":"blockquote","key":"do1nu"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"эквивалентно:","type":"unstyled","key":"7et0m"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"price >= 1000 AND price <= 5000","type":"blockquote","key":"94q8b"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Для дат лучше быть осторожным, особенно если поле имеет тип TIMESTAMP.","type":"unstyled","key":"40uv7"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Плохой вариант:","type":"unstyled","key":"4s1fo"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"WHERE created_at BETWEEN '2026-08-01' AND '2026-08-02'","type":"blockquote","key":"c3gsh"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Он не включит записи почти за весь день 2026-08-02, потому что фактически верхняя граница будет 2026-08-02 00:00:00.","type":"unstyled","key":"275lv"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Надёжнее:","type":"unstyled","key":"5u6do"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"WHERE created_at >= '2026-08-01'","type":"blockquote","key":"2qoj1"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"AND created_at < '2026-08-03';","type":"blockquote","key":"ap88a"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"LIKE: поиск по шаблону","type":"header-three","key":"2fbma"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT *","type":"blockquote","key":"9u8la"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM users","type":"blockquote","key":"384dc"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"WHERE email LIKE '%@gmail.com';","type":"blockquote","key":"cthel"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Символы:","type":"unstyled","key":"ca5rm"},{"data":{"image":{"id":"6a846abd9c99491f1f839433"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"atomic:image","key":"f5hqr"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Примеры:","type":"unstyled","key":"9rpvj"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT *","type":"blockquote","key":"7if9v"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM products","type":"blockquote","key":"a05lf"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"WHERE name LIKE 'iPhone%';","type":"blockquote","key":"eoram"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Найдёт товары, названия которых начинаются с iPhone.","type":"unstyled","key":"6ij2i"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT *","type":"blockquote","key":"2bbgb"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM users","type":"blockquote","key":"79bk8"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"WHERE name LIKE 'Ан_а';","type":"blockquote","key":"agmag"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Может найти Анна, Aнка (и т.д.) так как _ соответствует одной букве.","type":"unstyled","key":"fpr8e"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"В PostgreSQL для регистронезависимого поиска есть ILIKE:","type":"unstyled","key":"416ro"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT *","type":"blockquote","key":"di52h"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM products","type":"blockquote","key":"cusv6"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"WHERE name ILIKE '%iphone%';","type":"blockquote","key":"172fk"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"8. NULL: самая частая ловушка SQL","type":"header-two","key":"4n3ue"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":37,"offset":45,"style":"BOLD"}],"text":"NULL означает не пустую строку и не ноль, а отсутствие или неизвестность значения.","type":"unstyled","key":"dlil7"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Например:","type":"unstyled","key":"c5sh3"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"пользователь не указал телефон;","type":"unordered-list-item","key":"695ge"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"дата оплаты ещё неизвестна;","type":"unordered-list-item","key":"b6lvp"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"поле не заполнено;","type":"unordered-list-item","key":"7tlbo"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"значение не применимо.","type":"unordered-list-item","key":"cfrpn"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Проверка NULL","type":"header-three","key":"e4g32"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Правильно:","type":"unstyled","key":"a84fe"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT *","type":"blockquote","key":"e0hp4"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM users","type":"blockquote","key":"1ugcl"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"WHERE email IS NULL;","type":"blockquote","key":"anos"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":12,"offset":0,"style":"BOLD"}],"text":"Неправильно:","type":"unstyled","key":"fc3gq"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT *","type":"blockquote","key":"cjh6u"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM users","type":"blockquote","key":"duqnu"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"WHERE email = NULL;","type":"blockquote","key":"nsd7"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Для проверки непустых значений:","type":"unstyled","key":"9scru"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT *","type":"blockquote","key":"2lqvp"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM users","type":"blockquote","key":"e13u8"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"WHERE email IS NOT NULL;","type":"blockquote","key":"3a2jh"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"NULL и логика","type":"header-three","key":"2u4b1"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Это очень частый источник дефектов и ошибок в запросах.","type":"unstyled","key":"eq1l5"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Например, вы хотите получить все заказы с суммой не равной 1000. И делаете вот такой запрос:","type":"unstyled","key":"7bvva"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT *","type":"blockquote","key":"81ir1"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM orders","type":"blockquote","key":"83t5n"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"WHERE total_amount <> 1000;","type":"blockquote","key":"3t893"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":42,"offset":13,"style":"BOLD"}],"text":"Этот запрос не вернёт строки, где total_amount IS NULL.","type":"unstyled","key":"85jbr"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Если нужны все записи, кроме записей с суммой 1000, включая NULL, пишите явно","type":"unstyled","key":"6ff7t"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT *","type":"blockquote","key":"frmmf"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM orders","type":"blockquote","key":"cn5ee"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"WHERE total_amount <> 1000","type":"blockquote","key":"965m8"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"OR total_amount IS NULL;","type":"blockquote","key":"58k8f"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"COALESCE: подставить значение вместо NULL","type":"header-three","key":"hs2j"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT","type":"blockquote","key":"b1qm6"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"id,","type":"blockquote","key":"bbviv"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"name,","type":"blockquote","key":"5g1a9"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"COALESCE(email, 'Email не указан') AS email","type":"blockquote","key":"7v1qa"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM users;","type":"blockquote","key":"8tp8k"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Еще пример, если нужно подставить число (например для последующих расчетов):\n","type":"unstyled","key":"edki1"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT","type":"blockquote","key":"bj9bk"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"id,","type":"blockquote","key":"a7dgg"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"total_amount,","type":"blockquote","key":"ejdd4"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"COALESCE(discount_amount, 0) AS discount_amount","type":"blockquote","key":"7l66g"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM orders;","type":"blockquote","key":"4g0s7"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"9. Сортировка и ограничение результатов","type":"header-two","key":"1h695"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"ORDER BY","type":"header-three","key":"9kvs9"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT id, name, created_at","type":"blockquote","key":"fgt19"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM users","type":"blockquote","key":"4knjp"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"ORDER BY created_at DESC;","type":"blockquote","key":"e717k"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"ASC — по возрастанию;","type":"unordered-list-item","key":"d2hpr"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"DESC — по убыванию.","type":"unordered-list-item","key":"d6igb"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT id, name, price","type":"blockquote","key":"e3bto"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM products","type":"blockquote","key":"6laig"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"ORDER BY price ASC, name ASC;","type":"blockquote","key":"d807b"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Сначала товары сортируются по цене, затем по имени.","type":"unstyled","key":"7s7au"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"LIMIT и OFFSET","type":"header-three","key":"8su8k"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Получить первые 10 строк:","type":"unstyled","key":"97vb3"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT *","type":"blockquote","key":"2vs9e"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM orders","type":"blockquote","key":"ct3vu"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"ORDER BY created_at DESC","type":"blockquote","key":"oth"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"LIMIT 10;","type":"blockquote","key":"2nfhd"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Пропустить первые 20 и взять следующие 10:","type":"unstyled","key":"uogu"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT *","type":"blockquote","key":"bneul"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM orders","type":"blockquote","key":"f7omb"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"ORDER BY created_at DESC","type":"blockquote","key":"7ped5"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"LIMIT 10 OFFSET 20;","type":"blockquote","key":"dkf4r"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":16,"offset":34,"style":"BOLD"},{"length":3,"offset":56,"style":"BOLD"}],"text":"В MS SQL Server обычно применяют OFFSET ... FETCH или TOP.","type":"unstyled","key":"25ffn"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Например:","type":"unstyled","key":"e8k0h"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT TOP (1000) [BranchName] ,[TimeZoneName]","type":"blockquote","key":"5jjvj"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM [BranchSettings]","type":"blockquote","key":"fqro3"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"10. Агрегатные функции","type":"header-two","key":"8e3bs"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Агрегатные функции работают с группой строк и возвращают одно значение.","type":"unstyled","key":"59ol5"},{"data":{"image":{"id":"6a8493c273eb9c195a970aac"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"atomic:image","key":"evl7g"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"COUNT","type":"header-three","key":"en78p"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Например получить количество пользователей:","type":"unstyled","key":"2a3dc"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT COUNT(*) AS users_count","type":"blockquote","key":"3g6ai"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM users;","type":"blockquote","key":"dfsjc"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Или с добавлением условия - получить количество активных пользователей:","type":"unstyled","key":"6c1r7"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT COUNT(*) AS active_users_count","type":"blockquote","key":"59guc"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM users","type":"blockquote","key":"2oh6s"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"WHERE status = 'ACTIVE';","type":"blockquote","key":"csj8f"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Разница между COUNT(*) и COUNT(column):","type":"unstyled","key":"b207c"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT COUNT(*) AS all_rows,","type":"blockquote","key":"ffisg"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"COUNT(email) AS rows_with_email","type":"blockquote","key":"bpmr6"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM users;","type":"blockquote","key":"dj513"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"COUNT(*) считает все строки;","type":"unordered-list-item","key":"33nqb"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"COUNT(email) считает только строки, где email IS NOT NULL.","type":"unordered-list-item","key":"8fnhb"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SUM, AVG, MIN, MAX","type":"header-three","key":"2met5"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":3,"offset":0,"style":"BOLD"}],"text":"SUM","type":"unstyled","key":"abbq1"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT SUM(total_amount) AS total_revenue","type":"blockquote","key":"8a26f"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM orders","type":"blockquote","key":"4d44a"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"WHERE status = 'PAID';","type":"blockquote","key":"as0j7"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":13,"offset":0,"style":"BOLD"}],"text":"AVG (среднее)","type":"unstyled","key":"ajsok"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT AVG(price) AS average_price","type":"blockquote","key":"512ug"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM products;","type":"blockquote","key":"7r9sm"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":8,"offset":0,"style":"BOLD"}],"text":"MIN, MAX","type":"unstyled","key":"8bhh7"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT MIN(price) AS min_price,","type":"blockquote","key":"bi7ef"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"MAX(price) AS max_price","type":"blockquote","key":"b5946"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM products;","type":"blockquote","key":"d908j"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"11. GROUP BY и HAVING","type":"header-two","key":"5gr7g"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"GROUP BY объединяет строки в группы.","type":"unstyled","key":"9sau9"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Например, посчитаем количество заказов по статусам:","type":"unstyled","key":"5amo3"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT","type":"blockquote","key":"5mijs"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" status,","type":"blockquote","key":"8vr6i"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" COUNT(*) AS orders_count","type":"blockquote","key":"9dbki"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM orders","type":"blockquote","key":"d4tr7"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"GROUP BY status;","type":"blockquote","key":"8npgt"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Результат:","type":"unstyled","key":"aoap9"},{"data":{"image":{"id":"6a8495c189b0a93ee2a240a7"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"atomic:image","key":"7c9jk"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Группировка по нескольким полям","type":"header-three","key":"2uhct"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT","type":"blockquote","key":"86msc"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" user_id,","type":"blockquote","key":"8km77"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" status,","type":"blockquote","key":"30fqj"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" COUNT(*) AS orders_count","type":"blockquote","key":"82flo"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM orders","type":"blockquote","key":"7ftkg"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"GROUP BY user_id, status;","type":"blockquote","key":"dghem"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"HAVING","type":"header-three","key":"aiu6t"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":14,"offset":24,"style":"BOLD"},{"length":17,"offset":60,"style":"BOLD"}],"text":"WHERE фильтрует строки до группировки, а HAVING — группы после группировки.","type":"unstyled","key":"87v9p"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Пользователи, у которых больше 5 заказов:","type":"unstyled","key":"crq5t"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT","type":"blockquote","key":"a95ph"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" user_id,","type":"blockquote","key":"dl9br"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" COUNT(*) AS orders_count","type":"blockquote","key":"8firn"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM orders","type":"blockquote","key":"s1et"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"GROUP BY user_id","type":"blockquote","key":"9gq6r"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"HAVING COUNT(*) > 5;","type":"blockquote","key":"5anjo"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Пользователи, у которых больше 5 оплаченных заказов:","type":"unstyled","key":"99ir8"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT","type":"blockquote","key":"3ctc"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" user_id,","type":"blockquote","key":"1q7vs"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" COUNT(*) AS paid_orders_count","type":"blockquote","key":"9i0h9"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM orders","type":"blockquote","key":"8d0ba"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"WHERE status = 'PAID'","type":"blockquote","key":"unos"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"GROUP BY user_id","type":"blockquote","key":"1jo4c"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"HAVING COUNT(*) > 5;","type":"blockquote","key":"6sror"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"12. JOIN: объединение таблиц","type":"header-two","key":"63qh2"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"JOIN — ключевая тема для тестировщика. В реальных системах почти всегда нужно сопоставлять данные из нескольких таблиц.","type":"unstyled","key":"5a8dk"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Например:","type":"unstyled","key":"2c0ts"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"заказ и пользователь;","type":"unordered-list-item","key":"cgi73"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"заказ и позиции заказа;","type":"unordered-list-item","key":"f2m31"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"заказ и платёж;","type":"unordered-list-item","key":"616b3"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"продукт и категория;","type":"unordered-list-item","key":"5o74t"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"пользователь и его роли.","type":"unordered-list-item","key":"1mhfo"},{"data":{},"depth":0,"entityRanges":[{"key":0,"length":17,"offset":65}],"inlineStyleRanges":[{"length":17,"offset":65,"style":"BOLD"}],"text":"Если хотите лучше разобраться в JOIN, рекомендую почитать статью Какие бывают JOIN.","type":"unstyled","key":"45dp0"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"INNER JOIN","type":"header-three","key":"blec0"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"INNER JOIN возвращает только строки, для которых есть соответствия в обеих таблицах.","type":"unstyled","key":"eqbom"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Пример:\nSELECT","type":"blockquote","key":"cftac"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" o.id AS order_id,","type":"blockquote","key":"4kejt"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" o.status AS order_status,","type":"blockquote","key":"51sgk"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" o.total_amount,","type":"blockquote","key":"2mu0k"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" u.id AS user_id,","type":"blockquote","key":"f5707"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" u.name AS user_name,","type":"blockquote","key":"2erbb"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" u.email","type":"blockquote","key":"bj6dq"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM orders AS o","type":"blockquote","key":"1vad8"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"INNER JOIN users AS u ON u.id = o.user_id;","type":"blockquote","key":"9h5ng"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"LEFT JOIN","type":"header-three","key":"anvec"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"LEFT JOIN возвращает все строки из левой таблицы и совпадающие строки из правой. Если соответствия нет — поля правой таблицы будут NULL.","type":"unstyled","key":"3tank"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Найти заказы без платежей:","type":"unstyled","key":"4aojg"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT","type":"blockquote","key":"615r2"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" o.id AS order_id,","type":"blockquote","key":"42v3f"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" o.status AS order_status,","type":"blockquote","key":"dtbrl"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" p.id AS payment_id","type":"blockquote","key":"ec8ab"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM orders AS o","type":"blockquote","key":"36cdm"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"LEFT JOIN payments AS p ON p.order_id = o.id","type":"blockquote","key":"cv3jf"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"WHERE p.id IS NULL;","type":"blockquote","key":"d2ugh"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"RIGHT JOIN","type":"header-three","key":"739nc"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"RIGHT JOIN аналогичен LEFT JOIN, только сохраняет все строки из правой таблицы.","type":"unstyled","key":"c9llu"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"На практике обычно проще переставить таблицы и использовать LEFT JOIN.","type":"unstyled","key":"fn8fu"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FULL OUTER JOIN","type":"header-three","key":"22aic"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Возвращает все строки из обеих таблиц, даже если соответствий нет.","type":"unstyled","key":"6vbf1"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Полезно для сравнения данных между источниками.","type":"unstyled","key":"3cp7t"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT","type":"blockquote","key":"56le0"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" a.id AS source_a_id,","type":"blockquote","key":"4qjo"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" b.id AS source_b_id","type":"blockquote","key":"9iugn"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM source_a AS a","type":"blockquote","key":"4jq51"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FULL OUTER JOIN source_b AS b ON b.id = a.id;","type":"blockquote","key":"8vmbn"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":48,"offset":0,"style":"ITALIC"}],"text":"MySQL не поддерживает FULL OUTER JOIN напрямую.","type":"unstyled","key":"4jgfi"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"JOIN нескольких таблиц","type":"header-three","key":"40tui"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Получить заказ, пользователя, товары в заказе и платёж:","type":"unstyled","key":"30kpo"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT","type":"blockquote","key":"3h0"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" o.id AS order_id,","type":"blockquote","key":"ct13s"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" u.name AS user_name,","type":"blockquote","key":"87d5v"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" p.name AS product_name,","type":"blockquote","key":"30tef"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" oi.quantity,","type":"blockquote","key":"b353t"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" oi.price,","type":"blockquote","key":"1apho"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" pay.status AS payment_status","type":"blockquote","key":"tgci"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM orders AS o","type":"blockquote","key":"6e9i4"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"JOIN users AS u","type":"blockquote","key":"1qah4"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" ON u.id = o.user_id","type":"blockquote","key":"5ka73"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"JOIN order_items AS oi","type":"blockquote","key":"7oo26"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" ON oi.order_id = o.id","type":"blockquote","key":"4a3nq"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"JOIN products AS p","type":"blockquote","key":"bea5l"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" ON p.id = oi.product_id","type":"blockquote","key":"9oe2s"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"LEFT JOIN payments AS pay","type":"blockquote","key":"agqj6"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" ON pay.order_id = o.id","type":"blockquote","key":"56hig"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"WHERE o.id = 1001;","type":"blockquote","key":"ddkp6"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Очень важная ошибка: JOIN без условия ON","type":"header-three","key":"dcva4"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Неправильно:","type":"unstyled","key":"fstvm"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT *","type":"blockquote","key":"51jkg"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM users","type":"blockquote","key":"dhiom"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"JOIN orders;","type":"blockquote","key":"234bs"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":5,"offset":132,"style":"BOLD"}],"text":"JOIN без ON/USING либо не выполнится (ошибка), либо будет эквивалентен CROSS JOIN и приведёт к декартовому произведению (например в MySQL). Если нужен декартов продукт — пиши CROSS JOIN явно, чтобы не было “случайной” логики.","type":"unstyled","key":"b878c"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Декартово произведение — каждая строка одной таблицы соединяется с каждой строкой другой.","type":"unstyled","key":"ps9a"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Если 100 пользователей и 500 заказов, получится 50 000 строк.","type":"unstyled","key":"7pkq2"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Кроме того, всегда проверяйте условие связи (сущности должны быть связаны):","type":"unstyled","key":"3sojn"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"ON u.id = o.user_id","type":"blockquote","key":"99k1n"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"13. Подзапросы","type":"header-two","key":"9qbro"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Подзапрос — это SQL-запрос внутри другого SQL-запроса.","type":"unstyled","key":"25v3"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT *","type":"blockquote","key":"bru58"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM users","type":"blockquote","key":"4k7j3"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"WHERE id IN (","type":"blockquote","key":"fu7nd"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" SELECT user_id","type":"blockquote","key":"d600l"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" FROM orders","type":"blockquote","key":"chn5i"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":");","type":"blockquote","key":"25sml"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"EXISTS","type":"header-three","key":"ejr70"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Найти пользователей, у которых есть хотя бы один заказ:","type":"unstyled","key":"2ifa0"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT *","type":"blockquote","key":"5i998"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM users AS u","type":"blockquote","key":"204rg"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"WHERE EXISTS (","type":"blockquote","key":"6vcim"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" SELECT 1","type":"blockquote","key":"4svi1"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" FROM orders AS o","type":"blockquote","key":"df8dm"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" WHERE o.user_id = u.id","type":"blockquote","key":"59iar"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":");","type":"blockquote","key":"2ro6k"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"EXISTS особенно хорош, когда важно проверить факт существования записи.","type":"unstyled","key":"6bkpp"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"NOT EXISTS","type":"header-three","key":"70ern"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Найти пользователей без заказов:","type":"unstyled","key":"a3dvh"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT *","type":"blockquote","key":"dl361"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM users AS u","type":"blockquote","key":"flvg"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"WHERE NOT EXISTS (","type":"blockquote","key":"6jipu"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" SELECT 1","type":"blockquote","key":"ffoo5"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" FROM orders AS o","type":"blockquote","key":"1iv6s"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" WHERE o.user_id = u.id","type":"blockquote","key":"8ui2j"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":");","type":"blockquote","key":"6vdt8"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Это часто безопаснее, чем NOT IN, если в данных может быть NULL.","type":"unstyled","key":"1dbdn"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Подзапрос в FROM","type":"header-three","key":"etguk"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT","type":"blockquote","key":"9mrd0"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" status,","type":"blockquote","key":"686rm"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" average_total","type":"blockquote","key":"79eb8"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM (","type":"blockquote","key":"9vke4"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" SELECT","type":"blockquote","key":"ea9lk"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" status,","type":"blockquote","key":"5dgm4"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" AVG(total_amount) AS average_total","type":"blockquote","key":"e776v"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" FROM orders","type":"blockquote","key":"9ko9b"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" GROUP BY status","type":"blockquote","key":"20obh"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":") AS order_statistics","type":"blockquote","key":"c976j"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"WHERE average_total > 1000;","type":"blockquote","key":"81i6e"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"unstyled","key":"3deqm"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"14. CASE: условная логика в SQL","type":"header-two","key":"dih9n"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"CASE — аналог if / else.","type":"unstyled","key":"2pjmm"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT","type":"blockquote","key":"f5k4u"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" id,","type":"blockquote","key":"aidm8"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" total_amount,","type":"blockquote","key":"8tvrv"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" CASE","type":"blockquote","key":"1r8fj"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" WHEN total_amount >= 10000 THEN 'VIP'","type":"blockquote","key":"fr833"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" WHEN total_amount >= 5000 THEN 'MEDIUM'","type":"blockquote","key":"1qhgv"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" ELSE 'STANDARD'","type":"blockquote","key":"bqmd"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" END AS order_segment","type":"blockquote","key":"e399v"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM orders;","type":"blockquote","key":"7urfa"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Пример возможной QA-проверки (её части): категория товара должна соответствовать цене:","type":"unstyled","key":"49cqg"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT","type":"blockquote","key":"dh4ul"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" id,","type":"blockquote","key":"64rgu"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" name,","type":"blockquote","key":"ds5ra"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" price,","type":"blockquote","key":"b8a58"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" CASE","type":"blockquote","key":"7crbd"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" WHEN price < 0 THEN 'ERROR: negative price'","type":"blockquote","key":"8loop"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" WHEN price = 0 THEN 'WARNING: free product'","type":"blockquote","key":"6a7ra"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" ELSE 'OK'","type":"blockquote","key":"ek2ii"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" END AS validation_result","type":"blockquote","key":"4f400"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM products;","type":"blockquote","key":"3aa6p"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"15. Работа со строками","type":"header-two","key":"fhv1m"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Синтаксис строковых функций может различаться между СУБД, но основные функции похожи.","type":"unstyled","key":"dm6sk"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"UPPER и LOWER","type":"header-three","key":"61etv"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT","type":"blockquote","key":"dkgk2"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" name,","type":"blockquote","key":"4a19m"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" UPPER(name) AS name_upper,","type":"blockquote","key":"98qvs"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" LOWER(email) AS email_lower","type":"blockquote","key":"cvj7k"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM users;","type":"blockquote","key":"6s6v6"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Проверка (точнее конечно будет сказать выборка или вообще поиск) email без учёта регистра:","type":"unstyled","key":"ebj9k"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT *","type":"blockquote","key":"6qkkl"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM users","type":"blockquote","key":"4gftf"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"WHERE LOWER(email) = LOWER('Test@Example.com');","type":"blockquote","key":"9fp70"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"LENGTH / CHAR_LENGTH","type":"header-three","key":"52qu1"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT","type":"blockquote","key":"1bcrr"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" name,","type":"blockquote","key":"9rgrd"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" CHAR_LENGTH(name) AS name_length","type":"blockquote","key":"b9pta"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM users;","type":"blockquote","key":"6h76g"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Проверка (выборка) слишком коротких имён:","type":"unstyled","key":"59ecc"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT *","type":"blockquote","key":"e1o5r"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM users","type":"blockquote","key":"31gmp"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"WHERE CHAR_LENGTH(name) < 2;","type":"blockquote","key":"3g901"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"TRIM","type":"header-three","key":"7i2f4"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Удаляет пробелы по краям строки:","type":"unstyled","key":"4a9ll"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT","type":"blockquote","key":"4bdp9"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" email,","type":"blockquote","key":"u0s"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" TRIM(email) AS cleaned_email","type":"blockquote","key":"4htsh"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM users;","type":"blockquote","key":"a8ea5"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Поиск данных с лишними пробелами:","type":"unstyled","key":"enpgn"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT *","type":"blockquote","key":"f3dn"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM users","type":"blockquote","key":"3l61h"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"WHERE email <> TRIM(email);","type":"blockquote","key":"d9vts"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"CONCAT (объединить)","type":"header-three","key":"76bdu"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT","type":"blockquote","key":"efldb"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" CONCAT(name, ' <', email, '>') AS user_info","type":"blockquote","key":"d2skf"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM users;","type":"blockquote","key":"f708e"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"В PostgreSQL также можно использовать:","type":"unstyled","key":"1fit2"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT name || ' <' || email || '>'","type":"blockquote","key":"b712e"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM users;","type":"blockquote","key":"8ag7a"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SUBSTRING (часть строки)","type":"header-three","key":"21uom"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT","type":"blockquote","key":"7nkkg"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" email,","type":"blockquote","key":"96k92"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" SUBSTRING(email FROM 1 FOR 5) AS first_five_characters","type":"blockquote","key":"9mab"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM users;","type":"blockquote","key":"5kcm"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"16. Работа с числами","type":"header-two","key":"9ufjb"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"ROUND (округление)","type":"header-three","key":"dpku2"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Округляет число до указанного количества знаков после запятой.","type":"unordered-list-item","key":"8ra4e"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Частый кейс: привести цены/суммы к “денежному” формату (2 знака).","type":"unordered-list-item","key":"5i3v9"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Важно: поведение “ровно на середине” (например, ...5) может зависеть от СУБД и настроек округления, поэтому для финансовых расчётов лучше фиксировать правила на уровне системы/БД.","type":"unordered-list-item","key":"fi490"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"unstyled","key":"b528"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT","type":"blockquote","key":"3in55"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" price,","type":"blockquote","key":"a62k2"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" ROUND(price, 2) AS rounded_price","type":"blockquote","key":"4pdkd"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM products;","type":"blockquote","key":"54qp2"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":32,"offset":18,"style":"BOLD"}],"text":"CEILING и FLOOR (округление вверх/вниз до целого)","type":"header-three","key":"3tovo"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":16,"offset":35,"style":"ITALIC"},{"length":3,"offset":61,"style":"BOLD"}],"text":"CEILING(x) — “потолок”: возвращает наименьшее целое, которое ≥ x (округляет вверх).","type":"unordered-list-item","key":"6ln34"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":16,"offset":29,"style":"ITALIC"},{"length":3,"offset":55,"style":"BOLD"}],"text":"FLOOR(x) — “пол”: возвращает наибольшее целое, которое ≤ x (округляет вниз).","type":"unordered-list-item","key":"8t8pp"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Полезно, когда нужно “целое количество” (например, упаковки, страницы, минимальные лимиты) или расчёт шагами.","type":"unordered-list-item","key":"8pbn8"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Для отрицательных чисел это особенно заметно:CEILING(-1.2) = -1\nFLOOR(-1.2) = -2\n","type":"unordered-list-item","key":"8s0f"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"unstyled","key":"6kb3b"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT","type":"blockquote","key":"5757d"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" price,","type":"blockquote","key":"dp993"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" CEILING(price) AS rounded_up,","type":"blockquote","key":"b831t"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" FLOOR(price) AS rounded_down","type":"blockquote","key":"bspou"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM products;","type":"blockquote","key":"2dq6k"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":29,"offset":7,"style":"BOLD"}],"text":"ABS - (модуль (абсолютное значение))","type":"header-three","key":"97f8h"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Возвращает число без знака: ABS(-15) = 15.","type":"unordered-list-item","key":"a38ih"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Часто используется для сравнения чисел с допуском (tolerance), чтобы не ловить ошибки из‑за представления дробей (особенно после вычислений).","type":"unordered-list-item","key":"6g39t"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"unstyled","key":"fdjqq"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT ABS(-15) AS absolute_value;","type":"blockquote","key":"drk3j"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Полезно при сравнении сумм с допустимой погрешностью:","type":"unstyled","key":"314oi"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Идея: вместо точного равенства проверяем, что разница “достаточно большая”.","type":"unordered-list-item","key":"f8sv5"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"В примере выбираем заказы, где сумма отличается от 999.99 больше чем на 1 копейку (0.01).","type":"unordered-list-item","key":"bai47"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"unstyled","key":"409hg"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT *","type":"blockquote","key":"a9rph"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM orders","type":"blockquote","key":"7q80g"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"WHERE ABS(total_amount - 999.99) > 0.01;","type":"blockquote","key":"5jp3p"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"17. Работа с датами и временем","type":"header-two","key":"dt7do"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Дата и время — частый источник дефектов: часовые пояса, переходы суток, форматирование, границы периодов.","type":"unstyled","key":"f11rp"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Текущая дата и время","type":"header-three","key":"5panl"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Стандартный SQL:","type":"unstyled","key":"5b7to"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT CURRENT_DATE;","type":"blockquote","key":"3iqhf"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"или","type":"unstyled","key":"mqfj"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT CURRENT_TIMESTAMP;","type":"blockquote","key":"45a1u"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Фильтрация по дате","type":"header-three","key":"7tcc2"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Заказы, созданные сегодня:","type":"unstyled","key":"eu5hd"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT *","type":"blockquote","key":"7scic"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM orders","type":"blockquote","key":"61t43"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"WHERE created_at >= CURRENT_DATE","type":"blockquote","key":"31egi"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"AND created_at < CURRENT_DATE + INTERVAL '1 day';","type":"blockquote","key":"c7loq"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":43,"offset":0,"style":"ITALIC"}],"text":"Синтаксис INTERVAL различается между СУБД.","type":"unstyled","key":"5jvmh"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Извлечение частей даты","type":"header-three","key":"b59cj"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"В PostgreSQL:","type":"unstyled","key":"5knv"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT","type":"blockquote","key":"3lv3p"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" created_at,","type":"blockquote","key":"cggd7"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" EXTRACT(YEAR FROM created_at) AS year,","type":"blockquote","key":"dr9jd"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" EXTRACT(MONTH FROM created_at) AS month,","type":"blockquote","key":"dfiej"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" EXTRACT(DAY FROM created_at) AS day","type":"blockquote","key":"7gdd1"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM orders;","type":"blockquote","key":"essa7"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"unstyled","key":"6la6a"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Проверка дат на аномалии","type":"header-three","key":"f8nnb"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Например, заказ не должен быть создан в будущем:","type":"unstyled","key":"5gtt"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT *","type":"blockquote","key":"9ikh1"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM orders","type":"blockquote","key":"ffafo"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"WHERE created_at > CURRENT_TIMESTAMP;","type":"blockquote","key":"6l58j"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Платёж не должен быть создан раньше заказа:","type":"unstyled","key":"8apik"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT","type":"blockquote","key":"bhaa4"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" o.id AS order_id,","type":"blockquote","key":"btm8u"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" o.created_at AS order_created_at,","type":"blockquote","key":"7efl1"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" p.created_at AS payment_created_at","type":"blockquote","key":"8kmpg"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM orders AS o","type":"blockquote","key":"6m5jl"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"JOIN payments AS p ON p.order_id = o.id","type":"blockquote","key":"eim38"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"WHERE p.created_at < o.created_at;","type":"blockquote","key":"aq8fu"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"18. INSERT: добавление данных (например тестовых)","type":"header-two","key":"1rv9t"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Выполняйте INSERT только на тестовых окружениях и только если у вас есть соответствующие права и согласование.","type":"blockquote","key":"csiv3"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Пример:","type":"unstyled","key":"95k9l"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"INSERT INTO users (","type":"blockquote","key":"4o9i0"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" id,","type":"blockquote","key":"62n7h"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" name,","type":"blockquote","key":"1ndd5"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" email,","type":"blockquote","key":"cfbfr"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" status,","type":"blockquote","key":"anaha"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" created_at","type":"blockquote","key":"aq1t2"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":")","type":"blockquote","key":"156pe"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"VALUES (","type":"blockquote","key":"95rru"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" 10001,","type":"blockquote","key":"al8mg"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" 'Тестовый Пользователь',","type":"blockquote","key":"89btc"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" 'test.user@example.com',","type":"blockquote","key":"9puag"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" 'ACTIVE',","type":"blockquote","key":"2bv5h"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" CURRENT_TIMESTAMP","type":"blockquote","key":"2q3s9"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":");","type":"blockquote","key":"85ovh"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Добавить несколько строк:","type":"unstyled","key":"1shdj"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"INSERT INTO products (","type":"blockquote","key":"7c9t5"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" id,","type":"blockquote","key":"ara8b"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" name,","type":"blockquote","key":"dllgn"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" category,","type":"blockquote","key":"blh0m"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" price,","type":"blockquote","key":"d7uma"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" stock_quantity,","type":"blockquote","key":"1r0rn"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" is_active","type":"blockquote","key":"augce"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":")","type":"blockquote","key":"64rtp"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"VALUES","type":"blockquote","key":"48mrk"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" (20001, 'Test Product A', 'Test', 100.00, 10, TRUE),","type":"blockquote","key":"57cu6"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" (20002, 'Test Product B', 'Test', 200.00, 5, TRUE);","type":"blockquote","key":"1htq6"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"После вставки обязательно проверьте результат:","type":"unstyled","key":"a0n8s"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT *","type":"blockquote","key":"f3snv"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM products","type":"blockquote","key":"7vf93"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"WHERE id IN (20001, 20002);","type":"blockquote","key":"fh7ra"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"19. UPDATE: изменение данных","type":"header-two","key":"2ob32"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"UPDATE users","type":"blockquote","key":"6a445"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SET status = 'BLOCKED'","type":"blockquote","key":"1ltj8"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"WHERE id = 10001;","type":"blockquote","key":"5fsdv"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Перед UPDATE всегда сначала делайте SELECT с тем же условием (чтобы явно увидеть у кого вы будете данные менять):","type":"unstyled","key":"73qeu"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT *","type":"blockquote","key":"22ftb"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM users","type":"blockquote","key":"965f"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"WHERE id = 10001;","type":"blockquote","key":"brp4i"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Только потом:","type":"unstyled","key":"e9r2g"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"UPDATE users","type":"blockquote","key":"38d4v"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SET status = 'BLOCKED'","type":"blockquote","key":"27tvj"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"WHERE id = 10001;","type":"blockquote","key":"ft3lr"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"20. DELETE: удаление данных","type":"header-two","key":"59bne"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"DELETE FROM users","type":"blockquote","key":"bb4ee"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"WHERE id = 10001;","type":"blockquote","key":"7v3ll"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Также как и с UPDATE - перед удалением выбираем (чтобы посмотреть):","type":"unstyled","key":"6hs30"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT *","type":"blockquote","key":"226nb"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM users","type":"blockquote","key":"5k9na"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"WHERE id = 10001;","type":"blockquote","key":"3jl55"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Потом удаление:","type":"unstyled","key":"bq9p3"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"DELETE FROM users","type":"blockquote","key":"bmep8"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"WHERE id = 10001;","type":"blockquote","key":"5qbsd"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":13,"offset":0,"style":"BOLD"}],"text":"Очень опасно:","type":"unstyled","key":"9qtp1"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"DELETE FROM users;","type":"blockquote","key":"6hiel"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Этот запрос удалит все строки из таблицы.","type":"unstyled","key":"qn4c"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"21. Транзакции: безопасная работа с данными","type":"header-two","key":"18d5d"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Транзакция — это группа операций, которая выполняется целиком или не выполняется вовсе.","type":"unstyled","key":"962gf"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Основные команды:","type":"unstyled","key":"3vj54"},{"data":{"image":{"id":"6a84a430ef064b01597006bb"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"atomic:image","key":"3nn7i"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Пример:","type":"unstyled","key":"8eur"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"BEGIN;","type":"blockquote","key":"8621r"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"blockquote","key":"ff3u3"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"UPDATE products","type":"blockquote","key":"eucfo"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SET stock_quantity = stock_quantity - 1","type":"blockquote","key":"3d0al"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"WHERE id = 20001;","type":"blockquote","key":"6d43j"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"blockquote","key":"e2m7v"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"INSERT INTO order_items (id, order_id, product_id, quantity, price)","type":"blockquote","key":"ej4m2"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"VALUES (50001, 1001, 20001, 1, 100.00);","type":"blockquote","key":"7punm"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"blockquote","key":"1jpt2"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"COMMIT;","type":"blockquote","key":"4muh8"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Если что-то пошло не так:","type":"unstyled","key":"2vm71"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"ROLLBACK;","type":"blockquote","key":"b4bc1"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Для тестировщика особенно полезен сценарий «подготовить данные — протестировать — откатить»:","type":"unstyled","key":"cj1nu"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"BEGIN;","type":"blockquote","key":"bshep"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"blockquote","key":"bb24m"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"UPDATE users","type":"blockquote","key":"43qgh"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SET status = 'BLOCKED'","type":"blockquote","key":"2kkgq"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"WHERE id = 10001;","type":"blockquote","key":"bl40f"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"blockquote","key":"cre0k"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"-- Выполняем проверку в UI или API","type":"blockquote","key":"d5c05"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"ROLLBACK;","type":"blockquote","key":"6t63j"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"После ROLLBACK изменения отменятся.","type":"unstyled","key":"5j535"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Не все операции одинаково откатываются во всех СУБД. Например, некоторые DDL-команды могут выполнять неявный commit.","type":"blockquote","key":"68n4f"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"22. DDL: создание и изменение таблиц","type":"header-two","key":"a41sg"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Тестировщик не всегда создаёт таблицы, но должен уметь читать DDL и проверять миграции.","type":"unstyled","key":"1bd1r"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"CREATE TABLE","type":"header-three","key":"370tu"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"CREATE TABLE test_users (","type":"blockquote","key":"etg5s"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" id BIGINT PRIMARY KEY,","type":"blockquote","key":"7f32t"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" name VARCHAR(100) NOT NULL,","type":"blockquote","key":"f8iqj"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" email VARCHAR(255) UNIQUE,","type":"blockquote","key":"f11no"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" status VARCHAR(20) DEFAULT 'ACTIVE',","type":"blockquote","key":"16tok"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP","type":"blockquote","key":"1922t"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":");","type":"blockquote","key":"1mafj"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Здесь используются важные ключевые слова:","type":"unstyled","key":"c2mea"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"PRIMARY KEY — уникальный идентификатор;","type":"unordered-list-item","key":"234mu"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"NOT NULL — значение обязательно;","type":"unordered-list-item","key":"1g975"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"UNIQUE — значение не должно повторяться;","type":"unordered-list-item","key":"f1ob9"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"DEFAULT — значение по умолчанию.","type":"unordered-list-item","key":"57p2r"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"ALTER TABLE (изменить таблицу)","type":"header-three","key":"f1o5j"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Добавить поле:","type":"unstyled","key":"7pqp9"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"ALTER TABLE users","type":"blockquote","key":"5kukq"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"ADD COLUMN phone VARCHAR(30);","type":"blockquote","key":"dp9cg"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Изменить тип поля — синтаксис зависит от СУБД:","type":"unstyled","key":"60sj4"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"ALTER TABLE users","type":"blockquote","key":"fordu"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"ALTER COLUMN name TYPE VARCHAR(200);","type":"blockquote","key":"7d44k"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"DROP TABLE (удалить таблицу)","type":"header-three","key":"cq7p7"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"DROP TABLE test_users;","type":"blockquote","key":"e3htj"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Удаляет таблицу вместе с данными.","type":"unstyled","key":"fdiai"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"TRUNCATE (удалить данные из таблицы)","type":"header-three","key":"bikmr"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"TRUNCATE TABLE test_users;","type":"blockquote","key":"dqv71"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Удаляет все строки из таблицы быстро.","type":"unstyled","key":"evqer"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Разница между DELETE и TRUNCATE зависит от СУБД, но в общем случае:","type":"unstyled","key":"8lbas"},{"data":{"image":{"id":"6a84a5a85bd85016af8b3d03"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"atomic:image","key":"c2230"},{"data":{},"depth":0,"entityRanges":[{"key":1,"length":33,"offset":52}],"inlineStyleRanges":[{"length":34,"offset":52,"style":"BOLD"}],"text":"Подробнее про отличия рекомендую прочитать в статье Сравнение TRUNCATE и DELETE в SQL.","type":"unstyled","key":"1l4qe"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"23. Ограничения целостности данных (DDL constraints)","type":"header-two","key":"a6s77"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Ограничения целостности задаются в схеме базы данных и предотвращают сохранение некорректных значений: NOT NULL, UNIQUE, FOREIGN KEY, CHECK.","type":"unstyled","key":"dclbj"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"NOT NULL","type":"header-three","key":"93r79"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Запрещает NULL в колонке.","type":"unstyled","key":"44v8g"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"name VARCHAR(100) NOT NULL","type":"blockquote","key":"67h2t"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"UNIQUE","type":"header-three","key":"cp7e0"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Запрещает дубли.","type":"unstyled","key":"d659p"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"email VARCHAR(255) UNIQUE","type":"blockquote","key":"2vtf7"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FOREIGN KEY","type":"header-three","key":"1vtdf"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Требует, чтобы ссылка в дочерней таблице указывала на существующую запись.","type":"unstyled","key":"cq9v5"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"CONSTRAINT fk_orders_user","type":"blockquote","key":"2l0nm"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FOREIGN KEY (user_id) REFERENCES users(id)","type":"blockquote","key":"1hn6u"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"CHECK","type":"header-three","key":"5o48v"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Ограничивает допустимые значения условием.","type":"unstyled","key":"5j8v4"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"price DECIMAL(10,2) CONSTRAINT chk_price_non_negative CHECK (price >= 0)","type":"blockquote","key":"7cr7g"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"unstyled","key":"c1q6"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"24. Оконные функции: уровень уверенного QA","type":"header-two","key":"bursv"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Оконные функции позволяют выполнять расчёты по набору строк без «схлопывания» результата, как при GROUP BY.","type":"unstyled","key":"b7sbe"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Они особенно полезны для:","type":"unstyled","key":"feen6"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"поиска дублей;","type":"unordered-list-item","key":"nmkv"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"ранжирования;","type":"unordered-list-item","key":"9vm0n"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"проверки истории изменений;","type":"unordered-list-item","key":"823k2"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"анализа последовательностей событий;","type":"unordered-list-item","key":"en02p"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"поиска первой или последней записи пользователя.","type":"unordered-list-item","key":"25r8b"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"ROW_NUMBER — уникальная нумерация строк в окне","type":"header-three","key":"1igb1"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":11,"offset":0,"style":"BOLD"},{"length":6,"offset":100,"style":"BOLD"}],"text":"Что делает: даёт каждой строке порядковый номер (1,2,3,…) внутри PARTITION BY, согласно ORDER BY. \nВажно: при равных значениях сортировки порядок может быть недетерминированным — для стабильности добавляй tie-breaker (например ORDER BY created_at, id).","type":"unstyled","key":"ftemv"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Пронумеровать заказы каждого пользователя:","type":"unstyled","key":"40h2h"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"unstyled","key":"5924t"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT","type":"blockquote","key":"a6i7k"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" user_id,","type":"blockquote","key":"bmgdl"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" id AS order_id,","type":"blockquote","key":"do29t"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" created_at,","type":"blockquote","key":"dd66b"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" ROW_NUMBER() OVER (","type":"blockquote","key":"beta8"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" PARTITION BY user_id","type":"blockquote","key":"eo6si"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" ORDER BY created_at","type":"blockquote","key":"mnuj"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" ) AS order_number","type":"blockquote","key":"8mncj"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM orders;","type":"blockquote","key":"7v83m"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"unstyled","key":"7689m"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Поиск дублей email","type":"header-three","key":"d9e1c"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Оконный подход полезен, когда тебе нужно:","type":"unstyled","key":"6lj7c"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":11,"offset":0,"style":"BOLD"},{"length":22,"offset":15,"style":"BOLD"}],"text":"и посчитать, и показать каждую строку, не теряя детализацию;","type":"unordered-list-item","key":"26qnk"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"выбрать “лишние” строки дублей.","type":"unordered-list-item","key":"f9iqo"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Вывести все дублирующиеся записи через ROW_NUMBER():","type":"unstyled","key":"frk3r"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"WITH ranked_users AS (","type":"blockquote","key":"523kv"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" SELECT","type":"blockquote","key":"1i2hc"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" id,","type":"blockquote","key":"7oe62"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" email,","type":"blockquote","key":"5pchp"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" ROW_NUMBER() OVER (","type":"blockquote","key":"6deen"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" PARTITION BY email","type":"blockquote","key":"fgo4q"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" ORDER BY id","type":"blockquote","key":"8hcf4"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" ) AS row_num","type":"blockquote","key":"5d9pl"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" FROM users","type":"blockquote","key":"6bqo"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" )","type":"blockquote","key":"1v2n3"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT *","type":"blockquote","key":"78vq9"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM ranked_users","type":"blockquote","key":"au3un"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"WHERE row_num > 1;","type":"blockquote","key":"1kp6h"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":10,"offset":0,"style":"BOLD"}],"text":"Пояснение: row_num = 1 — “первая” запись email, row_num > 1 — остальные дубли.","type":"unstyled","key":"bgan8"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Но можно и без оконной функции, будет проще, но менее информативно:","type":"unstyled","key":"6fvoc"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT","type":"blockquote","key":"2phtr"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" email,","type":"blockquote","key":"9cjpj"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" COUNT(*) AS duplicates_count","type":"blockquote","key":"2a9s1"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM users","type":"blockquote","key":"2hnfd"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"GROUP BY email","type":"blockquote","key":"769du"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"HAVING COUNT(*) > 1;","type":"blockquote","key":"c7b3a"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"LAG() — сравнение с предыдущей записью","type":"header-three","key":"68fnr"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":11,"offset":0,"style":"BOLD"},{"length":10,"offset":35,"style":"BOLD"}],"text":"Что делает: возвращает значение из предыдущей строки в окне (по заданному ORDER BY). Это намного проще и надёжнее, чем self join по “предыдущей дате”.","type":"unstyled","key":"ap13t"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Проверить историю статусов заказа:","type":"unstyled","key":"537ej"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT","type":"blockquote","key":"60k5r"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" order_id,","type":"blockquote","key":"mdva"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" status,","type":"blockquote","key":"be0cg"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" changed_at,","type":"blockquote","key":"fpeaa"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" LAG(status) OVER (","type":"blockquote","key":"3606v"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" PARTITION BY order_id","type":"blockquote","key":"49be2"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" ORDER BY changed_at","type":"blockquote","key":"cboip"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" ) AS previous_status","type":"blockquote","key":"7p2ab"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM order_status_history;","type":"blockquote","key":"r012"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":15,"offset":0,"style":"BOLD"}],"text":"Как это читать: для каждой строки истории ты видишь previous_status — статус на шаг назад.","type":"unstyled","key":"6nbch"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Остальные оконные функции:","type":"header-two","key":"1plu0"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Ниже — список самых употребимых в реальной проверке данных/аналитике. Можно просто добавить как “шпаргалку”.","type":"unstyled","key":"ape77"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":12,"offset":0,"style":"BOLD"}],"text":"Ранжирование","type":"header-three","key":"b1qd5"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"RANK() — ранги с пропусками (1,1,3…)","type":"unordered-list-item","key":"3qv3v"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"DENSE_RANK() — ранги без пропусков (1,1,2…)","type":"unordered-list-item","key":"89qcn"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"PERCENT_RANK() — относительный ранг (0..1)","type":"unordered-list-item","key":"bdclu"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"CUME_DIST() — кумулятивное распределение (0..1)","type":"unordered-list-item","key":"3nj29"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"NTILE(n) — разбивает на n “корзин” (квантили)","type":"unordered-list-item","key":"1uvu0"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":26,"offset":0,"style":"BOLD"}],"text":"Смещения (соседние строки)","type":"header-three","key":"8b1p6"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":9,"offset":25,"style":"BOLD"}],"text":"LEAD(expr) — значение из следующей строки (парно к LAG)","type":"unordered-list-item","key":"4uase"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"LAG(expr, offset, default) / LEAD(expr, offset, default) — со смещением и значением по умолчанию","type":"unordered-list-item","key":"e3b2h"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":27,"offset":0,"style":"BOLD"}],"text":"“Вытащить значение из окна”","type":"header-three","key":"bffap"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FIRST_VALUE(expr) — первое значение по порядку в окне","type":"unordered-list-item","key":"680nc"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"LAST_VALUE(expr) — последнее значение (тут часто важно правильно задать frame!)","type":"unordered-list-item","key":"91psh"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"NTH_VALUE(expr, n) — n‑ое значение","type":"unordered-list-item","key":"4u2rt"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Агрегаты как оконные (без схлопывания строк)","type":"header-three","key":"cd9i9"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SUM() OVER (...), AVG() OVER (...), COUNT() OVER (...)","type":"unordered-list-item","key":"6t9ai"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"MIN() OVER (...), MAX() OVER (...)","type":"unordered-list-item","key":"7mk0g"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Важное про frame (кадр) для бегущих расчётов","type":"header-three","key":"fqfg0"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Для running sum/avg и для корректного LAST_VALUE() часто нужно явно задавать:","type":"unstyled","key":"3t9g8"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — “с начала окна до текущей строки”","type":"unordered-list-item","key":"69aah"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING — “всё окно целиком”","type":"unordered-list-item","key":"beoqu"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"25. CTE: читаемые сложные запросы","type":"header-two","key":"ehb5d"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"CTE (Common Table Expression) создаётся через WITH. Это временный именованный результат внутри одного SQL-запроса.","type":"unstyled","key":"8gj7p"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"WITH paid_orders AS (","type":"blockquote","key":"83j5f"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" SELECT *","type":"blockquote","key":"1b2j"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" FROM orders","type":"blockquote","key":"djebk"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" WHERE status = 'PAID'","type":"blockquote","key":"219jc"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":")","type":"blockquote","key":"3jrfq"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT","type":"blockquote","key":"4alpg"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" user_id,","type":"blockquote","key":"dq15b"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" COUNT(*) AS paid_orders_count,","type":"blockquote","key":"8esmh"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" SUM(total_amount) AS paid_total","type":"blockquote","key":"al466"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM paid_orders","type":"blockquote","key":"c0uo4"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"GROUP BY user_id;","type":"blockquote","key":"41t5l"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"CTE особенно полезен, когда сложную проверку нужно сделать понятной для команды.","type":"unstyled","key":"1dafk"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"26. Порядок выполнения SQL-запроса","type":"header-two","key":"akd4n"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SQL пишется в таком порядке:","type":"unstyled","key":"1he4"},{"data":{"image":{"id":"6a84ac6a6a98d64808d8d2f0"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"atomic:image","key":"5bap7"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Но логически СУБД обычно обрабатывает запрос примерно так:","type":"unstyled","key":"b9hgo"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM;","type":"ordered-list-item","key":"202er"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"JOIN;","type":"ordered-list-item","key":"1iq7s"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"ON;","type":"ordered-list-item","key":"4ij2c"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"WHERE;","type":"ordered-list-item","key":"5du7t"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"GROUP BY;","type":"ordered-list-item","key":"te1j"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"агрегатные функции;","type":"ordered-list-item","key":"35gvn"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"HAVING;","type":"ordered-list-item","key":"akmmi"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT;","type":"ordered-list-item","key":"ahl5q"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"DISTINCT;","type":"ordered-list-item","key":"d53oj"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"ORDER BY;","type":"ordered-list-item","key":"6antu"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"LIMIT / OFFSET.","type":"ordered-list-item","key":"ffcki"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Это объясняет, почему нельзя всегда использовать алиас из SELECT в WHERE.","type":"unstyled","key":"8q6dg"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":12,"offset":0,"style":"BOLD"}],"text":"Неправильно:","type":"unstyled","key":"apg61"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT","type":"blockquote","key":"9pthn"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" price * quantity AS total","type":"blockquote","key":"2diba"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM order_items","type":"blockquote","key":"1n9sh"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"WHERE total > 1000;","type":"blockquote","key":"arsh6"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":10,"offset":0,"style":"BOLD"}],"text":"Правильно:","type":"unstyled","key":"ejc4c"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT","type":"blockquote","key":"c6tbo"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" price * quantity AS total","type":"blockquote","key":"a8c04"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM order_items","type":"blockquote","key":"9757d"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"WHERE price * quantity > 1000;","type":"blockquote","key":"ekm5f"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":14,"offset":0,"style":"BOLD"}],"text":"Или через CTE:","type":"unstyled","key":"4er40"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"WITH items AS (","type":"blockquote","key":"125l3"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" SELECT","type":"blockquote","key":"3dpno"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" id,","type":"blockquote","key":"6uafn"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" price * quantity AS total","type":"blockquote","key":"7mf"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" FROM order_items","type":"blockquote","key":"gg9s"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":")","type":"blockquote","key":"8bb86"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT *","type":"blockquote","key":"4k0ed"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"FROM items","type":"blockquote","key":"b3tg3"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"WHERE total > 1000;","type":"blockquote","key":"50v21"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"27. Безопасность: правила работы тестировщика с SQL","type":"header-two","key":"budqb"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Всегда соблюдайте эти правила","type":"header-three","key":"btpfa"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":45,"offset":0,"style":"BOLD"}],"text":"1. Не выполняйте UPDATE и DELETE без WHERE","type":"unstyled","key":"8eumu"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":25,"offset":0,"style":"BOLD"}],"text":"2. Используйте транзакции","type":"unstyled","key":"1g2b3"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":46,"offset":0,"style":"BOLD"}],"text":"3. Не работайте с production без необходимости","type":"unstyled","key":"4dmnf"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"На production чаще всего QA должен иметь:","type":"unstyled","key":"eqc61"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"доступ только на чтение;","type":"unordered-list-item","key":"dsisi"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"ограниченный набор таблиц;","type":"unordered-list-item","key":"51jup"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"маскированные персональные данные;","type":"unordered-list-item","key":"da0fi"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"аудит запросов.","type":"unordered-list-item","key":"6n3gl"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":52,"offset":0,"style":"BOLD"}],"text":"4. Не передавайте персональные данные в баг-репортах","type":"unstyled","key":"19taq"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Не копируйте в Jira, чаты и документацию:","type":"unstyled","key":"a9rt"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"реальные пароли;","type":"unordered-list-item","key":"1304l"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"токены;","type":"unordered-list-item","key":"9apcc"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"номера карт;","type":"unordered-list-item","key":"fd1n9"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"паспортные данные;","type":"unordered-list-item","key":"37urj"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"полные адреса;","type":"unordered-list-item","key":"frudi"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"персональные email и телефоны без необходимости.","type":"unordered-list-item","key":"1r34f"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":45,"offset":0,"style":"BOLD"}],"text":"5. Используйте понятные имена тестовых данных","type":"unstyled","key":"9k7gd"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"28. Задания для самостоятельной подготовки","type":"header-two","key":"aka11"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Используйте учебную схему users, products, orders, order_items, payments, описанную в статье (раздел 4).","type":"unstyled","key":"5vl6g"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Базовый уровень","type":"header-three","key":"6h3qa"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Создайте таблицы указанные в схеме, заполните тестовыми данными (любыми по схеме)","type":"ordered-list-item","key":"bbedf"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Выведите все поля из таблицы users.","type":"ordered-list-item","key":"bbd7g"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Выведите только id, name, email активных пользователей.","type":"ordered-list-item","key":"8pj8s"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Найдите пользователей, зарегистрированных после 2026-08-01.","type":"ordered-list-item","key":"f32kt"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Получите список уникальных статусов заказов.","type":"ordered-list-item","key":"7ekng"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Найдите товары с ценой от 1000 до 5000.","type":"ordered-list-item","key":"fv8p6"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Найдите товары, в названии которых есть слово Phone.","type":"ordered-list-item","key":"2il5k"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Выведите 10 самых дорогих товаров.","type":"ordered-list-item","key":"9a9r6"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Найдите пользователей без email.","type":"ordered-list-item","key":"2k3dn"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Найдите все заказы со статусами NEW и PAID.","type":"ordered-list-item","key":"56h8j"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Посчитайте общее количество заказов.","type":"ordered-list-item","key":"2g3df"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Средний уровень","type":"header-three","key":"7vjei"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Посчитайте количество заказов по каждому статусу.","type":"ordered-list-item","key":"airbd"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Найдите статусы, в которых больше 10 заказов.","type":"ordered-list-item","key":"dqtfv"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Выведите все заказы вместе с именем и email пользователя.","type":"ordered-list-item","key":"9p9m"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Найдите пользователей, у которых нет заказов.","type":"ordered-list-item","key":"6d682"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Найдите заказы, в которых нет позиций ( order_items).","type":"ordered-list-item","key":"ebmop"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Найдите товары, которые ни разу не покупали.","type":"ordered-list-item","key":"ed8ag"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Найдите пользователей, сделавших более трёх заказов.","type":"ordered-list-item","key":"18o80"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Посчитайте сумму успешных платежей по каждому способу оплаты.","type":"ordered-list-item","key":"7fsk8"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Найдите заказы, у которых сумма total_amount не совпадает с суммой позиций.","type":"ordered-list-item","key":"e2if8"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Найдите оплаченные заказы без успешного платежа.","type":"ordered-list-item","key":"690p0"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Продвинутый уровень","type":"header-three","key":"cdi9l"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Найдите дублирующиеся email без учёта регистра.","type":"ordered-list-item","key":"bqtdr"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Для каждого пользователя выведите его первый заказ.","type":"ordered-list-item","key":"e0nsk"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Для каждого пользователя выведите последний заказ.","type":"ordered-list-item","key":"15inl"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Найдите заказы, созданные в будущем.","type":"ordered-list-item","key":"bb5pg"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Найдите платежи, созданные раньше соответствующего заказа.","type":"ordered-list-item","key":"2c094"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Найдите продукты с отрицательной ценой или отрицательным остатком.","type":"ordered-list-item","key":"73kt8"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Напишите запрос, который выводит топ-3 пользователей по сумме успешных оплат.","type":"ordered-list-item","key":"f671l"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Найдите пользователей, у которых есть заказы, но нет ни одного успешного платежа.","type":"ordered-list-item","key":"3j71v"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Найдите заказы, содержащие более пяти различных товаров.","type":"ordered-list-item","key":"b9b1b"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"С помощью CASE разделите заказы на категории:SMALL, если сумма меньше 1000;\nMEDIUM, если сумма от 1000 до 9999.99;\nLARGE, если сумма 10000 и больше.\n","type":"ordered-list-item","key":"f00d4"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"29. Что изучать дальше","type":"header-two","key":"c6nro"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Урок получился достаточно большим и все же это не все, что опытному тестировщику может пригодиться в ежедневной работе. ","type":"unstyled","key":"4smp5"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"После освоения материала переходите к следующим темам:","type":"unstyled","key":"e69q6"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"индексы и производительность запросов;","type":"ordered-list-item","key":"2p5lk"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"EXPLAIN и EXPLAIN ANALYZE;","type":"ordered-list-item","key":"aien"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"сложные JOIN;","type":"ordered-list-item","key":"ergp3"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"оконные функции (подробнее);","type":"ordered-list-item","key":"endnv"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"CTE и рекурсивные запросы;","type":"ordered-list-item","key":"f4prl"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"транзакции и уровни изоляции;","type":"ordered-list-item","key":"13khp"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"блокировки и конкурентный доступ;","type":"ordered-list-item","key":"2ann5"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"JSON/JSONB в PostgreSQL;","type":"ordered-list-item","key":"8rv64"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SQL-инъекции и безопасные параметризованные запросы;","type":"ordered-list-item","key":"ekh42"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"миграции БД: Flyway, Liquibase, Alembic;","type":"ordered-list-item","key":"e5rpv"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"проверка данных в ETL, DWH и Data Lake;","type":"ordered-list-item","key":"bk15u"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"автоматизация SQL-проверок в API- и UI-тестах.","type":"ordered-list-item","key":"3o9eg"},{"data":{},"depth":0,"entityRanges":[{"key":2,"length":17,"offset":27}],"inlineStyleRanges":[{"length":26,"offset":0,"style":"BOLD"},{"length":17,"offset":27,"style":"BOLD"}],"text":"Рекомендую почитать раздел Базы данных и SQL","type":"unstyled","key":"akvg3"},{"data":{},"depth":0,"entityRanges":[{"key":3,"length":35,"offset":75}],"inlineStyleRanges":[{"length":110,"offset":0,"style":"BOLD"}],"text":"Не забудьте подписаться на канал, чтобы не пропустить полезную информацию: QA Helper - справочник тестировщика","type":"unstyled","key":"cgk0d"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":75,"offset":0,"style":"BOLD"}],"text":"Пишите в комментариях что еще было бы интересно рассмотреть более подробно.","type":"unstyled","key":"53p83"},{"data":{"image":{"id":"6a84afb44f4c650b656a90a2"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"atomic:image","key":"3huc9"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"unstyled","key":"3sd6e"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"unstyled","key":"2vfqd"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"unstyled","key":"1vipa"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"unstyled","key":"8n3ee"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"marker","key":"e8vmPzYQWOyUppZkF63J"}],"entityMap":{"0":{"data":{"url":"https://dzen.ru/a/ZtfSgiJxP3ymu5TP?share_to=link"},"mutability":"MUTABLE","type":"LINK"},"1":{"data":{"url":"https://dzen.ru/a/Zyyw9VzGCGHxivIH?share_to=link"},"mutability":"MUTABLE","type":"LINK"},"2":{"data":{"url":"https://dzen.ru/suite/02faad3e-6229-405a-bac6-4a6c98dcf33e?share_to=link"},"mutability":"MUTABLE","type":"LINK"},"3":{"data":{"href":"https://dzen.ru/id/667312f273e0422ea27887ee?share_to=link","url":"https://dzen.ru/id/667312f273e0422ea27887ee?share_to=link"},"mutability":"MUTABLE","type":"LINK"}}},"mentions":[]}