Перевод статьи «25 Advanced SQL Interview Q&As with Code Snippets and Expected Output».
За более чем десять лет работы у меня было немало собеседований по SQL. Они могут быть достаточно сложными, особенно когда речь идёт о продвинутых концепциях и запутанных сценариях. Чтобы помочь вам подготовиться, я составил список из 25 вопросов по SQL с фрагментами кода и ожидаемыми результатами.
Друзья, поддержите нас вступлением в наш телеграм канал QaRocks. Там много туториалов, задач по автоматизации и книг по QA.
1. В чём разница между операторами HAVING и WHERE
Оператор HAVING используется в связке с GROUP BY, чтобы фильтровать строки на основе агрегатных функций. В свою очередь, оператор WHERE фильтрует строки перед группировкой. Проще говоря, WHERE используется для сужения поиска на уровне строк, а HAVING выбирает конкретные результаты после группировки.
-- Example SELECT department, COUNT(*) AS total_employees FROM employees GROUP BY department HAVING total_employees > 10;
2. Объясните разницу между UNION и UNION ALL
Оператор UNION используется для объединения результатов двух или более операторов SELECT и удаляет дублирующиеся строки. С другой стороны, UNION ALL объединяет наборы результатов без удаления дубликатов. Поскольку UNION ALL не осуществляет дополнительный шаг по удалению дубликатов, он обычно быстрее. Однако если необходимо удалить дубликаты, то следует использовать UNION.
-- Example SELECT employee_id FROM employees UNION SELECT employee_id FROM former_employees; -- Example with UNION ALL SELECT employee_id FROM employees UNION ALL SELECT employee_id FROM former_employees;
3. Как найти зарплату сотрудника, которая является n-й по величине в таблице
Чтобы найти n-ю по величине зарплату, можно использовать конструкцию LIMIT и упорядочить зарплаты в порядке убывания. Предположим, мы хотим найти третью по величине зарплату:
-- Example SELECT salary FROM employees ORDER BY salary DESC LIMIT 2, 1;
В приведённом выше примере мы пропускаем первые две зарплаты (LIMIT 2) и извлекаем следующую строку (LIMIT 1), которая соответствует третьей по величине зарплате.
4. Объясните концепцию подзапросов в SQL
Подзапрос SQL — это запрос, вложенный в другой запрос. Он может использоваться внутри операторов WHERE, FROM или HAVING. Результат подзапроса используется в качестве условия или источника данных для внешнего запроса (оператора, который содержит подзапрос). Подзапросы полезны для выполнения сложных запросов или доступа к данным из нескольких таблиц.
-- Example SELECT employee_name FROM employees WHERE department_id IN ( SELECT department_id FROM departments WHERE region = 'North America' );
В этом примере внутренний запрос извлекает идентификаторы отделов в регионе «Северная Америка». Затем внешний запрос выбирает имена сотрудников из этих отделов.
5. Как найти второе по величине значение в столбце
Для этого можно использовать комбинацию операторов ORDER BY и LIMIT, как показано в примере:
-- Example SELECT MAX(salary) AS second_highest_salary FROM employees WHERE salary < ( SELECT MAX(salary) FROM employees );
Здесь мы сравниваем столбец зарплаты с максимальным значением зарплаты в таблице employees. Исключив самую высокую зарплату с помощью WHERE, мы легко находим вторую по величине зарплату.
6. Объясните функцию COALESCE в SQL
Функция COALESCE используется для возврата первого значения, отличного от null, из списка выражений. Она принимает несколько аргументов и возвращает первое значение, содержащее какие-либо данные. Если все значения равны null, то возвращается null. COALESCE особенно полезна при работе со столбцами, в которых могут отсутствовать данные.
-- Example SELECT COALESCE(salary, 0) AS final_salary FROM employees;
В этом примере, если столбец зарплаты содержит значение null, функция COALESCE заменяет его на 0, в результате чего значение final_salary будет содержать данные.
7. Как найти наиболее часто встречающееся значение в столбце
Чтобы найти наиболее часто встречающееся значение в столбце, можно использовать операторы GROUP BY, COUNT и ORDER BY. Например:
-- Example SELECT column_name FROM table_name GROUP BY column_name ORDER BY COUNT(*) DESC LIMIT 1;
В этом примере мы группируем строки по интересующему нас столбцу, подсчитываем количество появлений каждого значения с помощью COUNT(*), упорядочиваем результаты по убыванию и извлекаем первую строку с помощью LIMIT 1.
8. Объясните оператор LIKE и его использование
Оператор LIKE используется в WHERE для поиска заданного шаблона в столбце. Он позволяет сопоставить шаблоны с использованием символов подстановки. Символ % демонстрирует любую последовательность из нуля или более символов, а символ _ представляет одиночный символ.
-- Example SELECT column_name FROM table_name WHERE column_name LIKE 'A%';
Здесь мы выбираем строки, в которых имя столбца начинается с «A». Подстановочный знак % соответствует любой последовательности символов после «A».
9. Для чего используется оператор CASE в SQL
Оператор CASE нужен для выполнения условной логики в SQL-запросах. Он позволяет определить различные результаты на основе заданных условий. Существует два формата оператора CASE — простой и поисковой.
Простое CASE-выражение сравнивает выражение с набором значений и возвращает результат на основе первого совпадения:
-- Example
SELECT column_name,
CASE column_name
WHEN value1 THEN 'Result1'
WHEN value2 THEN 'Result2'
ELSE 'Result3'
END
FROM table_name;
Поисковое CASE-выражение использует логические условия для определения результата:
-- Example
SELECT column_name,
CASE
WHEN condition1 THEN 'Result1'
WHEN condition2 THEN 'Result2'
ELSE 'Result3'
END
FROM table_name;
10. Объясните различные типы оператора JOIN в SQL
В SQL существует несколько типов оператора JOIN, которые позволяют объединять строки из нескольких таблиц на основе связанного столбца:
- INNER JOIN возвращает только совпадающие строки из обеих таблиц
- LEFT JOIN возвращает все строки из левой таблицы и совпадающие строки из правой таблицы
- RIGHT JOIN возвращает все строки из правой таблицы и совпадающие строки из левой таблицы
- FULL OUTER JOIN возвращает все строки из обеих таблиц, объединяя результаты левой и правой таблиц
-- Example SELECT * FROM employees INNER JOIN departments ON employees.department_id = departments.department_id;
В приведённом примере мы объединяем таблицы employees и departments на основе столбца department_id с помощью INNER JOIN.
11. Как найти дублирующиеся строки в таблице
Для поиска дублирующихся строк в таблице можно использовать операторы GROUP BY и HAVING вместе с агрегатной функцией COUNT:
-- Example SELECT column1, column2, COUNT(*) FROM table_name GROUP BY column1, column2 HAVING COUNT(*) > 1;
В этом примере мы группируем строки по указанным столбцам, подсчитываем количество вхождений каждой группы и фильтруем результат, чтобы включить только группы с количеством больше 1, что указывает на наличие дубликатов.
12. Зачем в SQL используются индексы?
INDEX — это структура базы данных, которая повышает скорость поиска данных в таблицах. Он создаётся для одного или нескольких столбцов таблицы и содержит отсортированную копию данных с указателем на исходные строки таблицы.
Индексы ускоряют выполнение запросов, позволяя быстрее находить нужные данные. Однако есть и недостаток: они требуют дополнительного пространства на диске и могут замедлять операции модификации данных (такие как вставка, обновление и удаление).
-- Example of creating an index CREATE INDEX index_name ON table_name (column1, column2);
Здесь мы создаём индекс с именем index_name для column1 и column2 в таблице table_name.
13. Объясните разницу между первичным и внешним ключом
Первичный ключ — это столбец или комбинация столбцов, которые уникально идентифицируют каждую строку в таблице. Он обеспечивает целостность и уникальность данных в пределах таблицы. У каждой таблицы может быть только один первичный ключ.
Внешний ключ — это столбец или комбинация столбцов, которые ссылаются на первичный ключ другой таблицы. Он устанавливает связь между двумя таблицами и обеспечивает ссылочную целостность. Таблица может иметь несколько внешних ключей, указывающих на её связь с разными таблицами.
14. Как вычислить среднее значение с помощью SQL
Чтобы вычислить среднее значение по столбцу, можно использовать функцию AVG. Она принимает столбец в качестве входных данных и возвращает среднее значение.
-- Example SELECT AVG(salary) AS average_salary FROM employees;
В этом примере мы вычисляем среднюю зарплату из таблицы employees и присваиваем имя average_salary столбцу результата.
15. Для чего используется оператор GROUP BY
Оператор GROUP BY нужен для группировки строк на основе одного или нескольких столбцов. Он часто используется с агрегатными функциями, такими как COUNT, SUM, AVG и т. д. При использовании GROUP BY результат разделяется на группы, основанные на указанном столбце, и к каждой группе применяются агрегатные функции.
-- Example SELECT department, COUNT(*) AS total_employees FROM employees GROUP BY department;
Здесь мы группируем сотрудников по отделам и вычисляем общее количество сотрудников в каждом отделе с помощью функции COUNT.
16. Как получить текущую дату и время в SQL
Большинство систем баз данных предоставляют функции для получения текущей даты и времени. Вот несколько примеров:
- MySQL:
SELECT CURDATE(), CURTIME(); - PostgreSQL:
SELECT CURRENT_DATE, CURRENT_TIME; - SQL Server:
SELECT GETDATE(); - Oracle:
SELECT SYSDATE FROM DUAL;
Эти функции возвращают текущую дату и время согласно внутреннему времени системы баз данных.
17. Объясните разницу между представлением и таблицей
Таблица — это основная структура в базе данных, которая хранит данные в строках и столбцах. Она используется для организации и хранения данных в табличном формате.
Представление — это виртуальная таблица, полученная из одной или нескольких таблиц. Она не хранит данные, но определяется запросом. Представления позволяют упростить сложные запросы, скрыть конфиденциальную информацию и представить подмножество данных пользователям, не давая им прямого доступа к основным таблицам.
18. Как удалить дублирующиеся строки из таблицы
Для этого можно использовать оператор DELETE с подзапросом, который идентифицирует дублирующиеся строки. Например:
-- Example DELETE FROM table_name WHERE (column1, column2) IN ( SELECT column1, column2 FROM table_name GROUP BY column1, column2 HAVING COUNT(*) > 1 );
В приведённом примере подзапрос определяет дублирующиеся строки на основе указанных столбцов, а оператор DELETE удаляет их из таблицы.
19. Для чего используется оператор TRUNCATE
Оператор TRUNCATE применяется для удаления всех строк из таблицы, что фактически означает удаление всех данных. В отличие от оператора DELETE, который удаляет строки по одной, TRUNCATE является более быстрой операцией, поскольку освобождает пространство хранения, занимаемое таблицей и связанными с ней объектами.
Однако нужно быть осторожным с использованием TRUNCATE, так как его нельзя откатить, к тому же он сбрасывает значения автоинкремента (автогенерацию первичного ключа) любых столбцов в таблице.
-- Example TRUNCATE TABLE table_name;
В этом примере удаляются все строки из указанной таблицы.
20. Как узнать длину строки с помощью SQL
Метод определения длины строки зависит от системы баз данных. Вот несколько примеров:
- MySQL:
SELECT LENGTH(string); - PostgreSQL:
SELECT LENGTH(string); - SQL Server:
SELECT LEN(string); - Oracle:
SELECT LENGTH(string) FROM DUAL;
В этих примерах функции LENGTH или LEN возвращают количество символов в заданной строке.
21. Объясните концепцию свойств ACID в транзакциях базы данных
ACID расшифровывается как атомарность, согласованность, изоляция и устойчивость. Эти свойства обеспечивают надёжность, целостность и последовательность транзакций баз данных.
- Атомарность. Транзакция рассматривается как единое целое и либо выполняется полностью, либо не завершается вообще. Если какая-либо часть транзакции выполняется неудачно, вся транзакция отменяется, оставляя базу данных в исходном состоянии.
- Согласованность. Транзакция переводит базу данных из одного согласованного состояния в другое. Это гарантирует, что ограничения целостности данных не будут нарушены во время транзакции.
- Изоляция. Транзакции выполняются отдельно друг от друга. Изменения, внесённые одной транзакцией, не видны другим транзакциям до тех пор, пока они не будут зафиксированы.
- Устойчивость. Как только транзакция зафиксирована, её изменения становятся постоянными и сохраняются после любых дальнейших сбоев, таких как сбои питания или системы.
22. Как можно объединить строки в SQL
Для этого можно использовать оператор конкатенации строк, который зависит от системы баз данных:
- MySQL:
SELECT CONCAT(string1, string2); - PostgreSQL:
SELECT string1 || string2; - SQL Server:
SELECT string1 + string2; - Oracle:
SELECT string1 || string2 FROM DUAL;
В этих примерах оператор или функция объединяет указанные строки в одну конкатенированную строку.
23. Для чего используется оператор ROLLBACK
Оператор ROLLBACK применяется для отмены изменений, внесённых в транзакцию, и восстановления базы данных в состояние до начала транзакции. Обычно он используется при возникновении ошибки или когда транзакцию необходимо отменить.
-- Example BEGIN TRANSACTION; -- Perform operations IF condition THEN ROLLBACK; ELSE COMMIT; END IF;
В этом примере, если выполняется определённое условие, транзакция откатывается с помощью оператора ROLLBACK. В противном случае она фиксируется с помощью оператора COMMIT.
24. Как узнать разницу между двумя датами в SQL
Чтобы найти разницу между двумя датами в SQL, можно использовать функции разности дат, предоставляемые системой базы данных. Вот пример использования функции DATEDIFF:
-- Example SELECT DATEDIFF(day, start_date, end_date) AS date_difference FROM table_name;
Здесь функция DATEDIFF вычисляет количество дней между столбцами start_date и end_date и возвращает результат в виде date_difference.
25. Как можно выполнить пагинацию в SQL
Пагинация используется для ограничения количества строк, возвращаемых запросом, и часто применяется для отображения данных в виде фрагментов. Разные системы баз данных используют разный синтаксис для пагинации. Вот пример с использованием операторов LIMIT и OFFSET:
-- Example SELECT column1, column2 FROM table_name LIMIT 10 OFFSET 20;
В этом примере мы выбираем 10 строк из таблицы, начиная с 21-й строки (OFFSET 20). Это позволяет получить третью страницу данных с 10-ю строками на странице.
Заключение
Помните, что практика — ключ к освоению SQL, поэтому не стесняйтесь экспериментировать с фрагментами кода и исследовать дополнительные возможности.