2269 подписчиков

Вопросы про SQL со * (сложные) на собеседовании тестировщика

18 минут

202 прочтения

8 сентября

Работа с базами данных - важная часть работы тестировщика, поэтому на собеседованиях почти всегда будут вопросы касающиеся SQL.

Простые вопросы мы разобрали вот в этой статье.

Настала очередь более сложных и интересных (некоторые вопросы нам уже встречались, но тут подразумевается более подробный ответ). Сначала вопросы (ответы будут ниже в статье):

1. Что такое индексы в SQL, и как они влияют на производительность запросов? Могут ли индексы замедлить работу?

2. Как написать запрос, который вернет третью по величине зарплату из таблицы сотрудников?

3. Что такое нормализация и денормализация? Приведите примеры, когда стоит применять каждую из них.

4. Как найти дубликаты в таблице и удалить их, оставив только одну запись?

5. Что такое оконные функции (window functions) в SQL? Приведите пример их использования.

6. Что такое транзакции в SQL, и как они работают? Какие уровни изоляции транзакций вы знаете?

7. Как оптимизировать медленный SQL-запрос?

8. Как работает операция UNION и чем отличается от UNION ALL? В каких случаях предпочтительнее использовать UNION ALL?

9. Что такое хранимая процедура в SQL? В чем её преимущества и недостатки? Как создать хранимую процедуру и передать в нее параметры?

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

1. Что такое индексы в SQL, и как они влияют на производительность запросов? Могут ли индексы замедлить работу?

Ответ:

Индекс в SQL — это объект базы данных, который создается для ускорения поиска и извлечения данных из таблицы.

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

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

Пример создания индекса:

Этот индекс ускорит выполнение запросов, которые используют столбец column_name в условиях поиска или сортировки.

Индексы ускоряют выполнение запросов, делая поиск данных более эффективным, особенно для больших таблиц. Однако они могут замедлить операции INSERT, UPDATE и DELETE, так как индексы нужно обновлять при изменении данных.

Также слишком большое количество индексов может замедлить работу базы данных из-за увеличения объема данных, которые нужно поддерживать.

2. Как написать запрос, который вернет третью по величине зарплату из таблицы сотрудников?

Этот вопрос проверяет ваше умение работать с оконными функциями и подзапросами.

Способ 1: Использование оконной функции

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

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

Пояснение:

Внутренний запрос использует оконную функцию DENSE_RANK(), которая присваивает каждой зарплате ранг, отсортированный по убыванию (самая высокая зарплата получает ранг 1). Если несколько сотрудников имеют одинаковую зарплату, они получат одинаковый ранг.
Внешний запрос возвращает строки, где ранг равен 3, что соответствует третьей по величине зарплате.

Способ 2: Использование подзапроса (без оконной функции)

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

Пояснение:

Вложенные подзапросы последовательно находят зарплату, которая меньше максимальной.
В первом подзапросе извлекается максимальная зарплата.
Во втором подзапросе — максимальная зарплата, которая меньше максимальной.
Внешний запрос находит максимальную зарплату, которая меньше второй максимальной, что и есть третья по величине зарплата.

Примечание:

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

Возможно вас попросят сделать так, чтобы возвращалась только 1 строка (даже если несколько сотрудников (у нас тут небольшая дискуссия в комментариях)), тогда можно сделать вот так:

Способ 1new: Использование оконной функции (возвращаем только одну строку)

Чтобы гарантировать, что будет возвращена только одна строка даже при наличии нескольких сотрудников с одинаковой третьей по величине зарплатой, можно использовать функцию ROW_NUMBER() вместо DENSE_RANK(). Это обеспечит уникальные номера строк.

Пояснение:

ROW_NUMBER() присваивает уникальный номер каждой строке, отсортированной по убыванию зарплаты.
Внешний запрос выбирает строку с номером 3 (третья по величине зарплата). Поскольку ROW_NUMBER() гарантирует уникальность строк, будет возвращена только одна строка.

Способ 2new: Использование подзапроса (возвращаем только одну строку)

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

Пояснение:

Мы сортируем зарплаты по убыванию.
Используя конструкцию LIMIT 1 OFFSET 2, мы пропускаем две первые (наибольшие) зарплаты и возвращаем только одну строку — третью по величине зарплату.

Этот запрос возвращает ровно одну строку с третьей по величине зарплатой.

Примечание:

Подход через LIMIT и OFFSET работает в таких СУБД, как PostgreSQL, MySQL и SQLite. Для других баз данных (например, Oracle или SQL Server) могут потребоваться другие конструкции, такие как FETCH FIRST или TOP.

3. Что такое нормализация и денормализация? Приведите примеры, когда стоит применять каждую из них.

Вопрос проверяет знание теории баз данных.

Ответ:

Нормализация: процесс структурирования данных для минимизации избыточности и зависимости. Пример: разделение таблицы с информацией о клиентах и заказах на две таблицы — Customers и Orders, чтобы избежать дублирования данных о клиентах.
Денормализация: процесс объединения таблиц для повышения производительности. Пример: объединение таблиц Customers и Orders в одну таблицу, чтобы уменьшить количество соединений в запросах и ускорить чтение данных.

4. Как найти дубликаты в таблице и удалить их, оставив только одну запись?

Пример: Найти дубликаты по столбцу email:

Удалить дубликаты, оставив одну запись:

Другое решение (если интервьюер душнит), без использования not in и min():

5. Что такое оконные функции (window functions) в SQL? Приведите пример их использования.

Ответ:

Оконные функции (window functions) в SQL позволяют выполнять вычисления по набору строк, которые связаны с текущей строкой, без необходимости группировки данных. Это отличает их от агрегатных функций, которые сводят результат к одной строке на группу.

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

Оконные функции включают такие функции, как:

ROW_NUMBER()
RANK()
DENSE_RANK()
SUM()
AVG()
LAG(), LEAD()
и другие.

Подробнее об оконных функциях читайте здесь.

Основные компоненты оконных функций:

1. OVER() — указывает, что это оконная функция и задает окно строк, по которым будет производиться вычисление.

2. PARTITION BY — разбивает строки на группы (необязательно).

3. ORDER BY — определяет порядок строк в окне (необязательно).

Пример использования оконной функции:

Предположим, у нас есть таблица sales с данными о продажах:

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

Результат:

Пояснение:

Функция SUM(sale_amount) с использованием OVER и PARTITION BY employee_id суммирует продажи для каждого сотрудника.
Ключевое слово ORDER BY sale_date определяет порядок, в котором будут накапливаться суммы.

6. Что такое транзакции в SQL, и как они работают? Какие уровни изоляции транзакций вы знаете?

Ответ:

Транзакция — это последовательность операций, которые выполняются как единое целое. Транзакции обеспечивают целостность данных.

Основные принципы транзакций известны как ACID:

Atomicity (атомарность) — все операции внутри транзакции либо выполняются полностью, либо не выполняются вовсе.
Consistency (согласованность) — данные переходят из одного согласованного состояния в другое.
Isolation (изоляция) — транзакции не должны мешать друг другу.
Durability (долговечность) — после завершения транзакции её результаты сохраняются навсегда.

Уровни изоляции:

Read Uncommitted: позволяет читать данные, которые еще не были зафиксированы.
Read Committed: гарантирует, что транзакция не будет читать данные, которые не были зафиксированы.
Repeatable Read: гарантирует, что данные, прочитанные в начале транзакции, останутся неизменными.
Serializable: самый высокий уровень изоляции, гарантирует полную изоляцию транзакций.

7. Как оптимизировать медленный SQL-запрос?

Краткий ответ:

Использование индексов: Создайте индексы на столбцах, которые часто участвуют в поисковых запросах или соединениях.
Избегайте SELECT *: Выбирайте только те столбцы, которые вам нужны.
Использование JOIN вместо подзапросов (в некоторых случаях).
Анализ выполнения запроса с помощью EXPLAIN или EXPLAIN ANALYZE.
Разбиение таблиц (sharding) и кэширование данных.
Избегайте сложных функций в условиях WHERE.
Использование табличных inline функций
Использование APPLY при работе с табличными функциями

Подробнее:

Медленные запросы могут быть вызваны различными факторами, и при оптимизации важно учитывать несколько аспектов. Вот пошаговое руководство по оптимизации медленных SQL-запросов:

1. Проверьте индексы

Использование индексов: Убедитесь, что на столбцах, которые используются в WHERE, JOIN, GROUP BY, ORDER BY или в условиях фильтрации, созданы индексы.
Тип индексов: Используйте правильные типы индексов (например, B-tree, хеш-индексы). Для уникальных столбцов используйте уникальные индексы.
Комбинированные индексы: Если запрос использует несколько столбцов, создайте составной (композитный) индекс, включающий все необходимые столбцы.

2. Используйте план выполнения запроса (EXPLAIN)

Выполните команду EXPLAIN для вашего запроса, чтобы увидеть, как база данных выполняет его. Это поможет понять, какие индексы используются, сколько строк сканируется и где могут быть узкие места.
Обратите внимание на такие индикаторы, как полные сканирования таблиц (Full Table Scan) — это обычно сигнал о том, что индексы не используются должным образом.

3. Ограничьте количество возвращаемых данных

Фильтрация: Попробуйте минимизировать количество строк, возвращаемых запросом, добавляя более специфичные условия WHERE.
Пагинация: Если вы работаете с большим количеством данных, используйте пагинацию (LIMIT и OFFSET), чтобы загружать данные частями.

4. Оптимизируйте JOIN-операции

Индексы на столбцах соединений: Убедитесь, что столбцы, по которым происходит соединение (например, в JOIN), индексированы.
Уменьшение количества соединений: Проверьте, нужно ли вам соединять все таблицы в запросе. Иногда можно предварительно выполнить обработку данных в подзапросах или временных таблицах.

5. Оптимизация подзапросов и IN

Замена подзапросов на JOIN: Если у вас есть подзапрос в WHERE или SELECT, попробуйте переписать его как JOIN. Это может значительно ускорить запрос.
Использование EXISTS вместо IN: В некоторых случаях использование EXISTS может быть более эффективным, чем IN, особенно при работе с большими подзапросами.

6. Оптимизация агрегатов и группировок

Индексы на столбцах группировки: Если вы используете операторы агрегации (COUNT, SUM, AVG) или GROUP BY, убедитесь, что на колонках, которые участвуют в группировке, есть индексы.
Использование HAVING только при необходимости: HAVING выполняется после агрегации данных, поэтому его использование может замедлять запрос. Если возможно, переместите условия в WHERE.

7. Разделение сложных запросов

Если ваш запрос очень сложный (много JOIN, подзапросов и т.д.), попробуйте разбить его на несколько более простых SQL-запросов и сохранить промежуточные результаты в временных таблицах. Это может снизить нагрузку на базу данных.

8. Используйте кэширование

Если запрос выполняется часто, подумайте о кэшировании результатов на уровне приложения или базы данных (если это поддерживается, например, Redis).
Некоторые СУБД поддерживают внутреннее кэширование запросов (например, MySQL Query Cache), но это следует использовать с осторожностью.

9. Оптимизация ORDER BY

Индексы на полях сортировки: Убедитесь, что сортируемые столбцы индексированы. Это поможет избежать дополнительных операций сортировки в памяти.
Ограничение сортируемых данных: Если запрос сортирует большое количество строк, попробуйте использовать LIMIT, чтобы сократить объем данных для сортировки.

10. Профилирование и мониторинг

Используйте инструменты профилирования, такие как SHOW PROFILE в MySQL, или внешние инструменты мониторинга для анализа длительности выполнения запросов.
Определите долгие операции, например, временные таблицы на диске, сортировки, сканирование таблиц.

11. Использование материалов или препроцессинг данных

Если определенные запросы выполняются очень часто и значения редко изменяются, подумайте о том, чтобы хранить результаты в отдельной таблице (материализованное представление) и обновлять её периодически.

12. Проверка настроек базы данных

Проверьте параметры конфигурации вашей базы данных, такие как объем выделенной оперативной памяти для кэша индексов или буферов запросов. Например, innodb_buffer_pool_size в MySQL может существенно влиять на производительность при работе с большими таблицами.

13. Оптимизация работы с табличными функциями

Использование оператора APPLY при работе с табличными функциями
Использование встроенныx (Inline) табличные функций вместо многооператорных табличных функций

Оптимизация SQL-запросов — это процесс, требующий анализа, тестирования и экспериментов. Используйте предложенные шаги и инструменты, чтобы улучшить производительность ваших запросов. Не забывайте, что каждая база данных имеет свои особенности, поэтому подходы к оптимизации могут немного различаться в зависимости от выбранной СУБД.

8. Как работает операция UNION и чем отличается от UNION ALL? В каких случаях предпочтительнее использовать UNION ALL?

Ответ:

UNION: объединяет результаты двух запросов, при этом удаляет дубликаты.
UNION ALL: объединяет результаты двух запросов, но не удаляет дубликаты, что делает его быстрее. Используйте UNION ALL, если вы уверены, что у вас нет дубликатов или если вам нужны все строки, включая дубликаты, для повышения производительности.

9. Что такое хранимая процедура в SQL? В чем её преимущества и недостатки? Как создать хранимую процедуру и передать в нее параметры?

Ответ:

Хранимая процедура — это заранее сохраненный набор SQL-инструкций, который можно многократно вызывать по имени. Она может принимать входные параметры и возвращать выходные значения. Хранимые процедуры обычно используются для выполнения часто повторяющихся операций, таких как вставка, обновление, удаление или сложные вычислительные процессы.

Преимущества хранимых процедур:

Повышение производительности: Хранимые процедуры компилируются и кэшируются на сервере, что снижает накладные расходы на интерпретацию SQL-запросов.
Повторное использование: Один раз написанную процедуру можно вызывать многократно.
Повышение безопасности: Можно ограничить прямой доступ к данным и разрешить только выполнение хранимых процедур, что снижает риски SQL-инъекций.
Упрощение логики: Комплексную бизнес-логику можно инкапсулировать в процедуру и скрыть от пользователей.

Недостатки хранимых процедур:

Портативность: Хранимые процедуры специфичны для СУБД (например, T-SQL для SQL Server, PL/pgSQL для PostgreSQL), что усложняет переносимость между различными базами данных.
Сложность поддержки: Изменение хранимой процедуры требует её перекомпиляции, что может быть неудобно в больших системах.
Отладка: Процесс отладки хранимых процедур сложнее, чем отладка обычных SQL-запросов.

Пример создания хранимой процедуры:

Вызов хранимой процедуры:

Пример процедуры с выходным параметром:

Вызов процедуры с выходным параметром:

Как создать хранимую процедуру в разных СУБД читайте здесь: Хранимая процедура в SQL? В чем её преимущества и недостатки? Как создать хранимую процедуру и передать в нее параметры?

Дополнительные вопросы по хранимым процедурам:

1. Чем отличаются хранимые процедуры и функции в SQL?

Хранимая процедура может выполнять операции INSERT, UPDATE, DELETE и возвращать несколько наборов данных. Функция же должна возвращать значение и не может изменять данные напрямую.

2. Как обрабатывать ошибки в хранимых процедурах?

Для обработки ошибок можно использовать конструкции вроде BEGIN TRY...END TRY...BEGIN CATCH...END CATCH в T-SQL (SQL Server) или EXCEPTION в PL/pgSQL (PostgreSQL).

3. Как передавать и использовать курсоры в хранимых процедурах?

Более подробно вопрос рассмотрен в статье по ссылке.

В реляционных базах данных курсор используется для поэтапного обхода набора строк, возвращаемого запросом. В хранимых процедурах курсоры могут быть полезны для последовательной обработки каждой строки результата запроса, когда невозможно или нецелесообразно обработать их все одновременно.

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

Ответ:

Функция в SQL — это объект базы данных, который выполняет вычисление и возвращает одно значение (скалярная функция) или таблицу (табличная функция). Функции могут принимать входные параметры и должны возвращать результат. Они обычно используются для выполнения вычислений, форматирования данных или работы с агрегированными значениями.

Отличия функций от хранимых процедур:

1. Возвращаемое значение:

Функция всегда возвращает значение (скалярное или набор строк).
Хранимая процедура может возвращать значения через выходные параметры, но не обязана возвращать результат.

2. Использование в запросах:

Функции можно использовать в SELECT, WHERE, JOIN и других местах запроса.
Хранимые процедуры не могут быть использованы в таких контекстах.

3. Операции с данными:

Функции обычно не могут изменять данные (выполнять INSERT, UPDATE, DELETE), за исключением специальных случаев (например, с использованием функций с побочными эффектами в некоторых СУБД).
Хранимые процедуры могут выполнять любые операции с данными.

4. Обработка ошибок:

В функциях нельзя использовать конструкции TRY...CATCH (в T-SQL) или EXCEPTION (в PL/pgSQL) для обработки ошибок, тогда как в хранимых процедурах это возможно.

Пример создания скалярной функции:

Функция для вычисления налога на зарплату:

Пример использования функции в запросе:

Пример создания табличной функции:

Функция, возвращающая всех сотрудников из определенного отдела:

Пример использования табличной функции:

Типы табличных функций:

1. Inline табличные функции (встроенные):

Возвращают результат сразу же, без необходимости выполнения дополнительных операций в теле функции.
Функция представляет собой один SQL-запрос, и оптимизатор запросов может эффективно встраивать её в основной запрос.
Преимущество производительности: Inline-функции могут быть значительно быстрее, т.к. они работают как обычные запросы, а не как подпрограммы.

Пример inline табличной функции:

Пример использования:

2. Многооператорные табличные функции:

Содержат несколько операторов SQL внутри тела функции (например, могут включать временные таблицы, циклы и т.д.).
Они выполняются как подпрограммы и могут быть медленнее по сравнению с inline-функциями.

Пример многооператорной табличной функции:

Пример использования:

Использование оператора APPLY при работе с табличными функциями:

Оператор APPLY (CROSS APPLY и OUTER APPLY) используется для применения табличной функции к каждой строке исходного набора данных. Это особенно полезно, когда нужно вызвать табличную функцию для каждой строки из основного запроса и получить результаты, зависящие от значений этой строки.

CROSS APPLY: возвращает только те строки, для которых функция возвращает результат.
OUTER APPLY: возвращает все строки из основного набора данных, даже если функция не возвращает результата (в этом случае результат функции будет NULL).

Пример использования CROSS APPLY:

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

Дополнительные вопросы по функциям:

1. Какие типы функций существуют в SQL?

Системные функции (например, COUNT(), SUM(), GETDATE()).
Пользовательские функции (скалярные и табличные функции).

2. В чем разница между скалярной и табличной функцией?

Скалярная функция возвращает одно значение (например, число, строку, дату).
Табличная функция возвращает набор строк (например, аналог таблицы).

3. Можно ли использовать функции в WHERE или JOIN?

Да, функции можно использовать в различных частях SQL-запросов, включая SELECT, WHERE, JOIN, и это одно из ключевых преимуществ функций перед хранимыми процедурами.

4. Как функции могут влиять на производительность?

Скалярные функции могут замедлять выполнение запросов, особенно если используются в больших наборах данных или включают сложные вычисления. Табличные функции могут быть более производительными, но их также следует использовать с осторожностью.

5. Можно ли изменять данные внутри функции?

В большинстве СУБД функции не могут напрямую изменять данные (выполнять INSERT, UPDATE, DELETE), за исключением специальных случаев (например, с использованием так называемых функций с побочными эффектами в некоторых СУБД).

Пример практического задания с использованием функции:

Задание:

Создайте функцию, которая принимает два параметра — минимальный и максимальный возраст, и возвращает всех сотрудников, чей возраст находится в этом диапазоне.

Решение: