SQL на практике: ставим PostgreSQL и пишем первые запросы

Всем привет!

Пролог

Так вышло, что я стал преподавателем курса по системному анализу в открытой школе от Цифровой академии — курс завершился 7 декабря. Эта статья пригодится всем, кто хочет поэкспериментировать с SQL на своём локальном ПК — на одной из самых популярных СУБД, PostgreSQL.

Никаких таинств, говорю всё сразу:

Открытая школа — это формат бесплатного интенсивного обучения для «молодых» специалистов с небольшим опытом по специализации. Формат применяют ГК Иннотех, Т1 групп и Цифровая академия. Лучшие получают оффера, остальные — знания.
О себе: я директор по управлению проектами, а также преподаватель открытой школы системных аналитиков и Data Engineer.
Цель статьи — практика SQL. Установка PostgreSQL и подключение через Docker вторичны: экспериментировать можно и на стенде работодателя, и на открытых ресурсах.

Что такое SQL и точно ли он мне нужен?

Всё зависит от твоей роли в команде, проекта и прочего — ведь такой ответ ты хочешь услышать?

А по делу: если ты в ИТ — SQL тебе точно нужен. Уровень колеблется в зависимости от роли и грейда, а про технические собеседования я вообще молчу.

В одной из компаний я провёл небольшое исследование — сопоставил потребность в SQL с ролью и грейдом. Это моё видение, на истину в последней инстанции не претендую.

Итого: SQL важен и нужен. Вот только как его прокачать, когда вокруг одни шпаргалки, а цельного гайда от А до Я нет — тем более бесплатного?) Попробую слегка помочь. Сегодня — установка, практика чуть позже.

Установка и настройка

Перейдём к делу! Чтобы подготовиться к собеседованию…

(Пошаговые скриншоты установки из оригинального поста здесь опущены.)

После 15 шагов у тебя установлен PostgreSQL, и можно сколько угодно экспериментировать со стендом. Поздравляю с первым шагом!

Немного о том, что нужно знать аналитику

Когда создан SQL, его нотации, стандарты, гора теории — не уверен, что это нужно тебе прямо сейчас. А вот практика нужна точно! Теорию не отрицаю: она показывает, насколько глубоко ты знаешь язык, и расширяет технический кругозор — постараюсь дать её как-нибудь позже. Но пока давай практиковаться.

Возможно, у тебя завтра собеседование, а ты ещё не готов и второпях ищешь нужное — а в обилии материалов попадается уйма мусора, и уходит куча времени. Или ты уже действующий специалист, которому нужно погрузиться в SQL, но под рукой нет внятного пособия. Плацебо обещать не буду, но хочу дать понятный туториал, где каждый возьмёт нужное.

Статья для начинающих и мидловых специалистов, сеньорам может быть скучно.

Что нужно знать аналитику?

Основы SQL. Знание основных операторов SQL, таких как CRUD: SELECT, INSERT, UPDATE, DELETE — для работы с данными.
Фильтрация и сортировка данных. Использование условий WHERE для фильтрации, ORDER BY для сортировки данных.
Соединение таблиц. Понимание различных типов соединений (JOIN), таких как INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, для объединения данных из разных таблиц.
Группировка данных. Использование GROUP BY и агрегатных функций (COUNT, SUM, AVG, MAX, MIN) для группирования данных и выполнения вычислений на группах.
Подзапросы и вложенные запросы. Использование подзапросов для создания сложных запросов.
Оконные функции. Применение оконных функций, таких как ROW_NUMBER, RANK, для выполнения расчётов в рамках определённого набора строк («окон»).
Оптимизация запросов. Понимание индексов, планов выполнения запросов и других аспектов для оптимизации производительности запросов.
Транзакции и управление версиями. Основы работы с транзакциями, уровни изоляции транзакций для обеспечения целостности данных. Senior!!!
Безопасность данных. Понимание ролей, разрешений и других аспектов безопасности для защиты данных. Senior!!! (А может вообще не пригодиться.)
Работа с разными СУБД. Осведомлённость о различиях в синтаксисе и функциональности между разными системами управления базами данных, такими как MySQL, PostgreSQL, Oracle, SQL Server.

Уровни аналитиков и SQL

Теперь разложим знания по уровням аналитиков.

Junior System Analyst

Основы SQL (CRUD);
Фильтрация и сортировка данных (WHERE);
Соединение таблиц (JOIN);
Простые агрегатные функции;
Основы работы с транзакциями.

Middle System Analyst

Продвинутое соединение таблиц (LEFT, RIGHT, FULL JOIN...);
Группировка данных в сочетании с агрегатными функциями;
Подзапросы;
Оконные функции;
Основы оптимизации запросов;
Безопасность данных.

Senior System Analyst

Продвинутая оптимизация запросов;
Расширенные транзакции и управление версиями;
Расширенная безопасность данных;
Работа с разными СУБД;
Практика в решении сложных аналитических задач.

Операторы языка по категориям

Теперь чётко разделим операторы языка по категориям и опишем каждый.

1. DDL (Data Definition Language)

CREATE: создание новых таблиц, представлений, индексов и т.д.
ALTER: изменение структуры существующих таблиц.
DROP: удаление таблиц, представлений, индексов и т.д.
TRUNCATE: очистка всех строк из таблицы, сохраняя её структуру.

2. DQL (Data Query Language)

SELECT: извлечение данных из базы данных.

3. DML (Data Manipulation Language)

INSERT: вставка новых строк в таблицу.
UPDATE: обновление существующих строк в таблице.
DELETE: удаление строк из таблицы.

4. DCL (Data Control Language) — АНАЛИТИКИ НЕ ИСПОЛЬЗУЮТ!

GRANT: предоставление прав доступа пользователям.
REVOKE: отзыв прав доступа у пользователей.

5. TCL (Transaction Control Language) — В РАМКАХ NoSQL не работает!

COMMIT: фиксация всех изменений, сделанных в транзакции.
ROLLBACK: отмена всех изменений, сделанных в текущей транзакции.
SAVEPOINT: установка точек, к которым можно вернуться в рамках транзакции.
SET TRANSACTION: установка свойств для транзакции.

Практика!

Для практики возьмём традиционные таблицы Employees (Сотрудники) и Departments (Отделы).

Логическая модель данных

Employees:

EmployeeID (ID сотрудника);
FIO (ФИО);
DepartmentID (ID отдела);
Salary (Зарплата).

Departments:

DepartmentID (ID отдела);
DepartmentName (Название отдела).

0. Подготовительные работы

Сначала создадим таблицы Employees и Departments и наполним их тестовыми данными по логической модели.

STEP 1. Создание таблицы отделов — Departments

CREATE TABLE Departments (
  DepartmentID INT PRIMARY KEY,
  DepartmentName VARCHAR(100)
);

STEP 2. Создание таблицы сотрудников — Employees

CREATE TABLE Employees (
  EmployeeID INT PRIMARY KEY,
  FIO VARCHAR(150),
  DepartmentID INT,
  Salary DECIMAL(10, 2),
  FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

STEP 3. Вставка данных в таблицу отделов — Departments

INSERT INTO Departments (DepartmentID, DepartmentName) VALUES
(1, 'IT'),
(2, 'Human Resources'),
(3, 'Finance'),
(4, 'Marketing'),
(5, 'Sales'),
(6, 'Research and Development'),
(7, 'Customer Service'),
(8, 'Legal'),
(9, 'Administration'),
(10, 'Production');

STEP 4. Вставка данных в таблицу — Employees

INSERT INTO Employees (EmployeeID, FIO, DepartmentID, Salary) VALUES
(1, 'Иванов Иван Иванович', 1, 50000),
(2, 'Петров Петр Петрович', 2, 45000),
(3, 'Сидоров Сидор Сидорович', 3, 48000),
(4, 'Алексеев Алексей Алексеевич', 1, 52000),
(5, 'Николаев Николай Николаевич', 4, 43000),
(6, 'Васильев Василий Васильевич', 5, 47000),
(7, 'Максимов Максим Максимович', 6, 51000),
(8, 'Григорьев Григорий Григорьевич', 7, 45000),
(9, 'Дмитриев Дмитрий Дмитриевич', 8, 49000),
(10, 'Егоров Егор Егорович', 9, 44000);

1. Основы SQL

А теперь — к тому, что должен знать любой аналитик, да и вообще любой ИТ-специалист.

SELECT. Получить все данные о сотрудниках (* — выводит все столбцы):

SELECT * FROM Employees;

Получить ФИО, зарплату сотрудников (прямое указание нужных атрибутов, выводит только нужные):

SELECT fio, salary FROM Employees;

INSERT. Добавить нового сотрудника:

INSERT INTO Employees (EmployeeID, Name, DepartmentID, Salary) VALUES (11, 'Иванов Иван Акатьевич', 3, 50000);

После добавления сотрудника снова сделай SELECT и убедись, что он создан.

UPDATE. Увеличить зарплату сотруднику с ID 11 на 5000:

UPDATE Employees SET Salary = Salary + 5000 WHERE EmployeeID = 11;

После обновления снова сделай SELECT и убедись, что данные изменились.

DELETE. Удалить сотрудника с ID 11:

DELETE FROM Employees WHERE EmployeeID = 123;

2. Фильтрация и сортировка данных

Получить имена сотрудников с зарплатой более 30000, отсортированные по зарплате в порядке убывания:

SELECT Name
FROM Employees
WHERE Salary > 30000
ORDER BY Salary DESC;

3. Соединение таблиц / объединение или работа с множествами

Получить список сотрудников вместе с названиями их отделов:

SELECT E.Name, D.DepartmentName
FROM Employees E
INNER JOIN Departments D ON E.DepartmentID = D.DepartmentID;

4. Простые агрегатные функции

Получить среднюю зарплату по каждому отделу:

SELECT D.DepartmentName, AVG(E.Salary) AS AverageSalary
FROM Employees E
INNER JOIN Departments D ON E.DepartmentID = D.DepartmentID
GROUP BY D.DepartmentName;

!!! Запомни: если рядом с агрегатной функцией выводишь дополнительные столбцы, по ним обязательно нужна группировка — без GROUP BY запрос просто не заработает.

Получить просто среднюю зарплату:

SELECT AVG(E.Salary) AS AverageSalary
FROM Employees E
INNER JOIN Departments D ON E.DepartmentID = D.DepartmentID;

!!! Запомни: а если выводишь только значение агрегатной функции, без дополнительных столбцов, — GROUP BY не нужен.

Получить суммарную зарплату по каждому отделу:

SELECT D.DepartmentName, SUM(E.Salary) AS SumSalary
FROM Employees E
INNER JOIN Departments D ON E.DepartmentID = D.DepartmentID
GROUP BY D.DepartmentName;

5. Транзакции (ACID)

Базовая транзакция

Увеличить зарплату сотрудника с EmployeeID = 1 на 10000 и одновременно уменьшить зарплату сотрудника с EmployeeID = 2 на 10000, обеспечивая, чтобы обе операции были выполнены как одна транзакция.

START TRANSACTION;
UPDATE Employees SET Salary = Salary + 10000 WHERE EmployeeID = 1;
UPDATE Employees SET Salary = Salary - 10000 WHERE EmployeeID = 2;
COMMIT;

Транзакция с откатом

Попробуем выполнить те же обновления, что и в предыдущем примере, но сделаем откат транзакции, если зарплата сотрудника уходит в минус.

START TRANSACTION;

UPDATE Employees SET Salary = Salary + 10000 WHERE EmployeeID = 1;

-- Предположим, что зарплата сотрудника с EmployeeID = 2 становится отрицательной
-- Здесь должна быть проверка, но для примера просто делаем откат
ROLLBACK;

-- Если бы проверка показала, что все в порядке, то использовался бы COMMIT.

Использование SAVEPOINT

В рамках одной транзакции изменить зарплаты нескольких сотрудников, но при ошибке в одном из обновлений откатиться к определённой точке, а не отменять всю транзакцию.

START TRANSACTION;

SAVEPOINT Savepoint1;
UPDATE Employees SET Salary = Salary + 5000 WHERE EmployeeID = 3;

SAVEPOINT Savepoint2;
UPDATE Employees SET Salary = Salary - 2000 WHERE EmployeeID = 4;

-- Предположим, что обновление для EmployeeID = 4 вызвало ошибку
ROLLBACK TO Savepoint1;

COMMIT;

Фух! Жду обратной связи — что получилось у тебя. Если понадобится, добавлю скрины, как всё должно выглядеть, или внесу правки. Ставь PostgreSQL и практикуйся. В следующей части разберу то, что нужно мидлу, а если останется время — и сеньору. До встречи!

Из моего Telegram-канала @it_underside.

Ваш ДПУПП