30+ вопросов на собеседовании по SQL

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

Cодержание

1. Концептуальные и теоретические вопросы
2. Вопросы по SQL-запросам
3. Каверзные вопросы

Прокачайте свои скиллы в SQL, отвечайте на вопросы с лёгкостью, практикуйте код и получите желаемую должность в data-сфере. Ознакомившись с этими 30 вопросами, которые точно встретятся на вашем следующем собеседовании, вы почувствуете себя более уверенно!

Концептуальные и теоретические вопросы

1. Напишите запрос для поиска топ-5 клиентов с наибольшей суммой заказов.

• Ответ:

SELECT CustomerID, SUM(OrderAmount) AS TotalOrderAmount
FROM Orders
GROUP BY CustomerID
ORDER BY TotalOrderAmount DESC
LIMIT 5;

2. Как можно оптимизировать медленно выполняющийся запрос?

• Ответ: Существует несколько методик, включая:
  • Использование соответствующих индексов
  • Избегание ненужных соединений и подзапросов
  • Применение эффективных функций и операторов
  • Анализ планов выполнения для выявления узких мест

3. Объясните концепцию нормализации при проектировании баз данных.

• Ответ: Нормализация — это процесс организации таблиц для минимизации избыточности и улучшения целостности данных. Она предполагает разбиение таблиц на более мелкие, более специализированные таблицы с четко определенными связями.

4. Напишите запрос для вычисления средней зарплаты по каждому отделу, исключая сотрудников с зарплатой выше определенного порога.

• Ответ:

SELECT Department, AVG(Salary) AS AverageSalary
FROM (
  SELECT Department, Salary
  FROM Employees
  WHERE Salary <= (SELECT MAX(Salary) FROM Employees) / 2
) AS Subquery
GROUP BY Department;

5. Как обрабатывать NULL значения в запросах?

• Ответ: Для обработки NULL значений можно использовать такие функции, как ISNULL, COALESCE или оператор CASE. Эти функции позволяют задавать альтернативные значения или выполнять различные операции в зависимости от проверок на NULL.

6. Опишите концепцию транзакций в SQL и их свойств ACID.

• Ответ: Транзакция — это единица работы, которая поддерживает согласованность данных. ACID означает атомарность («всё или ничего»), согласованность (целостность данных), изолированность  (одновременные транзакции не мешают друг другу) и долговечность (изменения сохраняются).

7. Как защитить SQL-запросы от атак SQL-инъекций?

• Ответ: Используйте подготовленные выражения с параметризованными запросами. Эти выражения разделяют код и данные, предотвращая выполнение вредоносного кода.

🔥 Хочешь больше SQL Задачек??? 🔥
В канале SqlQuestions более 1000 разных SQL задач с ответами. Переходи и посмотри 

8. Объясните разницу между UNION и UNION ALL в SQL.

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

9. Опишите функциональность триггеров в SQL и их типы.

• Ответ: Триггеры — это хранимые процедуры, которые автоматически выполняются в ответ на определенные события в таблице (например, INSERT, UPDATE, DELETE). Они могут использоваться для проверки данных, реализации бизнес-логики или поддержания согласованности данных.

10. Объясните разницу между FULL OUTER JOIN и FULL JOIN в SQL.

• Ответ:  Оба типа соединения возвращают все строки из обеих таблиц, но FULL OUTER JOIN сохраняет значения NULL в несопоставленных столбцах, тогда как FULL JOIN может заменять их значениями по умолчанию в зависимости от СУБД.

11. Опишите назначение регулярных выражений в SQL и их применение в запросах.

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

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

• Ответ: Партиционирование базы данных делит большую таблицу на более мелкие, управляемые сегменты на основе выбранного ключа. Это повышает производительность, позволяя запросам обращаться к определенным патрициям и сокращая операции ввода-вывода.

13. Опишите функциональность операторов MERGE в SQL.

• Ответ: Оператор MERGE объединяет операции INSERT, UPDATE и DELETE в единую инструкцию. Он обеспечивает эффективное изменение данных, позволяя выполнять условные действия на основе критериев существования или соответствия.

Вопросы по SQL-запросам

14. Напишите запрос для вычисления разницы в днях между датой заказа и датой отгрузки для каждого заказа.

• Ответ:

SELECT OrderID, DATEDIFF(day, OrderDate, ShipDate) AS DaysDiff
FROM Orders;

15. Напишите запрос для поиска менеджера для каждого сотрудника компании, даже если сотруднику не назначен менеджер.

• Ответ:

SELECT e.EmployeeID, m.ManagerName
FROM Employees e
LEFT JOIN Employees m ON e.ManagerID = m.EmployeeID;

16. Напишите запрос для переноса данных из строк в столбцы, отображающий общий объем продаж по категориям товаров с разбивкой по месяцам.

• Ответ:

SELECT Month,
       SUM(CASE WHEN ProductCategory = 'Electronics' THEN Sales ELSE 0 END) AS 
Electronics,
       SUM(CASE WHEN ProductCategory = 'Clothing' THEN Sales ELSE 0 END) AS Clothing,
       ... (add more categories)
FROM SalesData
GROUP BY Month;

17. Напишите запрос для поиска сотрудников, которые никогда не оформляли заказы.

• Ответ:

SELECT e.EmployeeID, e.EmployeeName
FROM Employees e
LEFT JOIN Orders o ON e.EmployeeID = o.CustomerID
WHERE o.CustomerID IS

18. Напишите запрос для поиска отдела с самой высокой средней зарплатой среди сотрудников, работающих в компании более 2 лет.

• Ответ:

SELECT d.DepartmentName, AVG(e.Salary) AS AverageSalary
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID
WHERE e.HireDate < DATEADD(year, -2, GETDATE())
GROUP BY d.DepartmentName
ORDER BY AverageSalary DESC
LIMIT 1;

19. Напишите запрос для поиска n-ой самой высокой зарплаты в таблице сотрудников.

• Ответ (с использованием подзапроса):

SELECT Salary
FROM Employees
WHERE Salary IN (
  SELECT TOP 1 Salary
  FROM (
    SELECT Salary, ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum
    FROM Employees
  ) AS Subquery
  WHERE RowNum = n
);

 

20. Напишите запрос для подсчета общего количества клиентов, сделавших заказы в каждом квартале прошлого года.

• Ответ:

SELECT DATEPART(quarter, OrderDate) AS Quarter, COUNT(DISTINCT CustomerID) AS Customers
FROM Orders
WHERE OrderDate >= DATEADD(year, -1, GETDATE())
GROUP BY DATEPART(quarter, OrderDate)
ORDER BY Quarter;

21. Напишите запрос для поиска иерархии менеджеров конкретного сотрудника, отображая все уровни вплоть до CEO.

• Ответ (с использованием рекурсивного CTE)

WITH ManagerHierarchy (EmployeeID, ManagerID, Level) AS (
  SELECT EmployeeID, ManagerID, 1 AS Level
  FROM Employees
  WHERE EmployeeID = <employee_id>
  UNION ALL
  SELECT e.EmployeeID, m.ManagerID, h.Level + 1
  FROM Employees e
  INNER JOIN ManagerHierarchy h ON e.EmployeeID = h.ManagerID
  INNER JOIN Employees m ON e.ManagerID = m.EmployeeID
  WHERE m.ManagerID IS NOT NULL
)
SELECT EmployeeID, ManagerID, Level
FROM ManagerHierarchy
ORDER BY Level DESC;

22. Напишите запрос для поиска категорий товаров с наибольшим и наименьшим общим объемом продаж за прошлый год.

• Ответ:

SELECT ProductCategory, SUM(SalesAmount) AS TotalSales
FROM SalesData
WHERE SaleDate >= DATEADD(year, -1, GETDATE())
GROUP BY ProductCategory
ORDER BY TotalSales DESC, TotalSales ASC
LIMIT 2;

23.  Напишите запрос для поиска сотрудников, чья зарплата превышает среднюю зарплату по их отделу.

• Ответ:

SELECT e.EmployeeID, e.EmployeeName, d.DepartmentName, e.Salary
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID =

Каверзные вопросы

Эти вопросы позволят оценить ваши навыки решения проблем. Используйте приведенную ниже таблицу для справки и отвечайте на вопросы в соответствии с ней.

• Настройка данных: Для этих вопросов используйте таблицы-образцы.
• Customers Table:

Customer ID	Name	City
1	John Levi	New York
2	Jane Tye	Los Angeles
3	Mike Foley	Chicago
4	Alice White	New York

• Orders Table:

Order ID	Customer ID	Order Date	Order Total
100	1	2023-07-01	100.00
101	2	2023-06-15	50.00
102	3	2023-07-05	150.00
103	1	2023-07-07	75.00
104	4	2023-07-02	200.00

Вопросы:

1. Найдите общее количество заказов, сделанных каждым клиентом, исключая заказы, оформленные в июне.

• Каверзный аспект: Исключение конкретного месяца требует фильтрации по дате.
• Ответ:

SELECT c.name, COUNT(*) AS num_orders
FROM Customers c
INNER JOIN Orders o ON c.customer_id = o.customer_id
WHERE MONTH(order_date) <> 6
GROUP BY c.name

2. Найдите клиента, который оформил заказы на наибольшую общую сумму.

• Каверзный аспект: Требуется агрегация и сортировка по общей сумме заказов.
• Ответ:

SELECT c.name, SUM(order_total) AS total_order_value
FROM Customers c
INNER JOIN Orders o ON c.customer_id = o.customer_id
GROUP BY c.name
ORDER BY total_order_value DESC
LIMIT 1;

3. Выведите все заказы, оформленные в указанные даты (например, 2023-07-04 и 2023-07-06) с именами соответствующих клиентов.

• Каверзный аспект: Требуется фильтрация по нескольким конкретным датам.
• Ответ:

SELECT c.name, o.order_date, o.order_total
FROM Customers c
INNER JOIN Orders o ON c.customer_id = o.customer_id
WHERE order_date IN ('2023-07-04', '2023-07-06');

4. Найдите среднюю стоимость заказа для каждого города.

• Каверзный аспект: Требуется соединение таблиц и группировка по городу.
• Ответ:

SELECT c.city, AVG(o.order_total) AS avg_order_value
FROM Customers c
INNER JOIN Orders o ON c.customer_id = o.customer_id
GROUP BY c.city

5. Определите клиентов, которые не сделали ни одного заказа.

• Каверзный аспект: Требуется использование LEFT JOIN и фильтрация по нулевым значениям.
• Ответ:

SELECT c.name
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

6. Найдите месяц с наибольшей общей суммой заказов.

• Каверзный аспект: Требуется извлечение месяца из даты и группировка по месяцам.
• Ответ:

SELECT MONTH(order_date) AS order_month, SUM(order_total) AS total_order_value
FROM Orders
GROUP BY MONTH(order_date)
ORDER BY total_order_value DESC
LIMIT 1;

7. Напишите запрос для вывода топ-2 клиентов с наибольшим количеством заказов за последние 30 дней.

• Каверзный аспект: Требуется фильтрация по диапазону дат и использование оконных функций для ранжирования.
• Ответ:

SELECT c.name, COUNT(*) AS num_orders
FROM Customers c
INNER JOIN Orders o ON c.customer_id = o.customer_id
WHERE order_date >= DATE_SUB (CURDATE)

Перевод статьи «30 Advanced SQL Interview Questions for Professionals (With Code)».