Скрипты с курса "Специалист Курс М10774 Создание запросов в Microsoft SQL Server 2012".
На виртуальной машине, для выполнения запросов, использовал SQL Server 2014.
Для создания базы Northwind использовал скрипт instnwnd.sql от MS
Копия скрипта сохранена и на моём сайте instnwnd.sql
Копия записи конспекта в прикреплённом файле М10774_day1.docx
day1
Операции:
Простые (не трансформирующие таблицу)
1. SELECT - фильтрация по столбцам
2. WHERE - фильтрация по строкам
3. ORDER BY - сортировка
4. TOP - рейтинг
5. DISTINCT - устранение дубликатов строк
Трансформирующие:
6. Агрегация
7. GROUP BY - группировка
8. HAVING - фильтрация сгруппированной таблицы
use Northwind
SELECT ShipName, LEFT(ShipName, 3), REPLACE(ShipName, 'u','$')
FROM Orders
WHERE LEFT(ShipName, 2) = 'Su'
-- ДАТА и ВРЕМЯ
DECLARE @D date
SET @D = GETDATE()
SELECT @D
-- самый простой способ привести к дате
SELECT CAST(OrderDate AS date) FROM Orders
SELECT GEtDate(),
YEAR(GEtDate()),
MONTH(GEtDate()),
DAY(GEtDate())
SELECT DATEPART (YEAR,GetDate()),DATEPART (MONTH,GetDate()),DATEPART (DAYOFYEAR,GetDate())
-- приведение типов
SELECT 1+1, '1'+'1', 1+'1', '1'+1
SELECT '1' + 1
SELECT '1' + CAST(1 AS varchar),
'1' + CONVERT(varchar, 1)
SELECT CONVERT(varchar, GetDate(),102),
CONVERT(varchar, GetDate(),2),
CONVERT(varchar, GetDate(),4),
CONVERT(varchar, GetDate(),104)
Решение задачек
-- day1 02:18 Заказы сделанные весной 1997 года
SELECT *
FROM Orders
WHERE YEAR(OrderDate) = 1997
AND MONTH(OrderDate) IN (3,4,5)
--
SELECT *
FROM Orders
WHERE OrderDate >='19970301'
AND OrderDate <'19970601'
-- day1 02:35 нет цен у книг как посчитать ?
SELECT * FROM Orders --(830)
SELECT * FROM Orders WHERE ShipRegion IS NULL --(507)
SELECT * FROM Orders WHERE ShipRegion IS NOT NULL --(323)
--IsNull
SELECT Convert(varchar,ShippedDate,102), ShipRegion,
ShipRegion + ' ('+ (Convert(varchar,ShippedDate,102) + ')'),
IsNull(ShipRegion,'') + ' ('+ (Convert(varchar,ShippedDate,102) + ')')
FROM Orders
-- day1 02:55 сортировка и рейтинг TOP(3) WITH
SELECT TOP(3) WITH TIES ContactName, City, Fax
FROM Customers
WHERE City = 'London'
AND Fax Is Not Null
ORDER BY Fax DESC
-- day1 03:05 в какую страну был отправлен последний заказ в 1996-м году?
SELECT TOP (1) WITH TIES ShipCountry--, ShippedDate
FROM Orders
WHERE YEAR(ShippedDate) = 1996
ORDER BY ShippedDate DESC
day1 03:09 порядок WHERE ---> ORDER BY ---> TOP ---> SELECT --->
-- day1 03:15 устранение дубликатов - в каких странах живут покупатели имеющие факс?
SELECT DISTINCT Country
FROM Customers
WHERE Fax IS NOT NULL
ТРАНСФОРМИРУЮЩИЕ ОПЕРАЦИИ
-- day1 03:20 ТРАНСФОРМИРУЮЩИЕ ОПЕРАЦИИ
-- АГРЕГАЦИЯ
SELECT AVG(Freight), MAX(Freight), MIN(Freight), SUM(Freight)
FROM Orders
-- COUNT(*) считает все строки включая NULL-ы
SELECT COUNT(*), COUNT(Freight)
FROM Orders
-- day1 03:25 сколько заказов отправили во Францию в 1997 году?
SELECT COUNT(*) --OrderID
FROM Orders
WHERE ShipCountry = 'France'
AND YEAR(OrderDate) = 1997
-- day1 03:25 каких покупателей больше с факсом или без?
SELECT COUNT(*) - COUNT(Fax), COUNT(Fax) --OrderID
FROM Customers
day1 03:40 GROUP BY
SELECT ShipCountry, COUNT(*)
FROM Orders
WHERE YEAR(OrderDate) = 1997
GROUP BY ShipCountry
ORDER BY COUNT(*) DESC
-- в какой категории больше всего товаров ?
SELECT TOP (1) CategoryID--, COUNT(*)
FROM Products
GROUP BY CategoryID
ORDER BY COUNT(*) DESC
-- day1 04:05 Какой продавец отправил больше всех заказов в Бразилию ?
SELECT TOP(1) WITH TIES EmployeeID, COUNT(OrderID)
FROM Orders
WHERE ShipCountry = 'Brazil'
GROUP BY EmployeeID
ORDER BY COUNT(OrderID) DESC
--
SELECT YEAR(OrderDate), ShipCountry, COUNT(*)
FROM Orders
WHERE ShipCountry = 'Brazil'
GROUP BY YEAR(OrderDate), ShipCountry
ORDER BY COUNT(*) DESC
-- day1 04:27 Какой продавец отправил больше всех заказов в один и тот же город?
SELECT TOP(1) WITH TIES EmployeeID, ShipCity, COUNT(*)
FROM Orders
GROUP BY EmployeeID, ShipCity
ORDER BY COUNT(*) DESC
HAVING фильтрация второй сгруппированной таблицы
-- day1 04:30 HAVING фильтрация второй сгруппированной таблицы
SELECT ShipCountry, COUNT(*)
FROM Orders
WHERE YEAR(OrderDate) = 1997
GROUP BY ShipCountry
HAVING COUNT(*) > 20
-- day1 04:33 Какие покупатели сделали больше 20 заказов?
SELECT CustomerID, COUNT(*)
FROM Orders
GROUP BY CustomerID
HAVING COUNT(*) > 20
INSERT UPDATE DELETE
создал тестовую базу Table_1
USE [testssrs]
GO
INSERT
INTO dbo.Table_1 (Name, Kod)
VALUES ('Kolia','KD00000004')
GO
UPDATE testssrs.dbo.Table_1
SET Kod = 'KD00000003'
WHERE Name = 'Kolia'
SELECT *
FROM testssrs.dbo.Table_1
DELETE
FROM testssrs.dbo.Table_1
WHERE Name = 'Kolia'
--SELECT '00000004' + 1
SELECT MAX(Kod), LEN(MAX(Kod)), left(MAX(Kod),2)--, LEN(left(MAX(Kod),2))
FROM testssrs.dbo.Table_1
day2
Копия записи конспекта в прикреплённом файле М10774_day2.docx
-- day2 00:13 Какие продавцы сумели обслужить больше 15 стран в одно календарном году?
SELECT DISTINCT EmployeeID
FROM Orders
GROUP BY EmployeeID, YEAR(OrderDate)
HAVING COUNT(DISTINCT ShipCountry) > 15
-- day2 00:25 Какие продавцы сумели в 1997-м обслужить больше 5 городов в одной стране?
SELECT DISTINCT EmployeeID--, ShipCountry, COUNT(DISTINCT ShipCity)
FROM Orders
WHERE YEAR(OrderDate) = 1997
GROUP BY EmployeeID, ShipCountry
HAVING COUNT(DISTINCT ShipCity) > 5
day2 Стратегии
-- UNION без ALL по умолчанию c DISTINCT
SELECT City
FROM Employees
UNION
SELECT City
FROM Customers
-- INTERSECT список пересечению где есть хотя бы 1 Employees и 1 Customers
SELECT City
FROM Employees
INTERSECT
SELECT City
FROM Customers
-- EXCEPT есть Employees, но нет Customers
SELECT City
FROM Employees
EXCEPT
SELECT City
FROM Customers
Для решения задач отвечаем себе на следующие вопросы:
1) делаем выбор подзапросы или Join
2) список чего я хочу получить ?
- список продавцов
3) как только чего то не хватает, задаём вопрос - где это лежит?
- поняв ставим скобочки и пишем запрос чтобы получить столбец чего не хватает
(подзапрос это как вложенный цикл)
-- day2 01:02 сколько заказов сделал каждый продавец?
SELECT FirstName +' '+LastName,
(
SELECT COUNT(*)
FROM Orders
WHERE EmployeeID = Employees.EmployeeID
) AS Total
FROM Employees
-- day2 01:12 сколько товаров в каждой категории + какая средняя цена?
SELECT CategoryID, CategoryName,
(
SELECT COUNT(*)
FROM Products
WHERE CategoryID = Categories.CategoryID
),
(
SELECT AVG(UnitPrice)
FROM Products
WHERE CategoryID = Categories.CategoryID
)
FROM Categories
-- day2 01:24 сколько денег заработали на каждом заказе?
SELECT OrderID,
(
SELECT SUM(UnitPrice * Quantity * (1 - Discount))
FROM [Order Details]
WHERE OrderID = Orders.OrderID
)
FROM Orders
следующие задачки нужно решить самостоятельно, для проверки ответы в вордовском файле
-- day2 01:54 Сколько заказов сделал каждый покупатель ?
-- day2 02:02 Сколько заказов отправил каждый продавец из США?
-- day2 02:05 АЛГОРИТМ ПРИ НАПИСАНИИ ПОДЗАПРОСА
-- day2 02:15 Какой покупатель сделал больше всех заказов в 1997-м?
-- day2 02:32 Диаграмма
-- day2 02:37 ещё одна задачка подзапросом
-- найти ProductName продажи которых больше 50000
-- day2 02:43 подзапрос в поле FROM для борьбы с синтаксическими ограничениями
-- 1) не должно быть сортировок
-- 2) у всех столбцов должны быть имена
-- 3) подзапрос тоже должен иметь название
-- day2 02:47 нужно получить данные из 1-й и 2-й таблиц используя 3-ю
-- day2 02:51 нужна сумма по конкретному чеку
-- нужно получить список чеков тех которые выписал конкретный продавец
-- day2 02:57 похожий пример
-- day2 03:15 Как зовут покупателя, купившего
-- самый широкий ассортимент товаров?
-- day2 03:37 Какие продавцы (имена) в 1997-м
-- обслужили больше 20 покупателей?
-- day2 03:38 CROSS JOIN (аналогично таблицы через , во FROM)
SELECT *
FROM Orders CROSS JOIN Employees
--FROM Orders, Employees
-- day2 03:51 пример для службы знакомств проставить все возможные пары
SELECT E1.FirstName+' '+E1.LastName,
E2.FirstName+' '+E2.LastName
FROM Employees E1 CROSS JOIN Employees E2
WHERE E1.TitleOfCourtesy = 'Mr.'
AND E2.TitleOfCourtesy = 'Ms.'
-- day2 03:54 Сколько заказов сделал каждый продавец
-- 1) перджойнить все таблицы
-- 2) написать WHERE чтобы оставить только те что связаны в реальном мире
-- 3) спросить себя список чего я вижу?
-- каждая строка это что ?
-- у продавцов может быть несколько заказов,
-- но у заказа не может быть двух продавцов, значит это заказы
-- 4) Список чего я хочу получить ? GROUP BY
SELECT FirstName+' ' +LastName, COUNT(*)
FROM Employees CROSS JOIN Orders
WHERE Employees.EmployeeID = Orders.EmployeeID
GROUP BY FirstName+' ' +LastName
-- day2 04:05 решить самому
-- day2 04:08 Сколько денег мы заработали на каждом товаре?
SELECT ProductName, SUM(OD.UnitPrice * Quantity * (1 - Discount))
FROM Products CROSS JOIN [Order Details] OD
WHERE Products.ProductID = OD.ProductID
GROUP BY ProductName
HAVING SUM(OD.UnitPrice * Quantity * (1 - Discount)) > 10000
ORDER BY SUM(OD.UnitPrice * Quantity * (1 - Discount)) DESC
-- day2 04:21 Сколько заказов отправил каждый продавец в Лондон?
-- больше 4-х
SELECT LastName+' '+FirstName, COUNT(*)
FROM Employees E CROSS JOIN Orders O
WHERE E.EmployeeID = O.EmployeeID
AND O.ShipCity = 'London'
GROUP BY LastName+' '+FirstName
HAVING COUNT(*) > 4
-- day2 04:42 Какие покупатели сделали больше 10 заказов 1997?
SELECT ContactName, COUNT(*)
FROM Customers C CROSS JOIN Orders O
WHERE C.CustomerID = O.CustomerID
AND YEAR(O.OrderDate) = 1997
GROUP BY ContactName
HAVING COUNT(*) > 10
-- day2 04:45 INNER JOIN
-- day2 04:48 INNER JOIN Какие покупатели сделали больше 10 заказов 1997?
SELECT ContactName, COUNT(*)
FROM Customers C INNER JOIN Orders O
ON C.CustomerID = O.CustomerID
WHERE YEAR(O.OrderDate) = 1997
GROUP BY ContactName
HAVING COUNT(*) > 10
-- day2 04:53 Сколько денег заработал каждый продавец?
-- переджойнить все таблицы
SELECT FirstName+' '+LastName,
SUM(UnitPrice*Quantity*(1-Discount))
FROM Employees E INNER JOIN Orders O
ON E.EmployeeID = O.EmployeeID
INNER JOIN [Order Details] OD
ON O.OrderID = od.OrderID
GROUP BY FirstName+' '+LastName
-- day2 04:57 в каких категориях средняя цена товаров больше 15?
SELECT C.CategoryName, P.CategoryID, AVG(OD.UnitPrice)
FROM Products P INNER JOIN [Order Details] OD
ON P.ProductID = OD.ProductID
INNER JOIN Orders O
ON O.OrderID = OD.OrderID
INNER JOIN Categories C
ON P.CategoryID = C.CategoryID
GROUP BY C.CategoryName, P.CategoryID
HAVING AVG(OD.UnitPrice) > 15
day3
Копия записи конспекта в прикреплённом файле М10774_day3.docx
-- day3 00:04 ПОДЗАПРОС - Сколько заказов отправил каждый продавец летом 1997 года?
SELECT FirstName+' '+LastName,
(
SELECT COUNT(*)
FROM Orders
WHERE Orders.EmployeeID = Employees.EmployeeID
AND YEAR(OrderDate) = 1997
AND MONTH(OrderDate) IN (6,7,8)
)
FROM Employees
ORDER BY FirstName+' '+LastName
-- day3 00:09 INNER JOIN
SELECT FirstName+' '+LastName, COUNT(*)
FROM Employees E INNER JOIN Orders O
ON E.EmployeeID = O.EmployeeID
WHERE YEAR(OrderDate) = 1997
AND MONTH(OrderDate) IN (6,7,8)
GROUP BY FirstName+' '+LastName
ORDER BY FirstName+' '+LastName
-- day3 00:14 Как зовут покупателей купивших более 100 шт товаров?
SELECT ContactName,
(
SELECT SUM(Quantity)
FROM [Order Details]
WHERE OrderID IN (
SELECT OrderID
FROM Orders
WHERE CustomerID=Customers.CustomerID
)
)
FROM Customers
WHERE (
SELECT SUM(Quantity)
FROM [Order Details]
WHERE OrderID IN (
SELECT OrderID
FROM Orders
WHERE CustomerID=Customers.CustomerID
)
) > 100
ORDER BY ContactName
-----------------------------------------------------------------
SELECT C.ContactName, SUM(Quantity)
FROM Customers C INNER JOIN Orders O
ON C.CustomerID = O.CustomerID
INNER JOIN [Order Details] OD
ON OD.OrderID = O.OrderID
GROUP BY C.ContactName
HAVING SUM(Quantity) > 100
ORDER BY C.ContactName
day3 ОШИБКИ и ОПАСНОСТИ JOIN
-- day3 00:27 ОШИБКИ и ОПАСНОСТИ JOIN
-- ТИПОВАЯ ОШИБКА №1
-- day3 00:30 Сколько заказов сделал каждый покупатель?
SELECT ContactName,
(
SELECT COUNT(*)
FROM Orders
WHERE CustomerID = Customers.CustomerID
)
FROM Customers
--------------------- неверно через INNER JOIN -----------------------------------
SELECT ContactName, COUNT(*)
FROM Customers C INNER JOIN Orders O
ON C.CustomerID = O.CustomerID
GROUP BY ContactName
-- day3 00:43 правильно через LEFT JOIN, не пропадут те кто ни разу не купил
SELECT ContactName, COUNT(*)
FROM Customers C LEFT JOIN Orders O
ON C.CustomerID = O.CustomerID
GROUP BY ContactName
COUNT(*) не писать в соответствии с LEFT or RICHT JOIN – считает не понятно что
всё в перемешку, нужно считать по той таблице что нужно
-- day3 02:34 ранжирующая функция
-- в OVER указать по чём нумеровать
-- Row_Nuber() нумерует дублирующие в отличии от Rank()
-- nTile(3) – делит на 3 группы
по группам
постраничный вывод
-- day3 02:48 OFFSET сколько строк нужно пропустить
-- FETCH без OFFSET нельзя использовать, хотя бы поставить OFFSET 0
Оконная функция, нарастающий итог:
На сколько следующий товар дешевле предыдущего?
day4
Копия записи конспекта в прикреплённом файле М10774_day4.docx
-- day4 00:41
-- Script - Сценарий
-- Batch - Пакет
-- Statement(Operator) - Инструкция (Оператор)
SELECT 1,2,3
SELECT 1,2,3
SELECT 1,2,3
go
SELECT 1,2,3
SELECT 1,2,3
SELECT 1,2,3
-- Интерактивный веду за руку и по шагам что нужно сделать
-- Декларативный говорю только что я хочу получить
--day4 00:54
-- Переменные DECLARE
-- объявление переменной
DECLARE @Var1 int
-- инициализация
SET @Var1 = 5
-- использование
SELECT @Var1*2
-- недостатки: только 1-но значение, не возможно загрузить с таблицы
-- инициализация SELECT-ом
DECLARE @Var1 int
SELECT @Var1 = COUNT(*)
FROM Orders
SELECT @Var1 /2
-- 00:59 условие IF
IF @Var1 < 3
SELECT 'A'
--условие IF с ELSE
IF @Var1 < 3
SELECT 'A'
ELSE
SELECT 'B'
-- 01:02 BEGIN ... END операторные скобки
DECLARE @Var1 int
IF @Var1 < 3
BEGIN
SELECT 'A1'
SELECT 'A2'
SELECT 'A3'
END
-- WHILE цикл
go
DECLARE @Var1 int
SET @Var1 = 5
WHILE @Var1 > 0
BEGIN
SELECT @Var1
SET @Var1 = @Var1 - 1
END
-- day4 01:04
-- CASE множественный выбор (формирует столбец)
-- Простая форма
SELECT CategoryID, ProductName, UnitPrice,
CASE CategoryID
WHEN 1 THEN UnitPrice * 0.1
WHEN 2 THEN 5
WHEN 3 THEN UnitPrice * 0.15
ELSE 15
END AS Tax1,
CASE
WHEN CategoryID IN (1,4,5) AND UnitPrice < 10
THEN UnitPrice * 0.1
WHEN CategoryID = 2 OR UnitPrice > 50
THEN UnitPrice * 0.15
ELSE 3
END AS Tax2
FROM Products
ORDER BY UnitPrice
-- CASE можно вынести в WHERE
SELECT CategoryID, ProductName, UnitPrice
FROM Products
WHERE CASE CategoryID
WHEN 1 THEN UnitPrice * 0.1
WHEN 2 THEN 5
WHEN 3 THEN UnitPrice * 0.15
ELSE 15
END > 20
-- 01:24 М: Имя, Фамилия
-- Ж: Фамилия, Имя
SELECT TitleOfCourtesy,
CASE TitleOfCourtesy
WHEN 'Ms.' THEN LastName + '' + FirstName
WHEN 'Mrs.' THEN LastName + '' + FirstName
ELSE FirstName + ' ' + LastName
END,
CASE
WHEN TitleOfCourtesy IN ('Ms.','Mrs.') THEN LastName + '' + FirstName
ELSE FirstName + ' ' + LastName
END
FROM Employees
-- 01:27 Динамические запросы нужны для преодоления синтаксических ограничений
-- SELECT * FROM @TableName
-- нельзя в поле FROM подставлять в виде переменной имя таблицы
-- EXECUTE (динамический запрос) это позволяет выполнить
DECLARE @TableName varchar(100)
SET @TableName = 'Products'
EXECUTE ('SELECT * FROM '+ @TableName)
-- 01:33 CURSOR (Курсор) -------------------------------------------------------------------------
-- Курсор это массив
-- Курсор это сохранённый результат SELECTа
-- курсор это объект, который необходимо обязательно объявить и остановить
DECLARE MyCursor CURSOR
FOR
SELECT ProductName, UnitPrice
FROM Products
ORDER BY UnitPrice DESC
OPEN MyCursor
FETCH NEXT FROM MyCursor
--@@FETCH_STATUS системная переменная для определения позиции FETCH
WHILE @@FETCH_STATUS=0
BEGIN
FETCH NEXT FROM MyCursor
END
CLOSE MyCursor
DEALLOCATE MyCursor
-----------------------------------------------------------
DECLARE @Name varchar(100), @Price money, @PrevPrice money
DECLARE MyCursor CURSOR
FOR
SELECT ProductName, UnitPrice
FROM Products
ORDER BY UnitPrice DESC
OPEN MyCursor
-- для записи в переменные
FETCH NEXT FROM MyCursor INTO @Name, @Price
WHILE @@FETCH_STATUS=0
BEGIN
-->
--FETCH NEXT FROM MyCursor
SELECT @Name, @Price, @PrevPrice - @Price
SELECT @PrevPrice = @Price
-->
FETCH NEXT FROM MyCursor INTO @Name, @Price
END
CLOSE MyCursor
DEALLOCATE MyCursor
-----------------------------------------------------------
-- 01:56 Временная таблица и табличные переменные
-- табличные переменные
DECLARE @MyTable TABLE (
Name varchar(100),
Price money,
PrevPrice money
)
DECLARE @Name varchar(100), @Price money, @PrevPrice money
DECLARE MyCursor CURSOR
FOR
SELECT ProductName, UnitPrice
FROM Products
ORDER BY UnitPrice DESC
OPEN MyCursor
-- для записи в переменные
FETCH NEXT FROM MyCursor INTO @Name, @Price
WHILE @@FETCH_STATUS=0
BEGIN
-->
INSERT
INTO @MyTable (Name, Price, PrevPrice)
VALUES (@Name, @Price, @PrevPrice)
SELECT @PrevPrice = @Price
-->
FETCH NEXT FROM MyCursor INTO @Name, @Price
END
CLOSE MyCursor
DEALLOCATE MyCursor
SELECT *
FROM @MyTable
-- 02:01 Временная таблица
-- Временная таблица это реальный объект в базе её нужно явно создать и явно удалить
-- MyTable - обычная таблица, которую видно в базе
-- #MyTable - временная таблица
-- ##MyTable - глобальная временная таблица
CREATE TABLE #MyTable (
Name varchar(100),
Price money,
PrevPrice money
)
DECLARE @Name varchar(100), @Price money, @PrevPrice money
DECLARE MyCursor CURSOR
FOR
SELECT ProductName, UnitPrice
FROM Products
ORDER BY UnitPrice DESC
OPEN MyCursor
-- для записи в переменные
FETCH NEXT FROM MyCursor INTO @Name, @Price
WHILE @@FETCH_STATUS=0
BEGIN
-->
INSERT
INTO #MyTable (Name, Price, PrevPrice)
VALUES (@Name, @Price, @PrevPrice)
SELECT @PrevPrice = @Price
-->
FETCH NEXT FROM MyCursor INTO @Name, @Price
END
CLOSE MyCursor
DEALLOCATE MyCursor
SELECT *
FROM #MyTable
DROP TABLE #MyTable
универсальный запрос
-- day4 02:10
-- универсальный запрос
-- первоначальный запрос
--SELECT CompanyName, [Seafood],[Beverages],[Condiments]
--FROM (
-- SELECT CategoryName, CompanyName, UnitPrice
-- FROM Products P INNER JOIN Suppliers S
-- ON P.SupplierID = S.SupplierID
-- INNER JOIN Categories C
-- ON P.CategoryID = C.CategoryID
-- ) MyTable
--PIVOT (
-- AVG(UnitPrice) FOR CategoryName IN ([Seafood],[Beverages],[Condiments])
-- ) MyReport
---------------------------------------------------------------------------------
-- 2) объявляю переменную @List
DECLARE @List varchar(MAX)
--SET @List = ''
-- 6) для категории объявляю переменную
DECLARE @Category varchar(100)
-- 4) объявляю КУРСОР для перебора категорий
DECLARE MyCursor CURSOR
FOR
-- 3) создаю нужный запрос для получения списка категорий
SELECT DISTINCT CategoryName
FROM Categories
-- 5) сразу прописываю для курсора OPEN, CLOSE, DEALLOCATE
OPEN MyCursor
-- 7) универсальный цикл, который перебирает весь курсор
FETCH NEXT FROM MyCursor INTO @Category
WHILE @@FETCH_STATUS = 0
BEGIN
-- 8) переменная @List изначально может быть пустой
IF @List IS NULL
SET @List = ''
ELSE
SET @List = @List + ','
-- 8)----
SET @List = @List+'['+@Category+']'
--SET @List = @List+','+'['+@Category+']'
FETCH NEXT FROM MyCursor INTO @Category
END
CLOSE MyCursor
DEALLOCATE MyCursor
PRINT @List
-- 1) делаем динамический запрос с переменной
EXECUTE(
--'SELECT *
'SELECT CompanyName, '+@List+'
FROM (
SELECT CategoryName, CompanyName, UnitPrice
FROM Products P INNER JOIN Suppliers S
ON P.SupplierID = S.SupplierID
INNER JOIN Categories C
ON P.CategoryID = C.CategoryID
) MyTable
PIVOT (
AVG(UnitPrice) FOR CategoryName IN ('+@List+')
) MyReport'
)
-- day4 02:20 Программные модули Представления, Функции и Процедуры
-- способ сохранения запроса (текста запроса, а не результата)
-- VIEW
-– 1) только SELECT not Insert, Update, Delete
-- 2) без сортировки, ORDER BY можно использовать, но для ТОР
-- 3) все поля имеют имена
Программные модули:
1. VIEW - представления
2. STORED PROCEDURE - процедупа
3. FUNCTION - функция
--CREATE VIEW VIEWPrice
ALTER VIEW VIEWPrice
AS
SELECT maker, Product.model, Product.type,
-- Laptop.price, PC.price, Printer.price,
(isnull(Laptop.price,0) + isnull(PC.price,0) + isnull(Printer.price,0)) as price
,(isnull(Laptop.speed,0) + isnull(PC.speed,0)) as speed
FROM product
LEFT JOIN laptop
ON Laptop.model = Product.model
LEFT JOIN PC
ON PC.model = Product.model
LEFT JOIN Printer
ON Printer.model = Product.model
--GROUP BY maker, Product.model, Product.type, (isnull(Laptop.price,0) +
-- isnull(PC.price,0) + isnull(Printer.price,0)) ,
-- (isnull(Laptop.speed,0) + isnull(PC.speed,0))
-- можно только SELECT
-- day4 02:50 PROCEDURE может возвращать как одно так и множество значений
--CREATE PROCEDURE PrPrice
ALTER PROCEDURE PrPrice
AS
SELECT maker, Product.model, Product.type,
-- Laptop.price, PC.price, Printer.price,
(isnull(Laptop.price,0) + isnull(PC.price,0) + isnull(Printer.price,0)) as price
,(isnull(Laptop.speed,0) + isnull(PC.speed,0)) as speed
FROM product
LEFT JOIN laptop
ON Laptop.model = Product.model
LEFT JOIN PC
ON PC.model = Product.model
LEFT JOIN Printer
ON Printer.model = Product.model
GROUP BY maker, Product.model, Product.type, (isnull(Laptop.price,0) +
isnull(PC.price,0) + isnull(Printer.price,0)) ,
(isnull(Laptop.speed,0) + isnull(PC.speed,0))
-- вызов PROCEDURE даёт только результат и все изменения только внутри процедуры
-- внутри полная гибкость можно использовать Insert, Update, Delete
-- можно оснастить параметрами
EXECUTE PrPrice
-- day4 02:58 добавляем параметры в процедуру
--CREATE PROCEDURE StoredProcedure @MinPr int=NULL,
ALTER PROCEDURE StoredProcedure @MinPr int=NULL,
@MaxPr int=NULL
AS
SELECT maker, Product.model, Product.type,
-- Laptop.price, PC.price, Printer.price,
(isnull(Laptop.price,0) + isnull(PC.price,0) + isnull(Printer.price,0)) as price
,(isnull(Laptop.speed,0) + isnull(PC.speed,0)) as speed
FROM product
LEFT JOIN laptop
ON Laptop.model = Product.model
LEFT JOIN PC
ON PC.model = Product.model
LEFT JOIN Printer
ON Printer.model = Product.model
-- добавляем параметры в процедуру
WHERE (isnull(Laptop.price,0) + isnull(PC.price,0) + isnull(Printer.price,0))
BETWEEN @MinPr AND @MaxPr
OR @MaxPr IS NULL
GROUP BY maker, Product.model, Product.type, (isnull(Laptop.price,0) +
isnull(PC.price,0) + isnull(Printer.price,0)) ,
(isnull(Laptop.speed,0) + isnull(PC.speed,0))
------------
EXECUTE PrPrice 300, 650
-- day4 03:11 FUNCTION – возвращает только ОДНО значение
--CREATE FUNCTION FUNCTIONPrice (
ALTER FUNCTION FUNCTIONPrice (
@MinPr int=NULL,
@MaxPr int=NULL
)
RETURNS TABLE
AS
RETURN
SELECT maker, Product.model, Product.type,
-- Laptop.price, PC.price, Printer.price,
(isnull(Laptop.price,0) + isnull(PC.price,0) + isnull(Printer.price,0)) as price
,(isnull(Laptop.speed,0) + isnull(PC.speed,0)) as speed
FROM product
LEFT JOIN laptop
ON Laptop.model = Product.model
LEFT JOIN PC
ON PC.model = Product.model
LEFT JOIN Printer
ON Printer.model = Product.model
-- добавляем параметры в функцию
WHERE (isnull(Laptop.price,0) + isnull(PC.price,0) + isnull(Printer.price,0))
BETWEEN @MinPr AND @MaxPr
OR @MaxPr IS NULL
GROUP BY maker, Product.model, Product.type, (isnull(Laptop.price,0) +
isnull(PC.price,0) + isnull(Printer.price,0)) ,
(isnull(Laptop.speed,0) + isnull(PC.speed,0))
-----------------------------------------------------
-- СРАВНЕНИЕ ИТОГОВЫХ ДАННЫХ
-- Представления (VIEW)
-- можно изменять снаружи
SELECT DISTINCT *
FROM VIEWPrice
WHERE price BETWEEN 300 AND 500
-- Хранимая процедура (StoredProcedure)
-- можно изменять только внутри, добавив только параметры
EXECUTE StoredProcedure 300, 500
-- Функция (FUNCTION)
-- изменять можно как внутри так и снаружи
SELECT *
FROM FUNCTIONPrice (300,500)
WHERE speed < 600
-- day4 03:26 Common Table Expression (CTE) Табличные выражения
-- (если нужно использовать 1раз и нет надобности мусорить базу вьюшками)
-- это временная вьюшка, которая живёт в течении одного SELECT, самоликвидируется
WITH CommonTableExpression (maker, model, type, price, speed)
AS (
SELECT maker, Product.model, Product.type,
(isnull(Laptop.price,0) + isnull(PC.price,0) + isnull(Printer.price,0)) as price
,(isnull(Laptop.speed,0) + isnull(PC.speed,0)) as speed
FROM product
LEFT JOIN laptop
ON Laptop.model = Product.model
LEFT JOIN PC
ON PC.model = Product.model
LEFT JOIN Printer
ON Printer.model = Product.model
GROUP BY maker, Product.model, Product.type, (isnull(Laptop.price,0) +
isnull(PC.price,0) + isnull(Printer.price,0)) ,
(isnull(Laptop.speed,0) + isnull(PC.speed,0))
)
-------------------------------------------------------------------------
SELECT *
FROM CommonTableExpression
WHERE price BETWEEN 300 AND 600
---- второй SELECT выдаёт ошибку так как СТЕ самоликвидировался
--SELECT *
--FROM CommonTableExpression
-- day4 03:45 Оконные функии
CREATE FUNCTION ListIntervals (@EmployeeID int)
RETURNS TABLE
AS
RETURN
--------------------- запрос остаётся без изменений ---------------------------
SELECT CAST(OrderDate AS date) AS Date,
CAST(LAG(OrderDate, 1, OrderDate) OVER (ORDER BY OrderDate ASC) AS date) AS PredDate,
DateDiff (
day,
LAG(OrderDate, 1, OrderDate)
OVER (ORDER BY OrderDate ASC),
OrderDate
) AS Interval
FROM Orders
WHERE EmployeeID = @EmployeeID
-- day4 04:20
-- Сколько у каждого продавца (имена) было периодов заказов
-- длиной более 20 дней?
SELECT FirstName+' '+LastName,
(
SELECT COUNT(*)
FROM ListIntervals(Employees.EmployeeID)
WHERE Interval = 20
)
FROM Employees
day5
Копия записи конспекта в прикреплённом файле М10774_day2.docx
Оператор APPLY
-- day5 Сколько у каждого продавца (имена) было периодов заказов
-- длиной более 20 дней?
SELECT FirstName+' '+LastName,
(
SELECT COUNT(*)
FROM ListIntervals(Employees.EmployeeID)
WHERE Interval = 20
)
FROM Employees
ORDER BY FirstName+' '+LastName
-- day5 00:12 корелированный JOIN
SELECT FirstName+' '+LastName, COUNT(Interval)
--Interval
FROM Employees E CROSS APPLY ListIntervals(E.EmployeeID) I
WHERE Interval = 20
GROUP BY FirstName+' '+LastName
-- day5 00:16 корелированный JOIN
SELECT FirstName+' '+LastName,
COUNT(
CASE Interval
WHEN 20 THEN 1
END
)
--Interval
FROM Employees E OUTER APPLY ListIntervals(E.EmployeeID) I
--WHERE Interval = 20
GROUP BY FirstName+' '+LastName
