text: USE [AdventureWorks2019];
  
  --Создать синоним
  
  CREATE SYNONYM
  	P1 FOR [Person].[Person];
  
  --Сделать выборку из исходной таблицы
  
  SELECT
  	*
  FROM
  	[Person].[Person];
  
  --Сделать выборку из исходной таблицы, обращаясь к её синониму
  
  SELECT
  	*
  FROM
  	P1;
  
  --Удалить созданный синоним
  
  DROP SYNONYM
  	P1;
  
  --создаем таблицу с кандидатами
  CREATE TABLE candidates ( 
  	candname VARCHAR(10) NOT NULL, 
  	gender CHAR(1) NOT NULL CONSTRAINT chk_gender CHECK (gender IN ('F', 'M')) 
    );
  
  --наполняем её данными
  INSERT INTO
  	dbo.candidates 
  VALUES
  	('Neil', 'M'),
  	('Trevor', 'M'), 
  	('Terresa', 'F'),
  	('Mary', 'F');
  
  --делаем выборку из нашей таблицы
  SELECT
  	*
  FROM
  	dbo.candidates;
  
  --строим нашу таблицу рекомендаций для свиданий
  
  SELECT
  	C1.candname, 
      C2.candname 
  FROM
  	dbo.candidates AS C1 
  CROSS JOIN
  	dbo.candidates AS C2
  WHERE
  	C1.candname <> C2.candname AND
  	C1.gender <> C2.gender AND
  	C1.gender = 'M';
  
  --TOP фильтры
  
  --Вывести все данные из нашей таблицы
  
  SELECT
  	Name,
  	ProductNumber,
  	StandardCost
  FROM
  	Production.Product
  ORDER BY
  	StandardCost DESC;
  
  --Вывести первые 20 значений
  
  SELECT
  	TOP 20 Name,
  	ProductNumber,
  	StandardCost
  FROM
  	Production.Product
  ORDER BY
  	StandardCost DESC;
  
  --Вывести первые 20 значений с повторами значений из столбца в ORDER BY
  
  SELECT
  	TOP 20 WITH TIES Name
  	ProductNumber,
  	StandardCost
  FROM
  	Production.Product
  ORDER BY
  	StandardCost DESC;
  
  --Вывести 20% значений из нашей исходной таблицы
  
  SELECT
  	TOP 20 PERCENT Name,
  	ProductNumber,
  	StandardCost
  FROM
  	Production.Product
  ORDER BY
  	StandardCost DESC;
  
  --Пропустить первые 20 строк и вывести следующие 20 строк из нашей таблицы
  
  SELECT
  	Name,
  	ProductNumber,
  	StandardCost
  FROM
  	Production.Product
  ORDER BY
  	StandardCost DESC
  OFFSET
  	20 ROWS
  FETCH
  	NEXT 20 ROWS ONLY;
  
  --Пропустить 0 строк и вывести следующие 20 строк из нашей таблицы
  
  SELECT
  	Name,
  	ProductNumber,
  	StandardCost
  FROM
  	Production.Product
  ORDER BY
  	StandardCost DESC
  OFFSET
  	0 ROWS
  FETCH
  	NEXT 20 ROWS ONLY;
  
  --Пропустить первые 20 строк и вывести все оставшиеся данные из нашей таблицы из нашей таблицы
  
  SELECT
  	Name,
  	ProductNumber,
  	StandardCost
  FROM
  	Production.Product
  ORDER BY
  	StandardCost DESC
  OFFSET
  	20 ROWS;
  
  SELECT
  	PI.ProductID,
  	PI.LocationID,
  	PI.Quantity
  FROM
  	Production.ProductInventory AS PI;
  
  SELECT TOP 3
  	PI.ProductID,
  	PI.LocationID,
  	PI.Quantity
  FROM
  	Production.ProductInventory AS PI
  ORDER BY
  	PI.Quantity DESC;
  
  SELECT
  	P.ProductID,
  	P.Name
  FROM
    Production.Product AS P;
  
  --CROSS APPLY
  
  SELECT
  	P.ProductID,
  	P.Name,
  	A.LocationID,
  	A.Quantity
  FROM
    Production.Product AS P
  CROSS APPLY
  	(SELECT TOP 3
  		PI.ProductID,
  		PI.LocationID,
  		PI.Quantity
  	FROM
  		Production.ProductInventory AS PI
  	WHERE
  		PI.ProductID = P.ProductID
  	ORDER BY
  		PI.Quantity DESC) AS A
  ORDER BY
  	P.ProductID;
  
  --OUTER APPLY
  
  SELECT
  	P.ProductID,
  	P.Name,
  	A.LocationID,
  	A.Quantity
  FROM
    Production.Product AS P
  OUTER APPLY
  	(SELECT TOP 3
  		PI.ProductID,
  		PI.LocationID,
  		PI.Quantity
  	FROM
  		Production.ProductInventory AS PI
  	WHERE
  		PI.ProductID = P.ProductID
  	ORDER BY
  		PI.Quantity DESC) AS A
  ORDER BY
  	P.ProductID;
  
  --создадим новую базу данных
  CREATE DATABASE
  	University;
  
  --переключимся на неё
  USE
  	University;
  
  --создадим в новой базе данных таблицу
  
  CREATE TABLE Class
  (
  	ID INT PRIMARY KEY IDENTITY,
  	StudentName VARCHAR(50),
  	Course VARCHAR(50),
  	Score INT
  );
  
  --наполним эту талицу тестовыми данными
  
  INSERT INTO
  	dbo.Class
  VALUES
  	('Sally', 'English', 95),
  	('Sally', 'History', 82),
  	('Edward', 'English', 45),
  	('Edward', 'History', 78);
  
  USE
  	University;
  
  --сделаем выборку из нашей исходной таблицы
  
  SELECT
  	*
  FROM
  	dbo.Class;
  
  --перевернем данные из строк в столбцы используя PIVOT
  
  SELECT
  	*
  FROM
  	(SELECT 
  		StudentName,
  		Course,
  		Score
  	FROM 
  		dbo.Class
  	) AS ClassTable
  PIVOT
  	(
  		SUM(Score)
  			FOR Course IN
  				([English],[History])
  	) AS SchoolPivot;
  
  --перевернутые данные обратно вернем в строки из столбцов используя UNPIVOT
  
  WITH original AS
  	(SELECT
  		*
  	FROM
  		(SELECT 
  			StudentName,
  			Course,
  			Score
  		FROM 
  			dbo.Class
  		) AS ClassTable
  	PIVOT
  		(
  			SUM(Score)
  				FOR Course IN
  					([English],[History])
  		) AS SchoolPivot
  	UNPIVOT
  		(Score
  			FOR Course IN
  				(English, History)
  		) AS SchoolUnpivot)
  SELECT
  	StudentName,
  	Course,
  	Score
  FROM
  	original
  ORDER BY
  	1 DESC;
  
  
  --Коррелируемый подзапрос:
  SELECT
  	P.Name,
  	P.ListPrice, 
  	(SELECT
  		PM.Name 
  	 FROM
  		Production.ProductModel AS PM
  	 WHERE
  		PM.ProductModelID = P.ProductID) AS Model 
  FROM
  	Production.Product AS P;
  
  --Вариант решения через LEFT JOIN
  
  SELECT
  	P.Name,
  	P.ListPrice,
  	PM.Name
  FROM
  	Production.Product AS P
  LEFT JOIN
  	Production.ProductModel AS PM
  		ON PM.ProductModelID = P.ProductID;
  
  --Пример не коррелируемого запроса:
  
  --Вариант 1
  
  SELECT
  	SOH.SalesOrderID,
  	SOH.OrderDate,
  	SOH.CustomerID
  FROM
  	Sales.SalesOrderHeader AS SOH
  WHERE
  	SOH.CustomerID = (SELECT
  				C.CustomerID
  			  FROM
  				Sales.Customer AS C
  			  JOIN
  				Person.Person AS P
  					ON C.PersonID = P.BusinessEntityID
  			  WHERE
  				P.LastName = 'Sullivan'
  				AND
  				P.FirstName = 'Michael');
  
  --Вариант 2
  
  SELECT
  	SP.BusinessEntityID 
  FROM
  	Sales.SalesPerson AS SP 
  WHERE
  	SP.Bonus > (SELECT
  		    	MIN(SP.Bonus) 
  		    FROM
  			Sales.SalesPerson AS SP);
  
  --Подзапрос с использованием ALL
  
  SELECT
  	BusinessEntityID,
  	Bonus
  FROM
  	Sales.SalesPerson 
  WHERE
  	Bonus > ALL (SELECT
  			Bonus 
  		     FROM
  			Sales.SalesPerson
  		     WHERE
  			SalesQuota = 250000);
  
  --Подзапрос с использованием ANY
  
  SELECT
  	BusinessEntityID,
  	Bonus
  FROM
  	Sales.SalesPerson 
  WHERE
  	Bonus > ANY (SELECT
  			Bonus 
  		     FROM
  			Sales.SalesPerson
  		     WHERE
  			SalesQuota = 250000);
  
  --подзапрос с использованием IN
  
  SELECT
  	SalesOrderID,
  	OrderDate,
  	AccountNumber,
  	CustomerID,
  	SalesPersonID,
  	TotalDue
  FROM
  	Sales.SalesOrderHeader
  WHERE
  	SalesPersonID
  		IN
  			(279, 286, 289);
  
  --подзапрос с использованием NOT IN
  
  SELECT
  	SalesOrderID,
      	OrderDate,
      	AccountNumber,
      	CustomerID,
      	SalesPersonID,
      	TotalDue
  FROM
  	Sales.SalesOrderHeader
  WHERE
  	SalesPersonID
  		NOT IN
  			(SELECT
  				BusinessEntityID
               		 FROM
  				Sales.SalesPerson
               		 WHERE
  				Bonus > 5000);
  
  --с помощью подзапроса проверить существуют ли у нас не заполненные идентификаторы заказа с помощью подзапроса с использованием IN
  
  SELECT
  	SalesOrderID,
  	RevisionNumber,
  	OrderDate
  FROM
  	Sales.SalesOrderHeader
  WHERE
  	SalesOrderID
  		IN (SELECT NULL);
  
  --подзапрос с использованием EXISTS
  
  SELECT
  	SOH.SalesOrderID,
  	SOH.OrderDate
  FROM
  	Sales.SalesOrderHeader AS SOH
  WHERE
  	EXISTS (SELECT
  			*
              	FROM
  			Sales.SalesPerson AS SP
              	WHERE
  			SP.SalesYTD > 3000000
  			AND
  			SOH.SalesPersonID = SP.BusinessEntityID);
  
  --подзапрос с использованием NOT EXISTS
  
  SELECT
  	SOH.SalesOrderID,
  	SOH.OrderDate
  FROM
  	Sales.SalesOrderHeader AS SOH
  WHERE
  	NOT EXISTS (SELECT
  			*
              	FROM
  			Sales.SalesPerson AS SP
              	WHERE
  			SP.SalesYTD > 3000000
  			AND
  			SOH.SalesPersonID = SP.BusinessEntityID);
  
  --Пример рекурсивного CTE
  
  WITH cte_numbers (number_day, weekday) AS
  (
  	SELECT 
  		0, 
          DATENAME(DW, 0)
      UNION ALL
      SELECT    
          number_day + 1, 
          DATENAME(DW, number_day + 1)
      FROM    
          cte_numbers
      WHERE
  		number_day < 6
  )
  SELECT 
  	number_day,
      weekday
  FROM 
      cte_numbers;
  
  --Вариант 1 написания конструкции CASE
  
  SELECT
  	ContactTypeID,
  	CASE ContactTypeID
  		WHEN 2 THEN 'Two'
  		WHEN 1 THEN 'One'
  		WHEN 0 THEN 'Zero'
  		ELSE 'Other'
  	END AS number
  FROM
  	Person.ContactType
  WHERE
  	ContactTypeID < 10;
  
  --Вариант 2 написания конструкции CASE
  
  SELECT
  	ContactTypeID,
      	CASE
  		WHEN ContactTypeID = 2 THEN 'Two'
  		WHEN ContactTypeID = 1 THEN 'One'
  		WHEN ContactTypeID = 0 THEN 'Zero'
  		ELSE 'Other'
  	END AS number
  FROM
  	Person.ContactType
  WHERE
  	ContactTypeID < 10;
  
  --Пример объясления переменных в конструкции CASE:
  
  --Пример 1
  
  DECLARE
  	@a TINYINT = 1,
  	@b VARCHAR(10) = 'Привет!',
  	@c BIT
  
  SELECT
  	CASE
  		WHEN @a > 2 THEN N'a больше 2. Это неверно.'
  		WHEN LEN(@b) = 10 THEN N'b состоит из 10 символов. Это неверно.'
  		WHEN @c IS NULL AND @b LIKE 'П%' THEN N'c содержит пустое значение! b начинается с буквы «П»! Это верно!'
  		ELSE N'Очевидно, мы не знаем, что происходит'
  	END AS выражение;
  
  --Пример 2 (меняем значение переменной а)
  
  DECLARE
  	@a TINYINT = 3,
  	@b VARCHAR(10) = 'Привет!',
  	@c BIT
  
  SELECT
  	CASE
  		WHEN @a > 2 THEN N'a больше 2.'
  		WHEN LEN(@b) = 10 THEN N'b состоит из 10 символов.'
  		WHEN @c IS NULL AND @b LIKE 'П%' THEN N'c содержит пустое значение! b начинается с буквы «П»!'
  		ELSE N'Очевидно, мы не знаем, что происходит'
  	END AS выражение;
  
  
  --Пример 3 (меняем значение переменной b)
  
  DECLARE
  	@a TINYINT = 1,
  	@b VARCHAR(10) = 'Объявление',
  	@c BIT
  
  SELECT
  	CASE
  		WHEN @a > 2 THEN N'a больше 2.'
  		WHEN LEN(@b) = 10 THEN N'b состоит из 10 символов.'
  		WHEN @c IS NULL AND @b LIKE 'П%' THEN N'c содержит пустое значение! b начинается с буквы «П»!'
  		ELSE N'Очевидно, мы не знаем, что происходит'
  	END AS выражение;
  
  --Пример 4 (меняем значение переменной b, чтобы выполнялось последнее условие)
  
  DECLARE
  	@a TINYINT = 1,
  	@b VARCHAR(10) = 'Новости',
  	@c BIT = 0
  
  SELECT
  	CASE
  		WHEN @a > 2 THEN N'a больше 2.'
  		WHEN LEN(@b) = 10 THEN N'b состоит из 10 символов.'
  		WHEN @c IS NULL AND @b LIKE 'П%' THEN N'c содержит пустое значение! b начинается с буквы «П»!'
  		ELSE N'Очевидно, мы не знаем, что происходит'
  	END AS выражение;
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  