SQL (Часть 2)

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

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

👉Ссылка на прошлую статью✍

👉Ссылка на мой канал в телеграмм✍

Теперь попробуем распределить по уровням аналитиков, какие знания должны быть

2. DQL (Data Query Language)

3. DML (Data Manipulation Language)

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

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

В рамках практики поработаем с традиционными таблицами Employees (Сотрудники), Departments (Отделы).

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

Employees:

Departments:

Давайте предварительно создадим таблицы 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);

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

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;

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

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

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

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

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

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;

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

Увеличить зарплату сотрудника с 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;

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

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

```sql

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. В следующей части затрону, то что нужно знать мидлу, и если останется время сеньору. До следующей встречи!