SQL практика

Вопросы:

1. Схема: employees(emp_id INT, name VARCHAR, department_id INT, manager_id INT) departments(department_id INT, department_name VARCHAR) Задача 1 (joins): Найдите имена сотрудников и названия отделов, в которых они работают. Выведите только тех сотрудников, у которых есть отдел. Проверяет умение делать INNER JOIN между таблицами.

2. Схема: orders(order_id INT, customer_id INT, order_date DATE) customers(customer_id INT, customer_name VARCHAR) order_items(order_item_id INT, order_id INT, product_id INT, quantity INT) Задача 2 (joins): Вывести список всех заказов с именами клиентов и количеством товаров в каждом заказе. Включить заказы без товаров. Проверяет использование LEFT JOIN и агрегацию с join.

3. Схема: products(product_id INT, product_name VARCHAR, category_id INT) categories(category_id INT, category_name VARCHAR) Задача 3 (joins): Вывести список всех продуктов вместе с их категориями и количеством продуктов в каждой категории, включая категории без продуктов. Проверяет умение делать RIGHT JOIN и группировки вместе с join.

4. Схема: employees(emp_id INT, name VARCHAR, manager_id INT) Задача 4 (joins): Выведите список всех сотрудников с именами их менеджеров. Если менеджера нет, показывать NULL. Проверяет использование SELF JOIN.

5. Схема: orders(order_id INT, customer_id INT, order_date DATE, total_amount DECIMAL) customers(customer_id INT, customer_name VARCHAR) Задача 5 (подзапросы): Вывести список клиентов, у которых сумма всех заказов выше среднего по всем клиентам. Проверяет использование подзапросов в WHERE с агрегатами.

6. Схема: employees(emp_id INT, name VARCHAR, salary DECIMAL, department_id INT) departments(department_id INT, department_name VARCHAR) Задача 6 (подзапросы): Найдите отдел, в котором средняя зарплата выше средней зарплаты по всем отделам. Покажите название отдела и среднюю зарплату. Проверяет подзапрос в FROM с агрегатами.

7. Схема: sales(sale_id INT, product_id INT, quantity INT, sale_date DATE) products(product_id INT, product_name VARCHAR) Задача 7 (подзапросы): Вывести продукты с максимальным количеством продаж за один день. Проверяет использование подзапроса для поиска максимума и фильтрацию по нему.

8. Схема: employees(emp_id INT, name VARCHAR, salary DECIMAL) Задача 8 (подзапросы): Вывести сотрудников, у которых зарплата выше зарплаты сотрудника с id = 10. Проверяет использование скалярного подзапроса в условии.

9. Схема: orders(order_id INT, customer_id INT, order_date DATE) order_items(order_item_id INT, order_id INT, product_id INT, quantity INT, price DECIMAL) Задача 9 (агрегации): Вывести количество заказов и общую сумму продаж по месяцам. Проверяет группировку по дате с вычислением агрегатов SUM и COUNT.

10. Схема: employees(emp_id INT, department_id INT, salary DECIMAL) Задача 10 (агрегации): Найдите среднюю, минимальную и максимальную зарплату в каждом департаменте. Покажите department_id и результаты агрегатов. Проверяет использование нескольких агрегатных функций и группировку.

11. Схема: products(product_id INT, category_id INT, price DECIMAL) Задача 11 (агрегации): Найдите категорию с максимальной средней ценой продукта. Выведите category_id и среднюю цену. Проверяет группировку и сортировку с LIMIT для поиска максимума.

12. Схема: sales(sale_id INT, product_id INT, quantity INT, sale_date DATE) Задача 12 (агрегации): Посчитайте суммарное количество проданных товаров и количество уникальных продуктов, проданных в каждом году. Проверяет группировку по году и использование COUNT DISTINCT вместе с SUM.

13. Схема: employees(emp_id INT, department_id INT, salary DECIMAL, hire_date DATE) Задача 13 (аналитические функции): Для каждого сотрудника вычислите его ранг по зарплате внутри отдела (1 — самая высокая зарплата). Проверяет использование функции RANK() OVER (PARTITION BY ... ORDER BY ...).

14. Схема: sales(sale_id INT, customer_id INT, sale_date DATE, total DECIMAL) Задача 14 (аналитические функции): Для каждого заказа вычислите накопительную сумму total по каждому клиенту, упорядоченную по дате покупки. Проверяет использование SUM() OVER с PARTITION BY и ORDER BY.

15. Схема: employees(emp_id INT, department_id INT, salary DECIMAL) Задача 15 (аналитические функции): Определите для каждого сотрудника, насколько его зарплата отличается от средней зарплаты отдела. Вывести emp_id, salary и эту разницу. Проверяет использование AVG() OVER и вычисление разницы.

16. Схема: orders(order_id INT, customer_id INT, order_date DATE) customers(customer_id INT, country VARCHAR) Задача 16 (аналитические функции): Для каждой страны определить количество заказов каждого клиента и общий суммарный заказ в стране, выведя заказчика с клиентским и страновым количеством. Проверяет использование окон функций с PARTITION BY для разных уровней агрегации.

17. Схема: employees(emp_id INT, department_id INT, salary DECIMAL, hire_date DATE) Задача 17 (window functions): Вывести каждого сотрудника вместе с зарплатой предыдущего по дате наёма сотрудника из того же отдела. Проверяет использование LAG() OVER с PARTITION BY и ORDER BY.

18. Схема: sales(sale_id INT, product_id INT, quantity INT, sale_date DATE) Задача 18 (window functions): Посчитать скользящее среднее продаж за последние 3 дня для каждого продукта, упорядоченных по дате. Проверяет использование оконной функции AVG() с ROWS BETWEEN.

19. Схема: employees(emp_id INT, salary DECIMAL, department_id INT) Задача 19 (window functions): Для каждого отдела вывести среднюю зарплату по отделу для текущей и всех предыдущих записей (по возрастанию emp_id). Проверяет использование оконной функции с RANGE/ROWS для накопительного среднего.

20. Схема: products(product_id INT, category_id INT, price DECIMAL) Задача 20 (window functions): Вывести продукты и для каждого с помощью оконной функции определить минимальную и максимальную цену в категории. Проверяет использование оконных функций MIN() и MAX() с PARTITION BY.

21. Схема: employees(emp_id INT, name VARCHAR, department_id INT, salary DECIMAL) Задача 21 (CTE): Используя CTE, найдите топ-3 сотрудника с самой высокой зарплатой в каждом департаменте. Проверяет создание рекурсивных или не рекурсивных CTE с функцией ROW_NUMBER() и фильтрацией.

22. Схема: orders(order_id INT, customer_id INT, order_date DATE) order_items(order_item_id INT, order_id INT, product_id INT, quantity INT, price DECIMAL) Задача 22 (CTE): С помощью CTE вычислите промежуточный результат — общую сумму по каждому заказу, а затем выведите заказы с общей суммой свыше 1000. Проверяет построение многоступенчатого запроса с CTE.

23. Схема: employees(emp_id INT, name VARCHAR, manager_id INT) Задача 23 (CTE): Используя рекурсивный CTE, найдите всех подчинённых сотрудника с emp_id = 1 (любого уровня вложенности). Проверяет навыки рекурсивных CTE и построения иерархических выборок.

24. Схема: sales(sale_id INT, product_id INT, quantity INT, sale_date DATE) Задача 24 (CTE): Используя CTE, вычислите порядковый номер (ранг) каждого дня по сумме продаж, упорядочив дни по убыванию суммы. Проверяет комбинирование агрегатной функции в CTE и оконной функции для ранжирования.