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

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

В моем канале вы найдете подробный разбор SQL задач с собеседований, полезные гайды и уроки для программистов

А здесь целая папка бесплатных полезных ресурсов и каналов.

Поехали!

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

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

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

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

Как вы можете видеть, желаемый результат заключается в том, чтобы 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 таблицы, называемые 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

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

Delete from sales where ImportLogId = @ImportLogid

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

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

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

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

CREATE INDEX IX_WholesalerSale_ImportSaleId ON WholesalerSales (ImportSaleId);

У меня есть таблица под названием 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;

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

Удалите дублирующие данные из таблицы 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]

У меня есть текущая таблица со столбцами даты и количества. Я хочу получить желаемую таблицу, как показано на рисунке ниже. Уточнение: мне нужно получить общее количество для каждого подъёма, за исключением 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

У меня есть текущая таблица с 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

У меня есть текущая таблица с 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;

У меня есть текущая таблица со столбцами 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;

У меня есть текущая таблица со столбцами 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

У меня есть таблица под названием 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

Статья была взята отсюда:

uproger.com

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

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

Задание 1

Решение

Задание 2

Решение

Задание 3

Решение

Задание 4

Решение

Задание 5

Решение

Задание 6

Решение

Задание 7

Решение

Задание 8

Решение

Задание 9

Решение

Задание 10

Решение

Задание 11

Решение

Задание 12

Решение

Задание 13

Решение