USE AdventureWorks
GO

SELECT JobTitle, count(*)
FROM HumanResources.Employee
GROUP BY JobTitle
GO

SELECT BusinessEntityID, JobTitle, count(*)
FROM HumanResources.Employee
GROUP BY JobTitle
GO

SELECT BusinessEntityID, JobTitle, count(*)
FROM HumanResources.Employee
GROUP BY BusinessEntityID, JobTitle
GO

SELECT b.BusinessEntityID, b.JobTitle, a.numtitles
FROM (SELECT JobTitle, count(*) numtitles
        FROM HumanResources.Employee
        GROUP BY JobTitle) a INNER JOIN HumanResources.Employee b ON a.JobTitle = b.JobTitle
GO

SELECT SalesOrderID, SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail sod
GROUP BY SalesOrderID
ORDER BY SalesOrderID
GO

SELECT Color, COUNT(*)
FROM Production.Product
GROUP BY Color
GO

SELECT CustomerID, SalesPersonID, TerritoryID, YEAR(OrderDate) OrderYear, 
MONTH(OrderDate) OrderMonth, SUM(TotalDue) Total
FROM Sales.SalesOrderHeader
GROUP BY CustomerID, SalesPersonID, TerritoryID, YEAR(OrderDate), MONTH(OrderDate)
WITH CUBE
GO

SELECT CustomerID, SalesPersonID, TerritoryID, YEAR(OrderDate) OrderYear, MONTH(OrderDate) OrderMonth, SUM(TotalDue) Total,
    GROUPING(SalesPersonID) SalesPersonGroup
FROM Sales.SalesOrderHeader
GROUP BY CustomerID, SalesPersonID, TerritoryID, YEAR(OrderDate), MONTH(OrderDate)
WITH CUBE
GO

--ROLLUP
SELECT CustomerID, SalesPersonID, TerritoryID, YEAR(OrderDate) OrderYear, 
MONTH(OrderDate) OrderMonth, SUM(TotalDue) Total
FROM Sales.SalesOrderHeader
GROUP BY CustomerID, SalesPersonID, TerritoryID, YEAR(OrderDate), MONTH(OrderDate)
WITH ROLLUP

SELECT CustomerID, SalesPersonID, TerritoryID, YEAR(OrderDate) OrderYear, 
MONTH(OrderDate) OrderMonth, SUM(TotalDue) Total, GROUPING(YEAR(OrderDate)) YearGroup
FROM Sales.SalesOrderHeader
GROUP BY CustomerID, SalesPersonID, TerritoryID, YEAR(OrderDate), MONTH(OrderDate)
WITH ROLLUP
GO

SELECT S.Name StoreName, ST.Name TerritoryName, 
ST.CountryRegionCode, ST.[Group],
P.FirstName + ' ' + P.LastName SalesPerson, SUM(SH.TotalDue) SalesTotal
FROM Sales.Customer C
    INNER JOIN Sales.Store S
        ON C.StoreID  = S.BusinessEntityID 
    INNER JOIN Sales.SalesTerritory ST ON C.TerritoryID = ST.TerritoryID
    INNER JOIN Sales.SalesPerson SP ON S.SalesPersonID = SP.BusinessEntityID
    INNER JOIN Person.Person P ON S.SalesPersonID = P.BusinessEntityID
    INNER JOIN Sales.SalesOrderHeader SH ON C.CustomerID = SH.CustomerID
GROUP BY GROUPING SETS((P.FirstName + ' ' + P.LastName),(S.Name),(ST.Name), (ST.Name,ST.CountryRegionCode), (ST.[Group]))
GO

SELECT P.FirstName + ' ' + P.LastName SalesPerson, SUM(SH.TotalDue) SalesTotal
FROM Sales.Customer C
    INNER JOIN Sales.Store S
        ON C.StoreID  = S.BusinessEntityID 
    INNER JOIN Sales.SalesTerritory ST ON C.TerritoryID = ST.TerritoryID
    INNER JOIN Sales.SalesPerson SP ON S.SalesPersonID = SP.BusinessEntityID
    INNER JOIN Person.Person P ON S.SalesPersonID = P.BusinessEntityID
    INNER JOIN Sales.SalesOrderHeader SH ON C.CustomerID = SH.CustomerID
GROUP BY P.FirstName + ' ' + P.LastName
GO

SELECT S.Name StoreName, SUM(SH.TotalDue) SalesTotal
FROM Sales.Customer C
    INNER JOIN Sales.Store S
        ON C.StoreID  = S.BusinessEntityID 
    INNER JOIN Sales.SalesTerritory ST ON C.TerritoryID = ST.TerritoryID
    INNER JOIN Sales.SalesPerson SP ON S.SalesPersonID = SP.BusinessEntityID
    INNER JOIN Person.Person P ON S.SalesPersonID = P.BusinessEntityID
    INNER JOIN Sales.SalesOrderHeader SH ON C.CustomerID = SH.CustomerID
GROUP BY S.Name
GO

SELECT ST.Name TerritoryName, SUM(SH.TotalDue) SalesTotal
FROM Sales.Customer C
    INNER JOIN Sales.Store S
        ON C.StoreID  = S.BusinessEntityID 
    INNER JOIN Sales.SalesTerritory ST ON C.TerritoryID = ST.TerritoryID
    INNER JOIN Sales.SalesPerson SP ON S.SalesPersonID = SP.BusinessEntityID
    INNER JOIN Person.Person P ON S.SalesPersonID = P.BusinessEntityID
    INNER JOIN Sales.SalesOrderHeader SH ON C.CustomerID = SH.CustomerID
GROUP BY ST.Name
GO

SELECT ST.Name TerritoryName, ST.CountryRegionCode, SUM(SH.TotalDue) SalesTotal
FROM Sales.Customer C
    INNER JOIN Sales.Store S
        ON C.StoreID  = S.BusinessEntityID 
    INNER JOIN Sales.SalesTerritory ST ON C.TerritoryID = ST.TerritoryID
    INNER JOIN Sales.SalesPerson SP ON S.SalesPersonID = SP.BusinessEntityID
    INNER JOIN Person.Person P ON S.SalesPersonID = P.BusinessEntityID
    INNER JOIN Sales.SalesOrderHeader SH ON C.CustomerID = SH.CustomerID
GROUP BY ST.Name,ST.CountryRegionCode
GO

SELECT ST.[Group], SUM(SH.TotalDue) SalesTotal
FROM Sales.Customer C
    INNER JOIN Sales.Store S
        ON C.StoreID  = S.BusinessEntityID 
    INNER JOIN Sales.SalesTerritory ST ON C.TerritoryID = ST.TerritoryID
    INNER JOIN Sales.SalesPerson SP ON S.SalesPersonID = SP.BusinessEntityID
    INNER JOIN Person.Person P ON S.SalesPersonID = P.BusinessEntityID
    INNER JOIN Sales.SalesOrderHeader SH ON C.CustomerID = SH.CustomerID
GROUP BY ST.[Group]
GO

SELECT SalesOrderID, SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail
WHERE LineTotal > 30000
GROUP BY SalesOrderID
ORDER BY SalesOrderID
GO

SELECT SalesOrderID, SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
HAVING SUM(LineTotal) > 30000
GO

SELECT SalesOrderID, SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
HAVING SalesOrderID > 45000
ORDER BY SalesOrderID ;

SELECT SalesOrderID, SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail
WHERE SalesOrderID > 45000
GROUP BY SalesOrderID
ORDER BY SalesOrderID 
GO

SELECT SH3.SalesPersonID, SH3.OrderDate, SH3.DailyTotal, SUM(SH4.DailyTotal) RunningTotal
FROM (SELECT SH1.SalesPersonID, SH1.OrderDate, SUM(SH1.TotalDue) DailyTotal
        FROM Sales.SalesOrderHeader SH1
        WHERE SH1.SalesPersonID IS NOT NULL
        GROUP BY SH1.SalesPersonID, SH1.OrderDate) SH3
INNER JOIN (SELECT SH2.SalesPersonID, SH2.OrderDate, SUM(SH2.TotalDue) DailyTotal
        FROM Sales.SalesOrderHeader SH2
        WHERE SH2.SalesPersonID IS NOT NULL
        GROUP BY SH2.SalesPersonID, SH2.OrderDate) SH4
ON SH3.SalesPersonID = SH4.SalesPersonID
    AND SH3.OrderDate >= SH4.OrderDate
GROUP BY SH3.SalesPersonID, SH3.OrderDate, SH3.DailyTotal
ORDER BY SH3.SalesPersonID, SH3.OrderDate
GO

SELECT VendorID, [2001], [2002], [2003], [2004]
FROM (SELECT VendorID, PurchaseOrderID, YEAR(OrderDate) ChangeYear
        FROM Purchasing.PurchaseOrderHeader) r
PIVOT
(COUNT(r.PurchaseOrderID)
    FOR ChangeYear
    IN ([2001],[2002],[2003],[2004]))
AS Results
ORDER BY VendorID  

SELECT VendorID, [2003]
FROM (SELECT VendorID, PurchaseOrderID, YEAR(OrderDate) ChangeYear
        FROM Purchasing.PurchaseOrderHeader) r
PIVOT
(COUNT(r.PurchaseOrderID)
    FOR ChangeYear
    IN ([2003]))
AS Results
ORDER BY VendorID  
GO

SELECT p.FirstName, p.LastName, 
    ROW_NUMBER() OVER(ORDER BY s.SalesYTD DESC) AS 'RowNumber', 
    s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s INNER JOIN Person.Person p 
        ON s.BusinessEntityID = p.BusinessEntityID
    INNER JOIN Person.BusinessEntityAddress ba ON p.BusinessEntityID = ba.BusinessEntityID
    INNER JOIN Person.Address a ON a.AddressID = ba.AddressID
WHERE s.TerritoryID IS NOT NULL 
GO

SELECT p.FirstName, p.LastName
    ,ROW_NUMBER() OVER (PARTITION BY s.TerritoryID ORDER BY SalesYTD DESC) AS 'RowNumber', 
    s.SalesYTD, s.TerritoryID
FROM Sales.SalesPerson s INNER JOIN Person.Person p 
        ON s.BusinessEntityID = p.BusinessEntityID
    INNER JOIN Person.BusinessEntityAddress ba ON p.BusinessEntityID = ba.BusinessEntityID
    INNER JOIN Person.Address a ON a.AddressID = ba.AddressID
WHERE s.TerritoryID IS NOT NULL 
GO

SELECT a.ProductID, b.Name, a.LocationID, a.Quantity
    ,RANK() OVER (PARTITION BY a.LocationID ORDER BY a.Quantity DESC) AS 'Rank'
FROM Production.ProductInventory a INNER JOIN Production.Product b 
    ON a.ProductID = b.ProductID
ORDER BY b.Name;

SELECT a.ProductID, b.Name, a.LocationID, a.Quantity
    ,RANK() OVER (PARTITION BY a.LocationID ORDER BY a.Quantity DESC) AS 'Rank'
FROM Production.ProductInventory a INNER JOIN Production.Product b 
    ON a.ProductID = b.ProductID
ORDER BY 'Rank';
GO

SELECT a.ProductID, b.Name, a.LocationID, a.Quantity
    ,DENSE_RANK() OVER (PARTITION BY a.LocationID ORDER BY a.Quantity DESC) AS 'DenseRank'
FROM Production.ProductInventory a INNER JOIN Production.Product b 
    ON a.ProductID = b.ProductID
ORDER BY b.Name;

SELECT a.ProductID, b.Name, a.LocationID, a.Quantity
    ,DENSE_RANK() OVER (PARTITION BY a.LocationID ORDER BY a.Quantity DESC) AS DenseRank
FROM Production.ProductInventory a INNER JOIN Production.Product b 
    ON a.ProductID = b.ProductID
ORDER BY DenseRank;
GO

SELECT p.FirstName, p.LastName, 
    NTILE(4) OVER(ORDER BY s.SalesYTD DESC) AS QuarterGroup,
    s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s INNER JOIN Person.Person p 
        ON s.BusinessEntityID = p.BusinessEntityID
    INNER JOIN Person.BusinessEntityAddress ba ON p.BusinessEntityID = ba.BusinessEntityID
    INNER JOIN Person.Address a ON a.AddressID = ba.AddressID
WHERE s.TerritoryID IS NOT NULL 
    
SELECT p.FirstName, p.LastName,
    NTILE(2) OVER(PARTITION BY s.TerritoryID ORDER BY s.SalesYTD DESC) AS QuarterGroup,
    s.SalesYTD, s.TerritoryID
FROM Sales.SalesPerson s 
    INNER JOIN Person.Person p 
        ON s.BusinessEntityID = p.BusinessEntityID
    INNER JOIN Person.BusinessEntityAddress ba ON p.BusinessEntityID = ba.BusinessEntityID
    INNER JOIN Person.Address a ON a.AddressID = ba.AddressID
WHERE s.TerritoryID IS NOT NULL 
GO

SELECT p.LastName, p.FirstName
FROM Person.Person p JOIN HumanResources.Employee e 
    ON p.BusinessEntityID = e.BusinessEntityID
WHERE e.BusinessEntityID = 2
UNION
SELECT p.LastName, p.FirstName
FROM Person.Person p JOIN HumanResources.Employee e 
    ON p.BusinessEntityID = e.BusinessEntityID
WHERE e.BusinessEntityID = 2
UNION
SELECT p.LastName, p.FirstName
FROM Person.Person p JOIN HumanResources.Employee e 
    ON p.BusinessEntityID = e.BusinessEntityID
WHERE e.BusinessEntityID = 2
GO

SELECT p.LastName, p.FirstName
FROM Person.Person p JOIN HumanResources.Employee e 
    ON p.BusinessEntityID = e.BusinessEntityID
WHERE e.BusinessEntityID = 2
UNION ALL
SELECT p.LastName, p.FirstName
FROM Person.Person p JOIN HumanResources.Employee e 
    ON p.BusinessEntityID = e.BusinessEntityID
WHERE e.BusinessEntityID = 2
UNION ALL
SELECT p.LastName, p.FirstName
FROM Person.Person p JOIN HumanResources.Employee e 
    ON p.BusinessEntityID = e.BusinessEntityID
WHERE e.BusinessEntityID = 2
GO

SELECT p.LastName, p.FirstName
FROM Person.Person p JOIN HumanResources.Employee e 
    ON p.BusinessEntityID = e.BusinessEntityID
WHERE e.BusinessEntityID = 2
UNION
SELECT p.LastName, p.FirstName
FROM Person.Person p JOIN HumanResources.Employee e 
    ON p.BusinessEntityID = e.BusinessEntityID
WHERE e.BusinessEntityID = 2
UNION ALL
SELECT p.LastName, p.FirstName
FROM Person.Person p JOIN HumanResources.Employee e 
    ON p.BusinessEntityID = e.BusinessEntityID
WHERE e.BusinessEntityID = 2
GO

SELECT ProductID
FROM Production.Product
INTERSECT
SELECT ProductID
FROM Production.ProductDocument
GO

SELECT a.ProductID
FROM Production.Product a INNER JOIN Production.ProductDocument b 
        ON a.ProductID = b.ProductID
GO

SELECT ProductID
FROM Production.Product
EXCEPT
SELECT ProductID
FROM Production.ProductDocument
GO

SELECT a.ProductID
FROM Production.Product a INNER JOIN Production.ProductDocument b 
        ON a.ProductID != b.ProductID
GO

WITH EMP_cte(BusinessEntityID, OrganizationNode, FirstName, LastName, JobTitle, RecursionLevel)
AS (SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, 0
    FROM HumanResources.Employee e INNER JOIN Person.Person as p
        ON p.BusinessEntityID = e.BusinessEntityID
    UNION ALL
    SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, RecursionLevel + 1
    FROM HumanResources.Employee e 
        INNER JOIN EMP_cte ON e.OrganizationNode = EMP_cte.OrganizationNode.GetAncestor(1)
        INNER JOIN Person.Person p ON p.BusinessEntityID = e.BusinessEntityID)

SELECT EMP_cte.RecursionLevel, EMP_cte.BusinessEntityID, EMP_cte.FirstName, EMP_cte.LastName, 
    EMP_cte.OrganizationNode.ToString() AS OrganizationNode, p.FirstName AS 'ManagerFirstName', p.LastName AS 'ManagerLastName'
FROM EMP_cte INNER JOIN HumanResources.Employee e 
    ON EMP_cte.OrganizationNode.GetAncestor(1) = e.OrganizationNode
    INNER JOIN Person.Person p ON p.BusinessEntityID = e.BusinessEntityID
ORDER BY RecursionLevel, EMP_cte.OrganizationNode.ToString()
OPTION (MAXRECURSION 25) 
GO

SELECT a.Name, a.ProductNumber, b.Instructions
FROM Production.Product a INNER JOIN Production.ProductModel b 
    ON a.ProductModelID = b.ProductModelID
WHERE b.Instructions IS NOT NULL
GO

SELECT a.Name, a.ProductNumber, 
    b.Instructions.query('declare 
    namespace AW="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
    AW:root/AW:Location[@MachineHours>2.5]') Locations
FROM Production.Product a INNER JOIN Production.ProductModel b 
    ON a.ProductModelID = b.ProductModelID
WHERE b.Instructions IS NOT NULL
GO

SELECT a.Name, a.ProductNumber, 
    b.Instructions.query('declare 
    namespace AW="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
    AW:root/AW:Location[@machineHours>2.5]') Locations
FROM Production.Product a INNER JOIN Production.ProductModel b 
    ON a.ProductModelID = b.ProductModelID
WHERE b.Instructions IS NOT NULL
GO

SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID IN (119,122)
FOR XML RAW;
GO

SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID IN (119,122)
FOR XML RAW, ELEMENTS;
GO

SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID IN (119,122)
FOR XML RAW, ELEMENTS, TYPE;
GO

SELECT ProductID, Name, Color
FROM Production.Product
FOR XML RAW, ELEMENTS;
GO

SELECT ProductID, Name, Color
FROM Production.Product
FOR XML RAW, ELEMENTS XSINIL
GO

SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID IN (119,122)
FOR XML RAW, XMLSCHEMA
GO

SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID IN (119,122)
FOR XML AUTO
GO