Задачи среднего уровня по SQL
Введение
SQL (Structured Query Language) — это язык запросов, используемый для взаимодействия с базами данных. Если вы уже освоили базовые команды SQL, такие как SELECT, INSERT, UPDATE и DELETE, то пора переходить к более сложным задачам. В этой статье мы рассмотрим задачи среднего уровня по SQL, которые помогут вам углубить свои знания и навыки. Эти задачи включают работу с различными типами JOIN, подзапросами, группировкой данных и использованием агрегатных функций, а также условной логикой с помощью оператора CASE.
Основные концепции и операторы SQL
Перед тем как перейти к задачам, важно освежить в памяти некоторые ключевые концепции и операторы SQL, которые будут использоваться в задачах среднего уровня. Понимание этих концепций поможет вам более эффективно решать задачи и писать сложные запросы.
JOIN
Операторы JOIN позволяют объединять данные из нескольких таблиц. Основные типы JOIN включают:
- INNER JOIN: возвращает строки, которые имеют совпадения в обеих таблицах. Это самый распространенный тип JOIN, который используется для получения данных, присутствующих в обеих таблицах.
- LEFT JOIN: возвращает все строки из левой таблицы и совпадающие строки из правой таблицы. Если совпадений нет, то строки из правой таблицы будут содержать NULL.
- RIGHT JOIN: возвращает все строки из правой таблицы и совпадающие строки из левой таблицы. Если совпадений нет, то строки из левой таблицы будут содержать NULL.
- FULL JOIN: возвращает строки, когда есть совпадение в одной из таблиц. Если совпадений нет, то строки из обеих таблиц будут содержать NULL.
Подзапросы
Подзапросы — это запросы, вложенные внутри другого запроса. Они могут использоваться в SELECT, INSERT, UPDATE и DELETE операциях. Подзапросы позволяют выполнять сложные операции, такие как фильтрация данных на основе результатов другого запроса.
Группировка и агрегатные функции
Группировка данных с помощью оператора GROUP BY и использование агрегатных функций (SUM, AVG, COUNT, MAX, MIN) позволяют выполнять вычисления на наборах данных. Это полезно для создания отчетов и анализа данных.
CASE
Оператор CASE позволяет выполнять условную логику в запросах SQL, что полезно для создания вычисляемых столбцов и сложных условий. С помощью CASE можно создавать выражения, которые возвращают разные значения в зависимости от условий.
Примеры задач среднего уровня
Задача 1: Поиск сотрудников с максимальной зарплатой в каждом отделе
Описание задачи: Найти сотрудников, которые получают максимальную зарплату в каждом отделе. Эта задача требует использования подзапросов и операторов JOIN для объединения данных из нескольких таблиц.
Таблицы:
- employees (id, name, department_id, salary)
- departments (id, name)
Задача 2: Определение среднего возраста пользователей по городам
Описание задачи: Определить средний возраст пользователей для каждого города. Эта задача требует использования операторов JOIN и агрегатных функций для вычисления среднего возраста.
Таблицы:
- users (id, name, age, city_id)
- cities (id, name)
Задача 3: Вычисление общего количества продаж и общей суммы продаж по категориям товаров
Описание задачи: Вычислить общее количество продаж и общую сумму продаж для каждой категории товаров. Эта задача требует использования нескольких операторов JOIN и агрегатных функций для вычисления итоговых значений.
Таблицы:
- sales (id, product_id, quantity, total_price)
- products (id, name, category_id)
- categories (id, name)
Решения и объяснения
Решение задачи 1
SELECT e.name, e.salary, d.name AS department
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE e.salary = (
SELECT MAX(salary)
FROM employees
WHERE department_id = e.department_id
);
Объяснение: В этом запросе мы используем подзапрос для нахождения максимальной зарплаты в каждом отделе и затем фильтруем сотрудников по этой зарплате. Подзапрос выполняется для каждой строки основной таблицы employees, что позволяет найти максимальную зарплату в соответствующем отделе.
Решение задачи 2
SELECT c.name AS city, AVG(u.age) AS average_age
FROM users u
JOIN cities c ON u.city_id = c.id
GROUP BY c.name;
Объяснение: Мы используем оператор JOIN для объединения таблиц users и cities, затем группируем данные по городам и вычисляем средний возраст пользователей в каждом городе с помощью функции AVG. Оператор GROUP BY позволяет группировать данные по столбцу city, а функция AVG вычисляет среднее значение возраста для каждой группы.
Решение задачи 3
SELECT c.name AS category, SUM(s.quantity) AS total_quantity, SUM(s.total_price) AS total_sales
FROM sales s
JOIN products p ON s.product_id = p.id
JOIN categories c ON p.category_id = c.id
GROUP BY c.name;
Объяснение: В этом запросе мы используем два оператора JOIN для объединения таблиц sales, products и categories. Затем группируем данные по категориям и вычисляем общее количество и сумму продаж с помощью функций SUM. Оператор GROUP BY позволяет группировать данные по столбцу category, а функции SUM вычисляют итоговые значения для каждой группы.
Заключение и дополнительные ресурсы
Эти задачи среднего уровня по SQL помогут вам углубить свои знания и навыки работы с базами данных. Практикуйтесь с различными типами JOIN, подзапросами и агрегатными функциями, чтобы стать более уверенным в написании сложных SQL-запросов. Решение таких задач поможет вам лучше понять, как работают различные операторы и функции SQL, и как их можно комбинировать для решения сложных задач.
Дополнительные ресурсы для изучения:
- SQLZoo: интерактивные уроки и упражнения по SQL.
- LeetCode SQL: задачи и упражнения по SQL для подготовки к интервью.
- HackerRank SQL: 10-дневный курс по SQL с упражнениями и задачами.
Продолжайте практиковаться и изучать новые концепции, и вы скоро станете экспертом в SQL! 😉