Уже больше 6 лет на собеседованиях кандидатам на должность разработчика баз данных дают одни и те же задачи и их неправильно решают.
В этой статье приведены варианты решения этих 6-ти задач.
Продолжай читать, это будет полезно и интересно.
Можете копировать сразу из статьи код и запускать в своем десктопном или онлайн SQL редакторе.
Задача 1. Удалить дубли из таблицы.
Имеется таблица TBL состоящая из одного поля fld
fld
1
2
3
3
4
5
Необходимо удалить дублирующие по значению записи из таблицы TBL, что бы остались только уникальные, с помощью запроса SQL
.
Решение Задачи 1.
--Шаг_1. Удаляем таблицу, если она существует. Создаем таблицу и наполняем ее данными.
DROP TABLE if EXISTS TBL;
CREATE TABLE TBL ( fld INT );
INSERT INTO TBL (fld) VALUES (1), (2), (3), (3), (4), (5);
/*Шаг_2. Удалям дублирующие значения из таблицы TBL используя оконную функцию ROW_NUMBER() для нумерации строк, сгруппированных по значению столбца fld. Затем удаляем строки с вхождением больше одного раза*/
WITH CTE AS (
SELECT fld,
ROW_NUMBER() OVER (PARTITION BY fld ORDER BY fld) AS row_num
FROM TBL
)
DELETE FROM CTE
WHERE row_num > 1;
--Шаг_3. Проверяем полученный результат
SELECT * FROM TBL;
Результат по Задаче 1.
Задача 2. Рассчитать сумму в рублях.
Имеется две таблицы:
Проводки (CARRY)
Дата Код валюты Сумма в валюте Сумма в рублях
05.02.2023 USD 100
07.02.2023 USD 200
11.02.2023 USD 300
Курсы валют (CURRENCY_RATE)
Дата Код валюты Курс
05.02.2023 USD 61
06.02.2023 USD 62
11.02.2023 USD 63
Необходимо для каждой проводки в таблице CARRY рассчитать соответствующую Сумму в рублях с помощью запроса SQL
Решение Задачи 2.
--Шаг_1. Удаляем таблицы, если они существуют. Создаем таблицы и наполняем их данными.
DROP TABLE if EXISTS CARRY;
DROP TABLE if EXISTS CURRENCY_RATE;
CREATE TABLE CARRY ( Дата DATE, Код_валюты VARCHAR(3), Сумма_в_валюте INT, Сумма_в_рублях INT );
INSERT INTO CARRY (Дата, Код_валюты, Сумма_в_валюте) VALUES ('2017-02-05', 'USD', 100), ('2017-02-07', 'USD', 200), ('2017-02-11', 'USD', 300);
CREATE TABLE CURRENCY_RATE ( Дата DATE, Код_валюты VARCHAR(3), Курс INT );
INSERT INTO CURRENCY_RATE (Дата, Код_валюты, Курс) VALUES ('2017-02-05', 'USD', 61), ('2017-02-06', 'USD', 62), ('2017-02-11', 'USD', 63);
/* Шаг_2.Вычисляем и записываем значение Сумма_в_рублях для таблицы CARRY
Подзапрос выбирает верхнюю первую запись (самую близкую прошедшую дату) из таблицы CURRENCY_RATE для каждой проводки в таблице CARRY. Подзапрос проверяет, что Код_валюты в таблице CURRENCY_RATE соответствует Код_валюты в таблице CARRY, а также что Дата в таблице CURRENCY_RATE меньше или равна Дате в таблице CARRY. Затем подзапрос сортирует результаты в обратном порядке по Дате, чтобы получить ближайшую прошедшую дату.*/
UPDATE CARRY SET Сумма_в_рублях = Сумма_в_валюте * ( SELECT TOP 1 Курс FROM CURRENCY_RATE WHERE CURRENCY_RATE.Код_валюты = CARRY.Код_валюты AND CURRENCY_RATE.Дата <= CARRY.Дата ORDER BY CURRENCY_RATE.Дата DESC ) FROM CARRY;
-- Проверяем результат:
SELECT * FROM CARRY;
Результат по Задаче 2
Задача 3. Анализ по Иерархии.
Имеется таблица Сотрудники (Employee).
Код сотрудника Код непосредственного руководителя Оклад
1 100
2 1 200
3 2 150
Необходимо вывести в запросе SQL все сочетания сотрудников, когда оклад руководителя (необязательно непосредственного) меньше чем оклад сотрудника.
Решение Задачи 3
--Шаг_1. Удаляем таблицы, если они существуют. Создаем таблицы и наполняем их данными.
DROP TABLE IF EXISTS Employee;
CREATE TABLE Employee (
id INTEGER NOT NULL PRIMARY KEY,
id_boss INTEGER FOREIGN KEY REFERENCES Employee(id),
salary SMALLMONEY);
INSERT INTO Employee(id, salary)
values (1, 100);
INSERT INTO Employee(id, id_boss, salary)
values (2, 1, 200);
INSERT INTO Employee(id, id_boss, salary)
values (3, 2, 150);
/* Шаг 2. Выводим в запросе SQL все сочетания сотрудников, когда оклад руководителя (необязательно непосредственного) меньше чем оклад сотрудника. */
WITH Employee_tree (id, id_boss, salary, min_salary) AS (
SELECT id, id_boss, salary, salary
FROM Employee
WHERE id_boss IS NULL
UNION ALL
SELECT e.id, e.id_boss, e.salary, IIF(et.salary < et.min_salary, et.salary, et.min_salary)
FROM Employee AS e
INNER JOIN Employee_tree AS et ON e.id_boss = et.id
)
SELECT id, salary
FROM Employee_tree
WHERE min_salary < salary;
Результат по Задаче 3.
Задача 4. Построение диапазонов.
Имеется таблица TBL
fld
1
2
5
6
7
9
Необходимо в одном запросе SQL вывести все диапазоны, внутри которых значение поля fld непрерывно (по значению)
Решение задачи 4.
/* Шаг_1. Удаляем таблицу, если она существует. Создаем таблицу и наполняем ее данными.*/
DROP TABLE if EXISTS TBL;
CREATE TABLE TBL ( fld INT );
INSERT INTO TBL (fld) VALUES (1), (2), (5), (6), (7), (9);
/*Шаг_2. Используется обобщенное табличное выражение (CTE) для создания идентификатора ранга и нумерации значений в поле "fld" в порядке возрастания. Затем мы выводим минимальное и максимальное значение "fld" для каждого ранга, группируя результаты по рангу. Результаты сортируются по значению "fld from"*/
WITH cte AS
(SELECT fld, ROW_NUMBER() OVER(ORDER BY fld) - fld AS rnk FROM tbl )
SELECT MIN(fld) AS [fld from], MAX(fld) AS [fld to] FROM cte GROUP BY rnk ORDER BY [fld from];
Результат по Задаче 4.
Задача 5.
В БД есть таблица Salaries, в которой у каждого сотрудника есть идентификатор, а также столбец для идентификатора отдела и справочник по отделу.
Напишите SQL-запрос, чтобы найти сотрудников, которые получают две самые высокие зарплаты в каждом отделе с указанием названия отдела.
Salary:
+----+-------+--------+-----------+
Id Name Salary DepartmentId
+----+-------+--------+-----------+
| 1 | Petr | 85000 | 1 |
| 2 | Ivan | 80000 | 2 |
| 3 | Alex | 60000 | 2 |
| 4 | Den | 90000 | 1 |
| 5 | Bob | 69000 | 1 |
| 6 | Kir | 87000 | 1 |
| 7 | Mike | 76000 | 1 |
Department
+--------+-----------+
DepartmentId DepartmentName
+--------+-----------+
| 1 | IT |
| 2 | Sales |
Решение Задачи 5
/*Шаг_1. Удаляем таблицы, если они существуют. Создаём таблицы и наполняем их данными.*/
DROP TABLE if EXISTS Salary;
DROP TABLE if EXISTS Department;
CREATE TABLE Salary ( Id INT, Name VARCHAR(50), Salary INT, DepartmentId INT );
INSERT INTO Salary (Id, Name, Salary, DepartmentId) VALUES (1, 'Petr', 85000, 1), (2, 'Ivan', 80000, 2), (3, 'Alex', 60000, 2), (4, 'Den', 90000, 1), (5, 'Bob', 69000, 1), (6, 'Kir', 87000, 1), (7, 'Mike', 76000, 1);
CREATE TABLE Department ( DepartmentId INT, DepartmentName VARCHAR(50) ); INSERT INTO Department (DepartmentId, DepartmentName) VALUES (1, 'IT'), (2, 'Sales');
/*Шаг_2. В этом запросе мы используем оконную функцию ROW_NUMBER, чтобы пронумеровать сотрудников в каждом отделе в порядке убывания их зарплат. Затем мы объединяем результаты со справочником Department используя JOIN и фильтруем записи, чтобы выбрать только те, у которых номер rn меньше или равен 2.*/
SELECT d.DepartmentName, s.Name, s.Salary FROM ( SELECT *, ROW_NUMBER() OVER(PARTITION BY DepartmentId ORDER BY Salary DESC) AS rn FROM Salary ) AS s JOIN Department AS d ON s.DepartmentId = d.DepartmentId WHERE s.rn <= 2;
Результат по Задаче 5.
Задача 6.
Есть таблица с двумя полями Id и Timestamp, где
Id- возрастающая последовательность, каждая вставка новой записи в таблицу приводит к генерации ID(n)=ID(n-1) + 1
Timestamp – временная метка, в стандартном процессе текущее время, при вставке задним числом может принимать любые значения меньше максимума времени всех предыдущих записей
Вставка задним числом – операция вставки записи в таблицу при которой
ID(n) > ID(n-1)
Timestamp(n) < max(timestamp(1):timestamp(n-1))
Пример таблицы
| 1 | 2016.09.11 |
| 2 | 2016.09.12 |
| 3 | 2016.09.13 |
| 4 | 2016.09.14 |
| 5 | 2016.09.09 |
| 6 | 2016.09.08 |
| 7 | 2016.09.15 |
Написать код SQL, который будет возвращать список всех id, подходящих под определение вставки задним числом.
Решение задачи 6.
--Шаг_1. Удаляем таблицу, если она существует. Создаем таблицу и наполняем ее данными.
DROP TABLE if EXISTS MyTable;
CREATE TABLE MyTable ( Id INT, Timestamp DATE );
INSERT INTO MyTable (Id, Timestamp) VALUES (1, '2016-09-11'), (2, '2016-09-12'), (3, '2016-09-13'), (4, '2016-09-14'), (5, '2016-09-09'), (6, '2016-09-08'), (7, '2016-09-15');
--Шаг_2. Возвращаем список ИД, подходящих под определение вставки задним числом
SELECT table_base.[Id] FROM
(SELECT
tt.[Id]
,tt.[Timestamp]
,max (tt.Timestamp) over ( order by Id rows between unbounded preceding and 1 preceding) as min_data
FROM MyTable as tt
) as table_base
WHERE table_base.[Timestamp] < table_base.[min_data]
Результат по Задаче 6.
Благодарю за прочтение статьи. Если хочешь детальнее подготовиться к собеседованию или экзамену по SQL воспользуйся этим обучающим ресурсом: https://stolyarovf.ru/