13 упражнений по SQL с решениями

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

В моем телеграм канале по базам данных, вы найдете много полезных библиотек и разборов вопросов с собеседований.

https://t.me/addlist/_FjtIq8qMhU0NTYy – а здесь целая полезная папка полезного, для разработчика.

Задание 1

Дана таблица событий. Я хотел бы исключить определённые значения, которые появляются несколько раз. EMI должен появиться только один раз и только первый EMI с 23 марта. Другие дубликаты, такие как EMD, могут остаться.

Вот запрос для создания таблицы и вставки данных:

DROP TABLE IF EXISTS #Movements
CREATE TABLE #Movements 
(
    CreateDate date,
    EventCode nvarchar(3)
)

INSERT INTO #Movements (CreateDate,EventCode) VALUES ('2023-03-20', 'EMA')
INSERT INTO #Movements (CreateDate,EventCode) VALUES ('2023-03-21', 'EMD')
INSERT INTO #Movements (CreateDate,EventCode) VALUES ('2023-03-22', 'EMD')
INSERT INTO #Movements (CreateDate,EventCode) VALUES ('2023-03-22', 'EMF')
INSERT INTO #Movements (CreateDate,EventCode) VALUES ('2023-03-23', 'EMI')
INSERT INTO #Movements (CreateDate,EventCode) VALUES ('2023-03-24', 'EMI')
INSERT INTO #Movements (CreateDate,EventCode) VALUES ('2023-03-25', 'EMI')
INSERT INTO #Movements (CreateDate,EventCode) VALUES ('2023-03-26', 'EMI')

SELECT * FROM #Movements

Решение

Этот запрос решает представленную выше задачи:

select t.*
from #Movements t
LEFT JOIN (
  SELECT EventCode, MIN(CreateDate) as CreateDate
  FROM #Movements
  WHERE EventCode = 'EMI'
  GROUP BY EventCode
) s on s.EventCode = t.EventCode and t.CreateDate > s.CreateDate
where s.CreateDate is null

Задание 2

Дана таблица с данными о ценах продажи товаров за разные даты.

Как вы можете видеть, желаемый результат заключается в том, чтобы DataEnd была на один день меньше даты следующего изменения цены.

Вот запрос для создания таблицы и вставки данных:

create table ##sales(
 item int,
 date date,
 price int
)

insert into ##sales(item, date, price) 
values
(1,'2021-05-01', 200),
(1,'2021-06-11', 210),
(1,'2021-06-27', 225),
(1,'2021-08-01', 250),
(2,'2021-02-10', 600),
(2,'2021-04-21', 650),
(2,'2021-06-17', 675),
(2,'2021-07-23', 700)

Решение

Этот простейший запрос является решением представленной выше задачи:

select item, date as DateStart, price, 
    lead(date,1, GETDATE()) over (partition by item order by date ) DateEnd
from ##sales

Задание 3

У меня есть 3 таблицы, называемые tables, years и codes. Результаты на рисунке ниже приведены для этих трёх таблиц.

Вот запрос для создания таблицы и вставки данных:

declare @tables table (year int, code int, import decimal(5,2))
insert into @tables values
(2019,390107,10.00),
(2021,390107,175.00),
(2022,390107,102.00),
(2022,470101,101.00),
(2022,53015101,140.00)

declare @years table (year int)
insert into @years values
(2018),
(2019),
(2020),
(2021),
(2022)

declare @codes table (code int)
insert into @codes values
(390107),
(470101),
(470103),
(471103),
(53010101),
(53015101)

Я хочу сделать запрос, который возвращает import для каждого года и для каждого кода, содержащегося в следующих таблицах (return import = 0, где нет записи для определённой комбинации года и кода).

Имея 6 codes и 5 years, я ожидаю 30 записей (по одной для каждой комбинации года и кода) вместе с соответствующим значением import из “@table” для этой комбинации year/ code(или 0, если комбинация не найдена).

Решение

Этот запрос вычисляет общий объём импорта для каждого кода за каждый год в таблицах @years и @codes, используя данные из таблицы @table. Он делает это, используя перекрёстное соединение (CROSS JOIN) для объединения данных из таблиц @years и @codes, а затем использует LEFT JOIN для ввода данных импорта из @table.

SELECT Y.year,
       C.code,
    ISNULL(T.import, 0) AS import
FROM   @years Y
       CROSS JOIN @codes C
       LEFT JOIN @table T
       ON T.year = Y.year AND T.code = C.code 
       order by y.year

Задание 4

Операция удаления выполняется медленно, и перестройка индекса, похоже, не решает проблему. У меня есть простой запрос на удаление:

Delete from sales
where ImportLogId = @ImportLogid

Таблица ImportLog содержит около 3 миллионов записей. У меня есть некластеризованный индекс, созданный в таблице ImportLog в ImportLogID. Фрагментация составляет менее 10%, но всё равно выполнение запроса занимает больше времени, а когда я перестраиваю индекс вручную, он выполняется в течение секунды. Через день та же проблема повторяется.

Вот план выполнения в поиске лучшего решения данной задачи:

Решение

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

Проиндексируйте столбец внешнего ключа в WholesalerSale, чтобы ускорить эту проверку:

CREATE INDEX IX_WholesalerSale_ImportSaleId ON WholesalerSales (ImportSaleId);

Задание 5

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

Вот запрос для создания таблицы и вставки данных:

create table ##Employee (
 id int identity(1,1) primary key,
 name varchar(255),
 paymenttype varchar(255),
 payment bigint
)

insert into ##Employee (name, paymenttype, payment)
values 
('John  ',       'Salary' ,     100),
('Peter ',       'Salary' ,     100),
('John  ',       'Bonus ' ,     20 ),
('Russel',       'Salary' ,     100),
('Bill  ',       'Salary' ,     100),
('Bill  ',       'Bonus ' ,     40 ),
('John  ',       'Salary' ,     100)

Решение

Этот запрос вычисляет общую сумму заработной платы и бонусов для каждого сотрудника в таблице ##Employee. Он делает это с помощью функции SUM в сочетании с регистровым выражением CASE. Выражение CASE проверяет значение столбца PaymentType для каждой строки и возвращает значение столбца Payment, если типом платежа является либо ‘Зарплата’, либо ‘Бонус’, в противном случае оно возвращает 0.

SELECT
    Name,
    SUM(CASE WHEN PaymentType = 'Salary' THEN Payment ELSE 0 END) AS Salary,
    SUM(CASE WHEN PaymentType = 'Bonus'  THEN Payment ELSE 0 END) AS Bonus
FROM ##Employee
GROUP BY Name;

Задание 6

У меня есть две таблицы под названием Ingresaron и Salieron:

Мне надо получить такой результат. Как это сделать?

Вот запрос для создания таблицы и вставки данных:

create table Ingresaron (DepartmentId int,Fecha_Lunes date,Entraron int);
insert into Ingresaron (DepartmentId,Fecha_Lunes,Entraron) values
(26,'2022-08-01',1),
(26,'2022-08-15',2),
(26,'2022-08-22',3), 
(26,'2022-08-08',3);

create table Salieron (DepartmentId int,Fecha_Lunes date,Salieron int);
insert into Salieron (DepartmentId,Fecha_Lunes,Salieron) values
(26,'2022-08-15',3),
(26,'2022-08-22',4),
(26,'2022-08-08',2),
(26,'2022-08-29',1);

Решение

Этот запрос использует оператор FULL JOIN для объединения данных из таблиц ingresaron и Salieron. Оператор FULL JOIN вернёт все строки из обеих таблиц, даже если в другой таблице нет совпадающих строк.

select coalesce(ing.DepartmentId, s.DepartmentId) as DepartmentId,
   coalesce(ing.Fecha_lunes, s.Fecha_lunes) as Fecha_lunes,
   s.Salieron,
   ing.Entraron
from ingresaron ing
full join Salieron s 
on ing.DepartmentId = s.DepartmentId and ing.Fecha_lunes = s.Fecha_lunes;

Задание 7

Удалите дублирующие данные из таблицы person.

Решение

Этот запрос использует общее табличное выражение (CTE) для удаления дубликатов из таблицы person. CTE выбирает столбец name и использует функцию ROW_NUMBER для присвоения уникального номера каждой строке в каждом разделе name. Затем запрос удаляет все строки, в которых столбец row_numbers больше 1. Это эффективно удаляет все повторяющиеся строки для заданного значения имени. Наконец, запрос выбирает оставшиеся значения имён из таблицы person.

with cte as(
 SELECT 
  [name] 
  ,row_numbers=ROW_NUMBER() OVER(PARTITION BY [name] ORDER BY [name])
 FROM [dbo].[person]
)

DELETE FROM CTE WHERE row_numbers > 1
select name from [dbo].[person]

Задание 8

У меня есть текущая таблица со столбцами даты и количества. Я хочу получить желаемую таблицу, как показано на рисунке ниже. Уточнение: мне нужно получить общее количество для каждого подъёма, за исключением 2022-12-01 на эту дату.

Вот тестовый код:

Create Table #table_name([date] DATE, [count] INT);

Insert Into #table_name Values
('2022-12-04',1),
('2022-12-03',2),
('2022-12-02',1),
('2022-12-01',3),
('2022-11-30',1),
('2022-11-29',1),
('2022-11-28',1),
('2022-11-27',2);

Решение

Это запрос, который выбирает столбцы dt и count_sum, причём dt является производным столбцом, который является либо полной датой, либо только месяцем и годом значения даты в столбце date, в зависимости от того, является ли день даты первым днём месяца.

Если днём значения date является первый день месяца, date преобразуется в строку вида 'YYYY-MM-DD' с помощью функции Cast. Если день не является первым днем месяца, функция Format используется для преобразования значения date в строку вида 'YYYY-MM'.

Результаты подзапроса check_date затем группируются по dt, а сумма значений count вычисляется для каждой группы с помощью функции sum. Результирующие строки упорядочиваются по dt в порядке убывания с использованием предложения order by.

with check_date as
(
  select case
          when Day([date])=1
           Then Cast([date] as varchar(10))
           else Format([date], 'yyyy-MM')
          end As dt,
         [count]
  from #table_name
)
select dt, sum([count]) as count_sum  
from check_date
group by dt
order by dt desc

Задание 9

У меня есть текущая таблица с Doctor, Case_Number и Field. Мне нужна желаемая таблица из одной строки для каждого Case_Number со столбцами всех типов полей и тем, что каждый врач сделал в каждом конкретном случае.

Вот тестовый код:

DECLARE @Cases TABLE (
 CaseID INT IDENTITY, 
 Doctor NVARCHAR(50), 
 Case_Number INT, 
 Field NVARCHAR(50)
)
INSERT INTO @Cases (Doctor, Case_Number, Field) VALUES
('Brian', 2234, 'Injection'),
('Brian', 2234, 'Surgery  '),
('Flor ', 2234, 'Surgery  '),
('Flor ', 2234, 'Discharge'), 
('Brian', 1156, 'Injection'),
('Brian', 3459, 'Surgery  '),
('Flor ', 3459, 'Surgery  '),
('Brian', 3459, 'H-Test   ')

Решение

Столбцы Injection, Surgery, H-Test и Discharge выводятся с использованием функции MAX с выражением CASE. Для каждого из этих столбцов выражение CASE вычисляет значение столбца field и возвращает ‘X’, если оно совпадает с именем выбранного столбца, и ‘ ‘ (один пробел), если это не так.

Функция MAX используется для того, чтобы гарантировать, что значение ‘X’ выбрано, если оно существует, и ‘ ‘ выбрано, если его нет. Это связано с тем, что функция MAX вернёт максимальное значение среди своих аргументов, поэтому, если есть значение ‘X’, оно будет возвращено, а если нет, то вместо него будет возвращено значение ‘ ‘.

Результирующие строки группируются по столбцам doctor и case_number, а результирующий вывод будет содержать одну строку для каждой уникальной комбинации значений doctor и case_number, со столбцом для каждого из производных столбцов, показывающим, присутствовало ли соответствующее значение поля в исходной таблице @Cases.

SELECT doctor, case_number,
       MAX(CASE WHEN field = 'Injection' THEN 'X' ELSE ' ' END) AS Injection,
       MAX(CASE WHEN field = 'Surgery' THEN 'X' ELSE ' ' END  ) AS Surgery,
       MAX(CASE WHEN field = 'H-Test' THEN 'X' ELSE ' ' END   ) AS [H-Test],
       MAX(CASE WHEN field = 'Discharge' THEN 'X' ELSE ' ' END) AS Discharge
  FROM @Cases
GROUP BY doctor, case_number

Задание 10

У меня есть текущая таблица с firstDate, LastDate и Code. Я хочу получить желаемую таблицу, как показано на рисунке ниже. Чтобы было понятно, объедините все строки с аналогичным кодом, взяв первую дату из 1-й строки и последнюю дату из последней строки.

CREATE TABLE ABCD(
    id int,
    FirstDate date,
    LastDate date,
    code varchar(23)
);

Insert into ABCD VALUES
(1, '2022-12-12','2022-12-12', 'A'),
(2, '2022-12-13','2022-12-13', 'A'),
(3, '2022-12-15','2022-12-15', 'A'),
(4, '2022-12-16','2022-2-16', 'B'),
(5, '2022-12-18','2022-12-18', 'A'),
(5, '2022-12-19','2022-12-19', 'A'),
(6, '2022-12-20','2022-12-20', 'C')

Решение

Вот решение:

WITH ABCD_with_newvals AS
  (SELECT *, 
         CASE WHEN LAG(code, 1) OVER (ORDER BY id) = [code] THEN 0 ELSE 1 END AS NewVal
  FROM ABCD
  ),
  ABCD_grouped AS
  (SELECT *,
         SUM(NewVal) OVER (ORDER BY id) AS GroupNum
  FROM ABCD_with_newvals
  )
SELECT GroupNum, Code, MIN(FirstDate) AS FirstDate, MAX(LastDate) AS LastDate
  FROM ABCD_grouped
  GROUP BY GroupNum, Code
  ORDER BY GroupNum;

Задание 11

У меня есть текущая таблица со столбцами countx, code и col3. Я хочу получить желаемую таблицу. Чтобы внести ясность, нужно провести группировку на основе столбца кода и суммировать количество строк по столбцу кода.

Вот тестовый код:

CREATE TABLE test(
    countx int,
    code char(1),
    col3 char(3)
);

insert into test values
(2,'A','xyz'),
(3,'A','1'),
(4,'A','f'),
(4,'B','d'),
(5,'B','2'),
(6,'B','yz')

Решение

В этом запросе используется оконная функция SUM() с предложением OVER() и предложением PARTITION BY.

Функция SUM() вычисляет сумму всех значений countx в разделе текущей строки. Функция OVER() определяет окно или набор строк в результирующем наборе запроса. Функция PARTITION BY делит строки в результирующем наборе на разделы, к которым применяется SUM().

Оператор SELECT извлекает сумму countx для каждого code в виде countx_sum, столбца code и столбца col3 из тестовой таблицы. Строки упорядочены по столбцу кода.

SELECT SUM(countx) OVER (PARTITION BY code) AS countx_sum, code, col3
FROM test
ORDER BY code;

Задание 12

У меня есть текущая таблица со столбцами id, line и cost. Я хочу получить желаемую таблицу, как показано на рисунке ниже. Чтобы было понятно, я хочу удалить последовательные повторяющиеся записи в таблице. В приведённой ниже таблице я хочу рассчитать общую стоимость без последовательного дублирования.

Например, строка 3 должна быть удалена, поскольку она последовательно дублируется строкой 2, ведь данные всех 3 столбцов одинаковы. То же самое относится и ко второй группе, строка 7 должна быть удалена, поскольку она дублирует строку 6. Общая стоимость в конце должна составить 10 долларов.

Вот тестовый код:

DECLARE @vClaims TABLE (
    ClaimID     NVARCHAR(16),
    ClaimLine   SMALLINT,
    Cost        SMALLINT
)

INSERT INTO @vClaims
VALUES
    ('M0001R1616878951', 2, 10),
    ('M0001R1616878951', 2, -10),
    ('M0001R1616878951', 2, -10),
    ('M0001R1616878951', 3, 10),
    ('M0001R1616878951', 3, -10),
    ('M0001R1616878951', 3, 10),
    ('M0001R1616878951', 3, 10)

Решение

Ниже представлено решение:

;WITH CTE_ClaimsWithSort AS (
    SELECT
        id  ,
        line,
        cost,
        RowNumber   = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
    FROM @test
), CTE_ClaimsFiltered AS (
    SELECT 
        id   ,
     line ,
     cost ,
        RowNumber,
        isDuplicate     = IIF(
                            LAG(id) OVER(ORDER BY RowNumber) = id
                            AND LAG(line) OVER(ORDER BY RowNumber) = line
                            AND LAG(cost) OVER(ORDER BY RowNumber) = cost
                        , 1, 0)
    FROM  CTE_ClaimsWithSort
)
SELECT 
    id,
 line,
 cost,
    RowNumber,
    isDuplicate
FROM   CTE_ClaimsFiltered WHERE isDuplicate = 0

Задание 13

У меня есть таблица под названием Location. Данные выглядят так, как показано на рисунке ниже. Я хочу разделить элемент данных на основе разделителя:

Вот тестовый код:

create table location( RecordId int, Location varchar(10))
insert into location select 1 , '/21/s15'
insert into location select 2   , '8/1/21c59'
insert into location select 3   , '1//'
insert into location select 4   , '9//72'

Решение

create or alter function fnBuildABC(@location varchar(10))
returns table 
as
return
select 
 Left(location, p1.v - 1) A,
 Substring(location, p1.v + 1, p2.v - p1.v - 1) B,
 Stuff(location,1, p2.v , '') C
from (select location = @Location)l
cross apply(values(CharIndex('/', location)))p1(v)
cross apply(values(CharIndex('/', location, p1.v + 1)))p2(v);

select A, B, C from Location
cross apply fnBuildABC(Location);

Вот ссылки на источники: link1, link2, link3, Link4, Link5, Link6, Link7