БД. ИТМО. Практикум санктПетербург 2020

• Название: Практикум санктПетербург 2020
• Дата: 27.11.2022
• Размер: 0.94 Mb.
• Имя файла: ИТМО.pdf
• Тип: Практикум; #814747
• страница: 3 из 5

Примеры запросов с решениями
1 Найти название продуктов и название подкатегорий этих продуктов, у которых отпускная цена больше 100, не включая случаи, когда продукт не относится ни к какой подкатегории.
SELECT P.Name, PSC.Name
FROM [Production].[Product] AS P INNER JOIN
[Production].[ProductSubcategory] AS PSC
ON P.ProductSubcategoryID=PSC.ProductSubcategoryID
WHERE [ListPrice]>100 2 Найти название продуктов и название подкатегорий этих продуктов, у которых отпускная цена больше 100, включая случаи, когда продукт не относится ни к какой категории.
SELECT P.Name, PSC.Name
FROM [Production].[Product] AS P LEFT JOIN
[Production].[ProductSubcategory] AS PSC
ON P.ProductSubcategoryID=PSC.ProductSubcategoryID
WHERE [ListPrice]>100 3 Найти название продуктов и название категорий из таблицы ProductCategory, с которой связан этот продукт, не включая случаи, когда у продукта нет подкатегории.
SELECT P.Name, PC.Name
FROM [Production].[Product] AS P INNER JOIN
[Production].[ProductSubcategory] AS PSC
ON P.ProductSubcategoryID=PSC.ProductSubcategoryID
INNER JOIN [Production].[ProductCategory] AS PC
ON PSC.ProductCategoryID=PC.ProductCategoryID
4 Найти название продукта, отпускную цену продукта, а также последнюю отпускную цену этого продукта (LAStReceiptCost), которую можно узнать из таблицы ProductVendor.
SELECT P.Name, P.ListPrice, PV.LAStReceiptCost
FROM [Production].[Product] AS P INNER JOIN
[PurchASINg].[ProductVendor] AS PV

27
ON P.ProductID=PV.ProductID
5 Найти название продукта, отпускную цену продукта, а также последнюю отпускную цену этого продукта (LAStReceiptCost), которую можно узнать из таблицы ProductVendor, для таких продуктов, у которых отпускная цена оказалась ниже последней отпускной цены у поставщика, исключив те товары, для которых отпускная цена равна нулю.
SELECT P.Name, P.ListPrice, PV.LAStReceiptCost
FROM [Production].[Product] AS P INNER JOIN
[PurchASINg].[ProductVendor] AS PV
ON P.ProductID=PV.ProductID
WHERE P.ListPrice!=0 AND P.ListPrice
6 Найти количество товаров, которые поставляют поставщики с самым низким кредитным рейтингом (CreditRatINg принимает целые значение от минимального, равного 1, до максимального, равного 5).
SELECT COUNT(DISTINCT PV.ProductID)
FROM [PurchASINg].[ProductVendor] AS PV INNER JOIN
[PurchASINg].[Vendor] AS V
ON PV.BusINessEntityID=V.BusINessEntityID
WHERE [CreditRatINg]=1 7 Найти, сколько товаров приходится на каждый кредитный рейтинг, т.е. сформировать таблицу, первая колонка которой будет содержать номер кредитного рейтинга, вторая – количество товаров, поставляемых всеми поставщиками, имеющими соответствующий кредитный рейтинг. Необходимо сформировать универсальный запрос, который будет валидным и в случае появления новых значений кредитного рейтинга.
SELECT [CreditRatINg], COUNT(DISTINCT PV.ProductID)
FROM [PurchASINg].[ProductVendor] AS PV INNER JOIN
[PurchASINg].[Vendor] AS V
ON PV.BusINessEntityID=V.BusINessEntityID
GROUP BY [CreditRatINg]
8 Найти номера первых трех подкатегорий (ProductSubcategoryID) с наибольшим количеством наименований товаров.
SELECT TOP 3 [ProductSubcategoryID]
FROM [Production].[Product]
WHERE [ProductSubcategoryID] IS NOT NULL
GROUP BY [ProductSubcategoryID]

28
ORDER BY COUNT(*) DESC
9 Получить названия первых трех подкатегорий с наибольшим количеством наименований товаров.
SELECT TOP 3 PC.ProductCategoryID
FROM [Production].[Product] AS P INNER JOIN
[Production].[ProductSubcategory] AS PSC
ON P.ProductSubcategoryID=PSC.ProductSubcategoryID
INNER JOIN [Production].[ProductCategory] AS PC
ON PSC.ProductCategoryID=PC.ProductCategoryID
GROUP BY PC.ProductCategoryID
ORDER BY COUNT(*) DESC
SELECT top 3 psc.name, count(*)
FROM [Production].[Product] AS p INner JOIN
[Production].[ProductSubcategory] AS psc
ON p.ProductSubcategoryID=psc.ProductSubcategoryID
WHERE p.ProductSubcategoryID is NOT null
GROUP BY p.ProductSubcategoryID, psc.Name
ORDER BY count(*) desc
10 Высчитать среднее количество товаров, приходящихся на одну подкатегорию, с точностью минимум до одной десятой.
Вариант 1
SELECT 1.0*COUNT(*)/COUNT(DISTINCT [ProductSubcategoryID])
FROM [Production].[Product]
WHERE [ProductSubcategoryID] IS NOT NULL
Вариант 2
SELECT
CAST(COUNT(*)
AS
FLOAT)/COUNT(DISTINCT
[ProductSubcategoryID])
FROM [Production].[Product]
WHERE [ProductSubcategoryID] IS NOT NULL
Вариант 3
SELECT cASt((cASt(count(p.ProductID)
AS float)/count(DISTINCT p.ProductSubcategoryID))
AS decimal(6,3))
FROM [Production].[Product] AS p

29
WHERE p.ProductSubcategoryID is NOT null
11 Вычислить среднее количество товаров, приходящихся на одну категорию, в целых числах.
SELECT COUNT(*)/COUNT(DISTINCT PC.ProductCategoryID)
FROM [Production].[Product] AS P INNER JOIN
[Production].[ProductSubcategory] AS PSC
ON P.ProductSubcategoryID=PSC.ProductSubcategoryID
RIGHT JOIN [Production].[ProductCategory] AS PC
ON PSC.ProductCategoryID=PC.ProductCategoryID
12 Найти количество цветов товаров, приходящихся на каждую категорию, без учета товаров, для которых цвет не определен.
SELECT COUNT(DISTINCT [Color])
FROM [Production].[Product] AS P INNER JOIN
[Production].[ProductSubcategory] AS PSC
ON P.ProductSubcategoryID=PSC.ProductSubcategoryID
RIGHT JOIN [Production].[ProductCategory] AS PC
ON PSC.ProductCategoryID=PC.ProductCategoryID
GROUP BY PC.ProductCategoryID
13 Найти средний вес продуктов. Просмотреть таблицу продуктов и убедиться, что есть продукты, для которых вес не определен. Модифицировать запрос так, чтобы при нахождении среднего веса продуктов те продукты, для которых вес не определен, считались как продукты с весом 10.
SELECT AVG(ISNULL([Weight],10))
FROM [Production].[Product]
14 Вывести названия продуктов и период их активных продаж (период между
SellStartDate и SellEndDate) в днях, отсортировав по уменьшению времени продаж. Если продажи идут до сих пор и SellEndDate не определен, то считать периодом продаж число дней с начала продаж и по текущие сутки.
SELECT [Name], [SellStartDate], [SellEndDate],
DATEDIFF(D,[SellStartDate],ISNULL([SellEndDate],GETDATE()))
FROM [Production].[Product]
WHERE [SellStartDate] IS NOT NULL
15 Разбить продукты по количеству символов в названии, и для каждой группы определить количество продуктов.
SELECT LEN([Name]), COUNT(*)

30
FROM [Production].[Product]
GROUP BY LEN([Name])
16 Найти для каждого поставщика количество подкатегорий продуктов, к которым относится продукты, поставляемые им, без учета ситуации, когда продукт не относится ни к какой подкатегории.
SELECT PV.BusINessEntityID, COUNT(DISTINCT P.ProductSubcategoryID)
FROM [Production].[Product] AS P INNER JOIN
[PurchASINg].[ProductVendor] AS PV
ON P.ProductID=PV.ProductID
WHERE P.ProductSubcategoryID IS NOT NULL
GROUP BY PV.BusINessEntityID
17 Проверить, есть ли продукты с одинаковым названием, если есть, то вывести эти названия.
Вариант 1
SELECT P1.Name
FROM [Production].[Product] AS P1,
[Production].[Product] AS P2
WHERE P1.ProductID!=P2.ProductID AND
P1.Name=P2.Name
Вариант 2
SELECT [Name]
FROM [Production].[Product]
GROUP BY [ProductID], [Name]
HAVING COUNT(*)>1 18 Найти первые 10 самых дорогих товаров, с учетом ситуации, когда цена цены у некоторых товаров могут совпадать.
SELECT TOP 10 WITH TIES [Name]
FROM [Production].[Product]
ORDER BY [ListPrice] DESC
19 Найти первые 10 процентов самых дорогих товаров, с учетом ситуации, когда цены у некоторых товаров могут совпадать.
SELECT TOP 10 PERCENT WITH TIES [Name]
FROM [Production].[Product]
ORDER BY [ListPrice] DESC

31 20 Найти первых трех поставщиков, отсортированных по количеству поставляемых товаров, с учетом ситуации, что количество поставляемых товаров может совпадать для разных поставщиков.
SELECT TOP 3 WITH TIES PV.BusINessEntityID
FROM [Production].[Product] AS P INNER JOIN
[PurchASINg].[ProductVendor] AS PV
ON P.ProductID=PV.ProductID
GROUP BY PV.BusINessEntityID
ORDER BY COUNT(P.ProductID) DESC
Задания для самостоятельной работы
1 Найти и вывести на экран название продуктов и название категорий товаров, к которым относится этот продукт, с учетом того, что в выборку попадут только товары с цветом Red и ценой не менее 100.
2 Вывести на экран названия подкатегорий с совпадающими именами.
3 Вывести на экран название категорий и количество товаров в данной категории.
4 Вывести на экран название подкатегории, а также количество товаров в данной подкатегории с учетом ситуации, что могут существовать подкатегории с одинаковыми именами.
5 Вывести на экран название первых трех подкатегорий с небольшим количеством товаров.
6 Вывести на экран название подкатегории и максимальную цену продукта с цветом Red в этой подкатегории.
7 Вывести на экран название поставщика и количество товаров, которые он поставляет.
8 Вывести на экран название товаров, которые поставляются более чем одним поставщиком.
9 Вывести на экран название самого продаваемого товара.
10 Вывести на экран название категории, товары из которой продаются наиболее активно.
11 Вывести на экран названия категорий, количество подкатегорий и количество товаров в них.
12 Вывести на экран номер кредитного рейтинга и количество товаров, поставляемых компаниями, имеющими этот кредитный рейтинг.

32
Практическая работа 4
Цель работы: использование подзапросов.
При написании запросов может возникнуть необходимость в данных, полученных на предыдущем этапе. Встает вопрос об актуальности данных.
Рассмотрим следующий пример. Необходимо найти название самого дорогого товара с цветом Red. Пользователь, не знакомый с механизмом использования подзапросов, может написать следующий набор запросов.
SELECT MAX([ListPrice]) FROM [Production].[Product]
WHERE [Color]='Red’
Данный запрос вернет таблицу, одна строка и один столбец без имени, со значением 3578,27. Подставив полученный результат в следующий запрос, пользователь получит искомые данные.
SELECT [Name]
FROM [Production].[Product]
WHERE [Color]='Red' AND [ListPrice]=3578.27
При выполнении данных операций пользователь, во-первых, вынужден выполнять промежуточные действия вручную, во-вторых, актуальность полученных данных может быть поставлена под сомнение.
Использование подзапросов решит обе эти проблемы.
SELECT [Name]
FROM [Production].[Product]
WHERE [Color]='Red'AND [ListPrice]=
(SELECT MAX([ListPrice]) FROM [Production].[Product]
WHERE [Color]='Red')
В данном случае показан простейший вариант использования подзапроса. Стоит отметить, что использован оператор сравнения на равенство, так как функция
MAX гарантированно возвращает одно значение.
Допускается использование логических операторов для сравнения скалярного значения с результатом выполнения подзапроса. Рассмотрим несколько примеров.
Необходимо получить список товаров, цвет которых может быть любой, кроме
Red, а цена равна цене любого товара с цветом Red. Для этого можно использовать запрос следующего вида:
SELECT [Name]
FROM [Production].[Product]
WHERE [Color]!='Red'AND [ListPrice] = ANY

33
(SELECT [ListPrice] FROM [Production].[Product]
WHERE [Color]='Red')
Оператор сравнения использован вместе с логическим оператором ANY, так как подзапрос возвращает потенциально более одного значения. Логический оператор ANY сравнивает скалярное значение с набором значений, состоящим из одного столбца, и условие сравнения должно быть выполнено хотя бы для одного значения из набора.
Необходимо получить список товаров, цена которых больше цены любого из товаров с цветом Red.
SELECT [Name]
FROM [Production].[Product]
WHERE [ListPrice] >ALL
(SELECT [ListPrice] FROM [Production].[Product]
WHERE [Color]='Red')
Логический оператор ALL сравнивает скалярное значение с набором значений, состоящим из одного столбца, и условие сравнения должно быть выполнено для каждого значения из набора.
Необходимо получить название товаров, чей цвет совпадает с цветом одного из товаров, чья цена больше 3000.
SELECT [Name]
FROM [Production].[Product]
WHERE [Color] IN
(SELECT [Color] FROM [Production].[Product]
WHERE [ListPrice]>3000)
Логический оператор IN определяет, совпадает ли указанное значение с одним из значений, содержащихся во вложенном запросе или списке.
Подзапросы могут сами содержать подзапросы. Следующий пример находит название категории, где содержится самый дорогой товар.
SELECT [Name]
FROM [Production].[ProductCategory]
WHERE [ProductCategoryID] IN
(SELECT [ProductCategoryID]
FROM [Production].[ProductSubcategory]
WHERE [ProductSubcategoryID] IN
(SELECT [ProductSubcategoryID]

34
FROM [Production].[Product]
WHERE [ListPrice] =
(SELECT MAX([ListPrice])
FROM [Production].[Product])))
Данную задачу можно было бы решить с помощью соединения таблиц, упорядочивания и выражения TOP, но использование подзапросов в данном случае предпочтительней, так как это позволяет избежать ресурсоемкой операции соединения. Это утверждение справедливо только для ситуаций, когда подзапрос простой.
Запрос может использовать более одного подзапроса на одном уровне вложенности. Например, необходимо получить с помощью одного запроса список товаров, у которых цвет совпадает с цветом самого дорогого товара, и стиль совпадает со стилем самого дорого товара.
SELECT [Name]
FROM [Production].[Product]
WHERE [Color] IN
(SELECT [Color]
FROM [Production].[Product]
WHERE [ListPrice] =
(SELECT MAX([ListPrice])
FROM [Production].[Product]))
AND
[Style] IN
(SELECT [Style]
FROM [Production].[Product]
WHERE [ListPrice] =
(SELECT MAX([ListPrice])
FROM [Production].[Product]))
Все рассмотренные ранее подзапросы являлись простыми, они возвращали фиксированный, неизменный набор данных, вне зависимости от того, с какой из строк запроса верхнего уровня в данный момент работает СУБД.
Подзапросы также используются для формирования выборки с использованием конструкции GROUP BY HAVING. Допустим, необходимо найти номер подкатегории товаров с наибольшим количеством товаров. Данный запрос можно реализовать несколькими способами, в том числе с использованием подзапроса.

35
SELECT [ProductSubcategoryID]
FROM [Production].[Product]
GROUP BY [ProductSubcategoryID]
HAVING COUNT(*)=
(SELECT TOP 1 COUNT(*)
FROM [Production].[Product]
GROUP BY [ProductSubcategoryID]
ORDER BY 1 DESC)
Рассмотрим следующую задачу. Необходимо получить список самых дорогих товаров в каждой из подкатегорий. Подобную задачу можно решить с использованием сложного, коррелирующего или связанного, подзапроса.
Коррелирующим подзапросом называют такой подзапрос, который формирует связанную выборку, зависимую от данных внешнего запроса. Фактически коррелирующий подзапрос выполняется для каждой строки запроса верхнего уровня.
SELECT [Name]
FROM [Production].[Product] AS P1
WHERE [ListPrice]=
(SELECT MAX([ListPrice])
FROM [Production].[Product] AS P2
WHERE P1.ProductSubcategoryID=P2.ProductSubcategoryID)
Связанные подзапросы могут использоваться для формирования выводимого столбца. Например, следующий запрос возвращает название продукта и название подкатегории, к которой он относится.
SELECT [Name],
(SELECT [Name]
FROM [Production].[ProductSubcategory] AS PS
WHERE P1.ProductSubcategoryID=PS.ProductSubcategoryID)
FROM [Production].[Product] AS P1
Примеры запросов с решениями
1 Найти название подкатегории с наибольшим количеством продуктов, без учета продуктов, для которых подкатегория не определена (еще одна возможная реализация).
SELECT [Name]
FROM [Production].[ProductSubcategory]

36
WHERE [ProductSubcategoryID] IN
(SELECT [ProductSubcategoryID]
FROM [Production].[Product]
WHERE [ProductSubcategoryID] IS NOT NULL
GROUP BY [ProductSubcategoryID]
HAVING COUNT(*)=
(SELECT TOP 1 COUNT(*)
FROM [Production].[Product]
WHERE [ProductSubcategoryID] IS NOT NULL
GROUP BY [ProductSubcategoryID]
ORDER BY 1 DESC
)
)
2 Вывести на экран такого покупателя, который каждый раз покупал только одну номенклатуру товаров, не обязательно в одинаковых количествах, т.е. у него всегда был один и тот же «список покупок».
Вариант 1
SELECT [CustomerID], count(*)
FROM [Sales].[SalesORDERHeader] AS SOH1
GROUP BY [CustomerID]
HAVING count(*)>1 AND count(*)=all
(SELECT count(*)
FROM [Sales].[SalesORDERHeader] AS SOH INner JOIN
[Sales].[SalesORDERDetail] AS SOD
ON soh.SalesORDERID=sod.SalesORDERID
GROUP BY soh.[CustomerID], sod.ProductID
HAVING soh.CustomerID=soh1.CustomerID)
Вариант 2
SELECT soh.CustomerID, p.Name
FROM [Sales].[SalesORDERHeader] AS SOH INner JOIN
[Sales].[SalesORDERDetail] AS sod
ON soh.SalesORDERID=sod.SalesORDERID INner JOIN
[Production].[Product] AS p
ON sod.ProductID=p.ProductID

37
WHERE soh.CustomerID IN (
SELECT [CustomerID]
FROM [Sales].[SalesORDERHeader] AS SOH1
GROUP BY [CustomerID]
HAVING count(*)>1 AND count(*)=all
(SELECT count(*)
FROM [Sales].[SalesORDERHeader] AS SOH INner JOIN
[Sales].[SalesORDERDetail] AS SOD
ON soh.SalesORDERID=sod.SalesORDERID
GROUP BY soh.[CustomerID], sod.ProductID
HAVING soh.CustomerID=soh1.CustomerID))
ORDER BY 1
Вариант 3
SELECT soh1.CustomerID
FROM [Sales].[SalesORDERDetail] AS sod1 INner JOIN
[Sales].[SalesORDERHeader] AS soh1
ON sod1.SalesORDERID=soh1.SalesORDERID
GROUP BY soh1.CustomerID
HAVING count(soh1.SalesORDERID)>1 AND count(DISTINCT sod1.ProductID)>1
AND count(DISTINCT sod1.ProductID)=all
(
SELECT count(*)
FROM [Sales].[SalesORDERDetail] AS sod2 INner JOIN
[Sales].[SalesORDERHeader] AS soh2
ON sod2.SalesORDERID=soh2.SalesORDERID
GROUP BY soh2.CustomerID, sod2.SalesORDERID
HAVING soh2.CustomerID=soh1.CustomerID
)
3 Вывести на экран следующую информацию: название товара (первая колонка), количество покупателей, покупавших этот товар (вторая колонка), количество покупателей, совершавших покупки, но не покупавших товар из первой колонки
(третья колонка).
SELECT

38 p.[ProductID],
(SELECT count(DISTINCT soh.CustomerID)
FROM [Sales].[SalesORDERDetail] AS sod INner JOIN
[Sales].[SalesORDERHeader] AS soh
ON sod.SalesORDERID=soh.SalesORDERID
WHERE sod.ProductID=p.ProductID),
(SELECT count(DISTINCT soh.CustomerID)
FROM [Sales].[SalesORDERDetail] AS sod INner JOIN
[Sales].[SalesORDERHeader] AS soh
ON sod.SalesORDERID=soh.SalesORDERID
WHERE soh.CustomerID NOT IN
(SELECT DISTINCT soh.CustomerID
FROM [Sales].[SalesORDERDetail] AS sod INner JOIN
[Sales].[SalesORDERHeader] AS soh
ON sod.SalesORDERID=soh.SalesORDERID WHERE sod.ProductID=p.ProductID))
FROM [Production].[Product] AS p
4 Найти такие товары, которые были куплены более чем одним покупателем, при этом все покупатели этих товаров покупали товары только из одной подкатегории.
SELECT name
FROM [Production].[Product]
WHERE ProductID IN(
SELECT sod.ProductID
FROM [Sales].[SalesORDERDetail] AS sod JOIN
[Sales].[SalesORDERHeader] AS soh
ON sod.SalesORDERID=soh.SalesORDERID
WHERE soh.CustomerID IN(
SELECT soh.CustomerID
FROM [Sales].[SalesORDERDetail] AS sod JOIN
[Sales].[SalesORDERHeader] AS soh
ON sod.SalesORDERID=soh.SalesORDERID JOIN
[Production].[Product] AS p ON sod.ProductID=p.ProductID

39
GROUP BY soh.CustomerID
HAVING count(DISTINCT p.ProductSubcategoryID)=1)
GROUP BY sod.ProductID
HAVING count(DISTINCT soh.CustomerID)>1)
5 Найти покупателя, который каждый раз имел разный список товаров в чеке (по номенклатуре).
SELECT DISTINCT CustomerID
FROM [Sales].[SalesORDERHeader]
WHERE CustomerID NOT IN (
SELECT soh.Customerid
FROM [Sales].[SalesORDERDetail] AS sod JOIN
[Sales].[SalesORDERHeader] AS soh
ON sod.SalesORDERID=soh.SalesORDERID
WHERE exists(SELECT ProductID
FROM [Sales].[SalesORDERDetail] AS sod1 JOIN
[Sales].[SalesORDERHeader] AS soh1
ON sod.SalesORDERID=soh.SalesORDERID
WHERE soh1.CustomerID=soh.CustomerID AND sod1.ProductID=sod.ProductID
AND sod.SalesORDERID!=sod1.SalesORDERID
))
6 Найти такого покупателя, что все купленные им товары были куплены только им и никогда не покупались другими покупателями.
Вариант 1
SELECT soh1.CustomerID
FROM[Sales].[SalesORDERDetail] AS sod1 INner JOIN
[Sales].[SalesORDERHeader] AS soh1
ON sod1.SalesORDERID=soh1.SalesORDERID
GROUP BY soh1.CustomerID
HAVING count(DISTINCT sod1.productid)=
(SELECT count(DISTINCT sod.ProductID)
FROM [Sales].[SalesORDERDetail] AS sod INner JOIN
[Sales].[SalesORDERHeader] AS soh

40
ON sod.SalesORDERID=soh.SalesORDERID
WHERE soh.CustomerID=soh1.CustomerID
AND sod.ProductID IN
(SELECT sod.ProductID
FROM[Sales].[SalesORDERDetail] AS sod INner JOIN
[Sales].[SalesORDERHeader] AS soh
ON sod.SalesORDERID=soh.SalesORDERID
GROUP BY sod.ProductID
HAVING count(DISTINCT soh.CustomerID)=1))
Вариант 2
SELECT DISTINCT soh.CustomerID
FROM [Sales].[SalesORDERHeader] AS soh
WHERE soh.CustomerID NOT IN(
SELECT DISTINCT soh.CustomerID
FROM [Sales].[SalesORDERDetail] AS sod JOIN
[Sales].[SalesORDERHeader] AS soh
ON sod.SalesORDERID=soh.SalesORDERID
WHERE ProductID NOT IN(
SELECT sod.ProductID
FROM [Sales].[SalesORDERDetail] AS sod JOIN
[Sales].[SalesORDERHeader] AS soh
ON sod.SalesORDERID=soh.SalesORDERID
GROUP BY sod.ProductID
HAVING count(DISTINCT soh.CustomerID)=1))