SQL

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

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

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

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

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

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

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;

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;

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

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;

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;

ВЫБОР 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 < '2017010101' THEN '2016y'
END as years
FROM Sales.Orders)
SELECT years, COUNT(years)
FROM whatever
GROUP BY years;

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

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');

SET IDENTITY_INSERT Hr.Employees ON;

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

SET IDENTITY_INSERT Hr.Employees OFF;

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 dbo.testlines
OUTPUT deleted.*
FROM dbo.testlines tol
INNER JOIN dbo.testorders tor ON tor.OrderID=tol.OrderID
WHERE OrderDate='20130101'

/* 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)
);

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

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)
);

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;

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;

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

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

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

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

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';

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;

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;

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;

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';

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

SELECT @ido_datetime, @ido_datetime2;

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;

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

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;

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;

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

DECLARE @ma DATETIME2 =GETUTCDATE();

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

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')

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)

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

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;

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

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

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;

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

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

ПРИНТ 10/0;

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

НАЧНИТЕ ПОПЫТКУ
    ПРИНТ 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;

   НАЧАТЬ ПОПЫТКУ
   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;

НАЧАТЬ ПОПЫТКУ
    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;

НАЧАТЬ ПОПЫТКУ
    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;

НАЧАТЬ ПОПЫТКУ
    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;

НАЧАТЬ ПОПЫТКУ
    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;

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;

НАЧНИТЕ ПОПЫТКУ
    ПРИНТ 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;

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

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

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%'

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

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;

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.Instal 10,0, @ertek OUTPUT;
SELECT @ret, @ertek;

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;

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;

ИСПОЛЬЗОВАТЬ 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;

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;

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);

ИСПОЛЬЗОВАНИЕ 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);

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);

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

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

/* версия подзапроса: 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;

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;

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;

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;

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 'C:\Users\mrhen\Documents\READER\MatReview\WEEK-4\w1d1_smalltototal.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;

ИСПОЛЬЗОВАНИЕ 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;
/* Версия функции Window */
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;

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

/* идентификатор следующего города */
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;

SELECT * FROM sys.tables;

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;

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 (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 [схема], 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

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

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
where session_id = 75;

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)

ИСПОЛЬЗОВАТЬ [CEGESADATA]
GO

UPDATE [samlazas].
   УСТАНОВИТЬ [СТАТУС] = 1
      ,[LASTUPDATE] = (GETDATE())
 ГДЕ СТАТУС=0
GO

ИСПОЛЬЗОВАТЬ [CEGESADATA]
GO

INSERT INTO [samlazas].[vevo]
           ([VEVONEV], [ADOSZAM], [CIM])
     VALUES
           ('öTÖDIK Nyrt','3499789012-4-04','4000 Kisvarda, Negyedik utca. 5'),
		   ('Sixth Plc','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',
           '',
            ноль,
           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

SELECT DISTINCT v.*
FROM szamlazas.vevo v INNER JOIN szamlazas.szamlahead szf ON v.VEVO_ID=szf.VEVO_ID
WHERE v.STATUS=1 и 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])

выберите 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')

выберите '2022/3/15'
select CONVERT(datetime, '2022/3/25')

/*Задача:
Найдите имена, адреса, налоговые номера и количество покупок клиентов, которые совершили не менее двух покупок
НЕПРАВИЛЬНО:
выберите 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
ГРУППА В.ВЕВОНЕВА
*/

select COUNT(szf.VEVO_ID), szf.VEVO_ID
							от szamlazas.szamlafej szf
							группировать по 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])) как SUM
from szamlazas.vevo v inner join szamlazas.szamlafej szf on v.VEVO_ID=szf.VEVO_ID
внутреннее соединение 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
внутреннее соединение samlazas.szamlatetel t on szf.SZAMLAFEJ_ID=t.SZAMLAFEJ_ID
внутреннее соединение 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
	установить STATUS='error'
where SZAMLAFEJ_ID not in (select SZAMLAFEJ_ID from szamlazas.szamlatetel)

перейти к

ИСПОЛЬЗОВАТЬ ЦЕГЕСДАТА
GO

/* Кто купил 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
																где ARUNEV='A7')))


select 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
			WHERE a.ARUNEV='A7'
ORDER BY 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
											где ARUNEV='A7'))

выберите *
От szamlazas.szamlatetel
где ARU_ID IN (select ARU_ID
					От samlazas.ARU
					где ARUNEV='A7')

/*
выберите *
From samlazas.tetelek t INNER JOIN samlazas.samlafej sz ON t.SZAMLAFEJ_ID=sz.SZAMLAFEJ_ID
где ARUNEV='A7'

выберите *
From samlazas.vevo v INNER JOIN (samlazas.tetelek t INNER JOIN samlazas.samlahead sz ON t.SZAMLAFEJ_ID=sz.SZAMLAFEJ_ID) ON
где ARUNEV='A7'
*/

ИСПОЛЬЗОВАТЬ ЦЕГЕСДАТА
GO

SELECT t.*, a.ARUNEV, a.MENNYEGYS, a.EGYSEGAR, a.EGYSEGAR*t.MENNYISEG as 'Netto ertek', a.EGYSEGAR*t.MENNYISEG*(a.AFAKULCS/100) as '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)*/

SELECT *
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

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')

SELECT имя, started_at
 ОТ когорт

SELECT имя, страна
ОТ учеников

SELECT имя, возраст
ОТ учеников
ГДЕ возраст < 21

SELECT id, name
ОТ учеников
Страна, которая не похожа на "Венгрию

SELECT count(id)
ОТ учеников
WHERE возраст >= 20 и возраст <= 30

SELECT count(id)
ОТ когорт
WHERE type like 'part-time'

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

SELECT count(id)
ОТ когорт
WHERE id NOT IN (SELECT cohort_id FROM apprentices)

SELECT a.name, c.started_at
FROM ученики a INNER JOIN когорты c ON a.cohort_id=c.id
ORDER BY a.name

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

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

USE [master]
GO
CREATE LOGIN [interface_svc] WITH PASSWORD=N'INTERFACE', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

СОЗДАТЬ БАЗУ ДАННЫХ [ИНТЕРФЕЙС]
 СДЕРЖИВАНИЕ = НЕТ
 НА ПЕРВИЧНОЙ ОСНОВЕ
( 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

использовать [AdventureWorks2016]
GO
CREATE USER [interface_svc] FOR LOGIN [interface_svc]
GO
GRANT SELECT ON [HumanResources].[vEmployee] TO [interface_svc]
GO

ИСПОЛЬЗОВАТЬ [ИНТЕРФЕЙС]
GO

/****** Объект: schema [AWDB] Script Date: 12/9/2022 8:21:47 AM ******/
СОЗДАТЬ СХЕМУ [AWDB]
GO

ИСПОЛЬЗОВАТЬ [ИНТЕРФЕЙС]
GO

/****** Объект: синоним [AWDB].[AW_Employee] Дата сценария: 12/6/2022 1:59:44 PM ******/
CREATE SYNONYM [AWDB].[AW_Employee] FOR [AdventureWorks2016].[HumanResources].[vEmployee]
GO

ИСПОЛЬЗОВАТЬ [ИНТЕРФЕЙС]
GO
SELECT *
FROM AWDB.AW_Employee

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

ВЫБЕРИТЕ @@VERSION

SELECT 'Hello World!'

ВЫБЕРИТЕ SQRT(30)

SELECT CAST(GETDATE() as date)

SELECT DATEDIFF(day,'1989-03-15', GETDATE());

/****** Сценарий для команды SelectTopNRows из SSMS ******/
ВЫБЕРИТЕ TOP (20) *
  FROM [AdventureWorks2016].[Person].[Address]

/****** Сценарий для команды SelectTopNRows из SSMS ******/
ВЫБЕРИТЕ TOP (20) *
  FROM [AdventureWorks2016].[Person].[Person].[Person]
  WHERE [AdventureWorks2016].[Person].[Person].PersonType='EM'

/****** Сценарий для команды SelectTopNRows из SSMS ******/
SELECT TOP (20) PersonType, FirstName, LastName
  FROM [AdventureWorks2016].[Person].[Person].[Person]
  WHERE [AdventureWorks2016].[Person].[Person].PersonType='EM'

/****** Сценарий для команды SelectTopNRows из SSMS ******/
SELECT TOP (20) PersonType, FirstName + ' ' + LastName
  FROM [AdventureWorks2016].[Person].[Person].[Person]
  WHERE PersonType='EM'

/****** Сценарий для команды SelectTopNRows из SSMS ******/
SELECT *
  FROM [AdventureWorks2016].[Person].[Person].[Person]
  WHERE PersonType='EM' AND FirstName='John'

/****** Сценарий для команды SelectTopNRows из SSMS ******/
SELECT *
  FROM [AdventureWorks2016].[Person].[Person].[Person]
  WHERE PersonType='EM' AND FirstName='John' AND RIGHT(LastName,1)='e'

/****** Сценарий для команды SelectTopNRows из SSMS ******/
SELECT *
  FROM [AdventureWorks2016].[Person].[Person].[Person]
  WHERE PersonType='EM' AND FirstName='John' AND (LastName='Chen' OR LastName='Kane')

/****** Сценарий для команды SelectTopNRows из SSMS ******/
SELECT COUNT(DISTINCT FirstName)
  FROM [AdventureWorks2016].[Person].[Person].[Person]

/****** Сценарий для команды SelectTopNRows из SSMS ******/
SELECT COUNT(FirstName)
  FROM [AdventureWorks2016].[Person].[Person].[Person]
  WHERE FirstName='Ken'

/****** Сценарий для команды SelectTopNRows из SSMS ******/
SELECT TOP (20) FirstName, LastName, COUNT(1)
  FROM [AdventureWorks2016].[Person].[Person].[Person]
  GROUP BY FirstName, LastName
  ORDER BY COUNT(1) DESC

SELECT FirstName, LastName, COUNT(1)
  FROM [AdventureWorks2016].[Person].[Person].[Person]
  GROUP BY FirstName, LastName
  ЕСЛИ COUNT(1) >= 4
  ORDER BY COUNT(1) DESC

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

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

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

/****** Сценарий для команды SelectTopNRows из SSMS ******/
SELECT [BusinessEntityID], CASE WHEN SalesYTD > 1000000 THEN SalesYTD ELSE NULL END as minsales
  FROM [AdventureWorks2016].[Sales].[SalesPerson]

/****** Сценарий для команды 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]

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

USE [master]
GO
CREATE LOGIN [isvc] WITH PASSWORD=N'interfAce21', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

СОЗДАТЬ БАЗУ ДАННЫХ [ИНТЕРФЕЙС]
 СДЕРЖИВАНИЕ = НЕТ
 НА ПЕРВИЧНОЙ ОСНОВЕ
( 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

USE [AdventureWorks]
GO
CREATE USER [isvc] FOR LOGIN [isvc]
GO
GRANT SELECT ON [HumanResources].[vEmployee] TO [isvc]
GO

ИСПОЛЬЗОВАТЬ [ИНТЕРФЕЙС]
GO

/****** Объект: schema [dbo] Script Date: 2023 Jan 15 13:45:32 ******/
CREATE SCHEMA [AWdatabase]
GO

ИСПОЛЬЗОВАТЬ [ИНТЕРФЕЙС]
GO
CREATE SYNONYM [AWdatabase].[AW_Employee ] FOR [AdventureWorks].[HumanResources].[vEmployee]
GO

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

ВЫБЕРИТЕ SQRT(25)

SELECT CAST(GETDATE() as date)

SELECT DATEDIFF(day,'2022-04-25',GETDATE())

/****** Сценарий для команды SelectTopNRows из SSMS ******/
USE [AdventureWorks]
GO
ВЫБЕРИТЕ ВЕРШИНУ (15) *
  FROM [AdventureWorks].[Person].[Address]

USE [AdventureWorks]
GO
ВЫБЕРИТЕ ВЕРШИНУ (5) *
  FROM [AdventureWorks].[Person].[Person].[Person]
  WHERE PersonType='EM'

USE [AdventureWorks]
GO
SELECT TOP (30) PersonType, FirstName, LastName
  FROM [AdventureWorks].[Person].[Person].[Person]
  WHERE PersonType='EM'

USE [AdventureWorks]
GO
SELECT TOP (20) PersonType, FirstName+ ' ' + LastName как FullName
  FROM [AdventureWorks].[Person].[Person].[Person]
  WHERE PersonType='EM'

USE [AdventureWorks]
GO
SELECT *
  FROM [AdventureWorks].[Person].[Person].[Person]
  WHERE PersonType='EM' AND FirstName='John'

USE [AdventureWorks]
GO
SELECT *
  FROM [AdventureWorks].[Person].[Person].[Person]
  WHERE PersonType='EM' AND FirstName='John' AND RIGHT(LastName,1)='e'

USE [AdventureWorks]
GO
ВЫБЕРИТЕ COUNT(*)
  FROM [AdventureWorks].[Person].[Person].[Person]
  WHERE FirstName='Ken'