Учебник по SQL

OFFSET - от линии x до линии y

ORDER BY Continent ASC, Subregion DESC
ПРИ СМЕЩЕНИИ 29 СТРОК ИЗВЛЕКАЮТСЯ ТОЛЬКО СЛЕДУЮЩИЕ 50 СТРОК;

С НИЧЬЕЙ - заняли последнее место

ВЫБЕРИТЕ 10 ЛУЧШИХ ПРОЦЕНТОВ С ГАЛСТУКАМИ;

COALESCE - 1. не NULL значение

SELECT COALESCE(NULL,NULL,2,5,NULL)
COALESCE(PickedByPersonID,BackorderOrderID);

WHERE - замена символов

Поиск с использованием диких символов (%) - SQL Server | Microsoft Learn

WHERE StockItemName LIKE '%ham[^m]%'
WHERE StockItemName LIKE 'a[l-m]%[l-p]'
WHERE CountryName LIKE 'H__[g,d]%'

WHERE IN - включен ли он в список

WHERE PickedByPersonID IN (3,4,17) AND OrderDate >= '2013-01-01' AND OrderDate <= '2013-12-30'

АГРЕГАТНЫЕ ЗАПРОСЫ - AVG(),SUM(), MIN(), MAX(), COUNT(), COUNT_BIG()

SUM(Количество) AS total_pack, AVG(UnitPrice) AS avg_unitprice, COUNT(OrderLineID) AS cnt_orderline

АГГРЕГАТОРЫ - не обрабатывать NULL

SELECT COUNT(ISNULL(PickingCompletedWhen,'1970-01-01'))
SELECT COUNT_BIG(*) FROM Sales.OrderLines;
COUNT(CASE WHEN PickingCompletedWhen IS NULL THEN 1 ELSE NULL END) AS unkown_values

подзапрос - самодостаточный/простой

SELECT OrderId, AVG(Quantity) as avg_perorderid
FROM Sales.OrderLines
GROUP BY OrderId
HAVING AVG(Quantity) > (SELECT AVG(Quantity) AS avg_q
FROM Sales.OrderLines)
ORDER BY avg_perorderid ASC;

SELECT OrderId, (SELECT AVG(Quantity) AS avg_q FROM Sales.OrderLines)
FROM Sales.OrderLines;

корреляционный подзапрос - последний OrderID 

SELECT CustomerId, OrderId, OrderDate
FROM Sales.Orders o1
WHERE OrderID=(SELECT MAX(OrderID))
FROM Sales.Orders o2
WHERE o1.CustomerId=o2.CustomerId)
Порядок по дате заказа DESC;

повторные запуски со списком

SELECT OrderID, CustomerId, OrderDate FROM Sales.Orders
WHERE CustomerID IN (SELECT CustomerID FROM Sales.Customers WHERE CreditLimit IS NULL OR CreditLimit < 2000)
ORDER BY CustomerID ASC;

EXISTS

SELECT DISTINCT CustomerID, CustomerName
FROM Sales.Customers c
WHERE PostalCityID = 33832
AND EXISTS
(SELECT * FROM Sales.Orders o
WHERE o.CustomerID=c.CustomerID);

run-total - gordulo lekerdezes

SELECT CityID, CityName, (SELECT MIN(CityID) FROM Application.Cities c2 WHERE c2.CityID > c1.CityID) AS nextcityid
FROM Application.Cities c1
ORDER BY CityID ASC;
--javitani
SELECT CityID, CityName, LatestRecordedPopulation, LatestRecordedPopulation + (SELECT SUM(LatestRecordedPopulation) FROM Application.Cities c2 WHERE c1.CityID=c2.CityID) AS run_total
FROM Application.Cities c1
WHERE StateProvinceID IN (SELECT StateProvinceID FROM Application.StateProvinces WHERE StateProvinceName='Colorado')
GROUP BY CityID, CityName, LatestRecordedPopulation;

Версия ДЕРВИРОВАННОЙ ТАБЛИЦЫ

SELECT order_count, month_number
FROM (
SELECT MONTH(OrderDate) AS month_number, COUNT(OrderDate) AS order_count
FROM Sales.Orders
GROUP BY MONTH(OrderDate)
) AS derivtable
WHERE order_count > 6000
ORDER BY month_number ASC;

SELECT order_count, month_number
FROM (
SELECT MONTH(OrderDate), COUNT(OrderDate)
FROM Sales.Orders
GROUP BY MONTH(OrderDate)
) AS derivedtable (month_number,order_count)
WHERE order_count > 6000
ORDER BY month_number ASC;

SELECT CustomerID, ORderID, OrderDate
FROM Sales.Orders AS a -- a=schema_name.table_name
INNER JOIN
(
SELECT MAX(OrderDate) AS maxorderdate
FROM Sales.Orders AS b
) AS derivedtable ON a.orderDate=derivedtable.maxorderdate;

CTE

WITH cte_table AS (
SELECT MONTH(OrderDate) AS month_number, COUNT(OrderDate) AS order_count
FROM Sales.Orders
GROUP BY MONTH(OrderDate)
)
SELECT order_count, month_number FROM cte_table
WHERE order_count > 6000
ORDER BY month_number ASC;

СЛУЧАЙ - НОРМА - ПРОИЗВОДНАЯ ТАБЛИЦА - CTE 

ВЫБОР CASE
WHEN 2013=YEAR(OrderDate) THEN '2013y'
WHEN 2014=FORMAT(OrderDate,'yyyy') THEN '2014y'

WHEN OrderDate >= '20160101' AND OrderDate = '20160101' AND OrderDate = '20160101' AND OrderDate = '20160101' AND OrderDate < '20170101' THEN '2016y'
END as years
FROM Sales.Orders)
SELECT years, COUNT(years)
FROM whatever
GROUP BY years;

JOIN AND - Это будет отношение "где в" и "где".

SELECT *
FROM dbo.Varosok v JOIN dbo.Orszagok o ON v.OrszagID = o.Id AND o.Nev='USA'

Boss Boss JOIN

SELECT subordinate.Nev, braids.Nev, braids.Nev FROM dbo.people as Subordinate
INNER JOIN dbo.People as fonok ON subordinate.FonokId = fonok.Id
LEFT JOIN dbo.People as majorfonok ON fonok.Fonok.FonokId = majorfonok.Id

длина данных

SELECT emp_name,
LEN(emp_name) LEN
, DATALENGTH(emp_name) data_length
FROM dbo.employees;

УНИКАЛЬНЫЙ ИДЕНТИФИКАТОР

CREATE TABLE #testunique(
id UNIQUEIDENTIFIER DEFAULT NEWID()
, name varchar(100)
);

идентификатор автосоздания

DROP TABLE IF EXISTS HR.Employees
CREATE TABLE HR.Employees (
id int IDENTITY(1,1)
, emp_name NVARCHAR(200)
)

СВОЙ ТИП

CREATE TYPE hunschema.hun_mySSN FROM varchar(9);
CREATE TABLE hunschema.testtable (
id INT NOT NULL
, socnumberHUN hunschema.hun_mySSN
);
INSERT INTO hunschema.testtable
VALUES (1, '123456789');

Избежать проблемы автоинкремента и INSERT

SET IDENTITY_INSERT Hr.Employees ON;

INSERT INTO HR.Employees (id,emp_name)
VALUES(666,'firstname');

SET IDENTITY_INSERT Hr.Employees OFF;

OUTPUT вставлен, удален

INSERT INTO HR.Employees (emp_name)
OUTPUT inserted.emp_name INTO HR.Emplog(emp_name)
VALUES ('testname')

УДАЛИТЬ Hr.Employees
OUTPUT deleted.emp_name INTO HR.Emplog
WHERE id=666;

DELETE testlines where orderdate='20130101′ > LOG 

DELETE dbo.testlines
OUTPUT deleted.*
FROM dbo.testlines tol
INNER JOIN dbo.testorders tor ON tor.OrderID=tol.OrderID
WHERE OrderDate='20130101'

ПЕРВИЧНЫЕ КЛЮЧИ, ВНЕШНИЕ КЛЮЧИ, УНИКАЛЬНЫЙ КЛЮЧ, ОГРАНИЧЕНИЯ

PRIMARY KEY

/* v1 */
ALTER TABLE dbo.employees
ALTER COLUMN emp_id int NOT NULL;

ALTER TABLE dbo.employees
ADD CONSTRAINT PK_employee_empid PRIMARY KEY (emp_id);
/* v2 */
ALTER TABLE dbo.employees
ADD emp_id int IDENTITY(1,1) NOT NULL
CONSTRAINT PK_employees_emp PRIMARY KEY;
/* v3 */
GO
CREATE TABLE dbo.testtable4(
id INT NOT NULL PRIMARY KEY
);
/* v4 */
DROP TABLE IF EXISTS dbo.testtable4;
CREATE TABLE dbo.testtable4(
id INT NOT NULL IDENTITY(1,1
, name varchar(50)
, CONSTRAINT PK_testtable4_id PRIMARY KEY(id)
);

ВНЕШНИЙ КЛЮЧ - 1 > N - ПЕРВИЧНЫЙ КЛЮЧ

ALTER TABLE dbo.employees
ADD CONSTRAINT FK_employees_depid FOREIGN KEY(dep_id) REFERENCES dbo.departments(dep_id);

каскадная версия

ALTER TABLE dbo.employee
ADD CONSTRAINT FK_employees_depid FOREIGN KEY(dep_id) REFERENCES dbo.departments(dep_id) ON UPDATE CASCADE; -- ON DELETE CASCADE

таблица переключения - сотрудники и образование - N > M

DROP TABLE IF EXISTS dbo.conn_educations_employees;
CREATE TABLE dbo.conn_educations_employees (
id INT NOT NULL IDENTITY(1,1)
CONSTRAINT PK_conn_eduemp_id PRIMARY KEY
edu_id INT NOT NULL
CONSTRAINT FK_conn_edu_eduid FOREIGN KEY (edu_id) REFERENCES dbo.educations(edu_id)
,emp_id INT NOT NULL
CONSTRAINT FK_conn_edu_empid FOREIGN KEY (edu_id) REFERENCES dbo.employees(emp_id)
);

1 > 1

DROP TABLE IF EXISTS dbo.itusers;
CREATE TABLE dbo.itusers(
id int NOT NULL IDENTITY(1,1)
CONSTRAINT PK_itusers_id PRIMARY KEY
, имя пользователя VARCHAR(50) NOT NULL
CONSTRAINT UK_itsuser_username UNIQUE
, emp_id INT
CONSTRAINT FK_itusers_empid FOREIGN KEY(emp_id) REFERENCES dbo.employees(emp_id)
CONSTRAINT UK_itusers_empid UNIQUE (emp_id)
);

ОДНОЗНАЧНЫЙ КЛЮЧ

ALTER TABLE dbo.employees
ADD CONSTRAINT UK_employees_person UNIQUE (emp_name, birth_date, mother_name);

ПРОВЕРКА ОГРАНИЧЕНИЙ

ALTER TABLE dbo.employees
ADD CONSTRAINT CK_employees_birthdate CHECK ( birth_date > '19600101');

работает ли контрольное ограничение

ALTER TABLE dbo.employees WITH CHECK
CHECK CONSTRAINT CK_employees_contacts;

СТАНДАРТНЫЙ КОНСТРАКТ

ALTER TABLE dbo.employees
ADD CONSTRAINT DK_phonenumber DEFAULT '0690111111' FOR phone_number;

временная таблица sql2017+ - продвинутый sql server 2019 cu18

CREATE TABLE dbo.persondate (
id int IDENTITY(1,1) PRIMARY KEY NOT NULL
, last_name NVARCHAR(50) NOT NULL
, first_name NVARCHAR(50) NOT NULL
, phone_number VARCHAR(22)
, адрес электронной почты VARCHAR(50)
, sysfirstdate DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL
, sysenddate DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL
, PERIOD FOR SYSTEM_TIME(sysfirstdate,sysenddate)
) WITH (
SYSTEM_VERSIONING = ON (HISTORY_RETENTION_PERIOD = 3 MONTHS
, HISTORY_TABLE = dbo.persondate_history)
)
/* временная таблица - удалена*/
SELECT * FROM dbo.persondate_history temp
LEFT JOIN dbo.persondate actual ON temp.id=actual.id
WHERE actual.id IS NULL;

ИНДЕКС

Типы индексов:
- индекс в памяти: oltp
- индекс хранилища колонок
- хранилище рядов: кластеризованное 1 , некластеризованное

типы индексов хранилища рядов:
- кластеризованный индекс - телефонный справочник, полная таблица
- non-clustered: heap - куча, если на таблице нет кластеризованного индекса
индекс, например, какая страница содержит слово "верховенство закона" на странице 120,130

индексный узел: индексный ключ, адрес
Уровень листьев: стол
корень > междоузлия > листовой узел

некластеризованный индекс. B-дерево

уровень листа: ключ индекса, кластеризованный индекс

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

КЛАСТЕРИЗОВАННЫЙ ИНДЕКС

DROP INDEX idx_testlines_clustered ON dbo.testlines;
CREATE CLUSTERED INDEX idx_testlines_clustered ON dbo.testlines(OrderLineID DESC);

некластеризованные индексы хороши, если вы составляете запрос, который часто используется, например, where

CREATE CLUSTERED INDEX idx_testlines_clustered ON dbo.testlines(OrderLineID DESC);

CREATE NONCLUSTERED INDEX idx_testlines_stockitemid ON dbo.testlines(StockItemID);

принудительное использование индекса - не используйте

стоимость: 2.24

SELECT OrderLineID, StockItemID, UnitPrice, Quantity
FROM dbo.testlines WITH (INDEX (idx_testlines_stockitemid))
WHERE StockItemID=148;

уровень листьев включен в UniteЦена, количество

DROP INDEX idx_testlines_stockitemid ON dbo.testlines;
CREATE NONCLUSTERED INDEX idx_testlines_stockitemid ON dbo.testlines(StockItemID) INCLUDE (UnitPrice,Quantity);

Фильтрованный индекс

CREATE UNIQUE INDEX idx_testorders_isinprogress ON dbo.testorders (CustomerID,isInProgress) WHERE isInProgress=1;
/*FOREIGN KEY > без индекса ! лучшая практика - некластеризованный индекс, отфильтрованный testdb isHIgh education table-ben*/
/*может быть шедевр - не годится для даты*/
CREATE INDEX idx_testorders_2016 ON dbo.testorders (OrderDate) INCLUDE(CustomerID) WHERE OrderDate >= '20160101' AND OrderDate<'20170101';

Переменные

Пакетная обработка и переменные (GO)
- между двумя GO то, что у вас есть - это партия
- переменные живут только в партии
DECLARE @stockItemId INT;
SET @stockItemId = 2;

DECLARE @maxValidTo DATETIME2 = '2020-02-02';

В качестве условия фильтрации, даже в нескольких запросах

SELECT * FROM Warehouse.StockItems
WHERE StockItemID = @stockItemId;

DECLARE @myStockItemName NVARCHAR(500);

SET @myStockItemName = (SELECT StockItemName FROM Warehouse.StockItems
WHERE StockItemID = @stockItemId);

SELECT @myStockItemName AS MyStockItemName;
/* Может хранить только одно значение */
/* То же, что и выше */
DECLARE @stockItemId INT;
SET @stockItemId = 2;
DECLARE @myStockItemName NVARCHAR(500);
SELECT @myStockItemName=StockItemName FROM Warehouse.StockItems
WHERE StockItemID = @stockItemId;

принимает последнее значение, если оно указано в select

DECLARE @myStockItemName NVARCHAR(500);
SELECT @myStockItemName=StockItemName FROM Warehouse.StockItems;

DECLARE @myStockItemName NVARCHAR(500);
DECLARE @myStockItemColorId NVARCHAR(500);

SELECT @myStockItemName=StockItemName, @myStockItemColorId=ColorID FROM Warehouse.StockItems;

SELECT @myStockItemName=StockItemName, @myStockItemColorId=ColorID
FROM Warehouse.StockItems
ORDER BY StockItemName;

SELECT @myStockItemName AS MyStockItemName;
SELECT @myStockItemColorId AS MyStockColorId;

Если результат запроса отсутствует, переменная принимает значение NULL

SELECT StockItemName FROM Warehouse.StockItems
WHERE StockItemID = 10000000;

DECLARE @myStockItemName NVARCHAR(500);
SET @myStockItemName = (SELECT StockItemName FROM Warehouse.StockItems
WHERE StockItemID = 10000000);
SELECT @myStockItemName AS MyStockItemName;

DECLARE @myStockItemName NVARCHAR(500);
SELECT @myStockItemName=StockItemName FROM Warehouse.StockItems
WHERE StockItemID = 10000000;
SELECT @myStockItemName AS MyStockItemName;

ДАТАТИПЫ

https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-types-transact-sql?view=sql-server-ver16

числовое число против плавающего

float approximator rounds, для больших чисел целая часть может быть не точной, только первые 7 точны, x*2^y => 199*2*5 - физика, статистические расчеты для больших значений, например, десятичных недостаточно
числовой/десятичный, то, что вы получаете, для финансовых вещей числовой, например, цена 1,99

деньги - на самом деле десятичные, только с четырьмя дробными частями, которые фиксированы - вы можете записать валюту, но не хранить ее

DECLARE @mymoney_sm SMALLMONEY = 3148.29,
        @mymoney MONEY = 3148.29;
SELECT CAST(@mymoney_sm AS VARCHAR) AS 'SM_MONEY varchar',
        CAST(@mymoney AS DECIMAL) AS 'MONEY DECIMAL';

datetime против datetime2

DECLARE @ido_datetime DATETIME = GETDATE();
DECLARE @ido_datetime2 DATETIME2 = GETDATE();

SELECT @ido_datetime, @ido_datetime2;

datetimeoffset

DECLARE @ido_datetimeoffset datetimeoffset = GETDATE();
DECLARE @ido_datetimeoffset2 datetimeoffset = SYSDATETIMEOFFSET()
SELECT @ido_datetime, @ido_datetimeoffset, @ido_datetimeoffset2;

DECLARE @mostaniIdoUTC datetime2=GETUTCDATE();
SELECT @ido_datetime, @ido_datetimeoffset, @ido_datetimeoffset2, @mostaniIdoUTC;

char против varchar

DECLARE @mychar CHAR(3)='test'
SELECT @mychar;

DECLARE @mychar CHAR(3)='te'
SELECT @mychar;

DECLARE @mychar CHAR(3)='te'
SELECT LEN(@mychar);
SELECT DATALENGTH(@mychar);

DECLARE @myvarchar VARCHAR(3)='test'
SELECT @myvarchar;

DECLARE @myvarchar VARCHAR(3)='te'
SELECT @myvarchar;

DECLARE @myvarchar VARCHAR(3)='te'
SELECT LEN(@myvarchar);
SELECT DATALENGTH(@myvarchar);

GO

nchar против nvarchar

DECLARE @mychar NCHAR(3)='test'
SELECT @mychar;

DECLARE @mychar NCHAR(3)='te'
SELECT @mychar;

DECLARE @mychar NCHAR(3)='te'
SELECT LEN(@mychar);
SELECT DATALENGTH(@mychar);

DECLARE @myvarchar NVARCHAR(3)='test'
SELECT @myvarchar;

DECLARE @myvarchar NVARCHAR(3)='te'
SELECT @myvarchar;

DECLARE @myvarchar NVARCHAR(3)='te'
SELECT LEN(@myvarchar);
SELECT DATALENGTH(@myvarchar);

бинарный

DECLARE @myBinary BINARY(10) = 0x00A1B2C3;
SELECT @myBinary;

перевести символы юникода в ascii

DECLARE @mychar CHAR(3) = 'Győr';
DECLARE @mychar2 CHAR(3) = 'Győr';
SELECT @mychar, @mychar2, LEN(@mychar), DATALENGTH(@mychar),LEN(@mychar2), DATALENGTH(@mychar2);

слишком высокие значения

DECLARE @chunk SMALLINT = 32768;
SELECT @darabszam;

 неявное преобразование

https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-type-conversion-database-engine?view=sql-server-ver16

CAST против CONVERT

DECLARE @chunk SMALLINT = 32764;
SELECT @darabszam;
SELECT CAST(@darabszam AS nvarchar)+'?';

DECLARE @ma DATETIME2 =GETUTCDATE();

SELECT @ma,CONVERT(nvarchar,@ma,1)

TRY_CAST против TRY_CONVERT

DECLARE @chunk SMALLINT = 32764;
SELECT @darabszam;
SELECT TRY_CAST(CAST(@darabszam AS nvarchar)+'?' AS INT);

ФОРМАТ

DECLARE @ma DATETIME2 =GETUTCDATE();

SELECT @ma,FORMAT(@ma,'yyyy. MM. dd.')

DECLARE @datum DATETIME2 = '2022-02-15', @datum2 DATETIME2;
SET @datum2 =CONVERT(DATETIME2, '2/15/2022',101)
SELECT @datum,@datum2

DECLARE @float FLOAT = 2.0 / 5;
SELECT @float,FORMAT(@float,'N2')

Цифровые операции: ОБРЕЗКА, ОБРЕЗАТЬ, ОБРЕЗАТЬ, ОБРЕЗАТЬ

Здесь Лен не смотрит на конец текста:

SELECT TRIM(' a '),LEN(' a '), LEN(TRIM(' a '));
SELECT LTRIM(' a '),LEN(' a '), LEN(LTRIM(' a '));
SELECT RTRIM(' a '),LEN(' a '), LEN(RTRIM(' a '));

SELECT TRIM(' a '),LEN(' a '), LEN(TRIM(' a '));
SELECT LTRIM(' a '),DATALENGTH(' a '), DATALENGTH(LTRIM(' a '));
SELECT RTRIM(' a '),LEN(' a '), LEN(RTRIM(' a '));

ВЕРХНИЙ, НИЖНИЙ

SELECT UPPER('Акцент')
SELECT LOWER('ПРЕДЛОЖЕНИЕ')

РЕПЛИЦИРОВАТЬ, ОСТАВИТЬ, ОБРАТИТЬ, ЗАМЕНИТЬ

SELECT REPLICATE('ho',3)
SELECT LEFT('HELLO WORLD', 5)

SELECT REVERSE('hello')

SELECT REPLACE('HELLO world', 'World', 'JOE')

UPDATE Warehouse.StockItems
SET StockItemName = REPLACE(StockItemName,'StockItemName', 'launcher')

Операции с датой времени: DATEDIFF

SELECT ValidFrom, '2022-01-01 00:00', DATEDIFF(yyyy, ValidFrom,'2022-01-01 00:00')
FROM Warehouse.StockItems

SELECT ValidFrom, '2022-01-01 00:00', DATEDIFF(s, ValidFrom,'2022-01-01 00:00')
FROM Warehouse.StockItems

SELECT ValidFrom, '2022-01-01 00:00', DATEDIFF(s, ValidFrom,'2022-01-01 00:00')
FROM Warehouse.StockItems

SELECT DATEDIFF(s,'2022-01-01 00:00','2022-01-01 05:01')

Округление

SELECT ROUND(123.45,0)
SELECT ROUND(123.55,0)
SELECT ROUND(123.55,1)

ВРЕМЕННАЯ ТАБЛИЦА, ТАБЛИЧНАЯ ПЕРЕМЕННАЯ

временная таблица

существует только в сессии, хранится в tempdb, слишком большая tempdb для многих, для хранения запросов таблиц, преимущество: фильтрованные данные, более быстрые запросы, меньше кода, производительность

CREATE TABLE #myTempTable
(
	Id уникальный идентификатор первичный ключ некластеризованный,
	name NVARCHAR(100) NOT NULL
);

Global temp talbe: сохраняется между сессиями, после завершения всех запросов и сессий он завершается

CREATE TABLE ##globalTempTable
(
	Id уникальный идентификатор первичный ключ некластеризованный,
	name NVARCHAR(100) NOT NULL
);
DROP TABLE ##globalTempTable;

Табличная переменная: существует только в пакетном режиме, не идеальна для большого количества данных

DECLARE @myTableVariable TABLE (
	Id uniqueidentifier первичный ключ некластеризованный,
	name NVARCHAR(100) NOT NULL
);
-- создание общей доски
ИСПОЛЬЗУЙТЕ WideWorldImporters;
GO

SELECT COUNT(*) FROM Sales.Orders;
-- стоимость 0,39
SELECT COUNT(*) FROM Sales.OrderLines;

SELECT TOP 1 * FROM Sales.OrderLines;

CREATE TABLE #Temp_OrderLines
(
	OrderLineID int PRIMARY KEY,
	StockItemID int NOT NULL, --/*FOREIGN KEY*/ References Warehouse.StockItems(StockItemID), -не делайте этого, потому что у него нет имени, вы должны найти имя
	ОПИСАНИЕ NVARCHAR(1000),
	Количество int NOT NULL
);
-- 164 id товара на складе
INSERT INTO #Temp_OrderLines(OrderLineID,StockItemID,[DESCRIPTION],Quantity)
SELECT OrderLineID,StockItemID,[Description],Quantity
FROM Sales.OrderLines
WHERE StockItemID=164;

SELECT * FROM #Temp_OrderLines;
--0.013 стоимость
SELECT COUNT(*) FROM #Temp_OrderLines;

INSERT INTO #Temp_OrderLines(OrderLineID,StockItemID,[DESCRIPTION],Quantity)
VALUES (10000,1,'Тест', 1);

SELECT COUNT(*) FROM #Temp_OrderLines;

DECLARE @myTableVariable TABLE (
	Id uniqueidentifier первичный ключ некластеризованный,
	Name NVARCHAR(100) NOT NULL
);

INSERT INTO @myTableVariable (Id,Name) VALUES (NEWID(), 'Teszt');

SELECT * FROM @myTableVariable;

USE WideWorldImporters;

/*
    Объявление типа таблицы
*/
DROP TYPE IF EXISTS mytable;
CREATE TYPE mytable AS table
(
    id int NOT NULL,
	name NVARCHAR(100) NOT NULL
);

/*
    С этого момента вам не нужно объявлять переменные таблицы
    с информацией о столбце, просто ссылайтесь на тип.
*/
DECLARE @t AS mytable;
INSERT INTO @t VALUES(1,'HELLO WORLD');

SELECT * FROM @t;


CREATE TABLE dbo.testtable AS mytable; -- нет

/*это да*/
ВЫБРАТЬ *
INTO dbo.testtable
из @t
WHERE 1 = 2; -столбцы работают, но не строки


SELECT * FROM dbo.testtecske;

ЭЛЕМЕНТЫ ПОТОКА УПРАВЛЕНИЯ (IF, WHILE)

разветвление кода

ЕСЛИ... ИЛИ
ЕСЛИ выражения

- трехзначная логика: TRUE, FALSE, UNKNOWN (NULL)
простой пример

DECLARE @year SMALLINT;
SET @year = 2022;

ЕСЛИ @year = YEAR(GETDATE())
BEGIN
PRINT 'Match! Текущий год: ' + CAST(@year AS varchar(5));
END
ELSE
BEGIN
    PRINT 'НЕТ СОВПАДЕНИЯ! Переменная THE Year:' + CAST(@year AS varchar(5));
    PRINT 'НЕТ СОВПАДЕНИЙ! текущий год:' + CAST(YEAR(GETDATE()) AS varchar(5));
END

DECLARE @year SMALLINT;
SET @year = (SELECT YEAR(MAX(OrderDate)) FROM WideWorldImporters.Sales.Orders);

IF @year = YEAR(GETDATE())
BEGIN
PRINT 'Совпадение! Текущий год: ' + CAST(@year AS varchar(5));
END
ELSE
BEGIN
    PRINT 'НЕТ СОВПАДЕНИЯ! Переменная THE Year:' + CAST(@year AS varchar(5));
    PRINT 'НЕТ СОВПАДЕНИЙ! текущий год:' + CAST(YEAR(GETDATE()) AS varchar(5));
END

NULL обработка вложенных элементов if

DECLARE @year SMALLINT;
SET @year = (SELECT YEAR(MAX(OrderDate)) FROM WideWorldImporters.Sales.Orders WHERE OrderID=500000);

IF @year = YEAR(GETDATE())
BEGIN
PRINT 'Совпадение! Текущий год: ' + CAST(@year AS varchar(5));
END
ELSE
BEGIN
    ЕСЛИ @year НЕ NULL
    BEGIN
    PRINT 'НЕТ СОВПАДЕНИЯ! Переменная THE Year:' + CAST(@year AS varchar(5));
    PRINT 'НЕТ СОВПАДЕНИЙ! текущий год:' + CAST(YEAR(GETDATE()) AS varchar(5));
    END
    ELSE
    BEGIN
    PRINT 'Значение @year равно NULL'
    END
END
GO

Активная БД, она только для ваших собственных, другие сессии все еще могут использовать ее, убейте ее

DECLARE @year SMALLINT;
SET @year = 2023;
ЕСЛИ @year = YEAR(GETDATE())
BEGIN
    USE WideWorldImporters
END

Сложное выражение if

последний день последнего месяца = today-20 || today is IN (monday, tuesday, friday) || today = 2023-02-20
истинный или ложный

PRINT DAY(GETDATE())
PRINT EOMONTH(GETDATE(),-1); -- февраль
PRINT DATEDIFF(dd, GETDATE(),1 )
PRINT CAST(DATEADD(day, -20,GETDATE()) AS date);
PRINT DATENAME(dw,GETDATE())
PRINT GETDATE()

IF EOMONTH(GETDATE(),-1)= CAST(DATEADD(day, -20,GETDATE()) AS date) OR DATENAME(dw,GETDATE()) IN ('Monday','Tuesday','Friday') OR CAST(GETDATE() AS date) = '2023-02-20'
BEGIN
PRINT 'ЭТО ПРАВДА!
END

/*testcase-2*/

IF EOMONTH('2023-03-20',-1)= CAST(DATEADD(day, -20,GETDATE()) AS date) OR DATENAME(dw,'2023-02-19') IN ('Monday','Tuesday','Friday') OR CAST(GETDATE() AS date) = '2023-02-20'
BEGIN
PRINT 'ЭТО ПРАВДА!
PRINT 'ЭТО ВСЕ ЕЩЕ ПРАВДА!
END

/*тестовый пример 3*/

IF EOMONTH('2023-03-20',-1)= CAST(DATEADD(day, -20,GETDATE()) AS date) OR DATENAME(dw,'2023-02-19') IN ('Monday','Tuesday','Friday') OR CAST(GETDATE() AS date) = '2023-01-28'
BEGIN
PRINT 'ЭТО ПРАВДА!
PRINT 'ЭТО ВСЕ ЕЩЕ ПРАВДА!
END
ELSE
BEGIN
ВЫВЕСТИ 'НЕПРАВДА'
END
DECLARE @yeara SMALLINT=2015, @yearb SMALLINT;
 SELECT @yearb;

ЕСЛИ СУЩЕСТВУЕТ

Используйте WideWorldImporters;
GO

DECLARE @yeara SMALLINT=2015, @yearb SMALLINT=2023;

IF EXISTS (SELECT OrderID FROM SAles.Orders WHERE YEAR(OrderDate) IN (@yeara,@yearb)
)
BEGIN
PRINT 'Есть заказы в ' + CAST(@yeara как VARCHAR(5))+ ' или в '+CAST(@yearb как VARCHAR(5))
END
ELSE
BEGIN
    PRINT 'Нет заказов в ' + CAST(@yeara как VARCHAR(5))+ ' или в '+CAST(@yearb как VARCHAR(5))
END ;
/* Та же задача с небольшим добавлением NULL*/
DECLARE @yeara SMALLINT=2015, @yearb SMALLINT;
 SELECT @yearb;

IF EXISTS (SELECT OrderID FROM SAles.Orders WHERE YEAR(OrderDate) IN (@yeara,@yearb)
)
BEGIN
PRINT 'Есть заказы в ' + CAST(@yeara как VARCHAR(5))+ ' или в '+CAST(@yearb как VARCHAR(5))
END
ELSE
BEGIN
    PRINT 'Нет заказов в ' + CAST(@yeara как VARCHAR(5))+ ' или в '+CAST(@yearb как VARCHAR(5))
END ;

GO
/* ОБРАБОТКА НУЛЕЙ*/

DECLARE @yeara SMALLINT=2015, @yearb SMALLINT;
 SELECT @yearb;

IF (@yeara IS NULL OR @yearb IS NULL)
BEGIN
PRINT 'Обнаружено значение(я) UNKNOWN';

END
ELSE
BEGIN
IF EXISTS (SELECT OrderID FROM SAles.Orders WHERE YEAR(OrderDate) IN (@yeara,@yearb) AND (@yeara IS NOT NULL OR @yearb IS NOT NULL)
)
BEGIN
PRINT 'Есть заказы в ' + CAST(@yeara как VARCHAR(5))+ ' или в '+CAST(@yearb как VARCHAR(5));
END
ELSE
BEGIN
    PRINT 'Нет заказов в ' + CAST(@yeara как VARCHAR(5))+ ' или в '+CAST(@yearb как VARCHAR(5));
END
END;

LOOPS

синтаксис:

WHILE (выражение)
НАЧАЛО

КОНЕЦ
(ПРОДОЛЖАТЬ, ПРЕРЫВАТЬ, ИДТИ 1000)

DROP TABLE IF EXISTS dbo.t1, dbo.t2, dbo.t3;
CREATE TABLE dbo.t1 (num int);
CREATE TABLE dbo.t2 (num int IDENTITY(1,1));
CREATE TABLE dbo.t3 (num int IDENTITY(1,1));

/* простой цикл вставки INSERT 1-1000 > dbo.t1 метод 1*/

DECLARE @cnt int=1;

WHILE @cnt 1000 кроме 5 С колонкой автоинкремента (метод 2)
 CONTINUE IF @cnt=5
*/

SET IDENTITY_INSERT dbo.t2 ON;

DECLARE @cnt int=0;
WHILE (@cnt < 1001)
BEGIN
    SET @cnt +=1;
    IF @cnt=5 CONTINUE;
    INSERT INTO dbo.t2 (num) VALUES (@cnt);
END

SELECT num FROM dbo.t2;

SET IDENTITY_INSERT dbo.t2 OFF;

/*
прервать цикл на @cnt=5
*/
TRUNCATE TABLE dbo.t2;

SET IDENTITY_INSERT dbo.t2 ON;

DECLARE @cnt int=0;
WHILE (@cnt  dbo.t3 метод 3
*/
INSERT INTO dbo.t3 DEFAULT VALUES;
GO 1000

SELECT * FROM dbo.t3;

цикл с задержкой

DECLARE @cnt int=1;

WHILE @cnt <= 10
BEGIN

    PRINT 'Значение счетчика равно '+CAST(@cnt AS varchar(5));
    ОЖИДАНИЕ ЗАДЕРЖКИ '00:00:01'
     SET @cnt+= 1;
   

END

/*ЭТО ХОРОШО*/

DECLARE @cnt int=1;

WHILE @cnt <= 10
BEGIN

    RAISERROR('Значение счетчика равно %d',0,1,@cnt) WITH NOWAIT;
    WAITFOR DELAY '00:00:01'
     SET @cnt+= 1;
END

@@ROWCOUNT

считает последнее утверждение

DECLARE @rows int;
UPDATE #mytemp
SET Количество=1000
WHERE Quantity>100;
SET @rows=@@ROWCOUNT;
Если @rows > 0
BEGIN
    SELECT CAST(@rows as varchar(5)) + 'строки были обновлены'
END
ELSE
BEGIN
SELECT 'Ни одна строка не была обновлена'
END

РАБОТА С ОШИБКАМИ

Типы ошибок:
- ошибка преобразования типа данных
- ошибка оценки (ошибка оценки)
- синтаксическая ошибка > не может быть обработана в t-sql
- логическая ошибка
- ошибка разрешения имени > не может быть обработана в t-sql
- ошибка нарушения ограничений
- ошибка при срабатывании

количество -1000

Зачем мы пишем обработку ошибок?
- Я не хочу ломать код
- Я хотел бы зарегистрировать ошибку
- выдавать код ошибки в более удобоваримой форме

Как?
- не потеряйте код
- или избавиться от кода, чтобы избежать серьезных проблем

Методы устранения неполадок:
- структурированная обработка ошибок с помощью: TRY > CATCH
- @@ERROR встроенная функция, не использовать

09:17:15Начало выполнения запроса на строке 42
Msg 8134, Уровень 16, Состояние 1, Строка 1
Возникла ошибка деления на ноль.
Общее время выполнения: 00:00:00.013

Msg 8134 > код ошибки (id)
Уровень > Уровни тяжести
Состояние > статус
Строка > строка, где находится ошибка

пример деления на ноль

ПРИНТ 10/0;

ПОПРОБУЙ... ПОЙМАЙ

НАЧНИТЕ ПОПЫТКУ
    ПРИНТ 10/0;
ЗАВЕРШИТЬ ПОПЫТКУ
НАЧАЛО СЛУЧАЯ
    PRINT '0 - тупой!';
END CATCH;

встроенные функции обработки ошибок

PRINT 'Произошла ошибка!';
PRINT 'Номер ошибки: ' + CAST(ERROR_NUMBER() AS nvarchar(10));
PRINT 'Сообщение об ошибке: ' + ERROR_MESSAGE();
PRINT 'Состояние ошибки: ' + CAST(ERROR_STATE() AS nvarchar(10));
PRINT 'Серьезность ошибки: ' + CAST(ERROR_SEVERITY() AS nvarchar(10));
PRINT 'Строка ошибки: ' + CAST(ERROR_LINE() AS nvarchar(10));
PRINT 'Модуль ошибки: ' + COALESCE(ERROR_PROCEDURE(),");

НАЧНИТЕ ПОПЫТКУ
    ПРИНТ 10/0;
ЗАВЕРШИТЬ ПОПЫТКУ
НАЧАЛО СЛУЧАЯ
    PRINT 'Произошла ошибка!';
    PRINT 'Номер ошибки: ' + CAST(ERROR_NUMBER() AS nvarchar(10));
    PRINT 'Сообщение об ошибке: ' + ERROR_MESSAGE();
    PRINT 'Состояние ошибки: ' + CAST(ERROR_STATE() AS nvarchar(10));
    PRINT 'Серьезность ошибки: ' + CAST(ERROR_SEVERITY() AS nvarchar(10));
    PRINT 'Строка ошибки: ' + CAST(ERROR_LINE() AS nvarchar(10));
    PRINT 'Модуль ошибки: ' + COALESCE(ERROR_PROCEDURE(),'');
END CATCH;

PRINT 'Модуль ошибки: ' + COALESCE(ERROR_PROCEDURE(),");

   НАЧАТЬ ПОПЫТКУ
   EXEC dbo.badprocedure
   ЗАВЕРШИТЬ ПОПЫТКУ
   НАЧАЛО КАТЧА

   КОНЕЦ КАТЧА

ошибка восстановления имени

НАЧАТЬ ПОПЫТКУ
    DECLARE @unitprice INT=0;
    SELECT * FROM dbo.notavailable;

    PRINT 10/@unitprice;
КОНЕЦ ПОПЫТКИ
BEGIN CATCH
    PRINT 'Произошла ошибка!';
    PRINT 'Номер ошибки: ' + CAST(ERROR_NUMBER() AS nvarchar(10));
    PRINT 'Сообщение об ошибке: ' + ERROR_MESSAGE();
    PRINT 'Состояние ошибки: ' + CAST(ERROR_STATE() AS nvarchar(10));
    PRINT 'Серьезность ошибки: ' + CAST(ERROR_SEVERITY() AS nvarchar(10));
    PRINT 'Строка ошибки: ' + CAST(ERROR_LINE() AS nvarchar(10));
    PRINT 'Модуль ошибки: ' + COALESCE(ERROR_PROCEDURE(),'');
END CATCH;

обработка ошибок разрешения имен модулем

GO
CREATE OR ALTER PROCEDURE dbo.badprocedure
AS
    SELECT * FROM dbo.notavailable;
GO
EXEC dbo.badprocedure;
BEGIN TRY
    DECLARE @unitprice int=0;
    EXEC dbo.badprocedure;
    PRINT 10/@unitprice;
КОНЕЦ ИСПЫТАНИЯ
BEGIN CATCH
    PRINT 'Произошла ошибка!';
    PRINT 'Номер ошибки: ' + CAST(ERROR_NUMBER() AS nvarchar(10));
    PRINT 'Сообщение об ошибке: ' + ERROR_MESSAGE();
    PRINT 'Состояние ошибки: ' + CAST(ERROR_STATE() AS nvarchar(10));
    PRINT 'Серьезность ошибки: ' + CAST(ERROR_SEVERITY() AS nvarchar(10));
    PRINT 'Строка ошибки: ' + CAST(ERROR_LINE() AS nvarchar(10));
    PRINT 'Модуль ошибки: ' + COALESCE(ERROR_PROCEDURE(),'');
END CATCH;

ошибка отрицательной цены единицы продукции путем обработки THROW

НАЧАТЬ ПОПЫТКУ
    DECLARE @unitprice INT=-20;
    ЕСЛИ @unitprice < 0
    BEGIN
    -- THROW допустимый диапазон начинается с 50000, уровень серьезности 16
        THROW 55555, 'Значение параметра @unitprice меньше нуля!', 1;
    END
    PRINT 'SOR'
END TRY
BEGIN CATCH
    PRINT 'Произошла ошибка!';
    PRINT 'Номер ошибки: ' + CAST(ERROR_NUMBER() AS nvarchar(10));
    PRINT 'Сообщение об ошибке: ' + ERROR_MESSAGE();
    PRINT 'Состояние ошибки: ' + CAST(ERROR_STATE() AS nvarchar(10));
    PRINT 'Серьезность ошибки: ' + CAST(ERROR_SEVERITY() AS nvarchar(10));
    PRINT 'Строка ошибки: ' + CAST(ERROR_LINE() AS nvarchar(10));
    PRINT 'Модуль ошибки: ' + COALESCE(ERROR_PROCEDURE(),'');
END CATCH;

THROW in CATCH - я ломаю код, но сначала я удаляю причину ошибки

НАЧАТЬ ПОПЫТКУ
    DECLARE @unitprice INT=-20;
    ЕСЛИ @unitprice < 0
    BEGIN
    -- THROW допустимый диапазон начинается с 50000, уровень серьезности 16
        THROW 55555, 'Значение параметра @unitprice меньше нуля!', 1;
    END
    PRINT 'SOR'
END TRY
BEGIN CATCH
    PRINT 'Произошла ошибка!';
    PRINT 'Номер ошибки: ' + CAST(ERROR_NUMBER() AS nvarchar(10));
    PRINT 'Сообщение об ошибке: ' + ERROR_MESSAGE();
    PRINT 'Состояние ошибки: ' + CAST(ERROR_STATE() AS nvarchar(10));
    PRINT 'Серьезность ошибки: ' + CAST(ERROR_SEVERITY() AS nvarchar(10));
    PRINT 'Строка ошибки: ' + CAST(ERROR_LINE() AS nvarchar(10));
    PRINT 'Модуль ошибки: ' + COALESCE(ERROR_PROCEDURE(),'');
    SELECT CAST(@unitprice AS NVARCHAR(100)) + ' значение цены единицы товара вызывает ошибку';
    ОТКАЗ;
END CATCH;

RAISE ERROR - в catch не ломает код

НАЧАТЬ ПОПЫТКУ
    DECLARE @myvar int;

    SET @myvar = -1;

    /* выброс ошибки с аргументом, если значение недопустимо */
    IF @myvar < 0
        RAISERROR ('Значение параметра @myvar равно %d!', 16, 1, @myvar);

    PRINT 'Эта строка не выполняется!'
END TRY
BEGIN CATCH
    PRINT 'Произошла ошибка!
    PRINT 'Номер ошибки: ' + CAST(ERROR_NUMBER() AS nvarchar(10));
    PRINT 'Сообщение об ошибке: ' + ERROR_MESSAGE();
    PRINT 'Состояние ошибки: ' + CAST(ERROR_STATE() AS nvarchar(10));
    PRINT 'Серьезность ошибки: ' + CAST(ERROR_SEVERITY() AS nvarchar(10));
    PRINT 'Строка ошибки: ' + CAST(ERROR_LINE() AS nvarchar(10));
    PRINT 'Модуль ошибки: ' + COALESCE(ERROR_PROCEDURE(),'');
END CATCH;

НАЧАЛО ПОПЫТКИ
    DECLARE @unitprice INT=-20;
    IF @unitprice < 0
    BEGIN
    -- THROW допустимый диапазон начинается с 50000, уровень серьезности 16
        THROW 55555, 'Значение параметра @unitprice меньше нуля!', 1;
    END
    PRINT 'SOR'
END TRY
BEGIN CATCH
    PRINT 'Произошла ошибка!';
    PRINT 'Номер ошибки: ' + CAST(ERROR_NUMBER() AS nvarchar(10));
    PRINT 'Сообщение об ошибке: ' + ERROR_MESSAGE();
    PRINT 'Состояние ошибки: ' + CAST(ERROR_STATE() AS nvarchar(10));
    PRINT 'Серьезность ошибки: ' + CAST(ERROR_SEVERITY() AS nvarchar(10));
    PRINT 'Строка ошибки: ' + CAST(ERROR_LINE() AS nvarchar(10));
    PRINT 'Модуль ошибки: ' + COALESCE(ERROR_PROCEDURE(),'''');
    SELECT CAST(@unitprice AS NVARCHAR(100)) + ' валус unitprice вызывает ошибку';
    RAISERROR ('%d значение цены единицы товара вызывает ошибку!', 15, 1, @unitprice);
END CATCH;

RAISERROR может интегрироваться в программу просмотра событий > журнал windows > приложения
генерировать уникальный код ошибки

НАЧАТЬ ПОПЫТКУ
    DECLARE @unitprice INT=-20;
    ЕСЛИ @unitprice < 0
    BEGIN
    -- RAISERROR
        DECLARE @myerrorcode UNIQUEIDENTIFIER=NEWID();
        DECLARE @myerrorstring varbinary(16)= CAST(@myerrorcode AS varbinary(16));
        RAISERROR ('Значение цены единицы товара %d вызывает ошибку! Ваш код ошибки: %x', 16, 1, @unitprice,@myerrorstring) WITH LOG;
    END
    PRINT 'SOR'
END TRY
BEGIN CATCH
    PRINT 'Произошла ошибка!
    PRINT 'Номер ошибки: ' + CAST(ERROR_NUMBER() AS nvarchar(10));
    PRINT 'Сообщение об ошибке: ' + ERROR_MESSAGE();
    PRINT 'Состояние ошибки: ' + CAST(ERROR_STATE() AS nvarchar(10));
    PRINT 'Серьезность ошибки: ' + CAST(ERROR_SEVERITY() AS nvarchar(10));
    PRINT 'Строка ошибки: ' + CAST(ERROR_LINE() AS nvarchar(10));
    PRINT 'Модуль ошибки: ' + COALESCE(ERROR_PROCEDURE(),'');
    SELECT CAST(@unitprice AS NVARCHAR(100)) + ' валус unitprice вызывает ошибку';
    ОТКАЗ;
END CATCH;

обработка ошибок при INSERT

USE testdb;
GO

DROP TABLE IF EXISTS dbo.military;
CREATE TABLE dbo.military(
    id INT IDENTITY(1,1) PRIMARY KEY
    , имя_солдата varchar(100) NOT NULL
    , soldier_bdate date CHECK (soldier_bdate > '19780101')
)

BEGIN TRY
    INSERT INTO dbo.military (soldier_name,soldier_bdate)
    VALUES ('John Ramobo', '19770101')
    -- RAISERROR
ЗАВЕРШИТЬ ПОПЫТКУ
НАЧАЛО СЛУЧАЯ
    ЕСЛИ НОМЕР_ОШИБКИ()=547
    BEGIN
    DECLARE @myerrorcode UNIQUEIDENTIFIER=NEWID();
        DECLARE @myerrorstring varbinary(16)= CAST(@myerrorcode AS varbinary(16));
        RAISERROR ('Проверка нарушения ограничения. Ваш код ошибки: %x', 16, 1,@myerrorstring) WITH LOG;
    END
    PRINT 'Произошла ошибка!
    PRINT 'Номер ошибки: ' + CAST(ERROR_NUMBER() AS nvarchar(10));
    PRINT 'Сообщение об ошибке: ' + ERROR_MESSAGE();
    PRINT 'Состояние ошибки: ' + CAST(ERROR_STATE() AS nvarchar(10));
    PRINT 'Серьезность ошибки: ' + CAST(ERROR_SEVERITY() AS nvarchar(10));
    PRINT 'Строка ошибки: ' + CAST(ERROR_LINE() AS nvarchar(10));
    PRINT 'Модуль ошибки: ' + COALESCE(ERROR_PROCEDURE(),'');
END CATCH;
/*любая ошибка*/
НАЧАЛО ПОПЫТКИ
    INSERT INTO dbo.military (soldier_name,soldier_bdate)
    VALUES ('John Ramobo', '19770101')
    -- RAISERROR
ЗАВЕРШИТЬ ПОПЫТКУ
НАЧАЛО КАТЧА
    DECLARE @newerrormessage NVARCHAR(1000);
    SET @newerrormessage = 'Номер ошибки: ' + CAST(ERROR_NUMBER() AS nvarchar(10)) + CHAR(13) + 'Сообщение об ошибке: ' + ERROR_MESSAGE()+ CHAR(13) + 'Состояние ошибки: ' + CAST(ERROR_STATE() AS nvarchar(10))+ CHAR(13) + 'Серьезность ошибки: ' + CAST(ERROR_SEVERITY() AS nvarchar(10))+ CHAR(13) + 'Строка ошибки: ' + CAST(ERROR_LINE() AS nvarchar(10)) + CHAR(13) + 'Модуль ошибки: ' + COALESCE(ERROR_PROCEDURE(),'');
    DECLARE @myerrorcode UNIQUEIDENTIFIER=NEWID();
        DECLARE @myerrorstring varbinary(16)= CAST(@myerrorcode AS varbinary(16));
        RAISERROR ('%s Check constraint violation. Your error code: %x', 16, 1,@newerrormessage,@myerrorstring) WITH LOG;
END CATCH;

вложенный блок try-catch

НАЧНИТЕ ПОПЫТКУ
    ПРИНТ 10/0;
ЗАВЕРШИТЬ ПОПЫТКУ
НАЧАЛО КАТЧА
    НАЧАЛО ПОПЫТКИ
        INSERT INTO dbo.military (id,soldier_name,soldier_bdate)
        VALUES (1, 'John Ramobo', '19790101')
    END TRY
    BEGIN CATCH
        THROW 55555, 'Значение параметра @unitprice меньше нуля!', 2;
    END CATCH
END CATCH

ВИДЕО

- ссылка, значения пересчитываются при расчете, если только не индексированные представления

- Я скрываю представление от пользователей, не помещаю некоторые столбцы в представление и предоставляю к ним доступ
- на практике мы помещаем его в отдельную схему и предоставляем к нему доступ

ЕГЭ WideWorldImporters
GO
SELECT c.CustomerName, o.OrderID, SUM(ol.Quantity) as QuantitySum
FROM Sales.Customers as c
JOIN Sales.Orders as o ON o.CustomerID = c.CustomerID
JOIN Sales.OrderLines as ol ON ol.OrderID = o.OrderID
JOIN Warehouse.StockItems as si ON si.StockItemID = ol.StockItemID
GROUP BY c.CustomerName, o.OrderID;
GO
DROP VIEW IF EXISTS FirsView;
GO
CREATE VIEW FirstView
AS
SELECT c.CustomerName, o.OrderID, SUM(ol.Quantity) as QuantitySum
FROM Sales.Customers as c
JOIN Sales.Orders as o ON o.CustomerID = c.CustomerID
JOIN Sales.OrderLines as ol ON ol.OrderID = o.OrderID
JOIN Warehouse.StockItems as si ON si.StockItemID = ol.StockItemID
GROUP BY c.CustomerName, o.OrderID;
GO
SELECT * FROM FirstView;

С ПРИВЯЗКОЙ К СХЕМЕ, ШИФРОВАНИЕМ

SCHEMABINDING: мы не можем изменить его, чтобы сделать непригодным для использования, например, удалить столбец
ЭНКРИПЦИЯ: мы не можем видеть ваш код (создать для)

GO
CREATE VIEW SecondView
С ПРИВЯЗКОЙ К СХЕМЕ, ШИФРОВАНИЕМ
AS
SELECT c.CustomerName, o.OrderID, SUM(ol.Quantity) as QuantitySum
FROM Sales.Customers as c
JOIN Sales.Orders as o ON o.CustomerID = c.CustomerID
JOIN Sales.OrderLines as ol ON ol.OrderID = o.OrderID
JOIN Warehouse.StockItems as si ON si.StockItemID = ol.StockItemID
GROUP BY c.CustomerName, o.OrderID;
GO

ИЗМЕНИТЬ ВИД

- изменение представления без сброса - DROP VIEW IF EXISTS dbo.VarosokOrsz odok2;
-Используйте страницу VarosokOrszegok2Пока еще существует SCHEMABIDING

GO
ALTER VIEW dbo.VarosokOrsz odours2
AS
SELECT v.Nev AS VarosNev
o.Nev AS OrszagNev
FROM dbo.Varosok v
JOIN dbo.Orszagok o ON v.OrszagId=o.Id;
GO

ИНДЕКСИРОВАННЫЙ ВИД

сохраняется?

- представление должно быть детерминированным
- схематизация, также для пользовательских функций
- могут использовать только знаки

в каких случаях? каковы преимущества? индексированный вид
- находится на диске, поэтому он быстрее

проблема: быстрее выполнять запрос, если есть сложное объединение
если вы изменяете, вы также должны изменить представление

если вы пишете в таблице больше или столько же, сколько читаете, вы не применяете

GO
CREATE VIEW dbo.NemDet
AS
SELECT Nev, GetDate() AS RandomDate
FROM dbo.Varosok
GO

SELECT * FROM dbo.NemDet

CREATE UNIQUE CLUSTERED INDEX ix_Nemdet ON dbo.NemDet(Nev);
-- недетерминированный
GO
ALTER VIEW dbo.NemDet
С ПРИВЯЗКОЙ К СХЕМЕ
AS
SELECT Nev, GetDate() AS RandomDate
FROM dbo.Varosok
GO
CREATE UNIQUE CLUSTERED INDEX ix_Nemdet ON dbo.NemDet(Nev);

-- JOIN, может быть только для внутреннего

DROP VIEW IF EXISTS dbo.TobbTabla;

GO
CREATE VIEW dbo.TobbTabla
С ПРИВЯЗКОЙ К СХЕМЕ
AS
SELECT v.Nev as VarosNev, o.Nev AS OrszagNev
FROM dbo.Varosok v
JOIN dbo.Orszagok o ON v.OrszagId=o.Id

GO
SELECT * FROM dbo.TobbTabla;

CREATE UNIQUE CLUSTERED INDEX ix_Nemdet ON dbo.TobbTabla(VarosNev,OrszagNev);

-- CRUD - создание, чтение, обновление, удаление

-- ORDER BY - по умолчанию не возможен
-- ORDER BY - с TOP
GO
CREATE VIEW dbo.ViewOrderBy
С ПРИВЯЗКОЙ К СХЕМЕ
AS
SELECT TOP 1 v.Nev as VarosNev, o.Nev AS OrszagNev
FROM dbo.Varosok v
JOIN dbo.Orszagok o ON v.OrszagId=o.Id
ORDER BY o.Nev ASC;
GO

Хранимые процедуры

- Входы
- Выходы
- Может вызываться несколько раз

Преимущества хранимых процедур:

- передача параметров
- создает > план выполнения для хранимой процедуры - производительность - поиск параметров - использование другого параметра может быть очень медленным > WITH RECPOMPILE (новый план выполнения для каждого вызова)
- Вы можете сделать это отдельным делом
-https://www.brentozar.com/archive/2013/06/the-elephant-and-the-mouse-or-parameter-sniffing-in-sql-server/

- для каждого параметра тип должен быть

GO
ALTER VIEW dbo.VarosokOrsz odours2
AS
SELECT
v.Nev как VarosNev,
o.Nev как OrszagNev
FROM
dbo.Varosok as v
JOIN dbo.Orszagok as o ON v.OrszagId = o.Id
WHERE v.Nev LIKE 'B%'
GO

CREATE PROCEDURE dbo.TaroltEljarasVarosokOrszagok
AS
BEGIN
	SELECT
	v.Nev как VarosNev,
	o.Nev как OrszagNev
	FROM
	dbo.Varosok as v
	JOIN dbo.Orszagok as o ON v.OrszagId = o.Id
	WHERE v.Nev LIKE 'B%'
END

SELECT * FROM dbo.VarosokOrszagok2;

EXEC dbo.TaroltEljarasVarosokOrszagok;

CREATE TABLE #Eredmeny1
(
	VarosNev NVARCHAR(100),
	LocationNev NVARCHAR(100),
)

INSERT INTO #Eredmeny1
EXEC dbo.TaroltEljarasVarosokOrszagok

SELECT * FROM #Eredmeny1
GO
CREATE PROCEDURE dbo.VarosKereses(@SearchName NVARCHAR(100))
AS
BEGIN
	SELECT
	v.Nev как VarosNev,
	o.Nev как OrszagNev
	FROM
	dbo.Varosok as v
	JOIN dbo.Orszagok as o ON v.OrszagId = o.Id
	WHERE v.Nev LIKE @SearchName
END
GO

EXEC dbo.VarosKereses 'B%'
EXEC dbo.VarosKereses 'V%'

Что такое SQL-инъекция?

- Что набирает пользователь? Нормально ли это?
- "V" > OK
- "V" ; DROP TABLE dbo.students; SELECT * FROMdbo.Teachers WHERE something LIKE '" > ОЧЕНЬ НЕ ОК
- WHERE studentId = 123 AND v.Nev LIKE '"' " или WHERE v.Nev LIKE ''ни один из этих ' OR 1=1″ > ОЧЕНЬ НЕ ОК
-/**/OR//**/

- Каково решение?

EXEC dbo.VarosKereses ''none OR 1=1 "''

DECLARE @param NVARCHAR(100) = ''нет таких ИЛИ 1=1 "'';
EXEC dbo.VarosKereses @param;

GO
CREATE PROCEDURE dbo.VarosKereses2(@SearchName NVARCHAR(100))
AS
BEGIN
	SELECT * FROM dbo.Varosok WHERE Id=1 AND Nev LIKE @SearchName
END
GO

INSERT INTO dbo.Varosok(ID, NEV, OrszagId)
VALUES (100, 'none' OR ''''=''', 1);

SELECT * FROM dbo.Varosok WHERE Id =100;

Вам нужно будет указать параметр таблицы хранимой процедуры
как ЧИТАЕМЫЙ

DROP TYPE IF EXISTS mytable;
CREATE TYPE mytable AS table
(
    id int
);

GO
CREATE OR ALTER PROCEDURE dbo.GetSales(@myt mytable READONLY)
AS
SELECT id FROM @myt WHERE id > 1;
GO

DECLARE @t mytable;
INSERT INTO @t VALUES(1);
INSERT INTO @t VALUES(2);
INSERT INTO @t VALUES(3);

EXEC dbo.GetSales @t

Как мы можем получить возмещение?
OUTPUT - параметр! мы можем передать более одного выходного значения из нашей процедуры хранения

GO
CREATE OR ALTER PROCEDURE dbo.OutputTest (@param1 INT, @outparam int OUTPUT)
AS
BEGIN
SET @outparam=@param1*2;
END
GO

-- вернуть NULL
DECLARE @doubledValue int;
EXEC dbo.OutputTest 2,@doubledValue
SELECT @doubledValue;
GO

-- Это хорошо:
DECLARE @doubledValue int;
EXEC dbo.OutputTest 2, @doubledValue OUTPUT
SELECT @doubledValue;

-- Порядок параметров не может быть обратным
DECLARE @doubledValue int;
EXEC dbo.OutputTest @doubledValue OUTPUT,2;
SELECT @doubledValue;
GO

DECLARE @doubledValue int;
EXEC dbo.OutputTest 2,@doubledValue OUTPUT;
SELECT @doubledValue;

-- за исключением того, что haa задается именем
GO
DECLARE @doubledValue int;
EXEC dbo.OutputTest 2,@outparam = @doubledValue OUTPUT;

SELECT @doubledValue;

Возвращаемое значение - RETURN

GO
CREATE OR ALTER PROCEDURE dbo.ReturnTest(@varosId INT, @varosNev NVARCHAR(100) OUTPUT )
AS
ЕСЛИ @varosId < 0
	RETURN 1;
ELSE
	SET @varosNev = (SELECT Nev FROM dbo.Varosok WHERE Id = @varosId);
	RETURN 0;
GO

DECLARE @ret int;
DECLARE @varos NVARCHAR(100);
EXEC @ret = dbo.ReturnTest 2, @varos OUTPUT
SELECT @ret, @varos
GO
DECLARE @ret int;
DECLARE @varos NVARCHAR(100);
EXEC @ret = dbo.ReturnTest 1, @varos OUTPUT
SELECT @ret, @varos
GO
DECLARE @ret int;
DECLARE @varos NVARCHAR(100);
EXEC @ret = dbo.ReturnTest -3, @varos OUTPUT
SELECT @ret, @varos

SELECT * FROM dbo.Varosok

Выполняется до первого ВОЗВРАТА

- ранее, так что это больше не отменяет

GO
CREATE OR ALTER PROCEDURE dbo.ReturnTest (@varosId INT, @varosNev nvarchar(100) OUTPUT)
AS
BEGIN
если @varosID < 0
BEGIN
RETURN 1;
END

SELECT @varosNev= (SELECT Nev FROM dbo.Varosok WHERE Id = @varosID)
SET @varosNev = @varosNev + '!'
RETURN 0;
END
GO

DECLARE @ret int;
DECLARE @varos NVARCHAR(100);

EXEC @ret = dbo.ReturnTest 2, @varos OUTPUT
SELECT @ret,@varos;
- ранее возвращался @varos, так что это больше не перезаписывается
EXEC @ret = dbo.ReturnTest -3, @varos OUTPUT
SELECT @ret,@varos;
----------------MEGOLDÁS
GO
CREATE OR ALTER PROCEDURE dbo.ReturnTest (@varosId INT, @varosNev nvarchar(100) OUTPUT)
AS
SET @varosNev = NULL;
BEGIN
ЕСЛИ @varosID < 0
BEGIN
RETURN 1;
END

SELECT @varosNev= (SELECT Nev FROM dbo.Varosok WHERE Id = @varosID)
SET @varosNev = @varosNev + '!'
RETURN 0;
END
GO

DECLARE @ret int;
DECLARE @varos NVARCHAR(100);

EXEC @ret = dbo.ReturnTest 2, @varos OUTPUT
SELECT @ret,@varos;
-введенный ранее, поэтому он больше не перезаписывается
EXEC @ret = dbo.ReturnTest -3, @varos OUTPUT
SELECT @ret,@varos;
GO

ОШИБКА

GO
CREATE OR ALTER PROCEDURE dbo.PlaceOrder (@PaymentDate datetime2)
AS
IF @PaymentDate > GETDATE()
THROW 50001, 'Cannot Place an order in future',1;
GO
EXEC dbo.PlaceOrder '2024-01-01'
------------------------------------------------
GO
CREATE OR ALTER PROCEDURE dbo.Osztas(@v1 INT, @v2 INT, @hanyados INT OUTPUT)
AS
BEGIN TRY
SET @hanyados = @v1 / @v2
RETURN 0;
END TRY
НАЧАЛО ПЕРЕХВАТА
SET @hanyados = NULL;
RETURN 1;
КОНЕЦ КАТЧА
GO
DECLARE @ertek INT,@ret INT;
EXEC @ret=dbo.Osztas 10,2, @ertek OUTPUT;
SELECT @ret, @ertek;
GO
DECLARE @ertek INT,@ret INT;
EXEC @ret=dbo.Dealt 10,0, @ertek OUTPUT;
SELECT @ret, @ertek;

значение DEFAULT

GO
CREATE OR ALTER PROCEDURE dbo.Osztas(@v1 INT, @v2 INT= 3, @hanyados INT OUTPUT)
AS
BEGIN TRY
SET @hanyados = @v1 / @v2
RETURN 0;
КОНЕЦ ИСПЫТАНИЯ
НАЧАЛО ПЕРЕХВАТА
SET @hanyados = NULL;
RETURN 1;
КОНЕЦ КАТЧА
GO
DECLARE @ertek INT,@ret INT;
EXEC @ret=dbo.Div 10, @hanyados = @ertek OUTPUT;
SELECT @ret, @ertek;
GO
DECLARE @ertek INT,@ret INT;
EXEC @ret=dbo.Instal 10,5, @ertek OUTPUT;
SELECT @ret, @ertek;

УСТАНОВИТЬ NOCOUNT НА

GO
CREATE OR ALTER PROCEDURE dbo.TaroltEljarasVarosokOrsz odors
AS
BEGIN
УСТАНОВИТЬ ЗНАЧЕНИЕ NOCOUNT НА
SELECT v.Nev AS VarosNev
o.Nev AS OrszagNev
FROM dbo.Varosok v
JOIN dbo.Orszagok o ON v.OrszagId=o.Id
WHERE v.Nev like 'B%';
END
GO
EXEC dbo.TaroltEljarasVarosokOrszagok

Сколько линий было изменено?

GO
CREATE OR ALTER PROCEDURE dbo.VarosFrissites (@searchParam nvarchar(10), @affectedRows INT OUTPUT)
AS
BEGIN
UPDATE dbo.Varosok
SET Nev =LOWER(Nev)
WHERE Nev like @searchParam;
SET @affectedRows=@@ROWCOUNT
END
GO

DECLARE @ar INT;
EXEC dbo.VarosFrissites 'B%', @ar OUTPUT;
PRINT @ar;

ОДС:

- всегда перезапускать
- не государственный, дает тип данных
- входной параметр
- хранимые процедуры не могут быть вызваны

Типы:
- функции системы
- Таблица-значение: не может быть параметризована в представлении
- Скалярные значения: могут быть в формате select
- Агрегат < внешние языки, например, .Net и функции C# могут быть импортированы
https://learn.microsoft.com/en-us/sql/t-sql/statements/create-aggregate-transact-sql?view=sql-server-ver15

TRY-CATCH - НЕ РАБОТАЕТ!!!!!!!!!!!!!!!!
но я вызываю процедуру из хранилища, и она находится там, тогда да.

DDL, вставка, обновление, удаление не работают
ИСПОЛЬЗОВАТЬ WideWorldImporters;
Перейти к
GO
CREATE OR ALTER FUNCTION Sales.function_date()
RETURNS datetime /* укажите тип данных возвращаемого значения */
AS
BEGIN
    /* возвращаем ваше значение */
    RETURN CURRENT_TIMESTAMP;
END;
GO
-- вызов функции
SELECT Sales.function_date();

общая_сумма_заказа

GO
CREATE FUNCTION Sales.total_sum_orderline(
    @ordelineID int
)
RETURNS decimal(20,3) --С СХЕМАБИНДИНГОМ - мы присоединяем его к структуре таблицы, что иногда помогает в производительности
AS
BEGIN
    RETURN (SELECT Quantity*UnitPrice FROM Sales.OrderLines Where OrderLineID = @ordelineID)
END;
GO
/*
    Использование UDF Scalar для возврата суммарного значения облагаемой налогом строки
    в предложении SELECT.
*/

SELECT Sales.total_sum_orderline(2);
SELECT Quantity*UnitPrice FROM Sales.OrderLines Where OrderLineID = 2;
/* для каждой строки total- будет медленно, подсчет по строкам и CPU будет высоким, нам не нравятся скалярные функции */
SELECT OrderLineID,Sales.total_sum_orderline(2)
FROM Sales.OrderLines;

Инкрустация скаляров UDF

- не выполняет UDF, а разбивает его на операции с таблицами > меньше процессора - ДОПОЛНИТЕЛЬНАЯ ФУНКЦИЯ
С 2019 года мы переупаковываем
Вы должны подчиниться:

Встраиваемая скалярная часть требований UDF
https://learn.microsoft.com/en-us/sql/relational-databases/user-defined-functions/scalar-udf-inlining?view=sql-server-ver15
Kiegészítés: https://support.microsoft.com/en-us/topic/kb4538581-fix-scalar-udf-inlining-issues-in-sql-server-2022-and-2019-f52d3759-a8b7-a107-1ab9-7fbee264dd5d

- например, возврат может быть только
- например, инкрустация Scalar UDF не работает с вычисляемым столбцом!

GO
CREATE OR ALTER FUNCTION dbo.total_sum_orderline(
    @ordelineID int
)
RETURNS decimal(20,3) WITH SCHEMABINDING -- мы присоединяем ее к структуре таблицы, что иногда помогает производительности
AS
BEGIN
    RETURN (SELECT Quantity*UnitPrice FROM dbo.testlines Where OrderLineID = @ordelineID)
END;
GO
SELECT dbo.total_sum_orderline(2);
SELECT Quantity*UnitPrice FROM dbo.testlines Where OrderLineID = 2;
/* вычисляемый столбец - мы теряем инлайнинг здесь - остаемся в пределах таблицы, если мы делаем это
мы также теряем его при проверке ограничений */
ALTER TABLE dbo.testlines
ADD totalsum AS dbo.total_sum_orderline(OrderID);

SELECT OrderLineID, totalsum FROM dbo.testlines
WHERE OrderLineID<200;
/*view-n - мы предпочитаем использовать его здесь */
GO
CREATE OR ALTER VIEW dbo.testview
С ПРИВЯЗКОЙ К СХЕМЕ
AS
SELECT OrderLIneID AS 'lineID
, dbo.total_sum_orderline(OrderLineID) as 'totalsum'
FROM dbo.testlines
GO
SELECT * FROM dbo.testview
WHERE LineID < 200;
/* если представление со схемотехникой содержит скалярную функцию, то скалярная функция также должна быть схемотехнической!!! */
/* проверьте ограничение - инлайнинг не работает */
ALTER TABLE dbo.testlines
ADD CONSTRAINT CK_test_1 CHECK (
(OrderLineID = 200);
UPDATE dbo.testlines
SET OrderLineID=1600
WHERE OrderLineID  500
    RETURN 500;

    RETURN @days;
END
GO
SELECT OrderID, OrderDate, dbo.datediffer(OrderID)
FROM dbo.testorders
WHERE OrderID  500
    BEGIN
        SET @days=500;
    END

    RETURN @days;
END
GO
SELECT OrderID, OrderDate, dbo.datediffer(OrderID)
FROM dbo.testorders
WHERE OrderID  500
    BEGIN
        SET @days=500;
    END

    RETURN @days;
END
GO
SELECT OrderID, OrderDate, dbo.datediffer(OrderID, GETDATE())
FROM dbo.testorders
WHERE OrderID  500
		BEGIN
			SET @days = 500;
		END
	RETURN @days
END
GO

SELECT OrderID,OrderDate,dbo.datediffer2(OrderDate,GETDATE()) AS 'days'
FROM dbo.testorders;

Значение DEFAULT для скалярного UDF

GO
CREATE OR ALTER FUNCTION dbo.defaultfn(@id int=6)
ВОЗВРАЩАЕТСЯ int
AS
BEGIN
    RETURN @id;
END
GO
SELECT dbo.defaultfn(2);
SELECT dbo.defaultfn(default);

Функции с табличными значениями:

- возвращаться всегда с таблицей
- нет JOIN

Типы:
- inline table-valued functions iTVF > 1 SELECT STATEMENT;
- multi statement - MSTVF(ужасный) - не используйте его, плохая оптимизация

ИСПОЛЬЗОВАНИЕ WideWorldImporters;
GO
/*
    Создание встроенного TVF для получения ColorId и ColorName
*/
CREATE FUNCTION Warehouse.GetColor(@colorid int)
ВОЗВРАЩАЕТ ТАБЛИЦУ
AS
RETURN
    (SELECT ColorID, ColorName
     FROM Warehouse.Colors
     WHERE ColorID = @colorid);
GO
/*
    Использование встроенного TVF в простом запросе SELECT в предложении FROM.
*/
SELECT ColorId, ColorName
FROM WareHouse.GetColor(36);

SELECT ColorId, ColorName
FROM WareHouse.GetColor(10);

Значение DEFAULT в TVF UDF

GO
CREATE OR ALTER FUNCTION Warehouse.GetColor(@colorid int=10)
ВОЗВРАЩАЕТСЯ В ТАБЛИЦУ
AS
RETURN
(SELECT ColorID, ColorName
FROM Warehouse.Colors
WHERE ColorID = @colorid);
GO
SELECT ColorId, ColorName
FROM WareHouse.GetColor(default);

APPLY - CROSS APPLY как внутреннее соединение

SELECT StockItemID,StockItemName,ColorName
FROM WareHouse.StockItems s
CROSS APPLY WareHouse.GetColor(s.ColorID) c;

APPLY - OUTER APPLY как LEFT OR RIGHT OUTER join

SELECT StockItemID,StockItemName,ColorName
FROM WareHouse.StockItems s
OUTER APPLY WareHouse.GetColor(s.ColorID) c;

нефункциональная часть
оператор стола:
cross aply вместо подзапроса (return )
определённый подзапрос

перекрестное применение вместо подзапроса (подзапрос возвращает 1 скалярное значение)
например
https://github.com/green-fox-academy/teaching-materials/blob/master/workshop/sql-server-subqueries-aggregations/correlated-quantity/correlated-quantity.md
Измените приведенный ниже запрос следующим образом:
- добавить новый столбец, который показывает максимальное количество для StockItemID
- добавить еще один столбец, показывающий разницу между максимальным
количество для идентификатора StockItemID и каждого количества заказа
| OrderID | StockItemID | Quantity | Max_StockItem_Qty | Max_Diff_Qty | Max_Diff_Qty

/* версия подзапроса: 0.1452 */
SELECT o.OrderID
				, o.StockItemID
				o.Quantity
				,( SELECT MAX(Quantity)
					FROM Sales.OrderLines
					WHERE StockItemID = 180
				 ) AS Max_StockItem_Qty
				,(SELECT maxq.qty - Quantity
						FROM ( SELECT MAX(Quantity) AS qty
										FROM Sales.OrderLines
										WHERE StockItemID = 180
								 ) AS maxq
				) AS Max_Diff_Qty
FROM Sales.OrderLines AS o
WHERE StockItemID = 180;
/* CROSS APPLY - версия оператора таблицы - стоимость CROSS APPLY 0.102256 */
SELECT OrderId,StockItemID,Quantity,Max_StockItem_Qty,Max_StockItem_Qty-Quantity AS Max_Diff_Qty
FROM Sales.OrderLines
	CROSS APPLY (SELECT MAX(Quantity) AS Max_StockItem_Qty FROM Sales.OrderLines
		WHERE StockItemID=180) temptable
WHERE StockItemID = 180;

TRIGGERS

- могу ли я запустить триггер? > НЕТ
- событие активирует триггер! "FIRE_TRIGGER"
- могу ли я дать параметр триггеру? -> НЕТ
- IF ... ELSE структура, пригодная для использования
- try ...catch структурированная обработка ошибок > YES
- создать простой триггер
Триггерные группы:
- Триггеры DML: INSERT, UPDATE, DELETE > МАСТЕРВЕРК
- DDL-триггер: ALTER, CREATE, DROP
- триггер входа в систему

Срабатывает LOGON: ОПАСНО! вы можете не войти в систему

USE testdb;
DROP TABLE IF EXISTS dbo.logonsactivity;
CREATE TABLE dbo.logonsactivity (
    id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    login VARCHAR(50),
    servername VARCHAR(50),
    serverversion VARCHAR(350),
    servicename VARCHAR(50),
    logintime datetime2 DEFAULT GETDATE()
);
-- имя пользователя
SELECT SUSER_NAME();
-- имя сервера \ (экземпляр)
SELECT @@SERVERNAME;
-- версия сервера
SELECT @@VERSION;
-- имя сервиса (имя экземпляра без сервера)
SELECT @@SERVICENAME;
/*триггер*/
GO
CREATE OR ALTER TRIGGER tr_logtouser
НА ВСЕХ СЕРВЕРАХ ДЛЯ ВХОДА В СИСТЕМУ
AS
BEGIN
    INSERT INTO testdb.dbo.logonsactivity
    SELECT SUSER_NAME(),@@SERVERNAME,@@VERSION,@@SERVICENAME,CURRENT_TIMESTAMP;
END
GO
SELECT * FROM testdb.dbo.logonsactivity;
SELECT * FROM sys.server_triggers;
-- удалить триггер входа в систему
DROP TRIGGER tr_logtouser ON ALL SERVER;

Триггеры DDL

DROP TABLE IF EXISTS dbo.Invoices;
CREATE TABLE dbo.Invoices (
    id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    total INT NOT NULL
);
INSERT INTO dbo.Invoices
VALUES
(3,5,200),(4,9,10000), (20,9,14000);
GO
CREATE OR ALTER TRIGGER invoiceprotector ON DATABASE FOR DROP_TABLE, ALTER_TABLE
КАК
BEGIN
PRINT 'вы не можете изменять или удалять никакие таблицы!'
ROLLBACK;
END
PRINT 'hello';
GO
SELECT * FROM sys.triggers;
ALTER TABLE dbo.Invoices
DROP COLUMN total;
DROP TRIGGER invoiceprotector ON DATABASE;

Триггеры DML:

- После срабатывания
- вместо спускового крючка

ПОСЛЕ УДАЛЕНИЯ

DROP TABLE IF EXISTS dbo.storno_invoices
CREATE TABLE dbo.storno_invoices (
    id INT, --PRIMARY KEY,
    customer_id INT NOT NULL,
    total INT NOT NULL,
    delete_time DATETIME2 NOT NULL DEFAULT CURRENT_TIMESTAMP
);
/* AFTER TRIGGER - создание триггера для перемещения удаленных счетов в таблицу strono_table
Триггер SQL SERVER работает на statement!!!!!
- Избегайте переменных! */
GO
CREATE TRIGGER dbo.TR_stroni_invoice ON dbo.Invoices AFTER DELETE
AS
УСТАНОВИТЬ NOCOUNT ON;
BEGIN
    INSERT INTO dbo.cancellation_invoices (id, customer_id, total)
    SELECT * FROM deleted;
END
GO
SELECT * FROM dbo.Invoices;
SELECT * FROM dbo.storno_invoices;

DELETE FROM dbo.Invoices
WHERE total > 1000;

SELECT * FROM dbo.Invoices;
SELECT * FROM dbo.storno_invoices;

создать триггер для запрета: изменение счета-фактуры

GO
CREATE OR ALTER TRIGGER dbo.tr_forbidden_modify_invoice
ON dbo.Invoices
ПОСЛЕ ОБНОВЛЕНИЯ
AS
УСТАНОВИТЬ NOCOUNT ON;
BEGIN
ROLLBACK;
END
GO
SELECT * FROM dbo.Invoices;

UPDATE dbo.Invoices
SET total=1000
WHERE id=3;

INSERT INTO dbo.Invoices
VALUES (25,9,2000),(92,20,20000),(100,19,21000);
UPDATE dbo.Invoices
SET total=1000
WHERE id=3;

где триггеры DML!

!!!!! TRUNCATE не может активироваться с помощью триггеров !!!!!!!!!!

ВЗАМЕН триггеров? > Триггеры DML

например, с самлаком большие уходят в другое место редко

создать триггер
где total >= 1100 > dbo.Invoces

где total dbo.smalltotalinvoices

DROP TABLE IF EXISTS dbo.smalltotalinvoices
  CREATE TABLE dbo.smalltotalinvoices (
    id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    total INT NOT NULL
);
SELECT * FROM dbo.Invoices;
GO
CREATE OR ALTER TRIGGER dbo.tr_smalltotal
на dbo.Invoices
ВМЕСТО ВСТАВКИ
AS
УСТАНОВИТЬ NOCOUNT ON;
BEGIN
 INSERT INTO dbo.Invoices
 SELECT * FROM inserted
 WHERE total >=1100;

 INSERT INTO dbo.smalltototalinvoices
 SELECT * FROM inserted
 WHERE total < 1100;
END
GO
 INSERT INTO dbo.Invoices
 VALUES(3,200,50000);

  INSERT INTO dbo.Invoices
 VALUES(5,200,900);

SELECT * FROM dbo.Invoices;
SELECT * FROM dbo.smalltotalinvoices;

массовая вставка по умолчанию игнорирует триггер

массовая вставка dbo.Invoices
  FROM N'C:\Users\mrhen\Documents\READER\MatReview\WEEK-4\w1d1_smalltotal.csv'
  WITH (fieldterminator=';',rowterminator='0x0A'/* не '\n', потому что это было из Zoli, FIRSTROW=2, если есть заголовок*/)
TRUNCATE TABLE dbo.invoices;
TRUNCATE TABLE dbo.smalltotalinvoices;
/* DE FIRE_TRIGGERS */
массовая вставка dbo.Invoices
  FROM N'C:\Users\mrhen\Documents\READER\MatReview\WEEK-4\w1d1_smalltotal.csv'
  WITH (fieldterminator=';',rowterminator='0x0A', FIRE_TRIGGERS/* не '\n', потому что это было из Zoli, FIRSTROW=2, если есть заголовок*/)

SELECT * FROM dbo.Invoices;
SELECT * FROM dbo.smalltotalinvoices;
SELECT * FROM sys.triggers;
DROP TRIGGER dbo.TR_stroni_invoice, dbo.tr_forbidden_modify_invoice, dbo.tr_smalltototal;

Функция окна

- Оконные агрегатные функции (COUNT, SUM, MIN, MAX)
- Функции ранжирования (ROW_NUMBER, RANK, DENSE_RANK, NTILE)
- Функции смещения (LAG, LEAD, FIRST_VALUE, LAST_VALUE)
- Статистические функции (PERCENTILE_CONT, PERCENTILE_DISC, PERCENT_RANK, CUME_DIST)

OVER() == Оконная функция / SELECT /

наборы строк, учитывая набор строк окна,

ИСПОЛЬЗОВАНИЕ WideWorldImporters;
GO
/* stockitemid, sum_stock_quantities */
SELECT StockItemid, SUM(Quantity) AS sum_stock_quantities
FROM Sales.OrderLines
GROUP BY StockItemID;
GO
SELECT SUM(количество) AS sum_all_quantities
FROM Sales.OrderLines;
/* ФЕЛАДАТ
orderlineid stockitems sum_stock sum_all
1 1 20000 9million
2 1 20000
3 2 1000
*/
WITH myCTE AS (
    SELECT StockItemId, SUM(Quantity) AS sum_stock_quantities
    FROM Sales.OrderLines
    GROUP BY StockItemID
),
myCTE2 AS (
    SELECT SUM(количество) AS sum_all_quantities
    FROM Sales.OrderLines
)
SELECT sol.StockItemID,OrderLineID, myCTE.sum_stock_quantities,myCTE2.sum_all_quantitis, FORMAT(myCTE.sum_stock_quantities/(myCTE2.sum_all_quantitis*1.0),'P2') AS pct_all_quantity
FROM Sales.OrderLines sol
INNER JOIN myCTE ON myCTE.StockItemID=sol.StockItemID
CROSS JOIN myCTE2
ORDER BY sol.[StockItemID] ASC, OrderLineID ASC;
/*ПЕРЕЗАГРУЗКА CTE2 с помощью оконной функции */
SELECT SUM(Quantity) OVER ()
FROM Sales.OrderLines;
/*замена cte1 новой функцией*/
SELECT SUM(Количество) OVER (PARTITION BY StockItemID)
FROM Sales.OrderLines;
/* Версия оконной функции */
SELECT sol.StockItemID,
OrderLineID,
SUM(Quantity) OVER (PARTITION BY StockItemID) AS sum_stock_quantitis,
SUM(Quantity) OVER () AS sum_all_quantities
, FORMAT(SUM(Quantity) OVER (PARTITION BY StockItemID)/(SUM(Quantity) OVER ()*1.0),'P2') AS pct_all_quantity
FROM Sales.OrderLines sol
ORDER BY sol.[StockItemID] ASC, OrderLineID ASC;

окна функция по группам по

SELECT sol.StockItemID,
SUM(Quantity) /*OVER (PARTITION BY StockItemID)*/ AS sum_stock_quantitis,
SUM(SUM(Quantity)) OVER () AS sum_all_quantities
, FORMAT(SUM(Quantity) /*OVER (PARTITION BY StockItemID)*//(SUM(SUM(Quantity)) OVER ()*1.0),'P2') AS pct_all_quantity
FROM Sales.OrderLines sol
GROUP BY StockItemID
ORDER BY sol.[StockItemID] ASC;

функции ранжирования

требование к синтаксису: ORDER BY внутри предложения OVER

ROW_NUMBER, RANK, DENSE_RANK, NTILE

SELECT OrderId, Customerid, OrderDate,
    ROW_NUMBER() OVER (ORDER BY OrderDate) AS ROW_NUMBER
    , Rank() OVER(ORDER BY OrderDate) AS rnk -- пропуски мертвой зоны и следующий ранг в строке
    , DENSE_RANK() OVER(ORDER BY OrderDate) AS dense_rnk -- пропуски тупиков в следующем ряду
    , NTILE (20) OVER(ORDER BY OrderDate) AS n_tile -- деление на 20 частей, возврат кучи
FROM Sales.Orders;

ранжирование по идентификатору заказа на идентификатор клиента

SELECT OrderId, CUstomerid, OrderDate,
    ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS ROW_NUMBER
    , Rank() OVER(PARTITION BY CustomerID ORDER BY OrderDate) AS rnk
    , DENSE_RANK() OVER(PARTITION BY CustomerID ORDER BY OrderDate) AS dense_rnk
    , NTILE (10) OVER(PARTITION BY CustomerID ORDER BY OrderDate) AS n_tile -- разделить на 20 частей, вернуть кучу
FROM Sales.Orders;

заказ в интернет-магазине какие товары находятся в заказе - общая сумма, номер_заказа

SELECT OrderId,
StockItemID,
Количество,
UnitPrice,
(UnitPrice*Количество) AS OrderLine_SUM,
ROW_NUMBER() OVER (PARTITION BY OrderID ORDER BY (SELECT NULL)) AS orderline_number,
SUM(UnitPrice*Quantity) OVER (PARTITION BY OrderID Order BY OrderLineID) as running_total
 FROM Sales.OrderLines;

 SELECT OrderId,
StockItemID,
Количество,
UnitPrice,
OrderLineID,
(UnitPrice*Количество) AS OrderLine_SUM,
ROW_NUMBER() OVER (PARTITION BY OrderID ORDER BY (SELECT NULL)) AS orderline_number,
SUM(UnitPrice*Quantity) OVER (PARTITION BY OrderID Order BY OrderLineID DESC) as running_total
 FROM Sales.OrderLines;

текущий итог

SELECT CityID, CityName, LatestRecordedPopulation AS population,
       (SELECT SUM(LatestRecordedPopulation)
        FROM Application.Cities c2
        WHERE c2.StateProvinceID = c1.StateProvinceID AND c2.CityID <= c1.CityID) AS run_total
FROM Application.Cities c1
WHERE StateProvinceID = 6
ORDER BY CityID;
/* версия функции для windows */
 SELECT CityID, CityName, LatestRecordedPopulation,
 SUM(LatestRecordedPopulation) OVER(ORDER BY CityID) as run_total
FROM Application.Cities
WHERE StateProvinceID = 6

функция смещения
(LAG, LEAD, FIRST_VALUE, LAST_VALUE)

/* идентификатор следующего города */
SELECT CityID, CityName, (SELECT MIN(CityID) FROM Application.Cities c2 WHERE c2.CityID > c1.CityID) AS nextcityid
FROM Application.Cities c1
ORDER by CityID ASC;
/* порядок функций windows */
SELECT CityID, CityName, LEAD(CityID) OVER(ORDER BY CityID) AS nextcityid
FROM Application.Cities
ORDER by CityID ASC;

SELECT CityID, CityName, LAG(CityID) OVER(ORDER BY CityID) AS nextcityid
FROM Application.Cities
ORDER BY CityID ASC;

Оценка кардинальности

ALTER DATABASE WideWorldImporters
SET Compatibility_Level = 150;
GO
ALTER DATABASE SCOPED CONFIGURATION
SET Legacy_Cardinality_Estimation = OFF

--------------
SELECT имя, значение
FROM sys.database_scoped_configurations
WHERE name = 'LEGACY_CARDINALITY_ESTIMATION' ;
-----------
SELECT *
FROM sys.databases
WHERE name = 'WideWorldImporters';

Первый пробный экзамен

/* ЭКЗАМЕН 1
Разработайте небольшое подмножество воображаемой базы данных Books.

В базе данных Books вы хотите хранить данные как минимум о следующем:
    - книги
    - писатели
    - отношения между книгами и писателями по мере необходимости

У каждого писателя может быть несколько книг, но у книги может быть только один писатель.

Следующие данные должны храниться как минимум:
    - Название книги
    - Объем книги в страницах
    - Дата публикации книги
    - Автор книги
    - Название жанра (только один на книгу: научная фантастика, боевик, ужасы и т.д.)

Создайте соответствующие таблицы с необходимыми столбцами данных и соответствующими типами данных.

Создайте как минимум следующие ограничения:
    - PRIMARY KEY
    - ЗАДНИЙ КЛЮЧ

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

Составьте сценарий таблиц.

сколько таблиц необходимо? книги, авторы, жанр
связь между таблицами ? 1->N (книги, авторы), 1->N (книги, жанр)


*/

CREATE DATABASE matrev20230303;
GO
USE matrev20230303;
GO
CREATE SCHEMA books;
GO

DROP TABLE IF EXISTS books.books, books.authors, book.genres;

CREATE TABLE books.authors (
     id INT IDENTITY(1,1) NOT NULL PRIMARY KEY
    имя_автора NVARCHAR(200) NOT NULL
);

CREATE TABLE books.genres (
     id INT IDENTITY(1,1) NOT NULL PRIMARY KEY
    , имя_жанра NVARCHAR(200) NOT NULL
        CONSTRAINT UK_genres_name UNIQUE
);

CREATE TABLE books.books (
     id INT IDENTITY(1,1) NOT NULL PRIMARY KEY
    , title NVARCHAR(200) NOT NULL CONSTRAINT UK_books_title UNIQUE
    , pagenr SMALLINT NOT NULL
        CONSTRAINT CK_books_pagenr CHECK (pagenr > 0)
    , pubdate DATE
    , genre_id INT
    , author_id INT
    CONSTRAINT FK_books_genreid FOREIGN KEY (genre_id) REFERENCES books.genres (id)
    , CONSTRAINT FK_books_authorid FOREIGN KEY (author_id) REFERENCES books.authors (id)
);

/*
INSERT INTO books.authors
VALUES ('Роберт Ладлэм'), ('Джек Хиггинс');

INSERT INTO books.genres
VALUES ('шпионский триллер'),('боевик');

INSERT INTO books.books
VALUES('Рукопись Канцлера',700,'19760101',1,1);

INSERT INTO books.books
VALUES('Ангел смерти',300,'19940302',2,2);

SELECT * FROM books.authors ba
INNER JOIN books.books bb ON ba.id=bb.author_id
INNER JOIN books.genres bg ON bg.id=bb.genre_id;

*/

/* ЭКЗАМЕН 2
-- сколько таблиц следует использовать? 1

-- Больше "наборов результатов" следует объединить? (подзапрос/оператор набора/КТЭ/и т.д...) ANTI SEMI JOIN (WHERE NOT EXISTS/EXCEPT)

-- Какой агрегат следует использовать? NOPE

-- какое объединение следует использовать? NOPE

-- какой столбец следует использовать ? переименовать ? NOPE

-- где следует использовать фильтр(ы) ? / ГДЕ/ ГДЕ, SELECT/ CASE/ ГДЕ

-- следует ли использовать "ORDER BY"? NOPE

Верните названия позиций на складе и их рекомендованные розничные цены
если цена единицы товара больше 20 и меньше 100,
но исключить "Ограниченный запас" и пустые теги.

Используйте соответствующий оператор SET.

Используйте следующую таблицу:
    - Warehouse.StockItems

    
В отчете должны отображаться следующие столбцы:
    - StockItemName
    - РекомендуемаяРозничнаяЦена

*/

/* версия 1 */

SELECT StockItemName,RecommendedRetailPrice FROM Warehouse.StockItems
WHERE UnitPrice > 20 AND UnitPrice  20 AND UnitPrice  20 AND UnitPrice  20 AND UnitPrice  20 AND UnitPrice  YEAR(MIN(OrderDate)) THEN 'Date : '+CAST(MIN(OrderDate) AS varchar(10))
    END AS [Дата первого заказа]
FROM Sales.Customers sc
INNER JOIN Sales.Orders so ON sc.CustomerID=so.CustomerID
GROUP BY sc.CustomerID,CustomerName
ORDER BY [Дата первого заказа] DESC;

/* версия 2 */

SELECT CustomerName,
    CASE
        WHEN 2016 = YEAR(MIN(OrderDate)) THEN 'Month: '+CAST(MONTH(MIN(OrderDate)) AS varchar(2))+', Day: '+CAST(DAY(MIN(OrderDate)) AS varchar(2))
        WHEN 2016 > YEAR(MIN(OrderDate)) THEN 'Date : '+CAST(MIN(OrderDate) AS varchar(10))
    END AS [Дата первого заказа]
FROM Sales.Customers sc
INNER JOIN Sales.Orders so ON sc.CustomerID=so.CustomerID
GROUP BY CustomerName
ORDER BY [Дата первого заказа] DESC;


/* версия 3 */

SELECT CustomerName,
    CASE
        WHEN 2016 = YEAR(MIN(OrderDate)) THEN 'Month: '+CAST(MONTH(MIN(OrderDate)) AS varchar(2))+', Day: '+CAST(DAY(MIN(OrderDate)) AS varchar(2))
        WHEN 2016 > YEAR(MIN(OrderDate)) THEN 'Date : '+CAST(MIN(OrderDate) AS varchar(10))
    END AS [Дата первого заказа]
FROM Sales.Customers sc
INNER JOIN Sales.Orders so ON sc.CustomerID=so.CustomerID
GROUP BY CustomerName
ORDER BY MIN(OrderDate) DESC;

/* версия 4 */


WITH FirstOrder AS (
    SELECT CustomerID, MIN(OrderDate) AS MinOrderDate
    FROM Sales.Orders
    GROUP BY CustomerID
)
SELECT CustomerName
, (CASE
WHEN fo.MinOrderDate >= '20160101' AND fo.MinOrderDate < '20170101' THEN 'Month: ' + CAST(MONTH(fo.MinOrderDate) AS varchar(2)) + ', Day: ' + CAST(DAY(fo.MinOrderDate) AS varchar(2))
WHEN fo.MinOrderDate < '20160101' THEN 'DATE: ' + CAST(fo.MinOrderDate AS varchar)
END) AS 'Дата первого заказа
FROM Sales.Customers c
INNER JOIN FirstOrder fo ON c.CustomerID=fo.CustomerID
ORDER BY [Дата первого заказа] DESC;

/* версия 5 */

SELECT CustomerName,
    CASE
    WHEN (OrderDate BETWEEN '2016-01-01' AND '2016-12-31') THEN
        'Месяц: ' + CAST(MONTH(OrderDate) AS varchar(2)) +
        ', День: ' + CAST(DAY(OrderDate) AS varchar(2))
    ELSE
        'Дата: ' + CAST(OrderDate AS varchar(10))
    END AS 'Дата первого заказа'
FROM (
    SELECT CustomerID, MIN(OrderDate) AS 'OrderDate
    FROM Sales.Orders
    GROUP BY CustomerID) AS first_orders
INNER JOIN Sales.Customers c
    ON first_orders.CustomerID = c.CustomerID
ORDER BY
    OrderDate DESC
;

/* ЭКЗАМЕН 5
-- сколько таблиц следует использовать?   2

-- Больше "наборов результатов" должно быть объединено (подзапрос/оператор набора/TE/etc...) ? NOPE

-- Какой агрегат следует использовать? COUNT

-- какое объединение должно быть использовано?  INNER

-- какой столбец следует использовать ? переименовать ? ДА

-- где следует использовать фильтр(ы) ? / ГДЕ/ГДЕ, ВЫБРАТЬ/СЛУЧАЙ/СЛУЧАЙ

-- следует ли использовать "ORDER BY"? NOPE

Напишите представление в схеме Sales с именем PickupReport.

Оно должно использовать привязку к схеме.

Используйте его для возврата забранных и не забранных заказов по PostalCityID
упорядоченных по убыванию не забранных заказов.

Используйте следующие таблицы:
    - Sales.Orders
    - Sales.Customers

    
В отчете должны отображаться следующие столбцы:
    [Postal ID]: столбец PostalCityID из Sales.Customers
    [Забрано] : количество заказов, которые уже забраны
	[Не забрано]: количество заказов, которые еще не забраны
*/
GO
CREATE OR ALTER VIEW Sales.PickupReport
С ПРИВЯЗКОЙ К СХЕМЕ
AS
SELECT PostalCityID,
    COUNT(CASE
        WHEN PickingCompletedWhen IS NOT NULL THEN 1
    END) AS [Picked up]
   , COUNT(CASE
        WHEN PickingCompletedWhen IS NULL THEN 0
    END) AS [Не забрали]
FROM Sales.Customers sc
INNER JOIN Sales.Orders so
ON sc.CustomerID=so.CustomerID
GROUP BY PostalCityID;
GO

/* версия 2 */
CREATE OR ALTER VIEW Sales.PickupReport
С ПРИВЯЗКОЙ К СХЕМЕ
AS
SELECT c.PostalCityID AS [Почтовый идентификатор]
		, COUNT(o.PickingCompletedWhen) AS [Picked up]
		, COUNT(*)-COUNT(o.PickingCompletedWhen) AS [Не забрали]
FROM Sales.Customers AS c
INNER JOIN Sales.Orders AS o
ON c.CustomerID=o.CustomerID
GROUP BY c.PostalCityID;
GO
/* SHOWCASE */

SELECT * FROM Sales.PickupReport
ORDER BY [Не забрали] DESC;

/* ЭКЗАМЕН 6
-- сколько таблиц должно быть использовано?  2

-- Как следует комбинировать "набор результатов" (подзапрос/оператор набора/КТЭ/и т.д.) ? ???

-- Какой агрегат следует использовать? COUNT/SUM

-- какое объединение следует использовать?  LEFT

-- какой столбец следует использовать ? переименовать ? ДА

-- где должен использоваться фильтр(ы) ? / ГДЕ/ГДЕ, ВЫБРАТЬ/СЛУЧАЙ/ФОРМАТ

-- следует ли использовать "ORDER BY" ? NOPE

Напишите представление в схеме Sales с именем SupplierReport.

Оно должно использовать привязку к схеме.

Показать поставщиков и отношение их товаров к общему количеству проданных товаров.
Если у поставщика нет товара, то должно быть показано 0.00

Используйте следующие таблицы:
    - Закупки.Поставщики
    - Склад.StockItems

    
Отчет должен отображать следующие столбцы:
    [Поставщик]: Имя поставщика из Purchasing.Suppliers
    [Общий процент] : двузначный процент товаров, проданных поставщиком
        по сравнению со всеми товарами, проданными всеми поставщиками.


*/
GO
CREATE OR ALTER VIEW Sales.SupplierReport
С ПРИВЯЗКОЙ К СХЕМЕ
AS
   SELECT
     SupplierName AS [Supplier]
    ,FORMAT(COUNT(ws.StockItemID)/(SUM(COUNT(ws.StockItemID)) OVER() *1.0),'P2') AS [Общий процент]
    FROM Закупки.Поставщики ps
    LEFT JOIN Warehouse.StockItems ws
    ON ps.SupplierID=ws.SupplierID
    GROUP BY SupplierName
GO

SELECT
     SupplierName
    ,FORMAT(COUNT(ws.StockItemID)/(maxstockitem*1.0),'P2') AS [Общий процент].
FROM Purchasing.Suppliers ps
    CROSS APPLY (SELECT COUNT(*) AS maxstockitem FROM Warehouse.StockItems) AS temptable
LEFT JOIN Warehouse.StockItems ws
ON ps.SupplierID=ws.SupplierID
GROUP BY SupplierName,maxstockitem

 -- версия 2

SELECT
     Имя поставщика
    ,FORMAT(COUNT(ws.StockItemID)/((SELECT COUNT(StockItemID) FROM Warehouse.StockItems)*1.0),'P2') AS [Общий процент]
FROM Закупки.Поставщики ps
LEFT JOIN Warehouse.StockItems ws
ON ps.SupplierID=ws.SupplierID
GROUP BY SupplierName

-- версия 3
SELECT
     SupplierName
    ,COUNT(ws.StockItemID)
    ,FORMAT(COUNT(ws.StockItemID)/(SUM(COUNT(ws.StockItemID)) OVER() *1.0),'P2') AS [Общий процент]
FROM Закупки.Поставщики ps
LEFT JOIN Warehouse.StockItems ws
ON ps.SupplierID=ws.SupplierID
GROUP BY SupplierName

/* ЭКЗАМЕН 7
-- сколько таблиц должно быть использовано?  2

-- Как следует комбинировать "набор результатов" (подзапрос/оператор набора/ТЕ/и т.д.) ?

-- Какой агрегат следует использовать?

-- Какое объединение следует использовать?  

-- какой столбец следует использовать ? переименовать ?

-- где следует использовать фильтр(ы) ? / WHERE/HAVING, SELECT/CASE/

-- следует ли использовать "ORDER BY" ?

Создайте встроенную таблично-значимую функцию в схеме Хранилища с именем StockUsbReport
которая возвращает все записи, связанные с USB.

Она должна использовать один параметр:
    - @stockitemname из nvarchar(10).

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

Используйте следующие таблицы:
    - Warehouse.StockItemHoldings
    - Warehouse.StockItems

В отчете должны отображаться следующие столбцы:
    [Stock Item Name] : колонка StockItemName из Warehouse.StockItems
    [Розничная цена] : колонка RecommendedRetailPrice из Warehouse.StockItems
    [Текущая стоимость запасов]: умножение цены единицы товара на его количество.
        Значения должны выглядеть следующим образом: $1,234,567

Продемонстрируйте, как вы используете функцию табличных значений!

*/
GO
CREATE OR ALTER FUNCTION Warehouse.StockUsbReport( @stockitemname nvarchar(10))
RETURNS TABLE
AS
RETURN (
    SELECT
         StockItemName AS [Stock Item Name]
        , RecommendedRetailPrice AS [Розничная цена]
        , FORMAT((QuantityOnHand*UnitPrice),'C0','en-US') AS [Текущая стоимость запаса]
    FROM Warehouse.StockItemHoldings wsh
    INNER JOIN Warehouse.StockItems wsi
    ON wsh.StockItemID=wsi.StockItemID
    WHERE StockItemName LIKE '%'+@stockitemname+'%'
    )
GO

/* SHOWCASE */

SELECT * FROM Warehouse.StockUsbReport('USB');

----
SELECT
     StockItemName
    , РекомендуемаяРозничнаяЦена
    , FORMAT((QuantityOnHand*UnitPrice),'C0','en-US') FROM Warehouse.StockItemHoldings wsh
INNER JOIN Warehouse.StockItems wsi
ON wsh.StockItemID=wsi.StockItemID
WHERE StockItemName LIKE '%'+'USB'+'%'

/* версия 2 */

SELECT
     StockItemName
    , РекомендуемаяРозничнаяЦена
    , FORMAT((QuantityOnHand*UnitPrice),'$###,###,###') FROM Warehouse.StockItemHoldings wsh
INNER JOIN Warehouse.StockItems wsi
ON wsh.StockItemID=wsi.StockItemID



/* УПРАЖНЕНИЕ 8 */
----------------------------------------------------------------------------------------------
 DROP TABLE IF EXISTS Application.Fruits;
    GO
    СОЗДАТЬ ТАБЛИЦУ Application.Fruits
    (
        FruitId int NOT NULL IDENTITY(1,1) PRIMARY KEY,
        FruitName nvarchar(50) NOT NULL
    );

    INSERT INTO Application.Fruits (FruitName)
        VALUES ('банан'),('банан'),('яблоко'),('персик'),('персик'),('персик');
/*
Создайте хранимую процедуру в схеме Application с именем DeleteFruit.

Хранимая процедура удаляет записи из таблицы Application.Fruits.

Хранимая процедура должна принимать в качестве параметров обязательные столбцы:
    - @FruitName nvarchar(50)

Хранимая процедура должна выводить при успехе:
	-@FruitsDeleted int

Используйте обработку ошибок и функции обработки ошибок для вывода номера ошибки,
сообщение об ошибке и степень серьезности ошибки!
Также выведите сообщение 'Delete fruit failed!'.

Если процесс прошел без ошибок, но удаленных строк было 0,
выдайте ошибку со следующим сообщением:
'No such fruit found' и с уровнем серьезности 16.

Покажите, как вы используете хранимую процедуру!
Не показывайте количество затронутых строк в запросе T-SQL в виде информационного сообщения.
*/
GO
CREATE OR ALTER PROCEDURE Application.DeleteFruit (
     @FruitName NVARCHAR(50)
    , @FruitsDeleted INT OUTPUT
    )
AS
    УСТАНОВИТЬ NOCOUNT ON;
    НАЧАЛО ПОПЫТКИ
        DELETE Application.Fruits
        WHERE FruitName=@FruitName;
        SET @FruitsDeleted= @@ROWCOUNT;
        ЕСЛИ @FruitsDeleted = 0
            BEGIN
                RAISERROR('Таких фруктов не найдено',16,7);
            END
    END TRY
    BEGIN CATCH
        PRINT 'Удаление фруктов не удалось!';
        PRINT 'Номер ошибки: ' + CAST(ERROR_NUMBER() AS NVARCHAR(10));
        PRINT 'Сообщение об ошибке: ' + ERROR_MESSAGE();
        PRINT 'Серьезность ошибки: ' + CAST(ERROR_SEVERITY() AS NVARCHAR(10));
    END CATCH
GO

/* версия 2 */

GO
CREATE OR ALTER PROCEDURE Application.DeleteFruit (
    @FruitName nvarchar(50),
    @FruitsDeleted int OUTPUT)
AS
BEGIN
    УСТАНОВИТЬ NOCOUNT ON;
    УСТАНОВИТЬ XACT_ABORT ON;
    НАЧАТЬ ПОПЫТКУ
        НАЧАТЬ ТРАНЗАКЦИЮ;
        DELETE FROM Application.Fruits WHERE FruitName = @FruitName;
        SET @FruitsDeleted = @@ROWCOUNT;
        IF @FruitsDeleted = 0
        BEGIN
            RAISERROR('Таких фруктов не найдено', 16, 1);
        END
        ЗАФИКСИРОВАТЬ ТРАНЗАКЦИЮ;
    ЗАВЕРШИТЬ ПОПЫТКУ
    BEGIN CATCH
        DECLARE @ErrorNumber int = ERROR_NUMBER();
        DECLARE @ErrorMessage nvarchar(4000) = ERROR_MESSAGE();
        DECLARE @ErrorSeverity int = ERROR_SEVERITY();
        RAISERROR('Удаление фрукта не удалось! Error number: %d, Error message: %s, Error severity: %d', 16, 1, @ErrorNumber, @ErrorMessage, @ErrorSeverity);
        ОТКАТ ТРАНЗАКЦИИ;
    КОНЕЦ КАТЧА
END;
GO


/* SHOWCASE */

DECLARE @outputSP INT;
EXEC Application.DeleteFruit 'banana',@FruitsDeleted = @outputSP OUTPUT;
SELECT @outputSP;
GO
DECLARE @outputSP INT;
EXEC Application.DeleteFruit 'banana',@FruitsDeleted = @outputSP OUTPUT;
SELECT @outputSP;

Первый экзамен

/*
Упражнение #1:
Разработайте небольшое подмножество воображаемой базы данных "Ресторан".

В базе данных Restaurant вы хотите хранить данные как минимум о следующем:

Франшиза
Магазин
Взаимосвязь между франшизами и магазинами
Несколько магазинов могут принадлежать одной франшизе. У магазина может быть только один тип франшизы.

Следующие данные должны храниться в минимальном объеме:

Название предприятия (планируйте хранение неанглийских символов).
Тип франшизы (планируется хранить здесь краткие описания - например, продукт, производство)
Адрес магазина
Дата открытия магазина
Работает ли магазин 24/7 или нет (true или false).
Создайте соответствующие таблицы с необходимыми столбцами данных и соответствующими типами данных.

Создайте, как минимум, следующие ограничения:

PRIMARY KEY
ЗАДАННЫЙ КЛЮЧ
При необходимости укажите дополнительные ограничения.

Составьте сценарий для таблиц.
*/
CREATE DATABASE Restaurants;
GO
USE Restraurants;
GO
СОЗДАТЬ СХЕМУ RT;
GO

DROP TABLE IF EXISTS RT.FranchiseTypes;
CREATE TABLE RT.FranchiseTypes (
    FranchiseTypeID INT IDENTITY(1,1) PRIMARY KEY,
    FranchiseName NVARCHAR(100) NOT NULL
        CONSTRAINT UK_FranchiseName UNIQUE
);

DROP TABLE IF EXISTS RT.Franchises;
CREATE TABLE RT.Franchise (
    FranchiseID INT IDENTITY(1,1) PRIMARY KEY,
    BusinessName NVARCHAR(100) NOT NULL,
    FranchiseTypeID INT NOT NULL,
    CONSTRAINT FK_Franchises_FranchiseTypeID FOREIGN KEY (FranchiseTypeID) REFERENCES RT.FranchiseTypes (FranchiseTypeID)
);
DROP TABLE IF EXISTS RT.Stores;
CREATE TABLE RT.Store (
    StoreID INT IDENTITY(1,1) PRIMARY KEY,
    FranchiseID INT NOT NULL,
    StoreAddress NVARCHAR(300) NOT NULL,
    OpeningDate DATE NOT NULL,
    IsOpen24Hours BIT NOT NULL,
    CONSTRAINT FK_Store_Franchise FOREIGN KEY (FranchiseID)
        REFERENCES RT.Franchise(FranchiseID)
);

/*
Упражнение #2
Создайте отчет по всем поставщикам, количеству их транзакций и комментариям.

Вернуть всех поставщиков независимо от количества транзакций (ноль или больше).

Сгруппируйте результаты по имени поставщика и его комментариям.

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

Используйте следующие таблицы:

Закупки.Поставщики
Purchasing.SupplierTransactions
Отчет должен отображать следующие столбцы:

[Имя поставщика]: имя поставщика из таблицы Purchasing.Suppliers
[Количество транзакций]: количество транзакций у каждого поставщика.
Не все поставщики имеют успешные транзакции. Если поставщик не имеет успешных транзакций, отобразите 0 (ноль).
[Примечания]: внутренние комментарии поставщика / InternalComments/
Не у всех поставщиков есть комментарии. Если у поставщика нет комментариев, отобразите 'N/A'
*/

SELECT *
ИЗ INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%transaction%'

SELECT * FROM Purchasing.Suppliers
SELECT * FROM Purchasing.SupplierTransactions;

SELECT
    s.SupplierName AS [Имя поставщика], st.SupplierTransactionID
FROM
    Закупки.Поставщики s
    LEFT JOIN Purchasing.SupplierTransactions st ON s.SupplierID = st.SupplierID


SELECT
    s.SupplierName AS [Имя поставщика], COUNT(st.SupplierTransactionID)
FROM
    Purchasing.Suppliers s
    LEFT JOIN Purchasing.SupplierTransactions st ON s.SupplierID = st.SupplierID
ГРУППА ПО
    s.SupplierName

SELECT s.SupplierName AS [Имя поставщика],
    ISNULL(COUNT(st.FinalizationDate), 0) AS [Количество транзакций],
    ISNULL(s.InternalComments, 'N/A') AS [Примечания]
FROM Purchasing.Suppliers s
    LEFT JOIN Purchasing.SupplierTransactions st ON s.SupplierID = st.SupplierID
GROUP BY s.SupplierName, s.InternalComments
ORDER BY [Количество сделок] DESC;

SELECT s.SupplierName AS [Имя поставщика],
    COUNT(st.SupplierTransactionID) AS [Количество транзакций],
    ISNULL(s.InternalComments, 'N/A') AS [Примечания]
FROM Purchasing.Suppliers s
    LEFT JOIN Purchasing.SupplierTransactions st ON s.SupplierID = st.SupplierID
GROUP BY s.SupplierName, s.InternalComments
ORDER BY [Количество сделок] DESC;
-----

USE WideWorldImporters;
GO
SELECT s.SupplierName AS [Имя поставщика],
    ISNULL(COUNT(st.SupplierTransactionID), 0) AS [Количество транзакций],
    ISNULL(s.InternalComments, 'N/A') AS [Примечания]
FROM Purchasing.Suppliers s
    LEFT JOIN Purchasing.SupplierTransactions st ON s.SupplierID = st.SupplierID
GROUP BY s.SupplierName, s.InternalComments
ORDER BY [Количество сделок] DESC;


/*
Упражнение #3
Создайте отчет о ссылках поставщиков / SupplierReference /, их ожидаемых общих внешних ценах (цены за единицу, умноженные на количество заказанных аутлетов /OrderedOuters*ExpectedUnitPricePerOuter/ )
и общее количество заказанных аутлетов / OrderedOuters / по каждой "ссылке поставщика".

Возвращать только тех поставщиков, у которых ожидаемая дата поставки больше, чем 2014-03-31.

Сгруппируйте результаты по ссылкам поставщиков.

Упорядочьте отчет по [Ссылка на поставщика] в порядке возрастания.

Верните цену, используя любой тип форматирования валюты США.

Используйте следующие таблицы:

Purchasing.PurchaseOrderLines
Purchasing.PurchaseOrders
Отчет должен отображать следующие столбцы:

[Ссылка на поставщика] -> из таблицы Purchasing.PurchaseOrders
[Ожидаемая общая внешняя цена] -> ожидаемая цена по внешним поставщикам
[Заказанные аутлеты] -> общее количество заказанных аутлетов
*/

SELECT * FROM Purchasing.PurchaseOrderLines
SELECT * FROM Purchasing.PurchaseOrders;

USE WideWorldImporters;
GO
SELECT
    po.SupplierReference AS [Ссылка на поставщика],
    FORMAT(SUM(pol.OrderedOuters * pol.ExpectedUnitPricePerOuter), 'C') AS [Ожидаемая общая внешняя цена],
    SUM(pol.OrderedOuters) AS [Ordered outers]
FROM
    Purchasing.PurchaseOrderLines pol
    INNER JOIN Purchasing.PurchaseOrders po ON pol.PurchaseOrderID = po.PurchaseOrderID
ГДЕ
    po.ExpectedDeliveryDate > '2014-03-31'
ГРУППА ПО
    po.SupplierReference
ORDER BY
    [Ссылка на поставщика] ASC;


/*
Упражнение #4
Создайте отчет о последнем заказе каждого клиента и покажите, когда он был сделан по сравнению с каким-либо моментом времени.

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

Затем объявите другую переменную, содержащую строку, и задайте ей значение '%Toys%'.

Ваш отчет не должен содержать ни одного имени клиента со значением созданной строковой переменной.

Используйте следующие таблицы:

Sales.Orders
Sales.Customers
Упорядочить отчет в порядке убывания [Имя клиента].

В отчете должны отображаться следующие столбцы:

[Имя клиента]: столбец CustomerName из таблицы Sales.Customers
[Прошедшие дни]: разница в днях между переменной даты создания и последней датой заказа из таблицы Sales.Orders
*/
USE WideWorldImporters;
GO

DECLARE @currentdate AS DATE = GETDATE();
DECLARE @excludestring AS NVARCHAR(50) = '%Toys%';

SELECT
    c.CustomerName AS [Имя клиента],
    DATEDIFF(DAY, o.OrderDate, @currentdate) AS [Прошедшие дни]
FROM
    Sales.Orders o
    INNER JOIN Sales.Customers c ON o.CustomerID = c.CustomerID
WHERE
    c.CustomerName NOT LIKE @excludeString
    AND o.OrderDate = (
        SELECT MAX(OrderDate)
        FROM Sales.Orders
        WHERE CustomerID = o.CustomerID
    )
ORDER BY
    [Имя клиента] DESC;

/*
Упражнение #5:
Создайте скалярную пользовательскую функцию с привязкой к схеме в схеме Sales с именем SmallestOrderQuantity.

Возвращает наименьшее количество, принадлежащее заказу. В качестве параметра передается идентификатор заказа.

Функция должна возвращать значение типа Int (Количество).

Используйте следующие таблицы:

Sales.OrderLines
Функция должна принимать один параметр:

@orderid int
Покажите, как вы используете скалярную пользовательскую функцию в запросе!
*/
SELECT TOP 1 Quantity
    FROM Sales.OrderLines
    WHERE OrderID = 1
    УПОРЯДОЧИТЬ ПО УБЫВАНИЮ КОЛИЧЕСТВА;

SELECT MIN(Quantity)
    FROM Sales.OrderLines
    WHERE OrderID = 1

USE WideWorldImporters;
GO
CREATE OR ALTER FUNCTION Sales.SmallestOrderQuantity(@orderid int)
ВОЗВРАЩАЕТСЯ int
С ПРИВЯЗКОЙ К СХЕМЕ
AS
BEGIN
    DECLARE @quantity int;
    SELECT @quantity = MIN(Quantity)
    FROM Sales.OrderLines
    WHERE OrderID = @orderid;
    RETURN @quantity;
END;
GO


-- SHOWCASE
SELECT OrderID, Sales.SmallestOrderQuantity(OrderID) AS SmallestQuantity
FROM Sales.OrderLines
ORDER BY OrderID;

/*
Упражнение #6:
Создайте представление с привязкой к схеме в схеме Sales с именем CustomerYearlyOrder.

Верните клиентов и связанные с ними номера годовых заказов вместе с успешным выполнением заказа.

Заказ является успешным, когда он завершен.

Сгруппируйте их по имени клиента и году заказа.

Используйте следующие таблицы:

Sales.Customers
Sales.Orders
Отчет должен отображать следующие столбцы:

[Имя клиента] : Имя клиента из Sales.Customers
[Order Year] : Год даты заказа из Sales.Orders
[Orders] : количество заказов из Sales.Orders
[Picking Completed] : Количество заказов, взятых из Sales.Orders / PickingCompletedWhen /
[Difference]: Разница между столбцами Orders и Picking Completed.
Покажите, как вы используете представление в запросе!
*/
GO
CREATE OR ALTER VIEW Sales.CustomerYearlyOrder
С ПРИВЯЗКОЙ К СХЕМЕ
AS
SELECT c.CustomerName AS [Имя клиента],
       YEAR(o.OrderDate) AS [Order Year],
       COUNT(*) AS [Заказы],
       COUNT(o.PickingCompletedWhen) AS [Picking Completed],
       COUNT(*) - COUNT(*) AS [Разница]
FROM Sales.Customers c
JOIN Sales.Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerName, YEAR(o.OrderDate);
GO
SELECT *
FROM Sales.CustomerYearlyOrder


USE WideWorldImporters;
GO
CREATE OR ALTER VIEW Sales.CustomerYearlyOrder
С ПРИВЯЗКОЙ К СХЕМЕ
AS
SELECT c.CustomerName AS [Имя клиента],
       YEAR(o.OrderDate) AS [Order Year],
       COUNT(o.OrderID) AS [Заказы],
       SUM(
           CASE WHEN o.PickingCompletedWhen IS NOT NULL THEN 1
           ELSE 0 END) AS [Комплектация завершена],
       COUNT(o.OrderID) - SUM(CASE WHEN o.PickingCompletedWhen IS NOT NULL THEN 1 ELSE 0 END) AS [Difference]
FROM Sales.Customers c
JOIN Sales.Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerName, YEAR(o.OrderDate);
GO
--SHOWCASE
SELECT *
FROM Sales.CustomerYearlyOrder;

/*
Упражнение #7:
Создайте встроенную таблично-значимую функцию в схеме Application с именем CityReport.

Она должна использовать два параметра:

@salesterritory из nvarchar(50), со значением по умолчанию "Новая Англия".
@bigcitylimit из decimal(18,2), со значением по умолчанию 100000.00.
Возвращает территории продаж и города, которые им принадлежат, вместе с населением городов, но только для тех городов, где население известно (не отсутствует)!

Используйте следующие таблицы:

Application.StateProvinces
Application.Cities
В отчете должны отображаться следующие столбцы:

[Территория продаж]: колонка SalesTerritory из Application.StateProvinces
[State] : колонка StateProvinceName из Application.StateProvinces
[Город] : столбец CityName из Application.Cities
[Население города]: колонка LatestRecordedPopulation из Application.Cities
[Big City Percentage]: Население города по последним данным в процентах от значения параметра @bigcitylimit Отображение процентного значения с точностью до 2 цифр.
Покажите, как вы используете функцию "табличное значение"!
*/
SELECT * FROM Application.StateProvinces
SELECT * FROM Application.Cities

SELECT sp.SalesTerritory AS [Территория продаж],
        sp.StateProvinceName AS [State],
        c.CityName AS [City],
        c.LatestRecordedPopulation AS [Население города],
        CAST((c.LatestRecordedPopulation / 100000.00 * 100) AS decimal(6,2)) AS [Процент большого города]
FROM Application.StateProvinces sp
INNER JOIN Application.Cities c ON sp.StateProvinceID = c.StateProvinceID
--WHERE sp.SalesTerritory = 'New England' AND c.LatestRecordedPopulation IS NOT NULL



ИСПОЛЬЗОВАНИЕ WideWorldImporters
GO

CREATE OR ALTER FUNCTION Application.CityReport (@salesterritory nvarchar(50) = 'New England',
                                        @bigcitylimit decimal(18,2) = 100000.00)
RETURNS TABLE
AS
RETURN
(
    SELECT sp.SalesTerritory AS [Территория продаж],
        sp.StateProvinceName AS [State],
        c.CityName AS [Город],
        c.LatestRecordedPopulation AS [Население города],
        CAST((c.LatestRecordedPopulation / @bigcitylimit * 100) AS decimal(18,2)) AS [Процент большого города]
    FROM Application.StateProvinces sp
    INNER JOIN Application.Cities c ON sp.StateProvinceID = c.StateProvinceID
    WHERE sp.SalesTerritory = @salesterritory AND c.LatestRecordedPopulation IS NOT NULL
)
GO

SELECT *
FROM Application.CityReport(default,default);
SELECT *
FROM Application.CityReport('Plains',1);


/*
Упражнение #8:
Создайте следующую таблицу:

DROP TABLE IF EXISTS Application.LogAudit;
GO
CREATE TABLE Application.LogAudit
(
    LogAuditId int NOT NULL IDENTITY(1,1) PRIMARY KEY,
    LogData nvarchar(50) NOT NULL
);
Вставьте записи в эту таблицу с помощью операторов INSERT:

INSERT INTO Application.LogAudit (LogData) VALUES ('Это данные журнала!');
Создайте хранимую процедуру в схеме Application с именем DeleteLogData.

Хранимая процедура удаляет запись из таблицы Application.LogAudit на основе LogAuditId, переданного в качестве параметра.

Хранимая процедура должна принимать в качестве параметров обязательные столбцы:

@logauditid int
Она также должна принимать параметр OUTPUT:

@deletedlogdata nvarchar(50)
Используйте обработку ошибок и функции обработки ошибок для отображения номера ошибки и сообщения об ошибке!

Если удаление прошло успешно, верните удаленное значение LogData в параметре OUTPUT!

Если произошла ошибка или удаленных строк было 0, обработайте ошибку:

print: 'Delete of log audit failed!'
print: 'Номер ошибки: ' и номер ошибки
print: 'Сообщение об ошибке: ' и сообщение об ошибке
вернуть -1 в качестве возвращаемого значения
Не показывайте количество затронутых строк в запросе T-SQL в качестве информационного сообщения.

Покажите, как вы используете хранимую процедуру!
*/

DELETE FROM Application.LogAudit
        WHERE LogAuditId = 10;

SELECT * FROM Application.LogAudit

 SELECT LogData
        FROM Application.LogAudit
        WHERE LogAuditId = 3


DECLARE @deletedlogdata nvarchar(50);
SELECT @deletedlogdata = LogData
        FROM Application.LogAudit
        WHERE LogAuditId = 2
SELECT @deletedlogdata

USE WideWorldImporters;
GO

DROP TABLE IF EXISTS Application.LogAudit;
GO
CREATE TABLE Application.LogAudit
(
    LogAuditId int NOT NULL IDENTITY(1,1) PRIMARY KEY,
    LogData nvarchar(50) NOT NULL
);

INSERT INTO Application.LogAudit (LogData) VALUES ('Это данные журнала!');


GO
CREATE OR ALTER PROCEDURE Application.DeleteLogData (
    @logauditid int,
    @deletedlogdata nvarchar(50) OUTPUT
)
AS
BEGIN
    УСТАНОВИТЬ NOCOUNT;

    НАЧАТЬ ПОПЫТКУ
        SELECT @deletedlogdata = LogData
        FROM Application.LogAudit
        WHERE LogAuditId = @logauditid;

        DELETE FROM Application.LogAudit
        WHERE LogAuditId = @logauditid;
        ЕСЛИ @@ROWCOUNT = 0
        BEGIN
            --БРОСОК 55555, 'ROWCOUNT=0', 1;
            RAISERROR ('ROWCOUNT=0', 16, 1);
        END
    END TRY
    BEGIN CATCH
        PRINT 'Удаление журнала аудита не удалось!
        PRINT 'Номер ошибки: ' + CAST(ERROR_NUMBER() AS nvarchar(10));
        PRINT 'Сообщение об ошибке: ' + ERROR_MESSAGE();
        SET @deletedlogdata = NULL;
        RETURN -1;
    END CATCH
END;
GO
--SHOWCASE
DECLARE @deletedlogdatavar nvarchar(50);
EXEC Application.DeleteLogData @logauditid = 10, @deletedlogdata = @deletedlogdatavar OUTPUT;
SELECT @deletedlogdatavar AS Deleted;
GO
DECLARE @deletedlogdatavar nvarchar(50);
EXEC Application.DeleteLogData @logauditid = 1, @deletedlogdata = @deletedlogdatavar OUTPUT;
SELECT @deletedlogdatavar AS Deleted;
GO

Судебное разбирательство

hsg-reader-sqlserver-final-trial-exam

Важно

  • Вы можете использовать любой интернет-ресурс, но, пожалуйста, работайте самостоятельно!
  • Форк репо, затем клонируйте форк репо на свою машину
  • Используйте кодировку символов UTF8 БЕЗ BOM
  • Не просто копируйте ответы и решения, вместо этого используйте свои собственные слова
  • Нет оценки автогрейдера.
  • Задача 4 требует наличия mongoDB и может быть решена после изучения материала по NoSQL
  • Для выполнения Задачи 4 вы можете установить mongoDB в своей системе

Задание #1:

Ведущая сеть ресторанов быстрого питания просит вас создать базу данных компаний по доставке, продающих их продукцию.

В этой базе данных "Homedelivery" вам предстоит спроектировать ее часть.

Необходимо обработать следующие данные:

  • Магазин (информация о магазине)
  • Меню (каталог актуальных товаров в магазинах)
  • Связи между таблицами

Необходимо обработать следующие данные:

  • Идентификатор магазина (получите уникальный идентификатор)
  • ID текущего активного каталога
  • Когда в последний раз магазин получил успешный каталог
  • Когда был последний успешный каталог
  • Компания по доставке из фиксированного списка
  • Часы работы
  • Магазин открыт 7/24

Магазин может иметь несколько каталогов, но только один магазин сможет использовать один каталог (по ID). Магазин пока может работать только с одним поставщиком.

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

Используйте следующие ограничения как минимум:

  • PRIMARY KEY
  • ЗАКЛЮЧИТЕЛЬНЫЙ КЛЮЧ

Определите дополнительные ограничения, индексы, если необходимо!

Осторожно, нормализация!

Отправьте обратно скрипты правления!

CREATE DATABASE Homedelivery;
GO
-- магазины, каталоги, поставщики
-- магазин->каталог 1:N, магазин->поставщики 1:N
USE Homedelivery;
GO


DROP TABLE IF EXISTS dbo.catalogs,dbo.stores,dbo.transporters;
GO

CREATE TABLE dbo.transporters(
	 id INT IDENTITY(1,1) NOT NULL PRIMARY KEY
	, transp_name NVARCHAR(150) NOT NULL UNIQUE
);

CREATE TABLE dbo.stores(
	 id INT IDENTITY(1,1) NOT NULL PRIMARY KEY
	, store_name NVARCHAR(150) NOT NULL UNIQUE
	, transporter_id INT
		CONSTRAINT FK_stores_transporterid FOREIGN KEY REFERENCES dbo.transporters(id)
	, opening_start TIME
	, открытие_конец ВРЕМЯ
	, isOpenAllday BIT NOT NULL
	CONSTRAINT CK_stores_openingcheck
		CHECK ( ( isOpenAllday = 1 AND opening_start IS NULL AND opening_end IS NULL) OR
		( isOpenAllday = 0 AND opening_start IS NOT NULL AND opening_end IS NOT NULL )
		)
	,CONSTRAINT CK_stores_openingstartend CHECK ( opening_start != opening_end )
);

CREATE TABLE dbo.catalogs (
	 id INT IDENTITY(1,1) NOT NULL PRIMARY KEY
	, catalog_name NVARCHAR(150) NOT NULL UNIQUE
	, catalog_success_created DATETIME2 NOT NULL
	, last_success_transport DATETIME2 NOT NULL
	, usedbystore_id INT
		CONSTRAINT FK_catalogs_storeid FOREIGN KEY REFERENCES dbo.stores(id)
	, isActive BIT NOT NULL
		INDEX idx_catalogs_activeforstore UNIQUE (usedbystore_id,isActive) WHERE isActive=1
	, CONSTRAINT CK_catalog_successtime CHECK (last_success_transport >catalog_success_created )
);

/*
INSERT INTO dbo.transporters
VALUES ('семья Корлеоне'),('нгдрангета'),('Братва');
INSERT INTO dbo.stores
VALUES('Джимми кебап',2,'09:10','09:10',0);
INSERT INTO dbo.stores
VALUES('Jimmy kebap',2,'09:10','09:15',0);
INSERT INTO dbo.stores
VALUES('videki robi',2,NULL,NULL,0);
INSERT INTO dbo.stores
VALUES('videki robi',2,'18:00','07:00',0);
INSERT INTO dbo.stores
VALUES('korcsma',2,NULL,NULL,1);
INSERT INTO dbo.catalogs
VALUES('BBQ Burger','2021-11-10','2021-11-11',2,0);
INSERT INTO dbo.catalogs
VALUES('Вега Бургер','2021-11-10','2021-11-11',2,1);
INSERT INTO dbo.catalogs
VALUES ('Салями Бургер','2021-11-10','2021-11-11',2,1);
*/

Задание #2:

Используйте базу данных примеров WideWorldImporters!

Напишите простую хранимую процедуру для таблицы Application.Countries, которая возвращает название континента и соответствующую сводку населения.

Хранимая процедура в схеме Application должна называться GetContinentRanking.

Хранимая процедура принимает один параметр: - @rank tinyint: по умолчанию 1

Значение параметра типа integer указывает, сколько континентов должна вернуть хранимая процедура на основе ранжирования по численности населения.

Например:

  • Если параметр имеет значение 3, то должно быть возвращено название и население третьего по численности населения континента.
  • Если параметр имеет значение 5, то должно быть возвращено название и население пятого по численности населения континента.
  • Если параметр установлен в 1, то должно быть возвращено название и население самого густонаселенного континента.

Используйте приведенные ниже таблицы:

  • Применение.Страны

Верните хранимую процедуру со следующими столбцами:

  • [рейтинг]: значение рейтинга
  • [название континента]: колонка континента в таблице Application.Countries
  • [население]: обобщенный столбец LatestRecordedPopulation в таблице Application.Countries

Продемонстрируйте, как вы используете хранимую процедуру!

ИСПОЛЬЗОВАНИЕ WideWorldImporters;
GO
CREATE OR ALTER PROCEDURE Application.GetContinentRanking
(@rank TINYINT=1)
AS
	УСТАНОВИТЬ NOCOUNT ON;
	SELECT @rank AS ranking
		,Continent AS [название континента]
		, SUM(LatestRecordedPopulation) население
	FROM Application.Countries
	GROUP BY Continent
	ORDER BY SUM(LatestRecordedPopulation) DESC
	OFFSET (@rank-1) ROWS FETCH NEXT 1 ROWS ONLY;
GO

/*
Хранимая процедура принимает один параметр: - @rank tinyint: значение по умолчанию 1
Хранимая процедура должна вернуть следующие столбцы:
    [ranking]: значение ранжирования
    [название континента]: столбец континента в таблице Application.Countries
    [население]: суммированный столбец LatestRecordedPopulation в таблице Application.Countries (Приложение.Страны)
*/

/*
Продемонстрируйте использование хранимой процедуры!
*/

EXEC Application.GetContinentRanking DEFAULT;

EXEC Application.GetContinentRanking 3;

-- версия оконной функции
GO
CREATE OR ALTER PROC Application.GetContinentRanking
    @rank TINYINT = 1
AS
BEGIN
    SELECT рейтинг, название_континента, население
    FROM (
        SELECT
            RANK() OVER (ORDER BY SUM(LatestRecordedPopulation) DESC) ranking,
            Continent AS 'continent_name',
            SUM(LatestRecordedPopulation) AS 'население
        FROM Application.Countries
        GROUP BY Continent) AS continent_population
    WHERE ranking = @rank
END;
GO

EXEC Application.GetContinentRanking DEFAULT;

EXEC Application.GetContinentRanking 3;

Задание #3:

Используйте базу данных примеров WideWorldImporters!

Напишите сценарий T-SQL, содержащий следующее:

  1. Он создает логин SQL под названием "SampleLogin" с паролем и устанавливает WideWorldImporters в качестве базы данных по умолчанию.
  2. В базе данных WideWorldImporters создайте пользователя "SampleUser" для предыдущего входа в систему.
  3. Добавляет пользователя в роль базы данных "Внешние продажи".
  4. Добавьте разрешение SELECT для этой роли в таблицу Application.People.
  5. Тестирует чтение данных, выдавая себя за пользователя "SampleUser" и используя запрос SELECT из таблицы Application.People.
  6. Восстанавливает контекст выполнения предыдущему пользователю.
  7. Получение имени текущего пользователя.

Каждая задача представляет собой оператор T-SQL.

Скопируйте следующие данные на github:

  • файл сценария
ИСПОЛЬЗОВАНИЕ WideWorldImporters;
GO
CREATE LOGIN [SampleLogin] WITH PASSWORD=N'password',
DEFAULT_DATABASE=[WideWorldImporters], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
GO
CREATE USER SampleUser FOR LOGIN [SampleLogin];
GO
ALTER ROLE [External Sales] ADD MEMBER [SampleUser];
GO
GRANT SELECT ON Application.People TO [External Sales];
GO
EXECUTE AS USER='SampleUser';
GO
SELECT TOP 5 * FROM Application.People;
--SELECT USER_NAME();
GO
REVERT
GO
SELECT USER_NAME();

Задание #5:

Воспроизведите тупиковую ситуацию с помощью следующего сценария!

Откройте 3 новых окна запросов!

Последовательно запустите следующие сценарии в указанном окне:

  1. Окно:
УНИЧТОЖИТЬ ТАБЛИЦА ЕСЛИ СУЩЕСТВУЕТ Приложение.Тупик;
GO
CREATE ТАБЛИЦА Приложение.тупик
(
    DeadlockId int NOT NULL PRIMARY KEY
);
НАЧАЛО ТРАНЗАКЦИЯ
INSERT INTO Приложение.Тупик (deadlockId) VALUES (1);
  1. Окно:
НАЧАЛО ТРАНЗАКЦИЯ
INSERT INTO Приложение.Тупик (deadlockId) VALUES (2);
  1. Окно:
ВЫБРАТЬ DeadlockId ОТ Приложение.Тупик ГДЕ DeadlockId = 2;
  1. Окно:
НАЧАЛО ТРАНЗАКЦИЯ
INSERT INTO Приложение.Тупик (deadlockId) VALUES (3);
  1. Окно:
ВЫБРАТЬ DeadlockId ОТ Приложение.Тупик ГДЕ DeadlockId = 3;
  1. Окно:
ВЫБРАТЬ DeadlockId ОТ Приложение.Тупик ГДЕ DeadlockId = 1;

Одно из 3 окон возвращается с красным сообщением об ошибке deadlock.

Команда ROLLBACK TRAN может быть использована для остановки любых инструкций, которые остались невыполненными.

  1. Соберите график тупиков xml для воспроизведенного вами тупика!

  2. Сохраните deadlock graph xml в файл формата .XDL!

  3. Отобразите график тупика графически (с помощью выбранного вами инструмента) и сделайте его скриншот.

  4. Настройте базу данных так, чтобы этот конкретный тупик не повторился!

Скопируйте следующее на github и пронумеруйте файлы, как показано ниже::

  • 1: файл .XDL
  • 2: файл изображения снимка экрана
  • 3: оператор T-SQL
ИСПОЛЬЗУЙТЕ DBAHEALTH;
GO

--EXEC dbo.sp_BlitzLock @EndDate='20230423', @StartDate='20230410';

ALTER DATABASE [WideWorldImporters] SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT
GO

------------------------------------ BAL
--- 1. ----------- покинул сессию:
BEGIN TRAN t1;
UPDATE dbo.ExamLefty SET Numbers = Numbers + 1;
--- 4. ----------- левая сессия:
SELECT * FROM dbo.ExamRighty;

ROLLBACK TRAN

------------------------------------ JOBB
--- 2. -- сессия правой стороны:
BEGIN TRAN t2;
UPDATE dbo.ExamRighty SET Numbers = Numbers + 1;
--- 3. -- Правая сессия:
SELECT * FROM dbo.ExamLefty;

Задание #6:

Создайте таблицу SQLTestDB с помощью следующего сценария!

USE [master]
GO

CREATE DATABASE [SQLTestDB]
GO

USE [SQLTestDB]
GO
CREATE ТАБЛИЦА SQLTest
   (
      ID INT NOT NULL PRIMARY KEY,
      c1 VARCHAR(100) NOT NULL,
      dt1 ДАННОЕ ВРЕМЯ NOT NULL DEFAULT getdate()
   );
GO

USE [SQLTestDB]
GO

INSERT INTO SQLTest (ID, c1) VALUES (1, 'тест1')
INSERT INTO SQLTest (ID, c1) VALUES (2, 'тест2')
INSERT INTO SQLTest (ID, c1) VALUES (3, 'тест3')
INSERT INTO SQLTest (ID, c1) VALUES (4, 'тест4')
INSERT INTO SQLTest (ID, c1) VALUES (5, 'тест5')
GO

ВЫБРАТЬ * ОТ SQLTest
GO

База данных SQLTestDB требует восстановления "точка-в-время" в соответствии с требованиями бизнеса.

Напишите сценарий T-SQL, содержащий следующее:

  1. Настройте модель восстановления базы данных в соответствии с потребностями бизнеса.

  2. Перед резервным копированием выполняется полная проверка целостности базы данных SQLTestDB, пропуская проверку для некластеризованных индексов.

  3. Создает полную резервную копию с фиксированным путем и именем SQLTestDB_Full.bak.

  4. Дифференциальное резервное копирование со сжатием по фиксированному пути с именем файла SQLTestDB_Diff.bak.

  5. Создайте резервную копию журнала со сжатием и контрольной суммой по фиксированному пути с именем файла SQLTestDB_Log.trn.

  6. Проверяет резервную копию на предмет полноты или читаемости набора резервных копий (но не восстанавливает его).

Каждая задача представляет собой оператор T-SQL. Операторы должны содержать только запрашиваемые опции/настройки!

  1. Запланируйте вышеуказанные операторы резервного копирования с помощью заданий SQL Agent (можно также использовать задания Ola Hallengren) и создайте правильное расписание резервного копирования с учетом следующего:
    • размер базы данных может составлять сотни гигабайт
    • оптимизировать восстановление в случае аварии
    • в случае аварии может быть потеряно до 5 минут данных
    • графики должны быть названы таким образом, чтобы четко указывать на тип резервного копирования

Сделайте скриншоты расписаний резервного копирования в SQL Server Management Studio, из диалоговых окон свойств расписаний заданий.

Скопируйте следующие данные на github:

  • файл сценария
  • файлы скриншотов
USE [master]
GO
ALTER DATABASE [SQLTestDB] SET RECOVERY FULL;
GO
--https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkdb-transact-sql?view=sql-server-ver15

DBCC CHECKDB (SQLTestDB, NOINDEX);
GO
USE master;
GO
BACKUP DATABASE [SQLTestDB]
TO DISK = N'S:\instance\MSSQL15.GFOX\MSSQL\Backup\SQLTestDB_Full.bak';
GO
BACKUP DATABASE [SQLTestDB]
TO DISK = N'S:\instance\MSSQL15.GFOX\MSSQL\Backup\SQLTestDB_Diff.bak'
С ДИФФЕРЕНЦИАЛЬНЫМ КОПИРОВАНИЕМ И СЖАТИЕМ;
GO
BACKUP LOG [SQLTestDB]
TO DISK = N'S:S\instance\MSSQL\MSSQL15.GFOX\MSSQL\Backup\SQLTestDB_Log.trn'
СО СЖАТИЕМ И КОНТРОЛЬНОЙ СУММОЙ;
GO

ВОССТАНОВЛЕНИЕ ТОЛЬКО ДЛЯ ПРОВЕРКИ
FROM DISK = N'S:\instance\MSSQL15.GFOX\MSSQL\Backup\SQLTestDB_Log.trn';
GO
ВОССТАНОВЛЕНИЕ ТОЛЬКО ДЛЯ ПРОВЕРКИ
FROM DISK = N'S:\instance\MSSQL15.GFOX\MSSQL\Backup\SQLTestDB_Diff.bak';
GO
ВОССТАНОВЛЕНИЕ ТОЛЬКО ДЛЯ ПРОВЕРКИ
FROM DISK = N'S:\instance\MSSQL15.GFOX\MSSQL\Backup\SQLTestDB_Full.bak';
GO
-- не hallegreen

/*
https://github.com/green-fox-academy/teaching-materials/blob/master/workshop/sql-server-database-maintenance/sql-server-database-maintenance.md
https://ola.hallengren.com/
*/
-- JOBS https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-add-jobschedule-transact-sql?view=sql-server-ver15
-- ПОЛНОЕ РЕЗЕРВНОЕ СОХРАНЕНИЕ
ИСПОЛЬЗОВАНИЕ [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job @job_name=N'sqltestdb full backup job',
		@enabled=1,
		@notify_level_eventlog=0,
		@notify_level_email=2,
		@notify_level_page=2,
		@delete_level=0,
		@category_name=N'[Uncategorized (Local)]',
		@owner_login_name=N'User3', @job_id = @jobId OUTPUT
выберите @jobId
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N'sqltestdb full backup job', @server_name = N'USER3-VM'
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'sqltestdb full backup job', @step_name=N'sqltestdb dbcc check',
		@step_id=1,
		@cmdexec_success_code=0,
		@on_success_action=3,
		@on_fail_action=2,
		@retry_attempts=0,
		@retry_interval=0,
		@os_run_priority=0, @subsystem=N'TSQL',
		@command=N'DBCC CHECKDB (SQLTestDB, NOINDEX);',
		@database_name=N'DBAHealth',
		@флаги=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'sqltestdb full backup job', @step_name=N'sqltestdb full backup job',
		@step_id=2,
		@cmdexec_success_code=0,
		@on_success_action=1,
		@on_fail_action=2,
		@retry_attempts=0,
		@retry_interval=0,
		@os_run_priority=0, @subsystem=N'TSQL',
		@command=N'EXECUTE dbo.DatabaseBackup
@Databases = ''SQLTestDB'',
@Directory = ''C:\MSSQL Server'',
@BackupType = ''FULL'',
@Verify = ''Y'';',
		@database_name=N'DBAHealth',
		@flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'sqltestdb full backup job',
		@enabled=1,
		@start_step_id=1,
		@notify_level_eventlog=0,
		@notify_level_email=2,
		@notify_level_page=2,
		@delete_level=0,
		@description=N'',
		@category_name=N''[Uncategorized (Local)]'',
		@owner_login_name=N'User3',
		@notify_email_operator_name=N'',
		@notify_page_operator_name=N''
GO
USE [msdb]
GO
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'sqltestdb full backup job', @name=N'sqltestdb full backup sch',
		@enabled=1,
		@freq_type=8, -weekly
		@freq_interval=1, --sunday
		@freq_subday_type=1,
		@freq_subday_interval=0,
		@freq_relative_interval=0,
		@freq_recurrence_factor=1,
		@active_start_date=20230419,
		@active_end_date=99991231,
		@active_start_time=30000,
		@active_end_time=235959, @schedule_id = @schedule_id OUTPUT
select @schedule_id
GO

-- DIFF BACKUP JOB

USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job @job_name=N'sqltestdb diff backup job',
		@enabled=1,
		@notify_level_eventlog=0,
		@notify_level_email=2,
		@notify_level_page=2,
		@delete_level=0,
		@category_name=N'[Uncategorized (Local)]',
		@owner_login_name=N'User3', @job_id = @jobId OUTPUT
выберите @jobId
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N'sqltestdb diff backup job', @server_name = N'USER3-VM'
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'sqltestdb diff backup job', @step_name=N'sqltestdb diff job',
		@step_id=1,
		@cmdexec_success_code=0,
		@on_success_action=1,
		@on_fail_action=2,
		@retry_attempts=0,
		@retry_interval=0,
		@os_run_priority=0, @subsystem=N'TSQL',
		@command=N'EXECUTE dbo.DatabaseBackup
@Databases = ''SQLTestDB'',
@Directory = ''C:\MSSQL Server'',
@BackupType = ''DIFF'',
@Compress = ''Y'';',
		@database_name=N'DBAHealth',
		@flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'sqltestdb diff backup job',
		@enabled=1,
		@start_step_id=1,
		@notify_level_eventlog=0,
		@notify_level_email=2,
		@notify_level_page=2,
		@delete_level=0,
		@description=N'',
		@category_name=N''[Uncategorized (Local)]'',
		@owner_login_name=N'User3',
		@notify_email_operator_name=N'',
		@notify_page_operator_name=N''
GO
USE [msdb]
GO
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'sqltestdb diff backup job', @name=N'sqltestdb diff job sch',
		@enabled=1,
		@freq_type=8,
		@freq_interval=8, --wednesday
		@freq_subday_type=1,
		@freq_subday_interval=0,
		@freq_relative_interval=0,
		@freq_recurrence_factor=1,
		@active_start_date=20230419,
		@active_end_date=99991231,
		@active_start_time=40000,
		@active_end_time=235959, @schedule_id = @schedule_id OUTPUT
select @schedule_id
GO

-- ЗАДАНИЕ РЕЗЕРВНОГО КОПИРОВАНИЯ ЖУРНАЛА

USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job @job_name=N'sqltest log backup',
		@enabled=1,
		@notify_level_eventlog=0,
		@notify_level_email=2,
		@notify_level_page=2,
		@delete_level=0,
		@category_name=N'[Uncategorized (Local)]',
		@owner_login_name=N'User3', @job_id = @jobId OUTPUT
выберите @jobId
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N'sqltest log backup', @server_name = N'USER3-VM'
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'sqltest log backup', @step_name=N'sqltestdb log backup',
		@step_id=1,
		@cmdexec_success_code=0,
		@on_success_action=1,
		@on_fail_action=2,
		@retry_attempts=0,
		@retry_interval=0,
		@os_run_priority=0, @subsystem=N'TSQL',
		@command=N'EXECUTE dbo.DatabaseBackup
@Databases = ''SQLTestDB'',
@Directory = ''C:\MSSQL Server'',
@BackupType = ''LOG'',
@Compress = ''Y'',
@CheckSum = ''Y'';',
		@database_name=N'DBAHealth',
		@flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'sqltest log backup',
		@enabled=1,
		@start_step_id=1,
		@notify_level_eventlog=0,
		@notify_level_email=2,
		@notify_level_page=2,
		@delete_level=0,
		@description=N'',
		@category_name=N''[Uncategorized (Local)]'',
		@owner_login_name=N'User3',
		@notify_email_operator_name=N'',
		@notify_page_operator_name=N''
GO
USE [msdb]
GO
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'sqltest log backup', @name=N'sqltestdb log backup sch',
		@enabled=1,
		@freq_type=4, --daily
		@freq_interval=1,
		@freq_subday_type=4, -- минуты
		@freq_subday_interval=5, -- пять ...
		@freq_relative_interval=0,
		@freq_recurrence_factor=1,
		@active_start_date=20230419,
		@active_end_date=99991231,
		@active_start_time=0,
		@active_end_time=235959, @schedule_id = @schedule_id OUTPUT
select @schedule_id
GO

Задание #7:

База данных SQLRestoreDB должна быть восстановлена из резервной копии до последней возможной точки восстановления. Базу данных необходимо восстановить, потому что она была случайно удалена.

Напишите команды восстановления, которые восстанавливают базу данных в цепочке восстановления и, таким образом, все содержимое таблицы.

Доступны следующие резервные копии:

  • SQLRestoreDB_Full.bak: Полная резервная копия
  • SQLRestoreDB_Log_2.trn: Резервное копирование журнала
  • SQLRestoreDB_Diff_3.bak: Дифференциальная резервная копия
  • SQLRestoreDB_Log_4.trn: Резервное копирование журнала

Имена файлов резервных копий также показывают тип резервной копии и порядок, в котором они были созданы.

База данных не была расположена на диске по умолчанию, поэтому ее, возможно, придется переместить. Напишите команду восстановления, которая перечислит, какие файлы находятся в файле полной резервной копии.

Данные были удалены после резервного копирования журнала "SQLRestoreDB_Log_4.trn"!

Используйте наиболее оптимальную цепь восстановления!

После восстановления базы данных выполните приведенный ниже запрос и сохраните результат в формате .csv.

USE SQLRestoreDB;
GO
ВЫБРАТЬ 
    GETDATE() AS [examstimestamp],
    @@SERVERNAME AS [myservername],
    * 
ОТ dbo.SQLRestoreDB;
USE master;
GO
-- список восстановления
RESTORE FILELISTONLY FROM DISK = N'Y:\x\SQLRestoreDB_Full.bak';
GO
/*
RESTORE HEADERONLY FROM DISK = N'Y:\x\SQLRestoreDB_Full.bak';
GO
*/
-- восстановление полной резервной копии вв
RESTORE DATABASE [SQLRestoreDB]
FROM DISK = N'Y:\x\SQLRestoreDB_Full.bak'
WITH MOVE N'SQLRestoreDB' TO N'T:\sqldata\SQLRestoreDB.mdf',
MOVE N'SQLRestoreDB_log' TO N'L:\sqllogs\SQLRestoreDB_log.ldf', NORECOVERY;
-- diff backup
RESTORE DATABASE [SQLRestoreDB] FROM DISK = N'Y:\x\SQLRestoreDB_Diff_3.bak' WITH NORECOVERY;
-- резервное копирование журнала
RESTORE LOG [SQLRestoreDB] FROM DISK = N'Y:\x\SQLRestoreDB_Log_4.trn' WITH RECOVERY;
GO
USE SQLRestoreDB;
GO
SELECT
    GETDATE() AS [examstimestamp],
    @@SERVERNAME AS [myservername],
    *
FROM dbo.SQLRestoreDB;

Задание #8:

Подготовьте следующую таблицу:

УНИЧТОЖИТЬ ТАБЛИЦА ЕСЛИ СУЩЕСТВУЕТ Приложение.LogAudit;
GO
CREATE ТАБЛИЦА Приложение.LogAudit
(
    LogAuditId int NOT NULL IDENTITY(1,1) PRIMARY KEY,
    LogData nvarchar(50) NOT NULL
);

Добавьте данные с помощью следующей команды INSERT INTO:

INSERT INTO Приложение.LogAudit (LogData) VALUES ('Это данные журнала!');

Создайте хранимую процедуру в схеме Application под названием DeleteLogData.

Процедура удаляет запись из таблицы Application.LogAudit на основании переданного параметра LogAuditId.

Хранимая процедура должна использовать следующий параметр:

  • @logauditid int

Хранимая процедура должна использовать следующий параметр OUTPUT:

  • @deletedlogdata nvarchar(50)

Используйте обработку ошибок с помощью соответствующих встроенных функций номера ошибки и сообщения об ошибке.

Если удаление прошло успешно, процедура возвращается с удаленным значением LogData в параметре OUTPUT.

Если произошла ошибка, справьтесь с ней:

  • print: 'Удаление журнала аудита не удалось!'
  • выводит: 'Номер ошибки: ' и номер ошибки
  • print message: 'Сообщение об ошибке: ' и сообщение об ошибке
  • вернуть значение -1

Приведите пример того, как можно использовать подготовленную вами процедуру.

ИСПОЛЬЗОВАНИЕ WideWorldImporters;
GO
DROP TABLE IF EXISTS Application.LogAudit;
GO
CREATE TABLE Application.LogAudit
(
    LogAuditId int NOT NULL IDENTITY(1,1) PRIMARY KEY,
    LogData nvarchar(50) NOT NULL
);
INSERT INTO Application.LogAudit (LogData) VALUES ('Это данные журнала!');
GO
SELECT * FROM Application.LogAudit;
GO
CREATE OR ALTER PROC Application.DeleteLogData (
	 @logauditid int
	, @deletedlogdata nvarchar(50) OUTPUT
)
AS
	УСТАНОВИТЬ NOCOUNT ON;
	НАЧАЛО ПОПЫТКИ
		DECLARE @Mytemptable TABLE (
			logdatas NVARCHAR(50)
		);
		DELETE Application.LogAudit
		OUTPUT deleted.LogData INTO @Mytemptable
		WHERE LogAuditId=@logauditid;

		ЕСЛИ @@ROWCOUNT  1
			BEGIN
				RAISERROR ('Удаление журнала аудита не удалось!',16,1);
			END;

		SELECT @deletedlogdata = logdatas FROM @Mytemptable;
	END TRY
	BEGIN CATCH
		PRINT 'Удаление журнала аудита не удалось!
		PRINT 'Номер ошибки: ' + CAST(ERROR_NUMBER() AS nvarchar(10));
		PRINT 'Сообщение об ошибке: ' + ERROR_MESSAGE();
		RETURN -1;
	КОНЕЦ КАТЧА
GO

-- витрина

DECLARE @outputvar NVARCHAR(50),@returnstatus INT;
EXEC @returnstatus=Application.DeleteLogData 1, @deletedlogdata = @outputvar OUTPUT;
SELECT @outputvar AS [значение удаленного журнала],@returnstatus AS [статус возврата];
GO
-- ошибка витрины
DECLARE @outputvar NVARCHAR(50),@returnstatus INT;
EXEC @returnstatus=Application.DeleteLogData 1, @deletedlogdata = @outputvar OUTPUT;
SELECT @outputvar AS [значение удаленного журнала],@returnstatus AS [статус возврата];
GO

HSG Reader Итоговый нормальный экзамен

Общий

  • Ссылка на раздел письменного теста в Gradescope
  • Тест будет проводиться с 9:00 до 10:00, и у вас будет 60 минут, чтобы ответить на 40 вопросов.
  • Решения к приведенным ниже упражнениям Градкоскоп на быть загружены в раздел "Задача проекта
  • Файлы должны быть названы так, как указано в заданиях
  • Решения могут быть загружены вместе в Grade-scop в виде .zip-файлов.
  • Оцениваться будет только содержимое Gradescope!
  • Убедитесь, что кодировка загружаемых файлов - UTF-8 БЕЗ BOM (подписи).
  • Убедитесь, что предоставленные вами файлы SQL являются "исполняемыми" из SSMS/Azure Data Studio

Начало работы

  • Создайте эту папку для вашего собственного пользователя github
  • Клонируйте форкнутый репозиторий из под своим именем на свою машину, работайте здесь
  • Регулярно выполняйте фиксацию и используйте четкие комментарии к фиксации
  • Все ваши решения также должны быть выложены здесь на github

Что вы обнаружите в результирующей среде Azure на машине?

  • База данных WideWorldImporters в рабочем состоянии.
  • База данных ExamDB в рабочем состоянии. (Вы также можете найти файл резервной копии ExamDB_Full.bak в ссылке Backups ниже, если вы работаете в своей собственной среде).
  • Также база данных под названием DBAHealth, на которую загружаются скрипты типа sp_Blitz и скрипты Олы Халленгрен.
  • Кроме того, папка резервного копирования по умолчанию содержит резервные копии базы данных RestoreDB (вы также можете найти их в ссылке Backups внизу, плюс).

Что можно использовать?

  • Можно использовать любой интернет-ресурс, но работать индивидуально
  • Не просто копируйте решения, используйте свои собственные знания, свои слова
  • NE Push-olj на GitHub до тех пор, пока наставник не объявит, что она может быть
  • Не забудьте загрузить ваши решения здесь вовремя: Gradescope

Задачи

Резервные файлы

Сценарий тупика

Задание #1:

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

Необходимо обработать следующие данные:

  • Сведения об учителях
  • Список предметов
  • Данные студентов
  • Связи между таблицами

Необходимо обработать следующие данные:

  • Идентификатор учителя/ученика/предмета (получите уникальный идентификатор)
  • Имя учителя/ученика, адрес электронной почты, дата рождения
  • Названия предметов
  • Примечание для студентов
  • Номер телефона преподавателей
  • Год зачисления учащихся (с момента поступления в школу) / сохраняется только номер года /
  • Текущий состав студентов

У ученика может быть более одного предмета, так же как предмет может преподаваться более чем одному ученику, но в данном случае учитель преподает только один предмет.

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

Используйте следующие ограничения как минимум:

  • PRIMARY KEY
  • ЗАКЛЮЧИТЕЛЬНЫЙ КЛЮЧ

При необходимости определите дополнительные ограничения!

Осторожно, нормализация!

Доказательства из SQL-скрипт, выполняющий описанную выше операцию
Имя файла WINSQL-01.sql
DROP DATABASE IF EXISTS SchoolDB;
CREATE DATABASE SchoolDB;
GO
USE SchoolDB;
GO
CREATE TABLE dbo.Subjects (
 ID INT IDENTITY(1,1),
 name NVARCHAR(150) NOT NULL);

CREATE TABLE dbo.Students (
 ID INT IDENTITY(1,1),
 Name NVARCHAR(500) NOT NULL,
 Email VARCHAR(255) NOT NULL,
 Birthdate DATE NOT NULL,
 YearOfEnrolment SMALLINT NOT NULL,
 Class NVARCHAR(60) NOT NULL,
 Comment NVARCHAR(5000));

CREATE TABLE dbo.Teachers (
 ID INT IDENTITY(1,1),
 Name NVARCHAR(500) NOT NULL,
 Email VARCHAR(255) NOT NULL,
 PhoneNumber VARCHAR(15) NOT NULL,
Birthdate DATE NOT NULL);

CREATE TABLE dbo.Conn_Students_Subjects (
 ID INT IDENTITY(1,1),
 SubID INT NOT NULL,
 StudID INT NOT NULL);
GO
ALTER TABLE dbo.Subjects
ADD CONSTRAINT PK_Subjects PRIMARY KEY (ID);
GO
ALTER TABLE dbo.Students
ADD CONSTRAINT PK_Students PRIMARY KEY (ID);
GO
ALTER TABLE dbo.Teachers
ADD CONSTRAINT PK_Teachers PRIMARY KEY (ID),
CONSTRAINT FK_Teachers_SubID FOREIGN KEY (SubID) REFERENCES dbo.Subjects(ID);
GO
ALTER TABLE dbo.Conn_Students_Subjects
ADD CONSTRAINT PK_Students_Subjects PRIMARY KEY (ID),
CONSTRAINT FK_Conn_Students_Subjects_SubID FOREIGN KEY (SubID) REFERENCES dbo.Subjects(ID),
CONSTRAINT FK_Conn_Students_Subjects_StudID FOREIGN KEY (StudID) REFERENCES dbo.Students(ID);
GO
ALTER TABLE dbo.Subjects
ADD CONSTRAINT UQ_Subjects_Name UNIQUE (Name);
GO
ALTER TABLE dbo.Students
ADD CONSTRAINT UQ_Students_Email UNIQUE (Email),
CONSTRAINT CK_Students_Birthdate CHECK (YearOfEnrolment > YEAR(Birthdate)),
CONSTRAINT CK_Students_YearOfEnrolment CHECK (YearOfEnrolment >= 1900 AND YearOfEnrolment > YEAR(Birthdate));
GO
ALTER TABLE dbo.Teachers
ADD CONSTRAINT UQ_Teachers_Email UNIQUE (Email),
CONSTRAINT UQ_Teachers_PhoneNumber UNIQUE (PhoneNumber);
GO

Задание #2:

Используйте базу данных примеров WideWorldImporters!

Напишите запрос, который возвращает ID, имя и значение CreditLimit клиента, начинающегося с имени "Abel", если клиент не размещал заказ между 01.05.2016 и 15.05.2016 (заказы, размещенные 01.05 и 15.05.2016, не должны быть включены !).

Убедитесь, что условие поиска даты является SARG-able!

Используйте приведенные ниже таблицы:

  • Продажи.Клиенты
  • Продажи.Заказы

Запрос должен вернуть следующие столбцы:

  • [ID клиента]: ID клиента
  • [Имя клиента]: имя клиента
  • [Кредитный лимит]: кредитный лимит покупателя
Доказательства из SQL-скрипт, выполняющий описанную выше операцию
Имя файла WINSQL-02.sql
ИСПОЛЬЗОВАНИЕ WideWorldImporters;
GO
SELECT
    c.CustomerID AS [Идентификатор клиента],
    c.CustomerName AS [Имя клиента],
    c.CreditLimit AS [Кредитный лимит]
FROM
    Sales.Customers c
WHERE
    c.CustomerName LIKE 'Abel%'
    И НЕ СУЩЕСТВУЕТ (
        SELECT 1
        FROM Sales.Orders o
        WHERE
            o.CustomerID = c.CustomerID
            AND o.OrderDate > '2016-04-30'
            AND o.OrderDate < '2016-05-16'
    );

Задание #3:

Используйте базу данных примера ExamDB!

Перед этим выполните следующий сценарий:

USE ExamDB;
GO
УНИЧТОЖИТЬ SCHEMA ЕСЛИ EXISTS Экзамен;
GO
CREATE SCHEMA Экзамен;
GO
ОТБРОСЬТЕ ПРОЦЕДУРУ, ЕСЛИ ОНА СУЩЕСТВУЕТ Экзамен.Proc1
GO
СОЗДАТЬ ПРОЦЕДУРУ Экзамен.Proc1
AS
ВЫБРАТЬ 1;
 

Напишите сценарий T-SQL, содержащий следующее:

  1. Он создает логин SQL под названием "NewExamLogin" с паролем и устанавливает базу данных ExamDB в качестве базы данных по умолчанию.
  2. В базе данных ExamDB создается пользователь "NewExamUser" для предыдущего входа в систему.
  3. Создайте новую роль базы данных в базе данных ExamDB под названием "Exam Admins".
  4. Создайте еще одну новую роль базы данных в базе данных ExamDB под названием "Exam Contributors".
  5. Добавляет пользователя "NewExamUser" к роли базы данных "Exam Admins".
  6. Для схемы Exam, EXECUTE дает право роли базы данных "Exam Admins".
  7. Схема Exam явно запрещает EXECUTE для роли "Exam Contributors".
  8. Проверяет выполнение хранимой процедуры Exam.Proc1, выдавая себя за пользователя "NewExamUser".
  9. Восстанавливает контекст выполнения предыдущему пользователю.
  10. Запрашивает текущее имя пользователя и имя входа в систему.

Каждая задача представляет собой оператор T-SQL.

Доказательства из SQL-скрипт, выполняющий описанную выше операцию
Имя файла WINSQL-03.sql
-- Создает SQL логин под названием "NewExamLogin" с паролем и устанавливает базу данных ExamDB в качестве базы данных по умолчанию
CREATE LOGIN NewExamLogin WITH PASSWORD = 'SomeVeryStrongPassword123!', DEFAULT_DATABASE = [ExamDB];

-- Создает пользователя "NewExamUser" в базе данных ExamDB для предыдущего логина
USE ExamDB;
CREATE USER NewExamUser FOR LOGIN NewExamLogin;

-- Создает новую роль базы данных в базе данных ExamDB под названием "Exam Admins
CREATE ROLE [Exam Admins];

-- Создает еще одну новую роль базы данных в базе данных ExamDB под названием "Exam Contributors
CREATE ROLE [Exam Contributors];

-- Добавляет пользователя "NewExamUser" к роли базы данных "Exam Admins
ALTER ROLE [Exam Admins] ADD MEMBER NewExamUser;

-- Дает роли базы данных "Exam Admins" право на EXECUTE схемы Exam
GRANT EXECUTE ON SCHEMA::Exam TO [Exam Admins];

-- На схеме Exam явно запретите EXECUTE для роли "Exam Contributors
DENY EXECUTE ON SCHEMA::Exam TO [Exam Contributors];

-- Проверяет выполнение хранимой процедуры Exam.Proc1, выдавая себя за пользователя "NewExamUser"
EXECUTE AS USER = 'NewExamUser';
EXEC Exam.Proc1;
REVERT;

-- Сброс контекста выполнения на предыдущего пользователя
-- (Оператор REVERT уже сбросил контекст в предыдущем шаге)

-- Запрос имени текущего пользователя и имени входа в систему
SELECT CURRENT_USER AS [Текущий пользователь], SUSER_NAME() AS [Имя входа];

Задание #4:

Напишите диагностический запрос (используя представления системного каталога), который возвращает параметры всех файлов (пользовательских и системных) в базе данных на сервере, такие как: тип файла (ROWS или LOG), физический путь к файлу, размер файла, и является ли он увеличиваемым или фиксированным.

Запрос выдает следующее:

  • database_id: идентификатор базы данных
  • имя: имя базы данных
  • recovery_model_desc: название модели восстановления базы данных (например, SIMPLE, FULL).
  • file_id: идентификатор файла в базе данных, например: 1, 2 и т.д.
  • type_desc: тип файла, например: ROWS, LOG и т.д.
  • имя: логическое имя файла
  • физическое_имя: физический путь и имя файла
  • file_size_MB: псевдоним и вычисляемое значение, размер файла в мегабайтах
  • is_percent_growth: прирост в процентах или фиксированный
Доказательства из SQL-скрипт, выполняющий описанную выше операцию
Имя файла WINSQL-04.sql
SELECT
    DB.database_id,
    DB.name AS database_name,
    DB.recovery_model_desc,
    FM.file_id,
    FM.type_desc,
    FM.name AS file_name,
    FM.physical_name,
    (FM.size * 8.0 / 1024) AS file_size_MB,
    FM.is_percent_growth
FROM
    sys.databases AS DB
JOIN
    sys.master_files AS FM
ON
    DB.database_id = FM.database_id;

Задание #5:

Для выполнения этого упражнения вам понадобится база данных ExamDB. База данных должна быть доступна (ONLINE).

Напишите сценарий обслуживания с операторами T-SQL, которые выполняют следующее:

  1. На уровне сервера он устанавливает по умолчанию создание сжатых резервных копий!
  2. Отключите распараллеливание для базы данных ExamDB (только для этой базы данных!) (не создавайте параллельные планы запросов для этой базы данных)
  3. Проверьте (запросите) настройку уровня сервера для сжатых резервных копий!
  4. Он проверяет (запрашивает) базу данных ExamDB, чтобы узнать, отключена ли параллелизация!
  5. Включает Query Store в базе данных ExamDB, в режиме чтения и записи, с максимальным размером 500MB, и так, чтобы Query Store ловил все запросы!
Доказательства из SQL-скрипт, выполняющий описанную выше операцию
Имя файла WINSQL-05.sql
-- Установка на уровне сервера для создания сжатых резервных копий по умолчанию
EXEC sp_configure 'backup compression default', 1;
ПЕРЕКОНФИГУРИРОВАТЬ;
-- Отключение распараллеливания для базы данных ExamDB (только!)
USE [ExamDB]
GO
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 1;
-- Проверка (запрос) конфигурации уровня сервера для сжатых резервных копий
EXEC sp_configure 'backup compression default';
-- Проверка (запрос) базы данных ExamDB на предмет отключения параллелизации
SELECT [value] FROM sys.database_scoped_configurations WHERE [name] = 'MAXDOP';
-- Включает Query Store на базе данных ExamDB, в режиме чтения и записи, с максимальным размером 500MB, и так, чтобы Query Store ловил все запросы
USE [master]
GO
ALTER DATABASE ExamDB
SET QUERY_STORE = ON (
    OPERATION_MODE = READ_WRITE,
    MAX_STORAGE_SIZE_MB = 500,
    QUERY_CAPTURE_MODE = ALL
);

Задание #6:

База данных под названием RestoreDB должна быть восстановлена из резервной копии до определенной точки восстановления (восстановление по времени).

Перед восстановлением напишите команду, которая покажет, какие файлы (данные и журнал) находятся в наборе резервных копий RestoreDB_Full.bak!

Затем напишите операторы восстановления, которые восстанавливают базу данных в цепочке восстановления.

Доступные резервные копии ниже (можно использовать только эти сохранения):

  • RestoreDB_Full.bak: Полная резервная копия
  • RestoreDB_Log_2.trn: Резервное копирование журнала
  • RestoreDB_Log_3.trn: Резервное копирование журнала
  • RestoreDB_Log_5.trn: Резервное копирование журнала
  • RestoreDB_Log_6.trn: Резервное копирование журнала

Имена файлов резервных копий также показывают тип резервной копии и порядок, в котором они были созданы.

База данных должна быть восстановлена с резервной копией журнала "RestoreDB_Log_5.trn" в качестве последней резервной копии!

После восстановления базы данных выполните приведенный ниже запрос и сохраните результат в формате .csv.

USE RestoreDB;
GO
ВЫБРАТЬ 
    GETDATE() AS [examstimestamp],
    @@SERVERNAME AS [myservername],
    * 
ОТ dbo.RestoreTable;
 
Доказательства из SQL-скрипт, выполняющий описанную выше операцию
Имя файла WINSQL-06.sql
Доказательства из Сохраненный результат в формате .csv
Имя файла WINSQL-06.csv
USE master;
GO
-- список восстановления
RESTORE FILELISTONLY FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\RestoreDB_Full.bak';
GO
/*
RESTORE HEADERONLY FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\RestoreDB_Full.bak';
GO
*/
-- восстановление полной резервной копии вв
RESTORE DATABASE [RestoreDB]
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\RestoreDB_Full.bak'
WITH MOVE N'RestoreDB' TO N'C:\exam\RestoreDB.mdf',
MOVE N'RestoreDB_log' TO N'C:\exam\RestoreDB_log.ldf', NORECOVERY; -- Я перемещаю его напрямую, чтобы мне было легче проверить папку, которую я создал на c
/*-- diff backup
RESTORE DATABASE [RestoreDB] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\RestoreDB_Diff_4.bak' WITH NORECOVERY;*//
-- резервное копирование журнала
RESTORE LOG [RestoreDB] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\RestoreDB_Log_2.trn' WITH NORECOVERY;
RESTORE LOG [RestoreDB] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\RestoreDB_Log_3.trn' WITH NORECOVERY;
RESTORE LOG [RestoreDB] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\RestoreDB_Log_5.trn' WITH NORECOVERY;
GO
USE RestoreDB;
GO
SELECT
    GETDATE() AS [examstimestamp],
    @@SERVERNAME AS [myservername],
    *
FROM dbo.RestoreTable;

Задание #7:

Перед выполнением задания запустите следующий сценарий:

USE [master];
GO
ALTER БАЗА ДАННЫХ [ExamDB] УСТАНОВИТЬ READ_COMMITTED_SNAPSHOT OFF
С ROLLBACK СРОЧНО;
 

Используйте сценарий тупика: deadlock_script_exam.sql к шагу 3! На шаге 3 запрос SELECT будет ждать (пока нет тупика!).

  • Напишите запрос в новом окне запроса, который показывает, какие блокировки в настоящее время есть у ожидающего SELECT-запроса (или сессии, в которой он выполняется) и какие блокировки ему требуются! Возвращайте данные о блокировках только для сеанса, который в данный момент находится в ожидании!

  • Как вы видите, после выполнения запроса какой тип блокировки потребовался ожидаемому запросу SELECT? Напишите свой ответ в комментарии рядом с запросом.

  • Измените конфигурацию сервера следующим образом: должны быть доступны расширенные опции конфигурации, и это изменение должно вступить в силу сразу после запуска сценария.

Доказательства из SQL-сценарий, выполняющий описанную выше операцию и включающий ответы в комментарий
Имя файла WINSQL-07.sql
/*
SELECT tl.request_session_id, tl.resource_type, tl.request_mode, tl.request_status, tl.resource_database_id, tl.resource_associated_entity_id, tl.resource_description
FROM sys.dm_tran_locks tl
JOIN sys.dm_exec_requests er ON tl.request_session_id = er.session_id
WHERE er.wait_type LIKE 'LCK%'
  AND tl.request_status = 'WAIT';
GO
*/
USE DBAHEALTH;
GO
dbo.sp_BlitzLock -- S - общая блокировка

EXEC sp_configure 'show advanced options', 1;
GO
ПЕРЕКОНФИГУРИРОВАТЬ С ПЕРЕОПРЕДЕЛЕНИЕМ;
GO

ALTER DATABASE [ExamDB] SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT

Задание #8

Используйте базу данных примеров WideWorldImporters!

Напишите хранимую процедуру в схеме Sales с именем GetPickedByDate, которая возвращает статистику по элементам строк заказа (Sales.OrderLines) в зависимости от того, когда определенные заказы поступили / могут закончиться как клиенты в заданном интервале времени (OrderDate) с деталями, объясненными ниже!

Хранимая процедура принимает два параметра:

  • @startDate datetime
    • значение по умолчанию должно быть датой начала 01.01.2013
  • @endDate datetime
    • значение по умолчанию должно быть датой завершения 01.06.2016

Например:

  • При запуске со значениями параметров по умолчанию хранимая процедура вернет статистику покупок, совершенных в период с 01.01.2013 по 01.06.2016 (OrderDate) по имени клиента (CustomerName) и по времени завершения выбора (PickingCompletedWhen).

Верните хранимую процедуру со следующими столбцами:

  • [Customer Name]: столбец CustomerName из таблицы Customers
  • [Pick Date]: дата завершения отбора товара (колонка PickingCompletedWhen) из таблицы OrderLines:
    • если дата неизвестна, выведите текущую дату плюс 3 дня
  • [Общая цена]: итоговые позиции строк (Количество * Цена за единицу) из таблицы OrderLines (2 знака после запятой)
  • [Количество покупок]: количество подсчитанных позиций (OrderLine)
  • [Refreshed Date]: если дата, когда будет произведена комплектация порядка сортировки (PickingCompletedWhen), неизвестна, выведите 'Expected Pick Date', если известна, выведите 'Already picked'.

Включите фильтрацию, чтобы возвращать только те, в которых группа покупателей клиента неизвестна.

Отфильтруйте далее, чтобы вернуть только те, у которых значение столбца [Ovarall Price] выше 20000.

Используйте приведенные ниже таблицы:

  • Sales.OrderLines
  • Продажи.Заказы
  • Продажи.Клиенты

Напишите хранимую процедуру таким образом, чтобы сообщение "n rows affected" не появлялось на вкладке Messages после выполнения.

Приведите пример его использования!

Доказательства из SQL-скрипт, выполняющий описанную выше операцию
Имя файла WINSQL-08.sql
ИСПОЛЬЗОВАНИЕ WideWorldImporters;
GO
/*
SELECT * FROM Sales.Customers
SELECT * FROM Sales.Orders
SELECT * FROM Sales.OrderLines
*/
GO
CREATE OR ALTER PROCEDURE Sales.GetPickedByDate
    @startDate datetime = '2013-01-01',
    @endDate datetime = '2016-06-01'
AS
BEGIN
    УСТАНОВИТЕ NOCOUNT;
    SELECT
        c.CustomerName AS [Имя клиента],
        COALESCE(ol.PickingCompletedWhen, DATEADD(DAY, 3, GETDATE())) AS [Pick Date],
        SUM(ol.Quantity * ol.UnitPrice) AS [Общая цена],
        COUNT(ol.OrderLineID) AS [Количество покупок],
        CASE
            WHEN ol.PickingCompletedWhen IS NULL THEN 'Expected Pick Date'
            ELSE 'Уже выбрано'
        END AS [Дата обновления]
    FROM Sales.OrderLines ol
    JOIN Sales.Orders o ON ol.OrderID = o.OrderID
    JOIN Sales.Customers c ON o.CustomerID = c.CustomerID
    WHERE c.BuyingGroupID IS NULL
        AND o.OrderDate >= @startDate
        AND o.OrderDate  20000
    ORDER BY c.CustomerName, ol.PickingCompletedWhen;
END;
GO
EXEC Sales.GetPickedByDate;
EXEC Sales.GetPickedByDate @startDate = '2014-01-01', @endDate = '2015-12-31';

ДИАГНОСТИЧЕСКИЕ ЗАПРОСЫ

Запросить таблицы из sys.tables

SELECT * FROM sys.tables;

Таблицы и их схемы - объединение представлений каталога sys.tables и sys.schemas

SELECT t.name AS [имя_таблицы], t.type_desc, s.name AS [имя_схемы]
FROM sys.tables t INNER JOIN sys.schemas s ON
t.schema_id = s.schema_id;

в какой сессии мы находимся

SELECT @@SPID;

получить информацию о типе данных столбцов для всех пользовательских таблиц

SELECT c.object_id, OBJECT_NAME(c.object_id) AS [имя_объекта],
s.name AS [schema], o.name AS [table_name], c.name AS [col_name],
type.name AS [col_type],
CASE c.is_nullable WHEN 1 THEN 'yes' ELSE 'no' END AS [nullable]
FROM sys.columns c INNER JOIN sys.objects o ON
c.object_id = o.object_id
INNER JOIN sys.tables t ON
t.object_id = o.object_id
INNER JOIN sys.types typ ON
c.user_type_id = typ.user_type_id
INNER JOIN sys.schemas s ON
s.schema_id = t.schema_id;
-- https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/recovery-models-sql-server?view=sql-server-ver15
	--модели восстановления для баз данных
	SELECT name, recovery_model_desc FROM sys.databases;

	-- все файлы на сервере
	SELECT db.name AS DBName, type_desc AS FileType, Physical_Name AS Location, size * 8 as FileSizeKB
	FROM sys.master_files mf INNER JOIN sys.databases db ON db.database_id = mf.database_id;

счетный индекс для каждой таблицы

SELECT S.name,COUNT(SYS.INDEXES.name) FROM SYS.INDEXES
ВНУТРЕННЕЕ СОЕДИНЕНИЕ SYS.OBJECTS S
ON S.OBJECT_ID=SYS.INDEXES.OBJECT_ID
WHERE SYS.INDEXES.OBJECT_ID IN (SELECT OBJECT_ID FROM SYS.OBJECTS WHERE name IN
    (SELECT name FROM SYS.TABLES WHERE NAME  'SYSDIAGRAMS'))
GROUP BY S.name;

индекс списка для каждой таблицы

SELECT S.name,SYS.INDEXES.name FROM SYS.INDEXES
ВНУТРЕННЕЕ СОЕДИНЕНИЕ SYS.OBJECTS S
ON S.OBJECT_ID=SYS.INDEXES.OBJECT_ID
WHERE SYS.INDEXES.OBJECT_ID IN (SELECT OBJECT_ID FROM SYS.OBJECTS WHERE name IN
    (SELECT NAME FROM SYS.TABLES WHERE name  'SYSDIAGRAMS'));

Фрагментация ИНДЕКСА

SELECT i. name, x.avg_fragmentation_in_percent, x.fragment_count
FROM sys.indexes i JOIN sys.dm_db_index_physical_stats(DB_ID('WideWor1dImporters' ) ,OBJECT_ID('INDEXTEST'), NULL,NULL,'DETAILED') x ON x.object_id = i.object_id
WHERE x.fragment_count > 1

--------------------------------------------------

SELECT
    dbschemas.[name] as 'Schema',
    dbtables.[name] as 'Table',
    dbindexes.[name] as 'Index',
    indexstats.avg_fragmentation_in_percent,
    indexstats.page_count
FROM
    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN
    sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN
    sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN
    sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND
    indexstats.index_id = dbindexes.index_id
WHERE
    indexstats.database_id = DB_ID()

СМЕРТЕЛЬНЫЙ ЗАМОК?

SELECT SESSION_ID, BLOCKING_SESSION_ID
ИЗ SYS.DM_EXEC_REQUESTS
ГДЕ BLOCKING_SESSION_ID != 0

подсчитать ограничения (проверка+по умолчанию) в базе данных

SELECT (SELECT COUNT(*) FROM sys.check_constraints)+(SELECT COUNT(*) FROM sys.default_constraints);

Детерминированный?

SELECT COLUMNPROPERTY(OBJECT_ID('dbo.testlines'),'total_price','isDeterministic') AS Determ;

Select * FROM sys.computed_columns;

Поиск по имени столбца

ВЫБРАТЬ *
ИЗ INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%region%'

Поиск по имени таблицы

SELECT c.object_id, OBJECT_NAME(c.object_id) AS [имя_объекта], s.name AS [schema], o.name AS [имя_таблицы], c.name AS [имя_колонки], typ.name AS [тип_колонки]
, CASE c.is_nullable WHEN 1 THEN 'yes' ELSE 'no' END AS [nullable] FROM sys.columns c
INNER JOIN sys.objects o ON c.object_id = o.object_id
INNER JOIN sys.tables t ON t.object_id = o.object_id
INNER JOIN sys.types typ ON c.user_type_id = typ.user_type_id
INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE c.name LIKE '%Postal%'

Поиск строки в базе данных

DECLARE @SearchStr nvarchar(100)
SET @SearchStr = 'Ограниченный запас

DECLARE @Results TABLE(ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL
BEGIN
    SET @ColumnName = ''
    SET @TableName =
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_TYPE = 'BASE TABLE'
            AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            И OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0
    )

    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
    BEGIN
        SET @ColumnName =
        (
            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
                AND TABLE_NAME = PARSENAME(@TableName, 1)
                AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
                AND QUOTENAME(COLUMN_NAME) > @ColumnName
        )

        IF @ColumnName IS NOT NULL
        BEGIN
            INSERT INTO @Results
            EXEC
            (
                'SELECT ''' + @TableName + '.'' + @ColumnName + '''', LEFT('' +
@ColumnName
+ ', 3630) FROM '
+
@TableName
+ ' (NOLOCK) ' +
                ' WHERE '
+
@ColumnName
+ ' LIKE '
+
@SearchStr2
                
            )
        END
    END
END

SELECT ColumnName, ColumnValue FROM @Results

Sys.messages & Изменение языка в сессии

8134 Сообщения об ошибках системы > диагностический запрос
SELECT * FROM sys.messages
WHERE message_id=8134;
/* изменение языка в сессии */
SET LANGUAGE hungarian;
PRINT 10/0;
SET LANGUAGE english;
PRINT 10/0;

VIEW диагностический запрос

SELECT * FROM sys.sql_modules WHERE [object_id] = OBJECT_ID('VarosokOrszagok2');
журналы базы данных по идентификатору базы данных
SELECT b.name,a.* FROM sys.databases b
CROSS APPLY sys.dm_db_log_info(b.database_id) a
WHERE b.name = 'WideWorldImporters';

перечислить все ТРИГГЕРА

SELECT * FROM sys.sql_modules WHERE [object_id] = OBJECT_ID('VarosokOrszagok2');

обнаружение заблокированных объектов

SELECT resource_type,request_status, request_mode, o.object_id, o.name, o.type_desc
FROM sys.dm_tran_locks l, sys.objects o
WHERE l.resource_associated_entity_id = o.object_id
    и resource_database_id = DB_ID();

определить уровень изоляции

SELECT CASE уровень_изоляции_транзакции
    WHEN 0 THEN 'Unspecified'
    WHEN 1 THEN 'ReadUncommitted'
    WHEN 2 THEN 'ReadCommitted'
    WHEN 3 THEN 'Repeatable
    WHEN 4 THEN 'Serializable
    WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL
FROM sys.dm_exec_sessions
-- используйте свой идентификатор сессии вместо 75
где session_id = 75;
------------------------------------------------------------
SELECT CASE transaction_isolation_level
    WHEN 0 THEN 'Unspecified'
    WHEN 1 THEN 'ReadUncommitted'
    КОГДА 2 КОГДА 'ReadCommitted'
    WHEN 3 THEN 'Repeatable
    WHEN 4 THEN 'Serializable
    WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL
FROM sys.dm_exec_sessions
где session_id = @@SPID

Викторина по SQL

https://github.com/green-fox-academy/safely-syllabus/tree/master/materialreview/mssql

###### SQL средства #######

SELECT IN SELECT, WHERE IN, WHERE NOT IN

SELECT TOP (1000) [SAMLAHEAD_ID]
      ,[VEVO_ID]
      ,[CELTE]
      ,[TELJESITES]
      ,[FIZHATARIDO]
      ,[NAVATKULDVE]
      ,[STATUS]
  ОТ [CEGESADATOK].[samlazas].[samlahead].
  WHERE VEVO_ID Not IN (select vevo_id
						от samlazas.vevo)

UPDATE

ИСПОЛЬЗОВАТЬ [CEGESADATA]
GO

UPDATE [samlazas].
   УСТАНОВИТЬ [СТАТУС] = 1
      ,[LASTUPDATE] = (GETDATE())
 ГДЕ СТАТУС=0
GO

INSERT INTO

ИСПОЛЬЗОВАТЬ [CEGESADATA]
GO

INSERT INTO [samlazas].[vevo]
           ([VEVONEV], [ADOSZAM], [CIM])
     VALUES
           ('öTÖDIK Nyrt','3499789012-4-04','4000 Kisvarda, Negyedik utca. 5'),
		   ('Hatodik Nyrt','3456789099-4-04','4000 Kisvarda, Negyedik utca. 6')
GO
/* MASIK */
ИСПОЛЬЗОВАТЬ [CEGESDATA]
GO

INSERT INTO [samlazas].[vevo]
           ([VEVONEV]
           ,[ADOSZAM]
           ,[CIM]
           ,[PHONE]
           ,[EMAIL]
           ,[STATUS])
     VALUES
           ('Hetedik Bt,
           '11223344-2-05',
           'None',
           '',
            null,
           0)
GO

УДАЛИТЬ

ИСПОЛЬЗОВАТЬ ЦЕГЕСДАТА
Перейти к

SELECT *
FROM szamlazas.szamlatetel
WHERE SZAMLAFEJ_ID Not IN (SELECT SZAMLAFEJ_ID
							FROM samlazas.samlahead)


SELECT HEAD_ID
FROM samlazas.samlahead

DELETE FROM samlazas.samlatetel
WHERE SZAMLAFEJ_ID Not IN (SELECT SZAMLAFEJ_ID
							FROM samlazas.samlahead)
GO

DISTINCT

SELECT DISTINCT v.*
FROM szamlazas.vevo v INNER JOIN szamlazas.szamlahead szf ON v.VEVO_ID=szf.VEVO_ID
WHERE v.STATUS=1 and szf.STATUS='normal'

КОНВЕРТ, АВГ

SELECT *
  ОТ [CEGESADATOK].[szamlazas].[ARU]

SELECT AVG(ASEGAR)
  ОТ [CEGESADATOK].[samlazas].[ARU]


SELECT *
  ОТ [CEGESADATOK].[samlazas].[ARU]
  где EGYSEGAR < (SELECT AVG(EGYSEGAR) as EGYSEGAR
					FROM [CEGESADATOK].[samlazas].[ARU])

SELECT ARU_ID, ARUNEV, MENNYEGYS, CONVERT(int,EGYSEGAR) as EGYSEGAR, CONVERT(int,AFAKULCS) as AFAKULCS, STATUS
  ОТ [CEGESADATOK].[szamlazas].[ARU]
  где EGYSEGAR < (SELECT AVG(EGYSEGAR) as EGYSEGAR
					FROM [CEGESADATOK].[samlazas].[ARU])

ИЗ ИСКУССТВА, ПРЕОБРАЗОВАТЬ

select getdate()
select FORMAT(getdate(), 'yyyy')
select FORMAT(getdate(), 'yyyy/MM')
select FORMAT(getdate(), 'yyyy/MM/dd')
select FORMAT(getdate()+1, 'yyyy/MM/dd')
select FORMAT(getdate()-2, 'yyyy/MM/dd')
select FORMAT(getdate(), 'yyyy/MM/dd HH:mm:ss.ms')

select '2022/3/15'
select CONVERT(datetime, '2022/3/25')

ИМЕЯ

/*Задача:
Найти имена, адреса, налоговые номера и количество покупок клиентов, которые совершили не менее двух покупок
НЕПРАВИЛЬНО:
select v.VEVONEV
from szamlazas.vevo v inner join szamlazas.szamlafej szf on v.VEVO_ID=szf.VEVO_ID
где (select COUNT(szf.VEVO_ID)
							из szamlazas.szamlafej
							group by szf.VEVO_ID) > 1
GROUP by v.VEVONEV
*/

select COUNT(szf.VEVO_ID), szf.VEVO_ID
							из szamlazas.szamlafej szf
							group by szf.VEVO_ID

выберите v.VEVONEV, v.CIM, v.ADOSZAM,
	COUNT(szf.VEVO_ID) as VASARLASSZAM
from szamlazas.vevo v inner join szamlazas.szamlafej szf on v.VEVO_ID=szf.VEVO_ID
GROUP by v.VEVONEV, v.CIM, v.ADOSZAM
HAVING COUNT(szf.VEVO_ID) >=2
ПОРЯДОК ПО VASARLASSZAM

/*Задача:
Получить имя, адрес, налоговый номер и количество покупок, общую сумму брутто*/.
select v.VEVO_ID,v.VEVONEV,v.CIM, v.ADOSZAM,
	CONVERT(int,SUM(t.[BRUTTO ERTEK])) as SUM
from szamlazas.vevo v inner join szamlazas.szamlafej szf on v.VEVO_ID=szf.VEVO_ID
inner join samlazas.tetelek t on szf.SZAMLAFEJ_ID=t.SZAMLAFEJ_ID
GROUP by v.VEVO_ID,v.VEVONEV,v.CIM,v.ADOSZAM
ORDER BY SZUMMABRUTTO desc


select v.VEVO_ID,v.VEVONEV,v.CIM,v.ADOSZAM,
	CONVERT(int,SUM(t.MENNYISEG*a.EGYSEGAR*(a.AFAKULCS/100+1))) as SUMABRUTTO
from szamlazas.vevo v inner join szamlazas.szamlafej szf on v.VEVO_ID=szf.VEVO_ID
inner join samlazas.szamlatetel t on szf.SZAMLAFEJ_ID=t.SZAMLAFEJ_ID
inner join samlazas.ARU a on t.ARU_ID=a.ARU_ID
WHERE v.CIM like '%Budapest%'
GROUP by v.VEVO_ID,v.VEVONEV,v.CIM,v.ADOSZAM
HAVING CONVERT(int,SUM(t.MENNYISEG*a.EGYSEGAR*(a.AFAKULCS/100+1))) > 121000
ORDER BY SUMABRUTTO desc

ПОСТОЯННЫЙ ФИКС

использовать ЦЕГЕСАДАТОК
перейти к

обновить szamlazas.szamlafej
	set STATUS='error'
where SZAMLAFEJ_ID not in (select SZAMLAFEJ_ID from szamlazas.szamlatetel)

перейти к

СОК ПРИСОЕДИНИТЬСЯ, СОК ГДЕ

ИСПОЛЬЗОВАТЬ ЦЕГЕСДАТА
ПЕРЕЙДИТЕ ПО ССЫЛКЕ

/* Кто купил A7?
выберите VEVONEV
из samlazas.vevo
WHERE VEVO_ID IN (select VEVO_ID
					FROM samlazas.samlahead
					WHERE SZAMLAFEJ_ID in (select SZAMLAFEJ_ID
											из samlazas.szamlatetel
											где ARU_ID IN (select ARU_ID
																из samlazas.ARU
																where ARUNEV='A7')))


выберите v.*
FROM szamlazas.vevo v
INNER JOIN samlazas.samlahead f ON v.VEVO_ID=f.VEVO_ID
	INNER JOIN samlazas.szamlatetel t ON f.SZAMLAFEJ_ID=t.SZAMLAFEJ_ID
		INNER JOIN samlazas.ARU a ON t.ARU_ID=a.ARU_ID
			ГДЕ a.ARUNEV='A7'
УПОРЯДОЧИТЬ ПО VEVO_ID

выберите *
From szamlazas.vevo v INNER JOIN szamlazas.szamlazfej sz ON v.VEVO_ID=sz.VEVO_ID
INNER JOIN samlazas.tetelek t ON sz.SZAMLAFEJ_ID=t.SZAMLAFEJ_ID
где t.ARUNEV='A7'




выберите *
FROM samlazas.samlahead
WHERE SZAMLAFEJ_ID in (select SZAMLAFEJ_ID
						из samlazas.szamlatetel
						где ARU_ID IN (select ARU_ID
											из samlazas.ARU
											where ARUNEV='A7'))

выберите *
Из szamlazas.szamlatetel
где ARU_ID IN (select ARU_ID
					из szamlazas.ARU
					where ARUNEV='A7')

/*
выберите *
From samlazas.tetelek t INNER JOIN samlazas.samlafej sz ON t.SZAMLAFEJ_ID=sz.SZAMLAFEJ_ID
где ARUNEV='A7'

выберите *
из samlazas.vevo v INNER JOIN (samlazas.tetelek t INNER JOIN samlazas.samlahead sz ON t.SZAMLAFEJ_ID=sz.SZAMLAFEJ_ID) ON
где ARUNEV='A7'
*/

ЗАПРОСИТЬ ВИД НА

ИСПОЛЬЗОВАТЬ ЦЕГЕСДАТА
ПЕРЕЙДИТЕ ПО ССЫЛКЕ

SELECT t.*, a.ARUNEV, a.MENNYEGYS, a.EGYSEGAR, a.EGYSEGAR*t.MENNYISEG как 'Netto ertek', a.EGYSEGAR*t.MENNYISEG*(a.AFAKULCS/100) как 'AFA ERTEK'
, (a.EGYSEGAR*t.MENNYISEG*(a.AFAKULCS/100))+(a.EGYSEGAR*t.MENNYISEG) как 'GROSS ERTEK'
FROM szamlazas.szamlatetel t INNER JOIN szamlazas.ARU a ON t.ARU_ID=a.ARU_ID
/*WHERE (t.ARU_ID=a.ARU_ID)*/

ВЫБРАТЬ *
FROM samlazas.tetelek

СОЗДАТЬ ВИД

создать представление samlazas.bad_samla_head
в качестве
выберите *
из samlazas.samlahead
where VEVO_ID not in (select vevo_id from szamlazas.vevo)

Создайте схему

ИСПОЛЬЗОВАТЬ [CEGESADATA]
GO

/****** Объект: schema [samlazas] Дата сценария: 2022. 10/25 0:11:57 ******/
CREATE SCHEMA [samlazas]
GO

Создание пользователя, входа в систему, разрешений, ролей

USE [master]
GO
CREATE LOGIN [test1] WITH PASSWORD=N'Password123', DEFAULT_DATABASE=[CEGESADATOK], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ИСПОЛЬЗОВАТЬ [CEGESADATOK]
GO
CREATE USER [test1] FOR LOGIN [test1]
GO
ИСПОЛЬЗОВАТЬ [CEGESDATA]
GO
ALTER ROLE [db_backupoperator] ADD MEMBER [test1]
GO
ИСПОЛЬЗОВАТЬ [CEGESDATA]
GO
ALTER ROLE [db_datareader] ADD MEMBER [test1]
GO
ИСПОЛЬЗОВАТЬ [CEGESADATA]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [test1]
GO
ИСПОЛЬЗОВАТЬ [CEGESDATA]
GO
ALTER ROLE [db_ddladmin] ADD MEMBER [test1]
GO
ИСПОЛЬЗОВАТЬ [CEGESDATA]
GO
ALTER ROLE [db_securityadmin] ADD MEMBER [test1]
GO

Удаление таблицы, усечение таблицы

/****** Сценарий для команды SelectTopNRows из SSMS ******/
SELECT TOP (1000) [SAMLAHEAD_ID]
      ,[VEVO_ID]
      ,[KELTE]
      ,[TELJESITES]
      ,[FIZHATARIDO]
      ,[NAVATKULDVE]
      ,[STATUS]
  ОТ [CEGESADATOK].[samlazas].[samlahead].

  усечь таблицу szamlazas.szamlafej

  массовая вставка samlazas.samlahead
  FROM N'E:\szamlafej.table.csv'
  with (fieldterminator=';',rowterminator='\n')

######3 экзамен SQL ########

Создание таблиц (создать таблицу)

CREATE TABLE apprentices(
	id int,
	имя varchar(50),
	возраст инт,
	страна varchar(50),
	cohort_id int)

CREATE TABLE cohorts(
	id int,
	имя varchar(50),
	дата начала_действия,
	тип varchar(50))

Импорт таблиц (массовая вставка)

/****** Скрипт для команды SelectTopNRows из SSMS ******/
  массовая вставка учеников
  FROM N'E:\apprentices.table.csv'
  WITH (fieldterminator=';',rowterminator='\n'/*, FIRSTROW=2 ha van fejlec*/)

  массовая вставка когорт
  FROM N'E:\cohorts.table.csv'
  WITH (fieldterminator=';',rowterminator='\n')

Перечислите имя (name) и дату начала (started_at) всех когорт (таблица cohorts).
Filename: cohorts.sql

SELECT имя, started_at
 ОТ когорт

Перечислите имя и страну происхождения всех студентов (таблица учеников)
Filename: apprentices.sql

SELECT имя, страна
ОТ учеников

Укажите имя и возраст студентов (таблица учеников), которые моложе 21 года
Filename: apprentices-younger-21.sql

SELECT имя, возраст
ОТ учеников
ГДЕ возраст < 21

Перечислите имена и фамилии студентов (таблица учеников), которые не являются жителями Венгрии (страна)
Filename: apprentices-not-from-hu.sql

SELECT id, name
ОТ учеников
WHERE страна не похожа на 'Венгрия'

Выпишите, сколько студентов (таблицы учеников) в базе данных в возрасте 20 или более и 30 или менее лет
Filename: apprentices-between-20-and-30.sql

SELECT count(id)
ОТ учеников
WHERE возраст >= 20 и возраст <= 30

Выпишите, сколько в базе данных имеется когорт, которые имеют тип "неполный рабочий день" (тип)
Filename: cohorts-part-time.sql

SELECT count(id)
ОТ когорт
WHERE type like 'part-time'

Перечислите имена учеников (как имя_ученика) и имена когорт (как имя_группы) студентов, которые начали обучение в когортах 2018 года.
Filename: apprentices-in-2018.sql

SELECT a.name as apprentice_name, c.name as cohort_name
FROM ученики a INNER JOIN когорты c ON a.cohort_id=c.id
WHERE FORMAT(started_at, 'yyyy')=2018

Выпишите, сколько классов в базе данных, для которых нет учеников в базе данных
Filename: cohorts-without-apprentice.sql

SELECT count(id)
ОТ когорт
WHERE id NOT IN (SELECT cohort_id FROM apprentices)

Перечислите имена студентов и дату начала обучения в алфавитном порядке по фамилии студента
Filename: apprentices-start-date-ordered.sql

SELECT a.name, c.started_at
FROM ученики a INNER JOIN когорты c ON a.cohort_id=c.id
ORDER BY a.name

Перечислить названия классов и количество учеников (как numberOfApprentices) в данном классе, в порядке убывания количества учеников. Включите когорту, в которой в настоящее время 0 студентов.
Filename: cohort-sizes.sql

SELECT c.name, count(a.id) as numberOfApprentices
FROM когорты c LEFT JOIN ученики a ON a.cohort_id=c.id
GROUP BY c.name
ORDER BY count(a.id) DESC

Задачи проекта SQL admin ######### ####

1) На сервер добавляется диск, который еще не используется
(20 ГБ). Добавьте S: в качестве диска и имени тома:
SQLDATA должна быть.

2) На диске S: создайте каталог SQLDATA, а в нем
еще два подкаталога с именами DB и LOG.

3) Войдите в SQL-сервер (экземпляр GREENFOX) (Этот шаг
вы не обязаны сохранять его! :-)

4) Восстановите файл C:\install\MSSQL\AdventureWorks2016.bak
базы данных на экземпляр сервера GREENFOX, чтобы база данных
файлы находятся в S:\SQLDATA\DB, а файлы журналов - в S:\SQLDATA\
Библиотека LOG.

 
USE [master]
RESTORE DATABASE [AdventureWorks2016] FROM DISK = N'C:\install\MSSQL\AdventureWorks2016.bak' WITH FILE = 1, MOVE N'AdventureWorks2016_Data' TO N'S:\SQLDATADB\AdventureWorks2016_Data.mdf', MOVE N'AdventureWorks2016_Log' TO N'S:\SQLDATA\LOG\AdventureWorks2016_Log.ldf', NOUNLOAD, STATS = 5

GO

5) Создайте аутентифицированную учетную запись interface_svc MS SQL,
с минимальными (публичными) правами! Ваш пароль должен быть INTERface.

USE [master]
GO
CREATE LOGIN [interface_svc] WITH PASSWORD=N'INTERFACE', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

6) Создайте базу данных под названием INTERFACE со следующими настройками:
- Имя базы данных: INTERFACE
- Владелец: interface_svc
- Модель восстановления: FULL
- Начальный файл базы данных:
(a)размер: 32 МБайт
(b)местоположение: S:\SQLDATA\DB
- Файл журнала запуска:
(a) размер: 16 МБайт
(b)местоположение: S:\SQLDATA\LOG

СОЗДАТЬ БАЗУ ДАННЫХ [ИНТЕРФЕЙС]
 СДЕРЖИВАНИЕ = НЕТ
 НА ПРИМАРИ
( NAME = N'INTERFACE', FILENAME = N'S:\SQLDATA\DB\INTERFACE.mdf' , SIZE = 32768KB , FILEGROWTH = 65536KB )
 ЗАГРУЗИТЬ
( NAME = N'INTERFACE_log' , FILENAME = N'S:\SQLDATA\LOG\INTERFACE_log.ldf' , SIZE = 16384KB , FILEGROWTH = 65536KB )
GO

ИСПОЛЬЗОВАТЬ [ИНТЕРФЕЙС]
GO
ALTER AUTHORIZATION ON DATABASE::[INTERFACE] TO [interface_svc]
/*
exec sp_changedbowner 'interface_svc'
GO
*/

ALTER DATABASE [INTERFACE] SET COMPATIBILITY_LEVEL = 150
GO
ALTER DATABASE [INTERFACE] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [INTERFACE] SET ANSI_NULLS OFF
GO
ALTER DATABASE [INTERFACE] SET ANSI_PADDING OFF
GO
ALTER DATABASE [INTERFACE] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [INTERFACE] SET ARITHABORT OFF
GO
ALTER DATABASE [INTERFACE] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [INTERFACE] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [INTERFACE] SET AUTO_CREATE_STATISTICS ON(INCREMENTAL = OFF)
GO
ALTER DATABASE [INTERFACE] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [INTERFACE] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [INTERFACE] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [INTERFACE] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [INTERFACE] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [INTERFACE] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [INTERFACE] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [INTERFACE] SET DISABLE_BROKER
GO
ALTER DATABASE [INTERFACE] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [INTERFACE] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [INTERFACE] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [INTERFACE] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [INTERFACE] SET READ_WRITE
GO
ALTER DATABASE [INTERFACE] SET RECOVERY FULL
GO
ALTER DATABASE [INTERFACE] SET MULTI_USER
GO
ALTER DATABASE [INTERFACE] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [INTERFACE] SET TARGET_RECOVERY_TIME = 60 SECONDS
GO
ALTER DATABASE [INTERFACE] SET DELAYED_DURABILITY = DISABLED
GO
ИСПОЛЬЗОВАТЬ [ИНТЕРФЕЙС]
GO
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = Off;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = Primary;
GO
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = PRIMARY;
GO
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = On;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = Primary;
GO
ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = Off;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET QUERY_OPTIMIZER_HOTFIXES = Primary;
GO
ИСПОЛЬЗОВАТЬ [ИНТЕРФЕЙС]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [INTERFACE] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO

7) Предоставьте учетной записи interface_svc только разрешение на запрос с
База данных AdventureWorks2016 в схеме HumanResources
vВидовой экран сотрудника (VIEW)!

использовать [AdventureWorks2016]
GO
CREATE USER [interface_svc] FOR LOGIN [interface_svc]
GO
GRANT SELECT ON [HumanResources].[vEmployee] TO [interface_svc]
GO

8) Создайте схему AWDB в базе данных INTERFACE. Сценарий
"Создать в формате "

ИСПОЛЬЗОВАТЬ [ИНТЕРФЕЙС]
GO

/****** Объект: schema [AWDB] Script Date: 12/9/2022 8:21:47 AM ******/
СОЗДАТЬ СХЕМУ [AWDB]
GO

9) В схеме AWDB создайте синоним под названием AW_Employee,
которая находится в схеме HumanResources базы данных AdventureWorks2016
указывает на область просмотра vEmployee!

ИСПОЛЬЗОВАТЬ [ИНТЕРФЕЙС]
GO

/****** Объект: синоним [AWDB].[AW_Employee] Дата сценария: 12/6/2022 1:59:44 PM ******/
CREATE SYNONYM [AWDB].[AW_Employee] FOR [AdventureWorks2016].[HumanResources].[vEmployee]
GO

10) Создайте запрос, который будет отправлен в базу данных INTERFACE с помощью AWDB
все строки и столбцы синонима AW_Employee в схеме
.................

ИСПОЛЬЗОВАТЬ [ИНТЕРФЕЙС]
GO
SELECT *
FROM AWDB.AW_Employee

11) Создайте ПОЛНУЮ резервную копию базы данных INTERFACE, используя следующее
согласно:
a) Имя файла резервной копии: S:\SQLdata\INTERFACE.bak
b) Сохранение перезаписывает одноименный медиа-набор!

BACKUP DATABASE [INTERFACE] TO DISK = N'S:\SQLdata\INTERFACE.bak' WITH NOFORMAT, INIT, NAME = N'S:\SQLdata\INTERFACE.bak', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

1) Узнайте версию SQL-сервера и выпуск SQL-сервера!

ВЫБЕРИТЕ @@VERSION

2) Составьте простой запрос "Hello World!". запрос!

SELECT 'Hello World!'

3) Создайте SQL-запрос, который вычисляет квадратный корень из 30!

ВЫБЕРИТЕ SQRT(30)

4) Создайте SQL-запрос, который возвращает сегодняшнюю дату!

SELECT CAST(GETDATE() as date)

5) Создайте SQL-запрос, который подсчитывает, сколько дней прошло
С 15 марта 1989 года!

SELECT DATEDIFF(day,'1989-03-15', GETDATE());

6) Создайте SQL-запрос, который возвращает загруженные ранее данные
Первые 20 элементов таблицы Person.Address в базе данных AdventureWorks
(все поля)!

/****** Сценарий для команды SelectTopNRows из SSMS ******/
ВЫБЕРИТЕ TOP (20) *
  FROM [AdventureWorks2016].[Person].[Address]

7) Создайте SQL-запрос, который возвращает загруженные ранее данные
Первые 20 элементов таблицы Person.Person в базе данных AdventureWorks
(все поля), но где поле PersonType - 'EM'!

/****** Сценарий для команды SelectTopNRows из SSMS ******/
SELECT TOP (20) *
  FROM [AdventureWorks2016].[Person].[Person].[Person]
  WHERE [AdventureWorks2016].[Person].[Person].PersonType='EM'

8) Создайте SQL-запрос, который возвращает загруженные ранее данные
первые 20 элементов таблицы Person.Person в базе данных AdventureWorks, но
чтобы в поле PersonType было 'EM' и только PersonType,
Поля FirstName, LastName должны быть в результате запроса!

/****** Сценарий для команды SelectTopNRows из SSMS ******/
SELECT TOP (20) PersonType, FirstName, LastName
  FROM [AdventureWorks2016].[Person].[Person].[Person]
  WHERE [AdventureWorks2016].[Person].[Person].PersonType='EM'

9) Создайте SQL-запрос, который возвращает загруженные ранее данные
первые 20 элементов таблицы Person.Person в базе данных AdventureWorks, но
чтобы в поле PersonType было 'EM' и только PersonType,
Поля FirstName, LastName должны быть в результате запроса, но поля
Имя и фамилия должны отображаться в столбце с одним пробелом
разделенные символом!

/****** Сценарий для команды SelectTopNRows из SSMS ******/
SELECT TOP (20) PersonType, FirstName + ' ' + LastName
  FROM [AdventureWorks2016].[Person].[Person].[Person]
  WHERE PersonType='EM'

10)Создайте SQL-запрос, который возвращает ранее загруженные
записи в таблице Person.Person базы данных AdventureWorks
(все поля), где поле PersonType равно 'EM', а FirstName
поле должно показывать только тех, у кого есть имя "Джон"!

/****** Сценарий для команды SelectTopNRows из SSMS ******/
SELECT *
  FROM [AdventureWorks2016].[Person].[Person].[Person]
  WHERE PersonType='EM' AND FirstName='John'

11)Создайте SQL-запрос, который возвращает ранее загруженные данные
записи в таблице Person.Person базы данных AdventureWorks
(все поля), где поле PersonType равно 'EM', а FirstName
поле должно отображать только тех, кто имеет имя 'John', где
Значение поля LastName заканчивается символом 'e'!

/****** Сценарий для команды SelectTopNRows из SSMS ******/
SELECT *
  FROM [AdventureWorks2016].[Person].[Person]
  WHERE PersonType='EM' AND FirstName='John' AND RIGHT(LastName,1)='e'

12)Создайте SQL-запрос, который возвращает ранее загруженные
записи в таблице Person.Person базы данных AdventureWorks
(все поля), где поле PersonType равно 'EM', а FirstName
поле должно отображать только тех, кто имеет имя 'John', где
Значения поля LastName содержат либо 'Chen', либо 'Kane'!

/****** Сценарий для команды SelectTopNRows из SSMS ******/
SELECT *
  FROM [AdventureWorks2016].[Person].[Person].[Person]
  WHERE PersonType='EM' AND FirstName='John' AND (LastName='Chen' OR LastName='Kane')

13) Посчитайте, сколько различных имен (FirstName) есть в
в таблице Person.Person ранее загруженной базы данных AdventureWorks!

/****** Сценарий для команды SelectTopNRows из SSMS ******/
SELECT COUNT(DISTINCT FirstName)
  FROM [AdventureWorks2016].[Person].[Person].[Person]

14) Подсчитайте количество записей с первым именем 'Ken' (FirstName) в списке
в таблице Person.Person ранее загруженной базы данных AdventureWorks!

/****** Сценарий для команды SelectTopNRows из SSMS ******/
SELECT COUNT(FirstName)
  FROM [AdventureWorks2016].[Person].[Person]
  WHERE FirstName='Ken'

15) Подсчитайте, сколько записей с одинаковым именем (FirstName и LastName)
это ранее загруженная база данных AdventureWorks Person.Person
на вашей доске! Расположите результаты так, чтобы наиболее используемые
пары имен в верхней части списка и только 20 лучших результатов
будьте включены!

/****** Сценарий для команды SelectTopNRows из SSMS ******/
SELECT TOP (20) FirstName, LastName, COUNT(1)
  FROM [AdventureWorks2016].[Person].[Person].[Person]
  GROUP BY FirstName, LastName
  ORDER BY COUNT(1) DESC

16) Подсчитайте, сколько записей с одинаковым именем (FirstName и LastName)
это ранее загруженная база данных AdventureWorks Person.Person
на вашей доске! Расположите результаты так, чтобы было только 4 и более 4
используемые для пары имен, должны быть указаны!

SELECT FirstName, LastName, COUNT(1)
  FROM [AdventureWorks2016].[Person].[Person].[Person]
  GROUP BY FirstName, LastName
  ЕСЛИ COUNT(1) >= 4
  ORDER BY COUNT(1) DESC

17)Создайте SQL-запрос, который возвращает ранее загруженный
База данных AdventureWorks Person.Person и Person.EmailAddress
таблицы со следующими полями: Person.Person.FirstName,
Person.Person.LastName, Person.EmailAddress.EmailAddress таким образом, чтобы
для связи таблиц, поле BusinessEntityID используется для обеих таблиц.
из совета директоров! Отобразите только первые 50 строк результата!

SELECT TOP (50) p.FirstName, p.LastName, e.EmailAddress
  FROM [AdventureWorks2016].[Person].[Person] p INNER JOIN [AdventureWorks2016].[Person].[EmailAddress] e on p.BusinessEntityID=e.BusinessEntityID

18)На основе предыдущего запроса создайте решение, в котором адрес thee-mail
(EmailAddress) только часть имени (до появления части @)!

SELECT TOP (50) p.FirstName, p.LastName, LEFT(e.EmailAddress,CHARINDEX('@',e.EmailAddress)-1)
  FROM [AdventureWorks2016].[Person].[Person] p INNER JOIN [AdventureWorks2016].[Person].[EmailAddress] e on p.BusinessEntityID=e.BusinessEntityID

19)Или запрос, в котором только доменная часть (после @)
заметно!

SELECT TOP (50) p.FirstName, p.LastName, RIGHT(e.EmailAddress,LEN(e.EmailAddress)- CHARINDEX('@',e.EmailAddress))
  FROM [AdventureWorks2016].[Person].[Person] p INNER JOIN [AdventureWorks2016].[Person].[EmailAddress] e on p.BusinessEntityID=e.BusinessEntityID

20) (ВРЕМЕННОЕ ЗАДАНИЕ) Создайте запрос, который возвращает
Sales.SalesPerson из поля BusinessEntityID и "minsales"
это вычисляемый столбец. Значение minsales должно быть, построчно, SalesYTD
поле, если SalesYTD > 1000000, иначе NULL
добавлять ценность.

/****** Сценарий для команды SelectTopNRows из SSMS ******/
SELECT [BusinessEntityID], CASE WHEN SalesYTD > 1000000 THEN SalesYTD ELSE NULL END as minsales
  FROM [AdventureWorks2016].[Sales].[SalesPerson]

21) (ОЧЕНЬ ВАЖНОЕ ЗАДАНИЕ) Создайте запрос, который возвращает значение
Sales.SalesPerson из поля BusinessEntityID и "salesinfo"
это вычисляемый столбец. Значение salesinfo должно быть установлено для каждой строки в SalesYTD
поле, если SalesYTD >= 2000000, Если
SalesYTD<2000000, но больше или равно 1000000, то
значение должно быть 1500000, если меньше 1000000, то NULL. например.
выходы

/****** Сценарий для команды SelectTopNRows из SSMS ******/
SELECT [BusinessEntityID], CASE WHEN SalesYTD >= 2000000 THEN SalesYTD ELSE CASE WHEN SalesYTD>=1000000 THEN 1500000 ELSE NULL END END as salesinfo
  FROM [AdventureWorks2016].[Sales].[SalesPerson]

Оценка уровня знаний Microsoft SQL Server (далее SQL)

  • Используйте код T-SQL
  • Обязательно используйте предоставленные имена файлов SQL
  • Загруженный вами ZIP-файл не должен содержать подкаталогов, только файлы
  • Убедитесь, что имена баз данных, пользователей и других объектов чувствительны к регистру символов
  • В запросах изменяйте названия столбцов, которые будут отображаться только в том случае, если этого специально требует задача
  • Запросы, которые будут отображаться в столбцы в порядке, указанном в упражнении появиться
  • Убедитесь, что вы всегда выбираете правильную базу данных
  • Используйте кодировку символов ASCII или UTF-8 (без BOM)
  • По умолчанию стартовой базой данных для MSSQL LOGIN, который "запускает" задания в контейнере Docker, являются первые 10 заданий: мастер.
  • В контейнере Docker стартовая база данных по умолчанию для MSSQL LOGIN, который "запускает" задания в контейнере Docker, составляет от 11) до 20) заданий: AdventureWorks.Для этих задач достаточно, если загруженный вами файл содержит только запрос.
  • Оценка файлов расширения sql в первую очередь автогрейдер остальные оцениваются вручную.

Задачи:

  1. На сервер добавляется диск, который еще не используется (20 ГБ). Установите его, создайте раздел размером 20 ГБ с файловой системой NTFS и добавьте S: монтируется как диск, а имя тома: SQLDATA должно быть.
Доказательства: Управление компьютером => Хранилище => Управление дисками
Имя файла: WINSQL-01.jpg
  1. S: диск для создания SQLDATA каталог, а затем еще два подкаталога в нем: DB и LOG с именем.
Доказательства: Открыв File Explorer => S: => каталог SQLDATA, вы увидите два запрашиваемых подкаталога.
Имя файла: WINSQL-02.jpg
  1. Войдите в сервер SQL (GREENFOX экземпляр) с помощью Microsoft SQL Server Management Studio!
Доказательства: Экземпляр GREENFOX, открытый в Microsoft SQL Server Management Studio.
Имя файла: WINSQL-03.jpg
  1. Извлечь C:\install\MSSQL\AdventureWorks2016.bak базы данных на экземпляр сервера GREENFOX AdventureWorks чтобы файлы базы данных хранились в S:\SQLDATA\DB, в то время как файлы журналов в S:\SQLDATA\LOG в библиотеку.

Gradescopeban вы можете найти резервную копию здесь: /AdventureWorks2016.bak, файлы базы данных: /SQLDATA/DB/AdventureWorks_Data.mdf, в то время как файлы журналов в /SQLDATA/LOG/AdventureWorks_Log.ldf файлы должны быть помещены в

Доказательства: Скопируйте или сохраните сценарий восстановления в Microsoft SQL Server Management Studio.
Имя файла: WINSQL-04.sql
USE [master]
RESTORE DATABASE [AdventureWorks] FROM DISK = N'/home/AdventureWorks2016.bak' WITH FILE = 1, MOVE N'AdventureWorks2016_Data' TO N'/SQLDATA/DB/AdventureWorks_Data.mdf', MOVE N'AdventureWorks2016_Log' TO N'/SQLDATA/LOG/AdventureWorks_Log.ldf', NOUNLOAD, STATS = 5

GO
  1. Создать isvc Аутентифицированная учетная запись MS SQL Server с минимальными (публичными) правами! Пароль должен быть: интерфАс21 Пароль не должен меняться пользователем при первом входе в систему.
Доказательства: Скопируйте или сохраните сценарий в Microsoft SQL Server Management Studio.
Имя файла: WINSQL-05.sql
USE [master]
GO
CREATE LOGIN [isvc] WITH PASSWORD=N'interfAce21', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
  1. Создать ИНТЕРФЕЙС со следующими настройками:
    1. Имя базы данных: INTERFACE
    2. Владелец: isvc
    3. Модель восстановления: FULL
    4. Начальный файл базы данных:
      1. размер: 64 МБайт
      2. местоположение: S:\SQLDATA\DB (Градкоскоп на /SQLDATA/DB)
    5. Файл журнала запуска:
      1. размер: 24 МБайт
      2. местоположение: S:\SQLDATA\LOG (Градкоскоп на /SQLDATA/LOG)
СОЗДАТЬ БАЗУ ДАННЫХ [ИНТЕРФЕЙС]
 СДЕРЖИВАНИЕ = НЕТ
 НА ПРИМАРИ
( NAME = N'INTERFACE', FILENAME = N'/SQLDATA/DB/INTERFACE.mdf' , SIZE = 65536KB , FILEGROWTH = 65536KB )
 ВКЛЮЧИТЬ ЖУРНАЛ
( NAME = N'INTERFACE_log', FILENAME = N'/SQLDATA/LOG/INTERFACE_log.ldf' , SIZE = 24576KB , FILEGROWTH = 65536KB )
GO

ИСПОЛЬЗОВАТЬ [ИНТЕРФЕЙС]
GO
ALTER AUTHORIZATION ON DATABASE::[INTERFACE] TO [isvc]

ALTER DATABASE [INTERFACE] SET COMPATIBILITY_LEVEL = 150
GO
ALTER DATABASE [INTERFACE] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [INTERFACE] SET ANSI_NULLS OFF
GO
ALTER DATABASE [INTERFACE] SET ANSI_PADDING OFF
GO
ALTER DATABASE [INTERFACE] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [INTERFACE] SET ARITHABORT OFF
GO
ALTER DATABASE [INTERFACE] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [INTERFACE] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [INTERFACE] SET AUTO_CREATE_STATISTICS ON(INCREMENTAL = OFF)
GO
ALTER DATABASE [INTERFACE] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [INTERFACE] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [INTERFACE] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [INTERFACE] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [INTERFACE] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [INTERFACE] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [INTERFACE] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [INTERFACE] SET DISABLE_BROKER
GO
ALTER DATABASE [INTERFACE] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [INTERFACE] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [INTERFACE] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [INTERFACE] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [INTERFACE] SET READ_WRITE
GO
ALTER DATABASE [INTERFACE] SET RECOVERY FULL
GO
ALTER DATABASE [INTERFACE] SET MULTI_USER
GO
ALTER DATABASE [INTERFACE] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [INTERFACE] SET TARGET_RECOVERY_TIME = 60 SECONDS
GO
ALTER DATABASE [INTERFACE] SET DELAYED_DURABILITY = DISABLED
GO
ИСПОЛЬЗОВАТЬ [ИНТЕРФЕЙС]
GO
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = Off;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = Primary;
GO
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = PRIMARY;
GO
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = On;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = Primary;
GO
ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = Off;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET QUERY_OPTIMIZER_HOTFIXES = Primary;
GO
ИСПОЛЬЗОВАТЬ [ИНТЕРФЕЙС]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [INTERFACE] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO
  1. Предоставьте права только на запросы isvc учитывать AdventureWorks база данных HumanResources в схеме vEmployee в свой список!
Доказательства: Скопируйте или сохраните сценарий в Microsoft SQL Server Management Studio.
Имя файла: WINSQL-07.sql
USE [AdventureWorks]
GO
CREATE USER [isvc] FOR LOGIN [isvc]
GO
GRANT SELECT ON [HumanResources].[vEmployee] TO [isvc]
GO
  1. ИНТЕРФЕЙС базу данных для создания База данных AWdatabase схема!
Доказательства: Microsoft SQL Server Management Studio в экземпляре GREENFOX в базе данных INTERFACE схема базы данных AWdatabase сценарий "Create To".
Имя файла: WINSQL-08.sql
ИСПОЛЬЗОВАТЬ [ИНТЕРФЕЙС]
GO

/****** Объект: schema [dbo] Script Date: 2023 Jan 15 13:45:32 ******/
CREATE SCHEMA [AWdatabase]
GO
  1. С сайта База данных AWdatabase схема для создания синоним AW_Employee который является AdventureWorks база данных HumanResources в схеме vEmployee указывает на область просмотра!
Доказательства: Microsoft SQL Server Management Studio в экземпляре GREENFOX базы данных INTERFACE AWdatabase schema AW_Employee синоним сценария "Create To".
Имя файла: WINSQL-09.sql
ИСПОЛЬЗОВАТЬ [ИНТЕРФЕЙС]
GO
CREATE SYNONYM [AWdatabase].[AW_Employee ] FOR [AdventureWorks].[HumanResources].[vEmployee]
GO
  1. Сделать ИНТЕРФЕЙС создайте ПОЛНУЮ резервную копию базы данных следующим образом:
  1. Имя файла резервного копирования: S:\SQLDATA\INTERFACE.bak (В Gradescope /var/backups/INTERFACE.bak)
  2. При сохранении перезаписывается одноименный медиа-набор!
Доказательства: В Microsoft SQL Server Management Studio в экземпляре GREENFOX сценарий резервного копирования базы данных INTERFACE.
Имя файла: WINSQL-10.sql
BACKUP DATABASE [INTERFACE] TO DISK = N'/var/backups/INTERFACE.bak' WITH NOFORMAT, INIT, NAME = N'INTERFACE-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
  1. Постройте SQL-запрос, который вычисляет квадратный корень из 25!
Доказательства: Скопируйте или сохраните сценарий в Microsoft SQL Server Management Studio.
Имя файла: WINSQL-11.sql
ВЫБЕРИТЕ SQRT(25)
  1. Создайте SQL-запрос, который возвращает сегодняшнюю дату в данном формате: 2022-10-25
Доказательства: Скопируйте или сохраните сценарий в Microsoft SQL Server Management Studio.
Имя файла: WINSQL-12.sql
SELECT CAST(GETDATE() as date)
  1. Создайте SQL-запрос, который подсчитывает, сколько дней прошло с 25 апреля 1994 года!
Доказательства: Скопируйте или сохраните сценарий в Microsoft SQL Server Management Studio.
Имя файла: WINSQL-13.sql
SELECT DATEDIFF(day,'2022-04-25',GETDATE())
  1. Создайте SQL-запрос, который возвращает ранее загруженные AdventureWorks база данных Person.Address таблицу (все поля - не меняйте порядок столбцов)!
Доказательства: Скопируйте или сохраните сценарий в Microsoft SQL Server Management Studio.
Имя файла: WINSQL-14.sql
/****** Сценарий для команды SelectTopNRows из SSMS ******/
USE [AdventureWorks]
GO
ВЫБЕРИТЕ ВЕРШИНУ (15) *
  FROM [AdventureWorks].[Person].[Address]
  1. Создайте SQL-запрос, который возвращает ранее загруженные AdventureWorks база данных Person.Person с сайта первые 5 элементов(все поля - не меняйте порядок столбцов), но так, чтобы там, где PersonType значение поля 'EM‘!
Доказательства: Скопируйте или сохраните сценарий в Microsoft SQL Server Management Studio.
Имя файла: WINSQL-15.sql
USE [AdventureWorks]
GO
ВЫБЕРИТЕ ВЕРХНИЕ (5) *
  FROM [AdventureWorks].[Person].[Person].[Person]
  WHERE PersonType='EM'
  1. Создайте SQL-запрос, который возвращает ранее загруженные AdventureWorks база данных Person.Person с сайта первые 30 пунктовно так, чтобы там, где PersonType значение поля 'EM' и только PersonType, FirstName, LastName поля в результате запроса!
Доказательства: Скопируйте или сохраните сценарий в Microsoft SQL Server Management Studio.
Имя файла: WINSQL-16.sql
USE [AdventureWorks]
GO
SELECT TOP (30) PersonType, FirstName, LastName
  FROM [AdventureWorks].[Person].[Person].[Person]
  WHERE PersonType='EM'
  1. Создайте SQL-запрос, который возвращает ранее загруженные AdventureWorks база данных Person.Person с сайта первые 20 пунктовно так, чтобы там, где PersonType значение поля 'EM' и только PersonType, FirstName, LastName поля в результате запроса, но Имя и Фамилия должны располагаться в столбце, разделенные одним символом пробела, Полное имя под именем!
Доказательства: Скопируйте или сохраните сценарий в Microsoft SQL Server Management Studio.
Имя файла: WINSQL-17.sql
USE [AdventureWorks]
GO
SELECT TOP (20) PersonType, FirstName+ ' ' + LastName as FullName
  FROM [AdventureWorks].[Person].[Person].[Person]
  WHERE PersonType='EM'
  1. Создайте SQL-запрос, который возвращает ранее загруженные AdventureWorks база данных Person.Person таблицы (все столбцы, все поля - не меняйте порядок столбцов), где PersonType значение поля 'EM' и FirstName поле содержит только 'Джон' должен быть включен!
Доказательства: Скопируйте или сохраните сценарий в Microsoft SQL Server Management Studio.
Имя файла: WINSQL-18.sql
USE [AdventureWorks]
GO
SELECT *
  FROM [AdventureWorks].[Person].[Person].[Person]
  WHERE PersonType='EM' AND FirstName='John'
  1. Создайте SQL-запрос, который возвращает ранее загруженные AdventureWorks база данных Person.Person таблицы (все столбцы, все поля - не меняйте порядок столбцов), где PersonType значение поля 'EM' и FirstName поле содержит только 'Джон', должны отображаться только те, где Фамилия значение поля 'eКонец персонажа!
Доказательства: Скопируйте или сохраните сценарий в Microsoft SQL Server Management Studio.
Имя файла: WINSQL-19.sql
USE [AdventureWorks]
GO
SELECT *
  FROM [AdventureWorks].[Person].[Person].[Person]
  WHERE PersonType='EM' AND FirstName='John' AND RIGHT(LastName,1)='e'
  1. Посчитайте, сколько 'Кен' имя (FirstName) запись является ранее загруженной AdventureWorks база данных Person.Person в вашем совете!
Доказательства: Скопируйте или сохраните сценарий в Microsoft SQL Server Management Studio.
Имя файла: WINSQL-20.sql
USE [AdventureWorks]
GO
ВЫБЕРИТЕ COUNT(*)
  FROM [AdventureWorks].[Person].[Person].[Person]
  WHERE FirstName='Ken'