Самые популярные вопросы на собеседованиях по SQL

🔥 Важное для QA-специалистов! 🔥
В QaRocks ты найдешь туториалы, задачи и полезные книги, которых нет в открытом доступе. Уже более 15.000 подписчиков – будь среди нас! Заходи к нам в телеграм канал QaRocks 

Вопрос 1.

В чем разница между TRUNCATE, DROP и DELETE?

Оператор DROP используется для удаления любых объектов базы данных, таких как таблицы, представления, функции, процедуры, триггеры и т. д.
DELETE — это оператор DML (Data Manipulation Language), поэтому для сохранения изменений в базе данных после его выполнения необходимо сделать COMMIT. В то время как TRUNCATE и DROP — это операторы DDL (Data Definition Language), поэтому коммит не требуется.

Например, следующая команда удалит только те записи из таблицы employee, где имя — “Tanya”:

DELETE FROM employee WHERE name = ‘Tanya’;
COMMIT;

Эта команда удалит все записи из таблицы employee:

DELETE FROM employee;
COMMIT;

А следующий оператор также удалит все записи из таблицы employee, но в данном случае коммит не нужен:

TRUNCATE TABLE employee;

Читайте также: 5 обязательных книг по SQL

Вопрос 2.

Разница между оконными функциями RANK, DENSE_RANK и ROW_NUMBER

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

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

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

Пример: Запрос по таблице managers, в которой содержатся зарплаты пяти менеджеров

SELECT *
, RANK() OVER(ORDER BY salary DESC) AS ranks
, DENSE_RANK() OVER(ORDER BY salary DESC) AS dense_ranks
, ROW_NUMBER() OVER(ORDER BY salary DESC) AS row_numbers
FROM managers;

Получаем три варианта нумерации на основе зарплаты по убыванию.

Вопрос 3.

Разница между уникальными ключами (UNIQUE), первичными ключами (PRIMARY KEY) и внешними ключами (FOREIGN KEY).
PRIMARY KEY, UNIQUE и FOREIGN KEY — это ограничения, которые можно задавать в таблицах баз данных.

Если задать столбец в таблице как первичный ключ (PRIMARY KEY), этот столбец всегда будет содержать только уникальные значения. Дубликаты и значение NULL в этом столбце не допускаются. В таблице может быть только один первичный ключ, но он может включать один или сразу несколько столбцов.

Если задать столбец в таблице как уникальный ключ (UNIQUE), этот столбец всегда будет содержать только уникальные значения. Дубликаты не допускаются, но разрешены значения NULL. В этом заключается основное различие между первичным и уникальным ключом.

Внешний ключ (FOREIGN) используется для создания связи «родитель-дочерняя таблица» между двумя таблицами. Если столбец в таблице объявлен внешним ключом, его значения должны ссылаться на значения другого столбца в другой таблице.

Вопрос 4.

Разница между WHERE и HAVING.

WHERE используется для фильтрации строк из таблицы. Также в WHERE можно указать условия объединения двух таблиц. Если в SQL-запросе есть и WHERE, и GROUP BY, то сначала записи будут отфильтрованы по условиям WHERE, а затем уже сгруппированы согласно GROUP BY.
Условия WHERE применяются к отдельным строкам таблицы.

HAVING используется для фильтрации уже сгруппированных данных (результата функции GROUP BY). Таким образом, если в SQL-запросе есть операторы WHERE, GROUP BY и HAVING, то сначала данные фильтруются по условию WHERE, после этого происходит группировка данных (GROUP BY), и только после этого HAVING применяет фильтрацию к агрегированным результатам.

Вопрос 5.

Разница между PARTITION BY и GROUP BY.

GROUP BY используется вместе с агрегатными функциями для группировки строк по одному или нескольким столбцам.

Обычно эта функция используется в запросах, где необходимо выполнить агрегированные вычисления (например, SUM, COUNT, AVG и т. д.) для групп строк, имеющих общие значения в указанных столбцах.

GROUP BY применяется до SELECT:

SELECT department, AVG(salary) AS avg_department_salary
FROM employees
GROUP BY department;

Вывод:

| department | avg_department_salary |
|------------|-----------------------|
| HR         | 52500.00              |
| IT         | 65000.00              |

PARTITION BY используется вместе с оконными функциями.

PARTITION BY разбивает результат запроса на “окна” — подмножества строк, к которым применяется оконная функция, при этом все строки сохраняются в результирующем наборе.

SELECT employee_id, department, salary,
       AVG(salary) OVER (PARTITION BY department) AS avg_department_salary
FROM employees;

Вывод:

| employee_id | department | salary   | avg_department_salary |
|-------------|------------|----------|-----------------------|
| 1           | HR         | 50000.00 | 52500.00              |
| 2           | HR         | 55000.00 | 52500.00              |
| 3           | IT         | 60000.00 | 65000.00              |
| 4           | IT         | 65000.00 | 65000.00              |
| 5           | IT         | 70000.00 | 65000.00              |

Вопрос 6.

Представьте, что в таблице есть столбец FULL_NAME, который имеет такие значения, как Elon Musk, Bill Gates, Jeff Bezos и т.д. То есть каждое полное имя состоит из имени, пробела и фамилии. Какие функции вы можете использовать для получения только первого имени из столбца FULL_NAME? Приведите пример.

SELECT
    SUBSTR(full_name, 1, POSITION(' ' IN full_name) - 1) as first_name
FROM
    your_table_name;

• SUBSTR(full_name, 1, POSITION(' ' IN full_name) - 1): В этой части запроса используется функция SUBSTR для извлечения подстроки из столбца full_name.
• full_name: Исходная строка, из которой извлекается подстрока.
• 1: Начальная позиция (с начала строки).
• POSITION(' ' IN full_name) - 1: Длина подстроки. Вычисляется позиция пробела (‘ ‘) в столбце full_name с помощью функции POSITION и вычитается 1, чтобы исключить сам пробел.
• as first_name: Эта часть запроса задаёт псевдоним для результата: извлечённое имя будет отображаться как “first_name”.

Вопрос 7.

Как преобразовать текст в формат даты? Рассмотри на примере строки “31–01–2021”.

В SQL для преобразования текстового представления даты в формат даты обычно используется функция TO_DATE. Синтаксис этой функции различается в разных системах баз данных, но приведённый пример предполагает формат “DD-MM-YYYY”.

Пример SQL-запроса:

SELECT TO_DATE('31-01-2023', 'DD-MM-YYYY') as date_value;

TO_DATE('31-01-2021', 'DD-MM-YYYY'): В этой части запроса функция TO_DATE преобразует текст “31-01-2021” в значение типа DATE. Первый аргумент (’31-01-2021′) — дата в виде текста, а второй аргумент (‘DD-MM-YYYY’) — формат даты.

as date_value: Эта часть запроса задаёт псевдоним ‘date_value’, который представляет собой преобразованную дату.

Вопрос 8.

Для чего нужен оператор CASE в SQL? Приведите пример.

Оператор CASE похож на оператор IF ELSE в других языках программирования. Его можно использовать для отображения определенного значения в зависимости от условия.

Оператор CASE в SQL используется для реализации условной логики в запросе.

Вот простой пример использования CASE в запросе SELECT:

SELECT
    employee_name,
    salary,
    CASE
        WHEN salary > 50000 THEN 'High Salary'
        WHEN salary > 30000 THEN 'Medium Salary'
        ELSE 'Low Salary'
    END AS salary_category
FROM
    employees;

В этом примере оператор CASE используется для классификации сотрудников на основе их зарплаты. Если зарплата больше 50 000, то категория — “Высокая зарплата”. Если зарплата в диапазоне от 30 000 до 50 000, категория — “Средняя зарплата”. В остальных случаях категория — “Низкая зарплата”.

Вопрос 9.

В чем разница между LEFT, RIGHT, FULL OUTER JOIN и INNER JOIN?

Чтобы лучше понять это, рассмотрим две таблицы CONTINENTS и COUNTRIES. На примере этих таблиц покажем несколько запросов.

В таблице CONTINENTS отражены данные о 6 континентах. Обратите внимание, что континент “Антарктида” намеренно пропущен.

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

INNER JOIN выбирает только те записи, которые присутствуют в обеих таблицах. Сопоставление записей происходит исключительно по столбцам, использованным для объединения. INNER JOIN можно также обозначить просто как JOIN в запросе SELECT.

Пример запроса с INNER JOIN

SELECT cr.country_name, ct.continent_name
FROM continents ct
INNER JOIN countries cr
ON ct.continent_code = cr.continent_code;

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

Пример запроса с LEFT JOIN

SELECT cr.country_name, ct.continent_name
FROM continents ct
LEFT JOIN countries cr
ON ct.continent_code = cr.continent_code;

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

*Важно: выбор между LEFT и RIGHT JOIN определяется тем, какая таблица при выполнении соединения размещена слева, а какая справа.

Пример запроса с RIGHT JOIN

SELECT cr.country_name, ct.continent_name
FROM continents ct
RIGHT JOIN countries cr
ON ct.continent_code = cr.continent_code;

FULL JOIN объединяет результаты всех типов соединений — INNER, LEFT и RIGHT. То есть FULL JOIN вернёт все совпадающие записи из обеих таблиц + все записи из левой таблицы (даже если этих записей нет в правой таблице) + все записи из правой таблицы (даже если этих записей нет в левой таблице). FULL JOIN также может быть записан в запросе как FULL OUTER JOIN.

Пример запроса с FULL OUTER JOIN

SELECT cr.country_name, ct.continent_name
FROM continents ct
FULL OUTER JOIN countries cr
on ct.continent_code = cr.continent_code;

Также посмотрите, как применяются SELF JOIN, NATURAL JOIN и CROSS JOIN.

SELF JOIN — это присоединение таблицы к самой себе. При этом не существует специального ключевого слова SELF для выполнения такого соединения. В этом случае просто используется INNER JOIN, но вместо двух разных таблиц соединяем одну и ту же таблицу саму с собой. Главное — задать разные псевдонимы (alias) для этих таблиц. В остальном SELF JOIN работает так же, как и обычный INNER JOIN.

Пример запроса с SELF JOIN

SELECT cr1.country_name
FROM countries cr1
JOIN countries cr2ON cr1.country_code = cr2.continent_code;

NATURAL JOIN похож на INNER JOIN, но при его использовании не требуется указывать ON. Это значит, что при NATURAL JOIN таблицы задаются без явного указания колонок, по которым должно происходить соединение. По умолчанию SQL выполнит соединение двух таблиц по общим названиям столбцов. Таким образом, при использовании NATURAL JOIN обе таблицы должны содержать столбцы с одинаковыми именами и одинаковыми типами данных.

Пример запроса с NATURAL JOIN

SELECT cr.country_name, ct.continent_name
FROM continents ct
NATURAL JOIN countries cr;

CROSS JOIN объединяет все записи из левой таблицы со всеми записями из правой таблицы. Это означает, что перекрестное соединение не основано на совпадении значений в столбцах. Независимо от наличия совпадений, результатом будет каждая строка из левой таблицы, соединённая с каждой строкой из правой. Другими словами, CROSS JOIN возвращает декартово произведение.

Пример запроса с CROSS JOIN

SELECT cr.country_name, ct.continent_name
FROM continents ct
CROSS JOIN countries cr;

Вопрос 10.

Можно ли использовать агрегатную функцию в качестве оконной? Если да, то как это сделать?

Да, мы можем использовать агрегатную функцию в качестве оконной функции, с помощью конструкции OVER. Агрегатные функции обычно уменьшают количество строк, так как они агрегируют значения в одну строку. В отличие от них, оконные функции не уменьшают количество строк, а просто добавляют вычисленные значения к каждой строке.

Рассмотрим на примере, как использовать функцию SUM в роли оконной функции для подсчёта накопительной суммы зарплаты в рамках каждого отдела, упорядочив строки по зарплате:

SELECT
    employee_id,
    employee_name,
    department,
    salary,
    SUM(salary) OVER (PARTITION BY department ORDER BY salary) AS running_total_salary
FROM
    employees;

Вывод:

| employee_id | employee_name | department | salary   | running_total_salary |
|-------------|---------------|------------|----------|-----------------------|
| 1           | John          | HR         | 50000.00 | 50000.00              |
| 3           | Bob           | HR         | 55000.00 | 105000.00             |
| 2           | Jane          | IT         | 60000.00 | 60000.00              |
| 4           | Alice         | IT         | 70000.00 | 130000.00             |

В этом примере столбец running_total_salary представляет собой текущую общую зарплату в каждом отделе, рассчитанную в порядке возрастания зарплат. PARTITION BY разбивает выборки по столбцу department, а ORDER BY определяет порядок строк внутри каждой группы на основе столбца salary. Функция SUM применяется в качестве оконной функции и вычисляет нарастающий итог зарплаты для каждой строки внутри своего отдела.

Перевод статьи «Most asked SQL Interview Questions».