USE AdventureWorks  --wszystkie fragmenty kodu trzeba wykonywać w adventureworks
GO

----------------------------------------------------------------------
-- Rozszerzenia mające wpływ na DML - Unieważnienie przestarzałego stylu złączeń zewnętrznych
----------------------------------------------------------------------

--spowoduje błąd
SELECT	*
FROM   sales.salesPerson as salesPerson,
       sales.salesTerritory as salesTerritory
WHERE  salesperson.territoryId *= salesTerritory.territoryId
go

SELECT *
FROM   sales.salesPerson as salesPerson
           LEFT OUTER JOIN sales.salesTerritory as salesTerritory
               on salesperson.territoryId = salesTerritory.territoryId
go

SELECT
FROM     sales.salesPerson as salesPerson,
         sales.salesTerritory as salesTerritory
WHERE    salesperson.territoryId = salesTerritory.territoryId
go


----------------------------------------------------------------------
-- Rozszerzenia mające wpływ na DML - Wspólne wyrażenia tabelowe
----------------------------------------------------------------------
WITH simpleExample as
(
     SELECT 'cześć' AS columnName
)

SELECT  columnName
FROM    simpleExample
GO
SELECT  columnName
FROM    (SELECT 'cześć' AS columnName) AS simpleExample
GO

--Wersja dla SQL Servera 2000
SELECT cast(c.LastName + ', ' + c.FirstName as varchar(30)) as SalesPerson,
--SPRZEDAŻ OD POCZĄTKU ROKU
       (SELECT amount
        FROM ( SELECT soh.SalesPersonID, sum(sod.LineTotal) as amount
               FROM sales.SalesOrderHeader soh
                     JOIN sales.SalesOrderDetail sod
                     ON sod.SalesOrderID = soh.SalesOrderID
               WHERE soh.Status = 5 -- sprzedaż zakończona
               and soh.OrderDate >= '20040101'
               GROUP by soh.SalesPersonID) as YTDSalesPerson
        where YTDSalesPerson.salesPersonId = salesperson.SalesPersonID) as YTDSales,

--PROCENT CAŁKOWITEJ SPRZEDAŻY
        (SELECT amount
         FROM (SELECT soh.SalesPersonID, sum(sod.LineTotal) as amount
               FROM sales.SalesOrderHeader soh
                     JOIN sales.SalesOrderDetail sod
                     ON sod.SalesOrderID = soh.SalesOrderID
               WHERE soh.Status = 5 -- sprzedaż zakończona
               and soh.OrderDate >= '20040101'
               GROUP by soh.SalesPersonID) as YTDSalesPerson
         where YTDSalesPerson.salesPersonId = salesperson.SalesPersonID) /
             (SELECT sum(amount)
              FROM (SELECT soh.SalesPersonID, sum(sod.LineTotal) as amount
                    FROM sales.SalesOrderHeader soh
                          JOIN sales.SalesOrderDetail sod
                          ON sod.SalesOrderID = soh.SalesOrderID
                    WHERE soh.Status = 5 -- sprzedaż zakończona
                    and soh.OrderDate >= '20040101'
                    GROUP by soh.SalesPersonID) as YTDSalesPerson
             ) as percentOfTotal,

--PORÓWNANIE Z WYZNACZONYM LIMITEM SPRZEDAŻY
        (SELECT amount
         FROM (SELECT soh.SalesPersonID, sum(sod.LineTotal) as amount
               FROM sales.SalesOrderHeader soh
                     JOIN sales.SalesOrderDetail sod
                     ON sod.SalesOrderID = soh.SalesOrderID
               WHERE soh.Status = 5 -- sprzedaż zakończona
               and soh.OrderDate >= '20040101'
               GROUP by soh.SalesPersonID)as YTDSalesPerson
         where YTDSalesPerson.salesPersonId = salesperson.SalesPersonID) -
         salesPerson.SalesQuota as MetQuota
         FROM sales.SalesPerson as salesPerson
              join HumanResources.Employee as e
                   on salesPerson.salesPersonId = e.employeeId
              join Person.Contact as c
                   on c.contactId = e.contactId
GO

-- Składnia CTE dla SQL Servera 2005
WITH YTDSalesPerson
AS
(
    SELECT soh.SalesPersonID, sum(sod.LineTotal) as amount
    FROM sales.SalesOrderHeader soh
         JOIN sales.SalesOrderDetail sod
         ON sod.SalesOrderID = soh.SalesOrderID
    WHERE soh.Status = 5 -- sprzedaż zakończona
      and soh.OrderDate >= '20040101'
    GROUP by soh.SalesPersonID
),
SalesPersonInfo
AS
(
    SELECT salesPersonId, SalesQuota as salesQuota,
           cast(c.LastName + ', ' + c.FirstName as varchar(30)) as SalesPerson
    FROM sales.SalesPerson as s
         JOIN HumanResources.Employee as e
             on s.salesPersonId = e.employeeId
         JOIN Person.Contact as c
             on c.contactId = e.contactId
)
SELECT SalesPersonInfo.SalesPerson,
       (SELECT amount
        FROM YTDSalesPerson
        WHERE YTDSalesPerson.salesPersonId = salespersonInfo.SalesPersonID)
             as YTDSales,
       (SELECT amount
        FROM YTDSalesPerson
        WHERE YTDSalesPerson.salesPersonId = salespersonInfo.SalesPersonID)
        / (SELECT sum(amount) FROM YTDSalesPerson) as percentOfTotal,

        (SELECT amount
         FROM YTDSalesPerson
         WHERE YTDSalesPerson.salesPersonId = salespersonInfo.SalesPersonID) -
         salesPersonInfo.SalesQuota as MetQuota
FROM SalesPersonInfo
Go

WITH YTDSalesPerson
AS
(
    SELECT soh.SalesPersonID, sum(sod.LineTotal) as amount
    FROM sales.SalesOrderHeader soh
         JOIN sales.SalesOrderDetail sod
         ON sod.SalesOrderID = soh.SalesOrderID
    WHERE soh.Status = 5 -- sprzedaż zakończona
      and soh.OrderDate >= '20040101'
    GROUP by soh.SalesPersonID
)
SELECT *
FROM YTDSalesPerson
GO

-- Przykład dla SQL Servera 2000

DECLARE @managerId int
SET @managerId = 140

-- przechowuje wyjściowy poziom drzewa, dzięki czemu w zapytaniu z pętlą można używać danego poziomu drzewa

DECLARE @outTable table (employeeId int, managerId int, treeLevel int)

-- przechowuje numer poziomu w drzewie, w którym aktualnie jest pętla
DECLARE @treeLevel as int
SET @treelevel = 1

-- odczytanie najwyższego poziomu
INSERT INTO @outTable
SELECT employeeId, managerId, @treelevel as treelevel
FROM HumanResources.employee as employee
WHERE (employee.managerId = @managerId)

WHILE (1 = 1) -- imituje konstrukcję do...until
 BEGIN

    INSERT INTO @outTable
    SELECT employee.employeeId, employee.managerId,
           treelevel + 1 as treelevel
    FROM HumanResources.employee as employee
         JOIN @outTable as ht
         ON employee.managerId = ht.employeeId
    -- klauzula WHERE wyodrębnia konkretny poziom w drzewie
    WHERE EXISTS( SELECT *
                  FROM @outTable AS holdTree
                  WHERE treelevel = @treelevel
                    AND employee.managerId = holdtree.employeeId)
    IF @@rowcount = 0
       BEGIN
          BREAK
       END
    SET @treelevel = @treelevel + 1
 END

SELECT Employee.EmployeeID,Contact.LastName,Contact.FirstName
FROM HumanResources.Employee as Employee
     INNER JOIN @outTable ot
         ON Employee.EmployeeID = ot.EmployeeID
     INNER JOIN Person.Contact as Contact
         ON Contact.contactId = Employee.contactId
go

-- wykorzystanie składni SQL Servera 2005
DECLARE @managerId int
SET @managerId = 140;

WITH EmployeeHierarchy (EmployeeId, ManagerId)
AS
(
    SELECT EmployeeID, ManagerID
    FROM HumanResources.Employee as Employee
    WHERE ManagerID=@managerId

    UNION ALL

    SELECT Employee.EmployeeID, Employee.ManagerID
    FROM HumanResources.Employee as Employee
         INNER JOIN EmployeeHierarchy
             on Employee.ManagerID= EmployeeHierarchy.EmployeeID)

SELECT Employee.EmployeeID,Contact.LastName,Contact.FirstName
FROM HumanResources.Employee as Employee
     INNER JOIN EmployeeHierarchy
         ON Employee.EmployeeID = EmployeeHierarchy.EmployeeID
     INNER JOIN Person.Contact as Contact
         ON Contact.contactId = Employee.contactId
GO

--rozszerzona wersja zapytania CTE
DECLARE @managerId int
SET @managerId = 140;
WITH EmployeeHierarchy(EmployeeID, ManagerID, treelevel, hierarchy)
AS
(
     SELECT EmployeeID, ManagerID,
            1 as treelevel, CAST(EmployeeId as varchar(max)) as hierarchy
     FROM HumanResources.Employee as Employee
     WHERE ManagerID=@managerId

     UNION ALL

     SELECT Employee.EmployeeID, Employee.ManagerID,
            treelevel + 1 as treelevel,
            hierarchy + '\' +cast(Employee.EmployeeId as varchar(20)) as hierarchy
     FROM HumanResources.Employee as Employee
          INNER JOIN EmployeeHierarchy
              on Employee.ManagerID= EmployeeHierarchy.EmployeeID
)

SELECT Employee.EmployeeID,Contact.LastName,Contact.FirstName,
       EmployeeHierarchy.treelevel, EmployeeHierarchy.hierarchy
FROM HumanResources.Employee as Employee
       INNER JOIN EmployeeHierarchy
           ON Employee.EmployeeID = EmployeeHierarchy.EmployeeID
       INNER JOIN Person.Contact as Contact
           ON Contact.contactId = employee.contactId
ORDER BY hierarchy
go

----------------------------------------------------------------------
-- Rozszerzenia mające wpływ na DML - Operator TOP
----------------------------------------------------------------------

DECLARE @rowsToReturn int

SELECT @rowsToReturn = 10

SELECT TOP(@rowsToReturn) * --TOP wymaga podawania parametrów w nawiasach
                            --nie dotyczy to natomiast wartości stałych
FROM HumanResources.Employee
GO

CREATE TABLE testTop
(
       value int primary Key
)

INSERT TOP (5) into testTop
SELECT * --tabela pochodna zwróci siedem wierszy
FROM (SELECT 1 as value union SELECT 2 union SELECT 3 union SELECT 4
      union SELECT 5 union SELECT 6 union SELECT 7) as sevenRows
go
SELECT *
FROM testTop
go

UPDATE TOP (2) testTop
SET value = value * 100

SELECT *
FROM testTop
GO

DELETE TOP(3) testTop
go

select * from testTop
go

/* instrukcja nie dotyczy żadnej konkretnej tabeli
INSERT TOP(10) otherTable (batchTableId, value)
SELECT batchTableId, value
FROM   batchTable
WHERE  not exists ( SELECT *
                    FROM   otherTable
                    WHERE  otherTable.batchTableId = batchTable.batchTableId)
*/

create table top10sales
(
 salesOrderId int,
 totalDue  money
)

insert  TOP (10) top10sales
SELECT salesOrderId, totalDue
FROM sales.salesOrderHeader
ORDER BY totalDue desc
go

--dodane
truncate table top10sales
go
INSERT top10sales
SELECT TOP (10) salesOrderId, totalDue
FROM   sales.salesOrderHeader
ORDER     BY totalDue desc
GO
BEGIN TRANSACTION
DECLARE @rowcount int
SET @rowcount = 100
WHILE (@rowcount = 100) --jeśli mniejsze niż 100 operacja zakończy się, jeśli większe
  BEGIN --nie zdarzy się
      DELETE TOP(100) sales.salesOrderHeader
      SET @rowcount = @@rowcount
  END
ROLLBACK TRANSACTION --tak naprawdę nie chcemy usuwać tych wierszy
GO

----------------------------------------------------------------------
-- Rozszerzenia mające wpływ na DML - Rozszerzenia klauzuli FROM
----------------------------------------------------------------------

/* powoduje błąd
SELECT Product.productNumber, SalesOrderAverage.averageTotal
FROM   Production.Product as Product
          JOIN       (     SELECT AVG(lineTotal) as averageTotal
                            FROM Sales.SalesOrderDetail as SalesOrderDetail
                            WHERE product.ProductID=SalesOrderDetail.ProductID
                            HAVING COUNT(*) > 1
                            ) as SalesOrderAverage

SELECT Product.productNumber, SalesOrderAverage.averageTotal
FROM   Production.Product as Product
         JOIN dbo.getAverageTotalPerProduct(product.productId)
                                                            as SalesOrderAverage

*/

--Cross Apply
SELECT Product.productNumber, SalesOrderAverage.averageTotal
FROM   Production.Product as Product
          CROSS APPLY (     SELECT AVG(lineTotal) as averageTotal
                            FROM Sales.SalesOrderDetail as SalesOrderDetail
                            WHERE product.ProductID=SalesOrderDetail.ProductID
                            HAVING COUNT(*) > 0
                            ) as SalesOrderAverage
go

CREATE FUNCTION production.getAverageTotalPerProduct
(
     @productId int
)
RETURNS @output TABLE (unitPrice decimal(10,4))
AS
     BEGIN
          INSERT  INTO @output (unitPrice)
          SELECT AVG(lineTotal) as averageTotal
          FROM Sales.SalesOrderDetail as SalesOrderDetail
          WHERE SalesOrderDetail.ProductID = @productId
          HAVING COUNT(*) > 0

          RETURN
     END
go

SELECT Product.ProductNumber, AverageSale.UnitPrice
FROM   Production.Product as Product
            CROSS APPLY
                      production.getAverageTotalPerProduct(product.productId)
                                                                      as AverageSale
go

SELECT Product.ProductNumber, AverageSale.UnitPrice
FROM   Production.Product as Product
            OUTER APPLY
                      production.getAverageTotalPerProduct(product.productId)
                                                                      as AverageSale
GO

----------------------------------------------------------------------
-- Rozszerzenia mające wpływ na DML - Losowe próbkowanie danych
----------------------------------------------------------------------

SELECT  *
FROM    sales.salesOrderDetail TABLESAMPLE SYSTEM (2 PERCENT)
go
SELECT  *
FROM    sales.salesOrderDetail TABLESAMPLE SYSTEM (500 rows)
go
SELECT  *
FROM    sales.salesOrderDetail TABLESAMPLE SYSTEM (500 rows) REPEATABLE (123456)
go

----------------------------------------------------------------------
-- Rozszerzenia mające wpływ na DML - Obracanie danych
----------------------------------------------------------------------

CREATE TABLE sales.salesByMonth
(
     year char(4),
     month char(3),
     amount money,
     PRIMARY KEY (year, month)
)
INSERT INTO sales.salesByMonth (year, month, amount)
VALUES('2004','Jan',   789.0000)
INSERT INTO sales.salesByMonth (year, month, amount)
VALUES('2004','Feb',   389.0000)
INSERT INTO sales.salesByMonth (year, month, amount)
VALUES('2004','Mar',   8867.0000)
INSERT INTO sales.salesByMonth (year, month, amount)
VALUES('2004','Apr',   778.0000)
INSERT INTO sales.salesByMonth (year, month, amount)
VALUES('2004','May',   78.0000)
INSERT INTO sales.salesByMonth (year, month, amount)
VALUES('2004','Jun',   9.0000)
INSERT INTO sales.salesByMonth (year, month, amount)
VALUES('2004','Jul',   987.0000)
INSERT INTO sales.salesByMonth (year, month, amount)
VALUES('2004','Aug',   866.0000)
INSERT INTO sales.salesByMonth (year, month, amount)
VALUES('2004','Sep',   7787.0000)
INSERT INTO sales.salesByMonth (year, month, amount)
VALUES('2004','Oct',   85576.0000)
INSERT INTO sales.salesByMonth (year, month, amount)
VALUES('2004','Nov',   855.0000)
INSERT INTO sales.salesByMonth (year, month, amount)
VALUES('2004','Dec',   5878.0000)

INSERT INTO sales.salesByMonth (year, month, amount)
VALUES('2005','Jan',   7.0000)
INSERT INTO sales.salesByMonth (year, month, amount)
VALUES('2005','Feb',   6868.0000)
INSERT INTO sales.salesByMonth (year, month, amount)
VALUES('2005','Mar',   688.0000)
INSERT INTO sales.salesByMonth (year, month, amount)
VALUES('2005','Apr',   9897.0000)
go

SELECT year,
       SUM(case when month = 'Jan' then amount else 0 end) AS 'Jan',
       SUM(case when month = 'Feb' then amount else 0 end) AS 'Feb',
       SUM(case when month = 'Mar' then amount else 0 end) AS 'Mar',
       SUM(case when month = 'Apr' then amount else 0 end) AS 'Apr',
       SUM(case when month = 'May' then amount else 0 end) AS 'May',
       SUM(case when month = 'Jun' then amount else 0 end) AS 'Jun',
       SUM(case when month = 'Jul' then amount else 0 end) AS 'Jul',
       SUM(case when month = 'Aug' then amount else 0 end) AS 'Aug',
       SUM(case when month = 'Sep' then amount else 0 end) AS 'Sep',
       SUM(case when month = 'Oct' then amount else 0 end) AS 'Oct',
       SUM(case when month = 'Nov' then amount else 0 end) AS 'Nov',
       SUM(case when month = 'Dec' then amount else 0 end) AS 'Dec'
FROM   sales.salesByMonth
GROUP  by year
go
SELECT Year,[Jan],[Feb],[Mar],[Apr],[May],[Jun],
            [Jul],[Aug],[Sep],[Oct],[Nov],[Dec]
FROM (
     SELECT year, amount, month
     FROM       sales.salesByMonth ) AS salesByMonth
     PIVOT  ( SUM(amount) FOR month IN
               ([Jan],[Feb],[Mar],[Apr],[May],[Jun],
                [Jul],[Aug],[Sep],[Oct],[Nov],[Dec])
     ) AS ourPivot
ORDER BY Year
go
SELECT [Jan],[Feb],[Mar],[Apr],[May],[Jun],
        [Jul],[Aug],[Sep],[Oct],[Nov],[Dec]
FROM (      SELECT amount, month
            FROM   sales.salesByMonth ) AS salesByMonth
     PIVOT  ( SUM(amount) FOR month IN
               ([Jan],[Feb],[Mar],[Apr],[May],[Jun],
                [Jul],[Aug],[Sep],[Oct],[Nov],[Dec])
     ) AS ourPivot
go

CREATE TABLE Person.ContactProperty
(
     ContactPropertyId int identity(1,1) PRIMARY KEY,
     ContactId      int REFERENCES Person.Contact(ContactId),
     PropertyName  varchar(20),
     PropertyValue sql_variant,
     UNIQUE (ContactID, PropertyName)
)

INSERT Person.ContactProperty (ContactId, PropertyName, PropertyValue)
VALUES(1,'imię psa','Fido')
INSERT Person.ContactProperty (ContactId, PropertyName, PropertyValue)
VALUES(1,'kolor sierści','brązowa')
INSERT Person.ContactProperty (ContactId, PropertyName, PropertyValue)
VALUES(1,'rodzaj samochodu','sedan')

INSERT Person.ContactProperty (ContactId, PropertyName, PropertyValue)
VALUES(2,'imię psa','Rufus')
INSERT Person.ContactProperty (ContactId, PropertyName, PropertyValue)
VALUES(2, 'kolor sierści','kremowy')

INSERT Person.ContactProperty (ContactId, PropertyName, PropertyValue)
VALUES(3,'imię psa','Einstein')
INSERT Person.ContactProperty (ContactId, PropertyName, PropertyValue)
VALUES(3,'rodzaj samochodu','coupe')
go

SELECT  cast(Contact.FirstName + ' ' + Contact.LastName as varchar(30)) as Name,
        ContactProperty.PropertyName, ContactProperty.PropertyValue
FROM    Person.Contact as Contact
          INNER JOIN Person.ContactProperty as ContactProperty
               ON  ContactProperty.ContactId = Contact.ContactID
go

SELECT cast(Contact.FirstName + ' ' + Contact.LastName as varchar(30)) as Name,
       pivotColumns.* --* zadziała, lecz nie powinno się go stosować
                      --w kodzie produkcyjnym
FROM Person.Contact as Contact
     INNER JOIN (SELECT ContactId, PropertyName,PropertyValue
         FROM Person.ContactProperty as property) as PivotTable
     PIVOT( MAX(PropertyValue)
            FOR PropertyName IN ([imię psa],[kolor sierści],[rodzaj samochodu])) AS PivotColumns
    ON Contact.ContactId = PivotColumns.ContactId
go

----------------------------------------------------------------------
-- Rozszerzenia mające wpływ na DML - UNPIVOT
----------------------------------------------------------------------

SELECT Year,[Jan],[Feb],[Mar],[Apr],[May],[Jun],
           [Jul],[Aug],[Sep],[Oct],[Nov],[Dec]
INTO  sales.salesByYear
FROM (
     SELECT year, amount, month
     FROM   sales.salesByMonth ) AS salesByMonth
     PIVOT  ( SUM(amount) FOR month IN
               ([Jan],[Feb],[Mar],[Apr],[May],[Jun],
                [Jul],[Aug],[Sep],[Oct],[Nov],[Dec])
     ) AS ourPivot
ORDER BY Year
go

SELECT  Year, cast(Month as char(3)) as Month, Amount
FROM    sales.salesByYear
            UNPIVOT (Amount FOR Month IN
               ([Jan],[Feb],[Mar],[Apr],[May],[Jun],
                [Jul],[Aug],[Sep],[Oct],[Nov],[Dec])) as unPivoted
go

----------------------------------------------------------------------
-- Rozszerzenia mające wpływ na DML - Output
----------------------------------------------------------------------

BEGIN TRANSACTION

DECLARE @changes table (change varchar(2000))

UPDATE TOP (10) Person.Contact
SET    FirstName = Reverse(FirstName)
OUTPUT 'Was: ''' + DELETED.FirstName +
                ''' Is: ''' + INSERTED.FirstName + ''''
INTO   @changes

SELECT *
FROM   @changes

ROLLBACK TRANSACTION

----------------------------------------------------------------------
-- Rozszerzenia mające wpływ na DML - Funkcje rankingowe
----------------------------------------------------------------------
CREATE VIEW contactSubset
as
     Select  TOP 20 *
     FROM    Person.Contact
     WHERE   FirstName like 'b%'
go

SELECT  FirstName,
          (    SELECT count(*)
               FROM   contactSubset as c
               WHERE  c.FirstName < contactSubset.FirstName) + 1 as RANK
FROM    contactSubset
ORDER   BY FirstName
go

SELECT   FirstName, LastName,
           (   SELECT count(*)
               FROM   contactSubset as c
               WHERE  c.LastName < contactSubset.LastName
                  OR  (c.LastName = contactSubset.LastName
                       AND c.FirstName< contactSubset.FirstName)
                    ) + 1 as orderNumber
FROM     contactSubset
ORDER    BY LastName, FirstName
GO
SELECT  FirstName,
        ROW_NUMBER() over (order by FirstName) as 'ROW_NUMBER',
        RANK() over (order by FirstName) as 'RANK',
        DENSE_RANK() over (order by FirstName) as 'DENSE_RANK',
        NTILE(4) over (order by FirstName) as 'NTILE(4)'
FROM    contactSubSet
ORDER   BY FirstName
GO
SELECT    FirstName, LastName,
          ROW_NUMBER() over (order by FirstName, LastName) as 'ROW_NUMBER',
          RANK() over (order by FirstName,LastName) as 'RANK',
          DENSE_RANK() over (order by FirstName,LastName) as 'DENSE_RANK',
          NTILE(4) over (order by FirstName,LastName) as 'NTILE(4)'
FROM      contactSubSet
ORDER     BY FirstName
GO
SELECT    firstName,
          ROW_NUMBER() over (order by FirstName) as ascending,
          ROW_NUMBER() over (order by FirstName desc) as descending
FROM      contactSubSet
ORDER     BY FirstName
GO
SELECT    FirstName, LastName,
          ROW_NUMBER()over ( partition by FirstName order by lastName) as
                                                               'ROW_NUMBER',
          RANK()over ( partition by FirstName order by LastName) as 'RANK',
          DENSE_RANK()over ( partition by FirstName order by LastName) as
                                                               'DENSE_RANK',
          NTILE(2) over ( partition by FirstName order by LastName) as
                                                                   'NTILE(2)'
FROM      contactSubSet
ORDER     BY FirstName
GO
WITH salesSubset AS
(
     SELECT  product.name as product,  sum(salesOrderDetail.lineTotal) as total
     FROM    sales.salesOrderDetail  as salesOrderDetail
			   JOIN sales.salesOrderHeader as salesOrderHeader
                    ON salesOrderHeader.salesOrderId = salesOrderDetail.salesOrderId
			   JOIN production.product as product
				ON product.productId = salesOrderDetail.productId
	 WHERE   orderDate >= '1/1/2004' and orderDate < '1/1/2005'
	 GROUP   BY product.name
)
SELECT	*
FROM    (SELECT  product, total,
		        RANK() over (order by total desc) as 'RANK',
		        NTILE(10) over (order by total desc) as 'NTILE(10)'
		FROM    salesSubset) as productOrders
WHERE	[NTILE(10)] = 10
gO

----------------------------------------------------------------------
-- Rozszerzenia mające wpływ na DML - EXCEPT i INTERSECT
----------------------------------------------------------------------
CREATE TABLE projectPerson
(
	personId     VARCHAR(10),
	projectId    VARCHAR(10),
	PRIMARY KEY (personId, projectId)
)
go

INSERT INTO projectPerson VALUES ('janb','projDuży')
INSERT INTO projectPerson VALUES ('janb','projMały')
INSERT INTO projectPerson VALUES ('alfredf','projDuży')
INSERT INTO projectPerson VALUES ('henrykr','projMały')
INSERT INTO projectPerson VALUES ('stefangr','projDuży')
INSERT INTO projectPerson VALUES ('stefangr','projMały')
go

SELECT personId
FROM projectPerson
WHERE projectId = 'projDuży'
UNION
SELECT personId
FROM projectPerson
WHERE projectId = 'projMały'
GO

--uczestnicy projBig, lecz nie projLittle
SELECT personId
FROM projectPerson as projLittle
WHERE projectId = 'projMały'
    AND NOT EXISTS ( SELECT *
                     FROM projectPerson as projBig
                     WHERE projBig.projectId = 'projDuży'
                       and projBig.personId = projLittle.personId)
GO

--uczestnicy projBig, lecz nie projLittle
SELECT personId
FROM projectPerson
WHERE projectId = 'projMały'
EXCEPT
SELECT personId
FROM projectPerson
WHERE projectId = 'projDuży'
go

--uczestnicy obydwu projektów
SELECT personId
FROM projectPerson
WHERE projectId = 'projDuży'
INTERSECT
SELECT personId
FROM projectPerson
WHERE projectId = 'projMały'
go

----------------------------------------------------------------------
-- Rozszerzenia mające wpływ na DML - Synonimy
----------------------------------------------------------------------
CREATE SYNONYM Emp FOR AdventureWorks.HumanResources.Employee
go
SELECT * from Emp
go
DROP SYNONYM Emp
go
CREATE LOGIN ANDREW WITH PASSWORD = 'ANDRZEJ1'
CREATE USER ANDREW FOR LOGIN ANDREW
go
CREATE SYNONYM synSecure FOR sales.customer
GO
CREATE VIEW viewSecure --jako przykład przeciwny
AS
SELECT *
FROM sales.customer
GO
GRANT SELECT,INSERT,UPDATE,DELETE  ON synSecure to ANDREW
GRANT SELECT,INSERT,UPDATE,DELETE ON viewSecure to ANDREW
go
EXECUTE AS LOGIN='ANDRZEJ'
--zwróci błąd uprawnień
SELECT * from sales.customer
go
SELECT * from viewSecure
SELECT * from synSecure
go
revert --not in text, reverts back to security context of primary user

----------------------------------------------------------------------
-- Ogólne programowanie - Obsługa błędów
----------------------------------------------------------------------
CREATE SCHEMA Entertainment
    CREATE TABLE TV
	(	
		TVid int primary key,
		location varchar(20),
		diagonalWidth int
                CONSTRAINT CKEntertainment_tv_checkWidth CHECK (diagonalWidth >= 30)
	)

go

CREATE TABLE dbo.error_log --(adventureworks posiada już swą tabelę rejestracji błędów)
(	
     tableName sysname,
     userName sysname,
     errorNumber int,
     errorSeverity int,
     errorState int,
     errorMessage varchar(4000)
)
GO

CREATE PROCEDURE entertainment.tv$insert
(
        @TVid             int,    
        @location         varchar(30),
	 @diagonalWidth    int
)
AS
declare @Error int

    BEGIN TRANSACTION

    --wstawienie wiersza
    INSERT entertainment.TV (TVid, location, diagonalWidth)
    VALUES(@TVid, @location, @diagonalWidth)
    --zapisanie @@ERROR, aby nie stracić informacji
    SET @Error=@@ERROR
    IF @Error<>0
        BEGIN
            -- wystąpił błąd
            GOTO ErrorHandler
        END
    COMMIT TRANSACTION

GOTO ExitProc

ErrorHandler:
    -- wycofanie transakcji
    ROLLBACK TRANSACTION
    -- zapisanie błędu w tabeli error_log
    INSERT dbo.error_log (tableName, userName,
                          errorNumber, errorSeverity ,errorState ,errorMessage)
    VALUES('TV',suser_sname(),@Error,0,0,'Komunikat nieznany!')
ExitProc:
GO

exec entertainment.tv$insert @TVid = 1, @location = 'Sypialnia', @diagonalWidth = 29
go
SELECT * FROM dbo.error_log
go
BEGIN TRY
     RAISERROR ('Czegoś tu brakuje',16,1)
END TRY
BEGIN CATCH
     select ERROR_NUMBER() as ERROR_NUMBER,
            ERROR_SEVERITY() as ERROR_SEVERITY,
            ERROR_STATE() as ERROR_STATE,
            ERROR_MESSAGE() as ERROR_MESSAGE
END CATCH
go
DELETE FROM entertainment.TV --na wypadek, gdyby dodano jakieś wiersze
DELETE FROM dbo.error_log
go

ALTER PROCEDURE entertainment.tv$insert
(
     @TVid          int,
     @location	      varchar(30),
     @diagonalWidth int
)
AS
    SET NOCOUNT ON
	BEGIN TRY
		   BEGIN TRANSACTION
		   INSERT TV (TVid, location, diagonalWidth)
                  VALUES(@TVid, @location, @diagonalWidth)
		   COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		    ROLLBACK TRANSACTION
            INSERT dbo.error_log (tableName, userName, 
                                 errorNumber, errorSeverity ,errorState ,
                                 errorMessage)
            VALUES('TV',suser_sname(),ERROR_NUMBER(),
                   ERROR_SEVERITY(), ERROR_STATE(), ERROR_MESSAGE())
            RAISERROR ('Błąd w trakcie tworzenia wiersza dla nowego zestawu TV',16,1)
	END CATCH
GO

exec entertainment.tv$insert @TVid = 1, @location = 'Sypialnia',
                             @diagonalWidth = 29
GO
SELECT * FROM dbo.error_log
GO

CREATE PROCEDURE dbo.raise_an_error
AS
    BEGIN
        BEGIN TRY
            raiserror ('Bum, bum, bum, bum',16,1)
        END TRY
        BEGIN CATCH --przechwycenie, zwrócenie w ramach select
                    --i wywołanie kolejnego błędu
            SELECT ERROR_NUMBER() AS ErrorNumber,
                   ERROR_SEVERITY() AS ErrorSeverity,
                   ERROR_STATE() as ErrorState, ERROR_LINE() as ErrorLine,
                   ERROR_PROCEDURE() as ErrorProcedure,
                   ERROR_MESSAGE() as ErrorMessage
                   RAISERROR ('Błąd w procedurze raise_an_error',16,1)
            END CATCH
    END
go
SET NOCOUNT ON
BEGIN TRY
    exec raise_an_error --@no_parm = 1 (odkomentujemy to dla celów testowych)
    select 'Nigdy tu nie dotrę'
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity,
           ERROR_STATE() as ErrorState, ERROR_LINE() as ErrorLine,
           Ecast(ERROR_PROCEDURE() as varchar(30)) as ErrorProcedure,
           cast(ERROR_MESSAGE() as varchar(40))as ErrorMessage
END CATCH
go
SET NOCOUNT ON
BEGIN TRY
    exec raise_an_error @no_parm = 1
	select 'cześć'
END TRY
BEGIN CATCH
END CATCH
go
SET NOCOUNT ON
BEGIN TRY
    exeec procedure --to jest celowy błąd!

END TRY
BEGIN CATCH
END CATCH
go
SET NOCOUNT ON
BEGIN TRY
    exec ('seeelect *')

END TRY
BEGIN CATCH
      SELECT  ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity,
              ERROR_STATE() as ErrorState, ERROR_LINE() as ErrorLine,
              cast(ERROR_PROCEDURE() as varchar(60)) as ErrorProcedure,
              cast(ERROR_MESSAGE() as varchar(550))as ErrorMessage
END CATCH
go

ALTER PROCEDURE entertainment.tv$insert
(
        @TVid                int,
        @location            varchar(30),
        @diagonalWidth       int
)
AS
    SET NOCOUNT ON
    DECLARE @errorMessage varchar(2000)
    BEGIN TRY
        BEGIN TRANSACTION
        SET @errorMessage = 'Błąd w trakcie wstawiania TV z diagonalWidth / 1'
        INSERT TV (TVid, location, diagonalWidth)
        VALUES(@TVid, @location, @diagonalWidth)
        --druga instrukcja insert:
        SET @errorMessage = 'Błąd w trakcie wstawiania TV z diagonalWidth / 2'
        INSERT TV (TVid, location, diagonalWidth)
        VALUES(@TVid, @location, @diagonalWidth / 2 )

        COMMIT TRANSACTION

       END TRY
       BEGIN CATCH
                 ROLLBACK TRANSACTION
                 INSERT dbo.error_log VALUES('TV',suser_sname(),
                        ERROR_NUMBER(),ERROR_SEVERITY(),
                        ERROR_STATE(), ERROR_MESSAGE())
                 RAISERROR (@errorMessage,16,1)
       END CATCH
GO

exec entertainment.tv$insert @TVid = 10, @location = 'Sypialnia',
                             @diagonalWidth = 29

exec entertainment.tv$insert @TVid = 11, @location = 'Sypialnia',
                             @diagonalWidth = 60

----------------------------------------------------------------------
-- Ogólne programowanie - Rozszerzenie .WRITE instrukcji UPDATE
----------------------------------------------------------------------

create table testBIGtext
(
    testBIGtextId  int PRIMARY KEY,
    value          varchar(max)
)
insert into testBIGtext
values(1,'')
go

DECLARE @offset int
SET @offset = 0
WHILE @offset < 26
    BEGIN
        UPDATE testBIGtext
           --Wypisywany tekst zaczyna się od litery --> char(97)
           --i jest zwiększany przez dodanie wartości przesunięcia do char(97) = a
           --char (98) = b. Jest to również przesunięcie w kolumnie varchar(max).
           --Dalej wykonywane jest mnożenie przez długość zapisanych danych, by uzyskać
           --wzorzec postaci aaabbbccc...zzz z tysiącem wystąpień każdej litery
       SET value.write(replicate(char(97 + @offset),1000),@offset*1000, 1000)
       WHERE testBIGTextId = 1

       SET @offset = @offset + 1
    END
go
select testBIGtextId, len(value) as CharLength
from  testBIGtext
go

----------------------------------------------------------------------
-- Ogólne programowanie - EXECUTE
----------------------------------------------------------------------

--jeśli SQL Server nie działa jako instancja domyślna, konieczne może być
--zastąpienie localhost nazwą instancji używanego serwera
EXECUTE sp_addlinkedserver @server='LocalLinkedServer', @srvproduct='',
@provider='SQLOLEDB', @datasrc='localhost'
--udostępnienie podłączonego serwera, by umożliwić zdalne wywoływanie procedur
EXECUTE sp_serveroption 'LocalLinkedServer','RPC OUT',True
go
EXECUTE('SELECT * FROM AdventureWorks.Production.Culture') AT LocalLinkedServer

EXECUTE sp_dropserver LocalLinkedServer
----------------------------------------------------------------------
-- Ogólne programowanie - Kontekst zabezpieczeń kodu
----------------------------------------------------------------------

--ten użytkownik będzie właścicielem głównego schematu
CREATE LOGIN mainOwner WITH PASSWORD = 'mainOwnery'
CREATE USER mainOwner FOR LOGIN mainOwner
GRANT CREATE SCHEMA to mainOwner
GRANT CREATE TABLE to mainOwner
--ten użytkownik będzie twórcą procedury
CREATE LOGIN secondaryOwner WITH PASSWORD = 'secondaryOwnery'
CREATE USER secondaryOwner FOR LOGIN secondaryOwner
GRANT CREATE SCHEMA to secondaryOwner
GRANT CREATE PROCEDURE to secondaryOwner
GRANT CREATE TABLE to secondaryOwner
--zwykły użytkownik, który potrzebuje dostępu do danych
CREATE LOGIN JanKowalski WITH PASSWORD = 'JanKowalski'
CREATE USER JanKowalski FOR LOGIN JanKowalski
go
EXECUTE AS USER='mainOwner'
Go
CREATE SCHEMA mainOwnersSchema
GO
CREATE TABLE  mainOwnersSchema.person
(
    personId    int constraint PKtestAccess_person primary key,
    firstName   varchar(20),
    lastName    varchar(20)
)
GO

INSERT INTO mainOwnersSchema.person
VALUES (1, 'Paul','McCartney')
INSERT INTO mainOwnersSchema.person
VALUES (2, 'Ozzy','Osborne')
GO
GRANT SELECT on mainOwnersSchema.person to secondaryOwner
REVERT --można by wrócić do stosu kontekstów głównych,
       --lecz nie można zmienić kontekstu bezpośrednio na secondaryOwner
go
EXECUTE AS USER = 'secondaryOwner'
go

CREATE SCHEMA secondaryOwnerSchema
GO
CREATE TABLE secondaryOwnerSchema.otherPerson
(
    personId    int constraint PKtestAccess_person primary key,
    firstName   varchar(20),
    lastName    varchar(20)
)
GO

INSERT INTO secondaryOwnerSchema.otherPerson
VALUES (1, 'Rocky','Balboa')
INSERT INTO secondaryOwnerSchema.otherPerson
VALUES (2, 'John','Rambo')
GO
CREATE PROCEDURE  secondaryOwnerSchema.person$asCaller
WITH EXECUTE AS CALLER --wartość domyślna
AS
FROM secondaryOwnerSchema.otherPerson --<-- własność taka sama jak procedury
SELECT personId, firstName, lastName
FROM mainOwnersSchema.person --<-- przerwanie łańcucha własności
GO

CREATE PROCEDURE secondaryOwnerSchema.person$asSelf
WITH EXECUTE AS SELF --procedura działa w kontekście secondaryOwner,
                     --ponieważ ten użytkownik ją utworzył
AS
SELECT personId, firstName, lastName
FROM secondaryOwnerSchema.otherPerson --<-- własność taka sama jak procedury

SELECT personId, firstName, lastName
FROM mainOwnersSchema.person --<-- przerwanie łańcucha własności
GO

GRANT EXECUTE ON secondaryOwnerSchema.person$asCaller to JanKowalski
GRANT EXECUTE ON secondaryOwnerSchema.person$asSelf to JanKowalski
go
REVERT
GO
EXECUTE AS USER = 'JanKowalski'
GO
--procedura zostanie wykonana w kontekście CALLER, czyli użytkownika JanKowalski
execute secondaryOwnerSchema.person$asCaller
go
--secondaryOwner, więc zadziała prawidłowo
execute secondaryOwnerSchema.person$asSelf
go