/*
Operatory przypisania
*/
DECLARE @zm1 INT = 2;
DECLARE @zm2 INT = @zm1;
SET @zm1 *= 2;
SELECT @zm2 /=2;
SET @zm1 -= @zm2;
SELECT @zm1,@zm2;

SET @zm1 ^= 4;
SET @zm2 ^= 3;
SELECT @zm1,@zm2;

UPDATE AdventureWorks2008.Production.Product
SET SafetyStockLevel -= O.OrderQty
FROM Production.Product AS P 
INNER JOIN Sales.SalesOrderDetail AS O 
ON P.ProductID = O.ProductID; 

/*
Konstruktor wierszy
*/
USE tempdb
DECLARE @zm INT = 1;
DECLARE @Tab TABLE(a VARCHAR(20), b INT);
INSERT @Tab(a, b) 
VALUES(1,2), (@zm, @zm);
SELECT *
FROM @Tab;

CREATE TABLE Znajomi (
Nazwisko VARCHAR(20), 
Wiek INT);
GO
INSERT INTO Znajomi 
VALUES('Bak', 15), ('Lis', 61);
SELECT * FROM Znajomi;

DECLARE @Tab TABLE(a VARCHAR(20), b INT);
INSERT @Tab(a, b) 
VALUES(1,2);
INSERT INTO Znajomi 
VALUES ((SELECT TOP(1) a FROM @Tab), (SELECT TOP(1) b FROM @Tab));
SELECT *
FROM Znajomi;

SELECT * 
FROM (VALUES('Nowak', 25),('Kowalski', 36)) Znajomi (Nazwisko, Wiek);

/*
Klauzula TOP w widokach
*/
USE AdventureWorks2008
GO
CREATE VIEW HumanResources.vPracownicy AS 
SELECT TOP 100 PERCENT e.BusinessEntityID,p.FirstName,p.LastName,e.JobTitle,
pp.PhoneNumber,a.City
FROM HumanResources.Employee AS e
INNER JOIN Person.Person AS p ON p.BusinessEntityID = e.BusinessEntityID
INNER JOIN Person.BusinessEntityAddress AS bea ON bea.BusinessEntityID = e.BusinessEntityID 
INNER JOIN Person.Address AS a ON a.AddressID = bea.AddressID
LEFT OUTER JOIN Person.PersonPhone AS pp ON pp.BusinessEntityID = p.BusinessEntityID
ORDER BY p.LastName;

SELECT *
FROM HumanResources.vPracownicy;

/*
Typy daty i czasu
*/
DECLARE @data DATE = GETDATE();
SELECT @data, DATALENGTH(@data);

DECLARE @czas1 TIME(0) = SYSDATETIME(), @czas2 TIME(7) = SYSDATETIME();
SELECT @czas1,DATALENGTH(@czas1),@czas1,DATALENGTH(@czas1);

DECLARE @dt1 DATETIME2(0) = SYSDATETIME(), @dt2 DATETIME2(7) = SYSDATETIME(); 
SELECT @dt1, DATALENGTH(@dt1), @dt2, DATALENGTH(@dt2);

DECLARE @o1 DATETIMEOFFSET(0) = SYSDATETIMEOFFSET();
DECLARE @o2 DATETIMEOFFSET(7) = SYSDATETIMEOFFSET();
SELECT @o1, DATALENGTH(@o1),SWITCHOFFSET(@o1,'+02:00')
UNION ALL
SELECT @o2, DATALENGTH(@o2),SWITCHOFFSET(@o1,'-10:00');

SELECT ISDATE('1/1/1700'), ISDATE('1/1/1800'), ISDATE('1/1/10000');

DECLARE @dt DATETIME = GETDATE();
DECLARE @dt2 DATETIME2(7) = GETDATE();
SELECT @dt,@dt2;

DECLARE @dt DATETIME = SYSDATETIME();
DECLARE @dt2 DATETIME2(7) = SYSDATETIME();
SELECT @dt,@dt2

DECLARE @dt DATETIME = SYSDATETIME();
SELECT @dt+1

DECLARE @dt2 DATETIME2(7) = SYSDATETIME();
SELECT @dt2+1

DECLARE @dt2 DATETIME2 = SYSDATETIME();
SELECT DATEADD(d,1,@dt2)

SET DATEFORMAT YDM
GO
DECLARE @dt DATETIME = '2008-5-11';
DECLARE @dt2 DATETIME2 = '2008-5-11';
SELECT MONTH(@dt), DAY(@dt);
SELECT MONTH(@dt2), DAY(@dt2);

CREATE INDEX IXSalesOrderHeaderOrderDate
ON Sales.SalesOrderHeader(OrderDate);

SELECT SalesOrderID, OrderDate, SalesOrderNumber
FROM Sales.SalesOrderHeader
WHERE OrderDate BETWEEN '20020324' AND '20020324';

SELECT SalesOrderID, OrderDate, SalesOrderNumber
FROM Sales.SalesOrderHeader
WHERE CONVERT(VARCHAR(10), OrderDate, 20) = '2002-03-24';
 
SELECT SalesOrderID, OrderDate, SalesOrderNumber
FROM Sales.SalesOrderHeader
WHERE CONVERT(DATE, OrderDate) = '2002-03-24'; 
 
/*
Typy i parametry tabelaryczneu
*/
USE tempdb
CREATE TABLE dbo.Zamowienia (
 IDZamowienia INT IDENTITY,
 Klient VARCHAR(5),
 Data DATETIME);
GO
CREATE TABLE dbo.PozycjeZamowienia (
 IDZamowienia INT,
 Towar INT,
 Ilosc INT);
 GO
 
 CREATE PROCEDURE dbo.DodajZamowienie (
 @Klient VARCHAR(5), 
 @IDZamowienia INTEGER OUTPUT, 
 @Data DATETIME OUTPUT)
AS 
 SET @Data = GETDATE();
 INSERT INTO dbo.Zamowienia (Data, Klient) 
 VALUES (@Data, @Klient); 
 SELECT @IDZamowienia = SCOPE_IDENTITY();
GO
CREATE PROCEDURE dbo.DodajSzczegolyZamowienia (
 @IDZamowienia INTEGER, 
 @Towar INTEGER, 
 @Ilosc INTEGER)
AS 
 INSERT INTO dbo.PozycjeZamowienia (IDZamowienia, Towar, Ilosc) 
 VALUES (@IDZamowienia, @Towar, @Ilosc);

DECLARE @IDZamowienia INT;
DECLARE @Data DATETIME; 
EXEC dbo.DodajZamowienie 'MAMSA', @IDZamowienia OUTPUT, @Data OUTPUT;
EXEC dbo.DodajSzczegolyZamowienia @IDZamowienia, 111, 1;
EXEC dbo.DodajSzczegolyZamowienia @IDZamowienia, 222, 2;
EXEC dbo.DodajSzczegolyZamowienia @IDZamowienia, 333, 3;

CREATE TYPE Tab1 AS TABLE (ID INT);
GO
DECLARE @tablica AS Tab1;

CREATE TYPE Zamowienia AS TABLE(Towar INTEGER, Ilosc INTEGER);
GO
CREATE PROCEDURE dbo.DodajZamowienia (
 @Klient VARCHAR(5), 
 @Pozycje ZAMOWIENIA READONLY, 
 @IDZamowienia INTEGER OUTPUT, 
 @Data DATETIME OUTPUT)
AS 
 SET @Data = GETDATE();
 INSERT INTO Zamowienia (Data, Klient) 
 VALUES (@Data, @Klient); 
 SELECT @IDZamowienia = SCOPE_IDENTITY(); 
 INSERT INTO dbo.PozycjeZamowienia (IDZamowienia, Towar, Ilosc) 
 SELECT @IDZamowienia, Towar, Ilosc
 FROM @Pozycje;

DECLARE @IDZamowienia INT;
DECLARE @Data DATETIME;
DECLARE @T ZAMOWIENIA ;
INSERT INTO @T 
VALUES(444, 4), (555,5), (666,6); 
EXEC dbo.DodajZamowienia 'ZYXWQ', @T, @IDZamowienia OUTPUT, @Data OUTPUT;

DECLARE @IDZamowienia INT;
DECLARE @Data DATETIME;
EXEC dbo.DodajZamowienia 'BEARTY',@IDZamowienia = @IDZamowienia OUTPUT, @Data=@Data OUTPUT;
GO

CREATE FUNCTION udfParametryTabelaryczne (@t as dbo.Zamowienia READONLY)
RETURNS INT
AS
BEGIN;
RETURN (SELECT COUNT(1) FROM @t);
END;
GO

DECLARE @t AS dbo.Zamowienia
SELECT dbo.udfParametryTabelaryczne(@t)

/*
Operator APPLY
*/
USE AdventureWorks2008;
SELECT P.Name, S1.*
FROM Production.Product AS P
CROSS APPLY (
SELECT TOP 1 WITH TIES PurchaseOrderID, LineTotal, DueDate
FROM Purchasing.PurchaseOrderDetail PU
WHERE PU.ProductID = P.ProductID
ORDER BY DueDate DESC) AS S1 
WHERE P.Name LIKE 'Ch%'
ORDER BY P.Name;

SELECT P.Name, S1.*
FROM Production.Product P
OUTER APPLY (
SELECT TOP 1 WITH TIES PurchaseOrderID, LineTotal, DueDate
FROM Purchasing.PurchaseOrderDetail PU
WHERE PU.ProductID = P.ProductID
ORDER BY DueDate DESC) AS S1 
WHERE P.Name LIKE 'Ch%'
ORDER BY P.Name;
GO

CREATE FUNCTION udfOstatnieZamowienia(@custid AS INT, @n AS INT)
RETURNS TABLE
AS RETURN
SELECT TOP(@n) SalesOrderID,OrderDate,CustomerID,TotalDue
FROM Sales.SalesOrderHeader
WHERE CustomerID = @custid
ORDER BY OrderDate DESC;

SELECT S.Name, F.OrderDate, F.CustomerID, F.TotalDue
FROM Sales.Customer AS C JOIN Sales.Store AS S
ON S.BusinessEntityID = C.StoreID
CROSS APPLY udfOstatnieZamowienia(C.CustomerID, 5) AS F
ORDER BY S.Name ,F.Orderdate;

/*
Grupowanie danych
*/
SELECT SC.ProductCategoryID,SC.Name,SUM(P.ListPrice)
FROM Production.Product AS P JOIN Production.ProductSubcategory AS SC
ON P.ProductSubcategoryID=SC.ProductSubcategoryID
WHERE SC.Name LIKE 'B%'
GROUP BY ROLLUP (SC.ProductCategoryID,SC.Name);

SELECT CAST(OrderDate AS DATE), CustomerID, SUM(TotalDue)
FROM Sales.SalesOrderHeader
WHERE SalesPersonID=287 AND YEAR(OrderDate)=2002
GROUP BY CUBE (OrderDate, CustomerID);

SELECT MONTH(OrderDate),TerritoryID ,CustomerID,SUM(TotalDue)
FROM Sales.SalesOrderHeader
WHERE SalesPersonID=285 AND YEAR(OrderDate)=2004
GROUP BY MONTH(OrderDate),TerritoryID,CustomerID
UNION ALL
SELECT MONTH(OrderDate),TerritoryID,NULL,SUM(TotalDue)
FROM Sales.SalesOrderHeader
WHERE SalesPersonID=285 AND YEAR(OrderDate)=2004
GROUP BY MONTH(OrderDate),TerritoryID
UNION ALL
SELECT MONTH(OrderDate),NULL,CustomerID,SUM(TotalDue)
FROM Sales.SalesOrderHeader
WHERE SalesPersonID=285 AND YEAR(OrderDate)=2004
GROUP BY MONTH(OrderDate),CustomerID
UNION ALL
SELECT NULL,NULL,NULL,SUM(TotalDue)
FROM Sales.SalesOrderHeader
WHERE SalesPersonID=285 AND YEAR(OrderDate)=2004
GROUP BY ();

SELECT MONTH(OrderDate),TerritoryID ,CustomerID,SUM(TotalDue)
FROM Sales.SalesOrderHeader
WHERE SalesPersonID=285 AND YEAR(OrderDate)=2004
GROUP BY GROUPING SETS (
(MONTH(OrderDate), TerritoryID,CustomerID),
(MONTH(OrderDate), TerritoryID),
(MONTH(OrderDate), CustomerID),
());

USE tempdb;
CREATE TABLE T (
 a INT,
 b INT,
 x INT );
GO
INSERT INTO T 
VALUES(1,2,10),
(2,2,15),
(1,NULL,20);

SELECT a, b, SUM(x) 
FROM T
GROUP BY GROUPING SETS (a, b, (a, b));

SELECT a, b, SUM(x),GROUPING(a), GROUPING(b)
FROM T
GROUP BY GROUPING SETS (a, b, (a, b));

SELECT a, b, SUM(x),GROUPING_ID(a,b)
FROM T
GROUP BY GROUPING SETS (a, b, (a, b));

USE AdventureWorks2008;
GO
SELECT SalesOrderID, TotalDue, AVG(TotalDue)
FROM Sales.SalesOrderHeader
WHERE CustomerID =29672;

SELECT SalesOrderID, TotalDue, AVG(TotalDue)
FROM Sales.SalesOrderHeader
WHERE CustomerID =29672
GROUP BY SalesOrderID, TotalDue;

SELECT SalesOrderID, TotalDue, AVG(TotalDue) OVER()
FROM Sales.SalesOrderHeader
WHERE CustomerID =29672;

SELECT SalesOrderID, TotalDue, 
MIN(TotalDue) OVER() AS Minimum,
MAX(TotalDue) OVER() AS Maksimum,
AVG(TotalDue) OVER() AS Średnia,
TotalDue - AVG(TotalDue) OVER() AS Różnica
FROM Sales.SalesOrderHeader
WHERE CustomerID =29672;

SELECT DISTINCT ProductCategoryID, COUNT(*)
OVER (PARTITION BY ProductCategoryID) AS LiczbaDuplikatow
FROM Production.ProductSubcategory
ORDER BY LiczbaDuplikatow;

SELECT FirstName, 
ROW_NUMBER() OVER(ORDER BY FirstName),
RANK() OVER(ORDER BY FirstName),
DENSE_RANK() OVER(ORDER BY FirstName),
NTILE(3) OVER (ORDER BY FirstName) 
FROM Person.Person
WHERE FirstName IN ('Tai','Juanita');

SELECT OD.ProductID, YEAR(H.OrderDate) AS Rok, SUM(OD.LineTotal) AS Sprzedaż
FROM Sales.SalesOrderDetail OD JOIN Sales.SalesOrderHeader H 
ON OD.SalesOrderID = H. SalesOrderID
WHERE OD.ProductID IN (723,945,949)
GROUP BY OD.ProductID, YEAR(H.orderdate)
ORDER BY OD.ProductID, YEAR(H.orderdate);

SELECT OD.ProductID, YEAR(H.OrderDate) AS Rok, SUM(OD.LineTotal) AS Sprzedaż
INTO #TabPivot
FROM Sales.SalesOrderDetail OD JOIN Sales.SalesOrderHeader H 
ON OD.SalesOrderID = H. SalesOrderID
WHERE OD.ProductID IN (723,945,949)
GROUP BY OD.ProductID, YEAR(H.orderdate);

SELECT P.ProductID, [2001], [2002],[2003], [2004]
FROM #TabPivot
PIVOT ( 
SUM(Sprzedaż)
FOR Rok IN ([2001], [2002], [2003], [2004]) ) AS P
ORDER BY P.ProductID;

SELECT P.ProductID, [2001], [2002],[2003], [2004]
INTO #TabUnpivot
FROM #TabPivot
PIVOT ( 
SUM(Sprzedaż)
FOR Rok IN ([2001], [2002], [2003], [2004]) ) AS P
ORDER BY P.ProductID;

SELECT Unpiv.ProductID, Unpiv.Rok, Unpiv.Sprzedaż
FROM #TabUnpivot
UNPIVOT (Sprzedaż FOR Rok IN ([2001], [2002], [2003], [2004])) AS Unpiv;

/*
CTE
*/
WITH WiekKobietMezczyzn AS
(SELECT Gender, DATEDIFF(YY, BirthDate, GETDATE()) AS Wiek ,BusinessEntityID
FROM HumanResources.Employee)
SELECT Gender,Wiek, COUNT(BusinessEntityID) AS LiczbaPracowników
FROM WiekKobietMezczyzn
WHERE Wiek>65
GROUP BY Gender, Wiek;

WITH Przedzialy AS 
(SELECT TotalDue, NTILE(5) OVER(ORDER BY TotalDue DESC) AS Sprzedaz
FROM Sales.SalesOrderHeader)
SELECT Sprzedaz, MIN(TotalDue), MAX(TotalDue), AVG(TotalDue)
FROM Przedzialy
WHERE Sprzedaz IN (2,3)
GROUP BY Sprzedaz
ORDER BY Sprzedaz;

WITH Sprzedaz AS
(SELECT CustomerID, TotalDue, YEAR(OrderDate) AS Rok
FROM Sales.SalesOrderHeader),
SprzedazLata AS 
(SELECT CustomerID, Rok, SUM(TotalDue) AS Suma
FROM Sprzedaz
GROUP BY CustomerID, Rok)
SELECT TOP 3 WITH TIES *
FROM SprzedazLata
ORDER BY Suma DESC;

WITH SumaLiczb (Suma, Pierwsza, Ostatnia) AS
(SELECT 0,1,5 -- Początkowa suma, pierwsza i ostatnia liczba
UNION ALL
SELECT Suma+Pierwsza,Pierwsza+1,Ostatnia
FROM SumaLiczb
WHERE Pierwsza<=Ostatnia)
SELECT Suma
FROM SumaLiczb
WHERE Pierwsza = Ostatnia+1;

WITH Generator AS
(SELECT i = 1
UNION All
SELECT i = i + 1 
FROM Generator 
WHERE i < 500 )
SELECT i
FROM Generator
ORDER BY i
OPTION (MAXRECURSION 500);

/*
Instrukcja MERGE
*/
USE tempdb;
CREATE TABLE Osoby (ID INT identity(1,1), Imie VARCHAR(100));
GO
MERGE INTO Osoby
USING (Values ('Danuta',1),('Marcin',2)) Znajomi(Imie,Numer)
ON Osoby.ID = Znajomi.Numer
WHEN NOT MATCHED THEN
INSERT (Imie) VALUES (Znajomi.Imie);
SELECT * FROM Osoby;

MERGE INTO Osoby
USING (Values ('Kris',3),('Tomek',2)) Znajomi(Imie,Numer)
ON Osoby.ID = Znajomi.Numer
WHEN MATCHED THEN 
  UPDATE SET Osoby.Imie = Znajomi.Imie
WHEN NOT MATCHED THEN
  INSERT (Imie) VALUES (Znajomi.Imie);
SELECT * FROM Osoby;

MERGE INTO Osoby
USING (VALUES ('Danuta',1),('Marcin',2)) Znajomi(Imie,Numer)
ON Osoby.ID = Znajomi.Numer
WHEN MATCHED THEN 
UPDATE SET Osoby.Imie = Znajomi.Imie
WHEN NOT MATCHED THEN
INSERT (imie) VALUES (Znajomi.Imie)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
SELECT * FROM Osoby;

CREATE TABLE Akcje
 (Firma VARCHAR(10) PRIMARY KEY, 
  Liczba INT CHECK (Liczba > 0));
CREATE TABLE Zmiany 
(Firma VARCHAR(10) PRIMARY KEY, 
Zmiana INT CHECK (Zmiana <> 0));
CREATE TABLE Sledzenie
(Akcja VARCHAR(6), 
Firma VARCHAR(6), 
Liczba INT);

INSERT Akcje 
VALUES('MSFT', 10), ('FORD', 5);
INSERT Zmiany 
VALUES('MSFT', 5), ('FORD', -5), ('BOEING', 3);

INSERT INTO Sledzenie
SELECT * FROM
(
MERGE Akcje AS A
  USING Zmiany AS Z
  ON A.Firma = Z.Firma
  WHEN MATCHED AND (Liczba + Zmiana = 0) THEN
    DELETE
  WHEN MATCHED THEN 
    UPDATE SET Liczba += Zmiana
  WHEN NOT MATCHED THEN
    INSERT VALUES(Firma, Zmiana)
  OUTPUT $action, Z.Firma, INSERTED.Liczba
) Tab (Akcja, Firma, Liczba);

SELECT * 
FROM Sledzenie;

CREATE TABLE Kontakty 
(id INT, 
Imie VARCHAR(100));
INSERT Kontakty 
VALUES(1, 'Marcin'),(2, 'Damian');

CREATE TABLE NoweOsoby 
(id INT, 
Imie VARCHAR(100));
INSERT NoweOsoby 
VALUES(2, 'Tomek'),(3, 'Jacek');

BEGIN TRANSACTION;
UPDATE K
SET Imie = N.Imie
FROM Kontakty AS K  JOIN NoweOsoby AS N ON (K.id = N.id);

INSERT Kontakty  
SELECT * 
FROM NoweOsoby
WHERE id NOT IN (SELECT id FROM Kontakty);
COMMIT;

SELECT * 
FROM Kontakty;

INSERT NoweOsoby 
VALUES(3, 'Karol');

SELECT * 
FROM NoweOsoby;

MERGE Kontakty AS K
  USING (SELECT * FROM NoweOsoby WHERE id > 1) AS N
  ON K.id = N.id
  WHEN MATCHED THEN
    UPDATE SET K.Imie = N.Imie;

/*
Łączenie wyników zapytań
*/
USE AdventureWorks2008;
SELECT BusinessEntityID, Name
FROM Sales.Store
UNION 
SELECT BusinessEntityID, LastName
FROM Person.Person;

SELECT BusinessEntityID, Name
FROM Sales.Store
INTERSECT 
SELECT BusinessEntityID, LastName
FROM Person.Person;

SELECT BusinessEntityID, Name
FROM Sales.Store
EXCEPT 
SELECT BusinessEntityID, LastName
FROM Person.Person;

