30 практических SQL-упражнений для новичков: от SELECT до JOIN

Для кого эта статья:

• Новички в программировании, желающие освоить SQL
• Студенты и специалисты в области аналитики данных и разработки баз данных

• Люди, интересующиеся повышением квалификации для карьерного роста в IT сфере

  Знаете ли вы, что по данным Stack Overflow, SQL остается в топ-5 самых востребованных языков программирования более 10 лет подряд? 📊 Освоение SQL открывает двери в мир аналитики данных, разработки и администрирования баз данных с зарплатами от 80 000 до 200 000 рублей. Однако теория без практики мертва. Именно поэтому я собрал 30 практических упражнений по SQL, которые помогут новичкам закрепить знания и сразу применить их в реальных задачах.

Хотите быстро и эффективно освоить SQL с нуля? Курс SQL от Skypro — это идеальное сочетание теории и практики. Наши студенты уже через 2 месяца решают сложные аналитические задачи и устраиваются на работу. В программе более 100 практических упражнений с обратной связью от действующих разработчиков баз данных. Начните свой путь к востребованной профессии уже сегодня!

30 практических упражнений SQL для закрепления базовых знаний

SQL (Structured Query Language) — это язык, который используется для взаимодействия с реляционными базами данных. Для эффективного изучения SQL необходима регулярная практика. Я подготовил 30 практических упражнений, которые помогут вам пройти путь от новичка до уверенного пользователя баз данных. 🚀

Все упражнения разработаны на основе простой учебной базы данных, содержащей следующие таблицы:

• employees — информация о сотрудниках (id, name, position, salary, department_id)
• departments — информация об отделах (id, name, location)
• projects — информация о проектах (id, name, budget, startdate, enddate)
• employee_projects — связь между сотрудниками и проектами (employeeid, projectid, hours)

Прежде чем начать выполнение упражнений, давайте создадим эту базу данных и заполним ее тестовыми данными. Вы можете использовать любую СУБД (MySQL, PostgreSQL, SQLite), но я буду приводить решения в синтаксисе PostgreSQL.

CREATE TABLE departments (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
location VARCHAR(100)
);

CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
position VARCHAR(100),
salary NUMERIC(10, 2),
department_id INTEGER REFERENCES departments(id)
);

CREATE TABLE projects (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
budget NUMERIC(12, 2),
start_date DATE,
end_date DATE
);

CREATE TABLE employee_projects (
employee_id INTEGER REFERENCES employees(id),
project_id INTEGER REFERENCES projects(id),
hours INTEGER,
PRIMARY KEY (employee_id, project_id)
);

-- Заполнение таблиц тестовыми данными
INSERT INTO departments (name, location) VALUES
('IT', 'Москва'),
('Marketing', 'Санкт-Петербург'),
('Finance', 'Москва'),
('HR', 'Новосибирск');

INSERT INTO employees (name, position, salary, department_id) VALUES
('Иван Петров', 'Developer', 120000, 1),
('Анна Смирнова', 'Designer', 90000, 1),
('Сергей Иванов', 'Manager', 150000, 2),
('Ольга Козлова', 'Analyst', 110000, 3),
('Дмитрий Соколов', 'HR Manager', 100000, 4),
('Елена Новикова', 'Senior Developer', 180000, 1),
('Павел Морозов', 'Junior Developer', 70000, 1),
('Мария Волкова', 'Accountant', 95000, 3);

INSERT INTO projects (name, budget, start_date, end_date) VALUES
('Website Redesign', 500000, '2023-01-15', '2023-05-30'),
('Mobile App', 1200000, '2023-02-01', '2023-08-15'),
('Data Migration', 300000, '2023-03-10', '2023-06-20'),
('Marketing Campaign', 450000, '2023-04-05', '2023-07-25');

INSERT INTO employee_projects (employee_id, project_id, hours) VALUES
(1, 1, 120),
(1, 2, 80),
(2, 1, 100),
(3, 4, 150),
(4, 3, 90),
(6, 2, 200),
(6, 3, 40),
(7, 1, 150);

Начинаем с SELECT: 10 упражнений для работы с данными

SELECT — основа основ в SQL. Этот оператор позволяет извлекать данные из таблиц. Давайте начнем наше путешествие в мир SQL с простых запросов выборки данных.

Алексей Федоров, преподаватель SQL и баз данных

Когда я только начинал преподавать SQL, столкнулся с интересным случаем. Один из моих студентов, Михаил, опытный программист на Python, никак не мог понять логику SQL запросов. Его мышление было настроено на процедурное программирование, а SQL требует декларативного подхода.

Я предложил ему начать с простых упражнений на SELECT, постепенно усложняя задачи. После выполнения первых 10 упражнений, подобных тем, что приведены ниже, у Михаила произошел "щелчок" в понимании. Он осознал, что в SQL мы описываем, КАКИЕ данные нам нужны, а не КАК их получить. Через месяц такой практики Михаил не только догнал группу, но и стал одним из самых сильных студентов курса.

Упражнение 1: Выберите все записи из таблицы employees.

SELECT * FROM employees;

Упражнение 2: Выберите только имена и должности всех сотрудников.

SELECT name, position FROM employees;

Упражнение 3: Выберите уникальные значения должностей в компании.

SELECT DISTINCT position FROM employees;

Упражнение 4: Выведите имена и зарплаты всех сотрудников, отсортированные по зарплате в порядке убывания.

SELECT name, salary FROM employees ORDER BY salary DESC;

Упражнение 5: Выведите первые 3 записи из таблицы employees.

SELECT * FROM employees LIMIT 3;

Упражнение 6: Выведите имена сотрудников и названия их отделов (используя JOIN).

SELECT e.name, d.name AS department_name
FROM employees e
JOIN departments d ON e.department_id = d.id;

Упражнение 7: Подсчитайте, сколько сотрудников работает в каждом отделе.

SELECT d.name, COUNT(e.id) AS employee_count
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id
GROUP BY d.name;

Упражнение 8: Выведите имена сотрудников и названия проектов, в которых они участвуют.

SELECT e.name AS employee_name, p.name AS project_name
FROM employees e
JOIN employee_projects ep ON e.id = ep.employee_id
JOIN projects p ON ep.project_id = p.id;

Упражнение 9: Найдите проекты с бюджетом более 400,000 и отсортируйте их по дате начала.

SELECT name, budget, start_date
FROM projects
WHERE budget > 400000
ORDER BY start_date;

Упражнение 10: Подсчитайте общую сумму зарплат по каждому отделу.

SELECT d.name, SUM(e.salary) AS total_salary
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id
GROUP BY d.name;

При выполнении этих упражнений вы познакомились с базовыми функциями SQL: выборкой данных, сортировкой, ограничением результатов, соединением таблиц и агрегацией данных. Теперь давайте перейдем к упражнениям на фильтрацию и сортировку. 💡

Фильтрация и сортировка: задачи для освоения WHERE и ORDER BY

Фильтрация данных — одна из ключевых операций при работе с базами данных. Используя WHERE, вы можете получать только те записи, которые соответствуют определенным условиям. A с помощью ORDER BY можно управлять порядком вывода результатов.

Упражнение 11: Найдите всех сотрудников с зарплатой выше 100,000.

SELECT * FROM employees WHERE salary > 100000;

Упражнение 12: Найдите всех сотрудников из отдела IT (отдел с id=1).

SELECT * FROM employees WHERE department_id = 1;

Упражнение 13: Найдите всех сотрудников, чье имя начинается с буквы "А".

SELECT * FROM employees WHERE name LIKE 'А%';

Упражнение 14: Найдите всех сотрудников с должностью "Developer" или "Designer".

SELECT * FROM employees WHERE position IN ('Developer', 'Designer');

Упражнение 15: Найдите проекты, которые начались в 2023 году и имеют бюджет более 400,000.

SELECT * FROM projects 
WHERE start_date >= '2023-01-01' 
AND budget > 400000;

Упражнение 16: Найдите сотрудников с зарплатой между 90,000 и 150,000, отсортированных по имени.

SELECT * FROM employees 
WHERE salary BETWEEN 90000 AND 150000 
ORDER BY name;

Упражнение 17: Найдите проекты без даты окончания или с датой окончания после 1 августа 2023.

SELECT * FROM projects 
WHERE end_date IS NULL OR end_date > '2023-08-01';

Упражнение 18: Отсортируйте сотрудников по отделу (по возрастанию), а внутри каждого отдела — по зарплате (по убыванию).

SELECT * FROM employees 
ORDER BY department_id ASC, salary DESC;

Упражнение 19: Найдите сотрудников, не привязанных ни к одному отделу.

SELECT * FROM employees WHERE department_id IS NULL;

Упражнение 20: Найдите проекты, название которых содержит слово "App" в любом регистре.

SELECT * FROM projects WHERE LOWER(name) LIKE '%app%';

Упражнения на фильтрацию и сортировку помогают научиться выбирать нужные данные из базы и представлять их в удобном виде. Теперь перейдем к агрегатным функциям, которые позволяют выполнять вычисления над группами записей. 🔍

Агрегатные функции и GROUP BY в практических задачах SQL

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

Марина Соколова, аналитик данных

В моей практике был случай с клиентом из e-commerce, который никак не мог понять, почему его бизнес теряет деньги. У них была огромная база данных с миллионами транзакций, но никто не мог увидеть общую картину.

Я начала с простых агрегатных запросов, подобных тем, что приведены ниже. После группировки транзакций по категориям товаров и применения функций SUM, AVG и COUNT, мы обнаружили, что 80% прибыли приносят всего 20% товаров, но компания тратила одинаковые ресурсы на продвижение всего ассортимента. Более того, некоторые "популярные" товары на самом деле приносили убытки из-за высокой стоимости обработки возвратов.

После перераспределения маркетингового бюджета в пользу действительно прибыльных категорий, доход компании вырос на 35% за квартал. Этот кейс наглядно показывает, как правильное применение агрегатных функций SQL может трансформировать бизнес-решения.

Упражнение 21: Найдите среднюю зарплату по компании.

SELECT AVG(salary) AS average_salary FROM employees;

Упражнение 22: Найдите минимальную и максимальную зарплату в каждом отделе.

SELECT department_id, MIN(salary) AS min_salary, MAX(salary) AS max_salary
FROM employees
GROUP BY department_id;

Упражнение 23: Подсчитайте, сколько сотрудников занимают каждую должность.

SELECT position, COUNT(*) AS employee_count
FROM employees
GROUP BY position;

Упражнение 24: Найдите отделы, где средняя зарплата превышает 100,000.

SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 100000;

Упражнение 25: Для каждого проекта подсчитайте общее количество часов, затраченных всеми сотрудниками.

SELECT p.name AS project_name, SUM(ep.hours) AS total_hours
FROM projects p
JOIN employee_projects ep ON p.id = ep.project_id
GROUP BY p.name;

Упражнение 26: Найдите сотрудников, которые работают более чем над одним проектом.

SELECT e.name, COUNT(ep.project_id) AS project_count
FROM employees e
JOIN employee_projects ep ON e.id = ep.employee_id
GROUP BY e.name
HAVING COUNT(ep.project_id) > 1;

Упражнение 27: Для каждого отдела рассчитайте процентное соотношение его зарплатного фонда к общему фонду компании.

SELECT 
d.name AS department_name,
SUM(e.salary) AS department_salary,
(SUM(e.salary) * 100.0 / (SELECT SUM(salary) FROM employees)) AS percentage
FROM departments d
JOIN employees e ON d.id = e.department_id
GROUP BY d.name;

Агрегатные функции и GROUP BY позволяют получать сводные данные и статистику, что крайне важно для аналитики. Теперь переходим к многотабличным запросам и соединениям. 📊

Многотабличные запросы: практикум по JOIN для начинающих

JOIN — это операция, которая позволяет объединять данные из нескольких таблиц на основе связей между ними. Это фундаментальный аспект реляционных баз данных, который необходимо хорошо понимать.

Упражнение 28: Найдите имена всех сотрудников и названия отделов, в которых они работают. Включите сотрудников без отдела.

SELECT e.name AS employee_name, d.name AS department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

Упражнение 29: Найдите названия всех отделов и количество сотрудников в них. Включите отделы, в которых нет сотрудников.

SELECT d.name AS department_name, COUNT(e.id) AS employee_count
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id
GROUP BY d.name;

Упражнение 30: Найдите сотрудников, которые работают над тем же проектом, что и "Иван Петров" (id=1).

SELECT DISTINCT e.name
FROM employees e
JOIN employee_projects ep ON e.id = ep.employee_id
WHERE ep.project_id IN (
SELECT project_id
FROM employee_projects
WHERE employee_id = 1
)
AND e.id != 1;

JOIN-запросы — это мощный инструмент для работы с реляционными базами данных. Они позволяют извлекать связанные данные из нескольких таблиц в одном запросе, что особенно полезно при анализе данных. 🔄

После выполнения всех 30 упражнений вы получите базовое понимание SQL и сможете составлять запросы для решения различных задач. Не забывайте, что лучший способ изучить SQL — это постоянная практика на реальных данных.

Вот несколько рекомендаций для дальнейшего изучения SQL:

• Работайте с реальными наборами данных, например, из открытых источников
• Изучите более сложные темы: подзапросы, оконные функции, индексы
• Ознакомьтесь со спецификой конкретной СУБД, которую планируете использовать
• Решайте задачи на таких платформах, как LeetCode, HackerRank, SQLZoo
• Создавайте собственные проекты с базами данных

Помните, что SQL — это язык, который требует практики для освоения. Чем больше запросов вы напишете, тем увереннее будете себя чувствовать. Удачи в изучении! 💪

Освоение SQL через практические упражнения — это как построение мышечной памяти для вашего мозга. Каждый запрос, который вы пишете, укрепляет нейронные связи и делает следующий запрос проще. Не переживайте, если поначалу кажется сложно — это нормальный процесс обучения. Постепенно вы начнете "думать на SQL", и решение даже сложных задач станет интуитивным. Главное — регулярная практика и постепенное усложнение задач. И помните: в мире данных SQL — это не просто навык, а ваш пропуск в профессиональное сообщество аналитиков и разработчиков.

Читайте также

• Особенности работы с SQLite
• Задачи среднего уровня по SQL
• Особенности работы с MS SQL Server
• Как выбрать СУБД: сравнение решений для разных бизнес-задач
• История и развитие SQL
• PostgreSQL: мощная СУБД с расширенными возможностями и гибкостью
• SQL PIVOT: преобразование строк в столбцы для мощной аналитики
• Оптимизация SQL запросов
• LEFT JOIN в SQL: полное руководство с примерами и оптимизацией
• Как устранить распространенные ошибки в SQL-запросах: руководство