Разработчик баз данных SQL (Профессиональный уровень)

Что такое нормализация баз данных и какие нормальные формы вы знаете?
Нормализация — это процесс упорядочивания структуры таблиц с целью устранения избыточности и повышения целостности данных. Основные нормальные формы: 1НФ (отсутствие повторяющихся групп), 2НФ (полная зависимость от первичного ключа), 3НФ (отсутствие транзитивных зависимостей), Бойса-Кодда, 4НФ и 5НФ.

В чём разница между CTE и подзапросом?
CTE (Common Table Expression) — это временная именованная таблица, определённая в рамках одного SQL-запроса. Подзапрос — это запрос внутри другого запроса, который может быть как именованным, так и анонимным. CTE делает запрос более читаемым и позволяет использовать рекурсию.

Что такое оконные функции и для чего они используются?
Оконные функции выполняют вычисления по набору строк, связанных с текущей строкой. Они позволяют рассчитывать ранги, накопительные суммы, скользящие средние и другие аналитические метрики без потери уровня детализации.

Как работает функция RANK() и чем она отличается от ROW_NUMBER()?
RANK() присваивает ранг строкам в порядке сортировки, оставляя пропуски при совпадении значений. ROW_NUMBER() присваивает уникальный номер каждой строке без пропусков.

Что такое индекс и какие типы индексов существуют?
Индекс — это объект базы данных, предназначенный для ускорения выборки данных. Типы: B-Tree, Hash, Bitmap, Функциональные, Составные, Уникальные, Обратные.

Как анализировать план выполнения запроса и зачем это нужно?
План выполнения запроса можно посмотреть с помощью команды EXPLAIN или EXPLAIN ANALYZE. Он показывает, как именно СУБД будет выполнять запрос: какие индексы используются, в каком порядке соединяются таблицы, сколько строк обрабатывается.

Что такое транзакция и какие свойства она имеет?
Транзакция — это последовательность операций над базой данных, которая рассматривается как единое целое. Её свойства (ACID): Атомарность, Согласованность, Изолированность, Долговечность.

В чём разница между READ COMMITTED и REPEATABLE READ в уровне изоляции транзакций?
READ COMMITTED гарантирует, что транзакция не увидит неподтверждённые изменения других транзакций. REPEATABLE READ обеспечивает, чтобы в рамках одной транзакции повторный запрос к данным возвращал те же значения.

Что такое триггер и в каких случаях он применяется?
Триггер — это хранимая процедура, автоматически вызываемая при наступлении определённого события (INSERT, UPDATE, DELETE). Используется для логирования изменений, контроля целостности, автоматизации действий.

Как создать хранимую процедуру и передать в неё параметры?
Хранимая процедура создаётся с помощью CREATE PROCEDURE, затем указываются входные/выходные параметры и тело процедуры. Пример: CREATE PROCEDURE get_user(IN id INT).

Что такое курсор и когда его стоит использовать?
Курсор — это механизм работы с результатом запроса построчно. Используется в сложной бизнес-логике, когда требуется обработка записей по одной, но требует осторожности из-за снижения производительности.

Что такое партиционирование и какие типы партиционирования существуют?
Партиционирование — это разделение большой таблицы на физически отдельные части для упрощения управления и повышения производительности. Типы: по диапазону, списку, хэшу, ключу.

Как реализовать безопасность доступа к данным в SQL?
Безопасность реализуется через систему ролей и привилегий (GRANT / REVOKE), использование представлений, ограничение прав на уровне столбцов, шифрование, защиту от SQL-инъекций.

Что такое VIEW и в чём его преимущества?
VIEW — это виртуальная таблица, основанная на результате SQL-запроса. Преимущества: абстракция, упрощение запросов, дополнительный уровень безопасности.

Что такое ETL-процесс и как он связан с SQL?
ETL (Extract, Transform, Load) — это процесс извлечения, преобразования и загрузки данных. SQL используется на всех этапах: для извлечения, очистки, агрегации и записи данных в хранилища.

Как работать с JSON в SQL?
В современных СУБД есть поддержка JSON. Например, в PostgreSQL — операторы ->, #>, функции jsonb_set(), jsonb_extract_path(). В MySQL — JSON_EXTRACT(), JSON_OBJECT().

Что такое материализованное представление и чем оно отличается от обычного?
Материализованное представление хранит данные физически и обновляется периодически. Обычное представление пересчитывается каждый раз при вызове.

Что такое блокировка и какие виды блокировок бывают?
Блокировка — это механизм, предотвращающий одновременное изменение данных несколькими пользователями. Виды: разделяемые (shared), исключительные (exclusive), обновляемые (update).

Как реализовать рекурсивный запрос с использованием CTE?
Рекурсивный CTE состоит из начального запроса и рекурсивной части. Пример: WITH RECURSIVE cte AS (SELECT ... UNION ALL SELECT ...) SELECT * FROM cte;.

Как защититься от SQL-инъекций?
SQL-инъекции можно предотвратить с помощью использования параметризованных запросов, подготовленных выражений, фильтрации и валидации ввода, использования ORM.

Что такое миграция базы данных и как её организовать?
Миграция — это управляемое изменение структуры БД. Реализуется с помощью специальных инструментов (например, Alembic, Flyway, Django Migrations) и скриптов, где описаны upgrade и downgrade.

Как интегрировать SQL с Python?
Интеграция осуществляется через драйверы (psycopg2, mysql-connector), ORM (SQLAlchemy, Django ORM) или пакеты pandas для работы с данными.

Что такое deadlock и как с ним бороться?
Deadlock — это ситуация, когда две или более транзакций ожидают освобождения ресурсов друг от друга. Боротьба: минимизация времени блокировок, соблюдение порядка обращения к данным, настройка timeout.

Что такое партиционированная таблица и как её создать?
Партиционированная таблица — это таблица, разделённая на несколько частей по определённому критерию. Создаётся с помощью PARTITION OF (в PostgreSQL) или PARTITION BY (в MySQL/Oracle).

Что такое MVCC и как он влияет на работу с транзакциями?
MVCC (Multiversion Concurrency Control) — это механизм управления параллелизмом, позволяющий читать данные без блокировок. Каждая транзакция видит свою версию данных, что увеличивает производительность и устойчивость системы.

Что такое индекс на функции и когда его стоит использовать?
Индекс на функции (functional index) — это индекс, созданный на основе выражения или функции над столбцом. Применяется, когда запросы часто фильтруют данные с использованием определённой функции, например: CREATE INDEX idx_lower_name ON users (LOWER(name));.

Как работает оператор EXCEPT в SQL и каков его аналог в других языках?
EXCEPT возвращает строки из первого запроса, которых нет во втором. Аналогичен операции "разность множеств". В Python это можно реализовать через set difference.

Что такое FULL OUTER JOIN и в каких случаях он применяется?
FULL OUTER JOIN возвращает все записи из обеих таблиц, объединяя их по условию. Если совпадений нет, поля одной из таблиц содержат NULL. Используется, когда важно не потерять данные ни из одной таблицы.

Что такое поддержка ACID в транзакциях и почему она важна?
ACID — это набор свойств (Атомарность, Согласованность, Изолированность, Долговечность), обеспечивающих надёжную обработку транзакций. Поддержка ACID гарантирует корректное выполнение транзакций даже при сбоях.

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

Что такое сериализуемый уровень изоляции транзакций?
Serializable — самый строгий уровень изоляции, исключающий все возможные конфликты между параллельными транзакциями. Гарантирует, что транзакции выполняются так, как будто они последовательны.

Как организовать автоматическое логирование изменений в таблице?
Это можно сделать с помощью триггеров, которые вызываются при INSERT, UPDATE или DELETE и записывают изменения в отдельную таблицу-лог.

Что такое материализованное представление и как его обновить?
Материализованное представление хранит результат запроса физически. Обновляется вручную (REFRESH MATERIALIZED VIEW) или автоматически по расписанию.

Что такое партиционирование по диапазону и приведите пример его использования?
Партиционирование по диапазону делит данные на части по заданным интервалам значений. Например, по датам: одна партиция — январь-март, другая — апрель-июнь и т.д.

Что такое шардинг баз данных и как он отличается от репликации?
Шардинг — это разделение базы данных на фрагменты, распределённые по разным серверам. Репликация — копирование данных с одного сервера на другой для отказоустойчивости и балансировки нагрузки.

Как проверить производительность запроса в PostgreSQL?
В PostgreSQL используется команда EXPLAIN ANALYZE, которая показывает план выполнения и реальное время исполнения запроса.

Что такое deadlock и как его избежать?
Deadlock — это ситуация, когда две или более транзакций ожидают освобождения ресурсов друг от друга. Чтобы избежать, следует соблюдать порядок обращения к данным, уменьшать длительность транзакций и использовать таймауты.

Какие типы соединений поддерживаются в SQL и какие из них самые эффективные?
INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, CROSS JOIN. INNER JOIN обычно самый эффективный, так как возвращает только совпадающие строки.

Что такое UNION ALL и чем он отличается от UNION?
UNION ALL объединяет результаты двух запросов без удаления дубликатов. UNION удаляет дубликаты, что требует дополнительных вычислений.

Что такое JSONB и чем он отличается от JSON в PostgreSQL?
JSONB хранит данные в двоичном формате, что обеспечивает более быстрое выполнение запросов. JSON хранится как текст и медленнее обрабатывается.

Что такое хранимая процедура и как её вызвать?
Хранимая процедура — это объект БД, содержащий SQL-код, который можно вызывать по имени. Вызывается с помощью CALL имя_процедуры(параметры);.

Как работает оператор INTERSECT и когда он используется?
INTERSECT возвращает пересечение двух наборов данных — те строки, которые есть и в первом, и во втором запросе. Используется для поиска общих записей.

Что такое оптимизация запроса и какие методы оптимизации существуют?
Оптимизация запроса — это процесс улучшения производительности SQL-запросов. Методы: использование индексов, переформулировка запроса, денормализация, проектирование правильных структур данных.

Что такое ORM и зачем она нужна?
ORM (Object-Relational Mapping) — это инструмент, позволяющий работать с данными в виде объектов, а не через SQL-запросы. Упрощает взаимодействие с БД и снижает риск SQL-инъекций.

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

Что такое миграция схемы БД и какие инструменты используются для этого?
Миграция схемы — это управляемое изменение структуры БД. Инструменты: Alembic, Flyway, Django Migrations, Liquibase.

Что такое MVCC и как он реализован в PostgreSQL?
MVCC (Multiversion Concurrency Control) — это механизм управления версиями данных, позволяющий разным транзакциям видеть разные состояния БД. В PostgreSQL реализован через системные столбцы xmin и xmax.

Как работает механизм VACUUM в PostgreSQL и зачем он нужен?
VACUUM очищает мёртвые версии строк и освобождает место для повторного использования. Необходим для поддержания высокой производительности и предотвращения роста размера БД.

Что такое WAL и как он влияет на работу СУБД?
WAL (Write-Ahead Logging) — это журнал операций, в который записываются изменения перед тем, как они будут применены к самой БД. Нужен для восстановления после сбоев и обеспечения целостности.

Что такое Sequence и как его использовать в SQL?
Sequence — это объект БД, генерирующий уникальные числа. Часто используется для автоинкремента. Создается командой CREATE SEQUENCE и вызывается через NEXTVAL() или CURRVAL().

Что такое индексированное представление и в каких СУБД оно используется?
Индексированное представление — это материализованное представление, для которого создаются индексы. Такой подход ускоряет выполнение запросов, основанных на этом представлении. Используется в Microsoft SQL Server. В других СУБД аналогичный эффект достигается через материализованные представления с индексами.

Какие типы триггеров существуют и чем они отличаются?
Существует два типа триггеров:

Что такое партиционирование по списку и приведите пример его использования?
Партиционирование по списку — это разделение таблицы на части по заданным значениям столбца. Например, можно разделить таблицу заказов по регионам: одна партиция — Москва, другая — Санкт-Петербург и т. д.

Как работает механизм двухфазной коммит-транзакции (2PC)?
2PC (Two-phase commit) — протокол согласованности распределённых транзакций. Состоит из двух фаз:

Что такое оптимизатор запросов и как он влияет на производительность?
Оптимизатор запросов — это компонент СУБД, который анализирует возможные пути выполнения запроса и выбирает наиболее эффективный. От его работы зависит скорость и нагрузка на сервер при обработке сложных SQL-запросов.

Что такое статистика таблицы и почему она важна для оптимизации?
Статистика содержит информацию о распределении данных в таблице (количество строк, уникальные значения, частота значений). Оптимизатор использует её для выбора наиболее эффективного плана выполнения запроса.

Что такое операция ANALYZE в PostgreSQL и когда её стоит использовать?
ANALYZE собирает статистику по данным в таблицах, чтобы помочь оптимизатору выбрать правильный план запроса. Её стоит выполнять после массовых изменений данных, например, после импорта.

Что такое индекс BRIN и в каких случаях он эффективен?
BRIN (Block Range Index) — это легковесный индекс, хранящий информацию о диапазонах значений в блоках данных. Эффективен для больших таблиц с упорядоченными данными, например, по дате или ID.

Как работают частичные индексы и в чём их преимущество?
Частичный индекс создается не на всей таблице, а только на подмножестве строк, соответствующему условию WHERE. Преимущество — меньший размер и более высокая скорость поиска.

Что такое оптимизация подзапросов и как она реализована в современных СУБД?
Оптимизация подзапросов — процесс преобразования вложенных запросов во внешние JOIN'ы или материализованные временные таблицы для повышения скорости выполнения. Современные СУБД автоматически применяют такие преобразования.

Что такое параллелизм в БД и какие механизмы его реализации вы знаете?
Параллелизм — это возможность одновременного доступа к данным несколькими пользователями. Реализуется через блокировки, MVCC, уровни изоляции транзакций и другие механизмы.

Какие ограничения накладывает использование JSON в индексах?
JSON-поля нельзя индексировать напрямую, но можно создать функциональный индекс на основе выражения, извлекающего значение из JSON. Также поддерживаются индексы GIN/GiST в PostgreSQL.

Что такое шаблон проектирования "звезда" и где он применяется?
Шаблон "звезда" — это схема модели данных, в которой центральная факт-таблица окружена измерениями. Применяется в системах бизнес-аналитики и хранилищах данных.

Что такое шаблон проектирования "снежинка" и как он связан со "звездой"?
"Снежинка" — это усложнённая версия "звезды", где измерения нормализуются и разбиваются на подчинённые таблицы. Позволяет экономить место, но может замедлить запросы.

Что такое OLAP и как он связан с SQL?
OLAP (Online Analytical Processing) — это технологии анализа данных, позволяющие строить многомерные отчёты. SQL предоставляет средства для реализации OLAP-запросов через CUBE, ROLLUP, GROUPING SETS.

Что такое индексированное представление и как его создать в SQL Server?
Индексированное представление — это представление, которое сохраняет свои данные в виде индекса. В SQL Server создается так: сначала создается обычное представление с WITH SCHEMABINDING, затем на него добавляется кластерный индекс.

Что такое оператор MERGE и в каких СУБД он поддерживается?
MERGE позволяет объединить логику INSERT, UPDATE и DELETE в одном запросе. Поддерживается в Oracle, SQL Server, PostgreSQL (как INSERT ON CONFLICT), MySQL (через специфику ON DUPLICATE KEY UPDATE).

Что такое оптимизация через переписывание запроса и приведите пример?
Это метод изменения структуры запроса для улучшения производительности. Например, замена подзапроса на JOIN или упрощение условия WHERE.

Что такое индексированный полнотекстовый поиск и как он реализован в SQL?
Полнотекстовый поиск позволяет находить слова в текстовых полях. Реализуется через специальные индексы (GIN/GiST в PostgreSQL, FULLTEXT в MySQL).

Что такое гиперлоглог и как он используется в анализе данных?
HyperLogLog — это алгоритм подсчёта уникальных элементов с использованием минимальной памяти. В SQL реализуется через функции вроде HLL (в PostgreSQL), особенно полезен в Big Data.

Что такое сканирование индекса и как оно отличается от сканирования таблицы?
Сканирование индекса — это чтение данных через индекс, что быстрее, чем сканирование всей таблицы (table scan), особенно при работе с большими объемами данных.

Что такое оператор OFFSET и в чём его недостаток при пагинации?
OFFSET используется для пропуска N записей при пагинации. Недостаток — снижение производительности при больших значениях OFFSET, так как СУБД всё равно обрабатывает пропущенные строки.

Что такое битовый индекс и в каких случаях он эффективен?
Битовый индекс хранит битовое значение для каждого уникального значения столбца. Эффективен для столбцов с малым количеством уникальных значений, например, пол, статус и т. д.

Что такое оптимизация через партиционирование и как она влияет на запросы?
При использовании партиционирования СУБД может игнорировать те части таблицы, которые не попадают в область фильтрации, что ускоряет выполнение запроса. Это называется partition pruning.

Что такое оператор EXPLAIN и как интерпретировать его вывод?
EXPLAIN показывает план выполнения запроса: какие индексы используются, какой порядок соединения таблиц, сколько строк ожидается обработать. Важно обращать внимание на типы соединений, стоимость и количество строк.