Задачи среднего уровня по 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! 😉