PRZYŁĄCZANIE

SELECT p.[Name], c.[Name]
FROM [SalesLT].[Product] AS p
FULL  JOIN [SalesLT].[ProductCategory] AS c
    ON c.ProductCategoryID = p.ProductCategoryID
/* LEFT, RIGHT, FULL JOIN */


_____________
OSOBY KTORE NIE ZLOZYLY ZAMOWIENIA

SELECT DISTINCT c.FirstName, c.LastName,
FROM [SalesLT].[SalesOrderHeader] AS p
FULL JOIN [SalesLT].[Customer] AS c
    ON c.CustomerID = p.CustomerID
    WHERE p.CustomerID IS NULL

________________
KIEDY TABELE NIE SA BEZPOSREDNIO POLACZONE RELACJAMI

SELECT  c.FirstName, c.LastName, p.Name
FROM [SalesLT].[Customer] AS c
INNER JOIN [SalesLT].[SalesOrderHeader] AS oh
    ON c.CustomerID = oh.CustomerID

INNER JOIN [SalesLT].[SalesOrderDetail] AS od
    ON oh.SalesOrderID = od.SalesOrderID

INNER JOIN [SalesLT].[Product] AS p
    ON od.ProductID = p.ProductID



___________________________________

SELECT p.Name, oh.SalesOrderNumber, od.SalesOrderDetailID, od.LineTotal
FROM [SalesLT].[Product] AS p
LEFT JOIN
    (
        [SalesLT].[SalesOrderDetail] AS od
        JOIN [SalesLT].[SalesOrderHeader] AS oh
        ON od.SalesOrderID = oh.SalesOrderID
    )

    ON p.ProductID = od.ProductID

    ORDER BY p.Name


___________________________________

SELECT c.[Name], COUNT(p.[ProductID]) AS l, avg([ListPrice]) AS s
FROM [SalesLT].[ProductCategory] AS c
JOIN [SalesLT].[Product] AS p
    ON c.ProductCategoryID = p.ProductCategoryID
WHERE [Color] IN ('Black', 'Red')
GROUP BY c.Name
HAVING   COUNT([ProductID]) > 5
ORDER BY s DESC

__________________________________
SELECT NULL, a.City, COUNT(ca.CustomerID) AS cnt
FROM [SalesLT].[Address] AS a
LEFT JOIN [SalesLT].[CustomerAddress] AS ca
    ON a.AddressID = ca.AddressID
    WHERE a.CountryRegion = 'United States'
    GROUP BY a.City



    UNION ALL

    SELECT a.StateProvince, NULL,  COUNT(ca.CustomerID) AS cnt
FROM [SalesLT].[Address] AS a
LEFT JOIN [SalesLT].[CustomerAddress] AS ca
    ON a.AddressID = ca.AddressID
    WHERE a.CountryRegion = 'United States'
    GROUP BY a.StateProvince

    ORDER BY 1


_________________________________________________
SELECT a.StateProvince, a.City,  COUNT(ca.CustomerID) AS cnt
FROM [SalesLT].[Address] AS a
JOIN [SalesLT].[CustomerAddress] AS ca
    ON a.AddressID = ca.AddressID
    WHERE a.CountryRegion = 'United States'
    GROUP BY GROUPING sets (
         (a.StateProvince), (a.City)    )
         ORDER BY a.StateProvince

_____________________________________________

SELECT c1.Name AS cat, c2.Name AS subcat, COUNT(p.[ProductID]) AS cnt, avg([ListPrice]) AS a
FROM [SalesLT].[ProductCategory] AS c1
JOIN [SalesLT].[ProductCategory] AS c2
    ON c1.ProductCategoryID = c2.ParentProductCategoryID
JOIN [SalesLT].[Product] AS p
    ON c2.ProductCategoryID = p.ProductCategoryID
    GROUP BY GROUPING sets ((), (c1.Name), (c2.Name))
ORDER BY 1, 2


______________________________________________________________________

SELECT a.City, SUM([OrderQty]) AS Bikes, SUM([LineTotal]) AS Total
FROM [SalesLT].[ProductCategory] AS pc
JOIN [SalesLT].[Product] AS p
    ON pc.ProductCategoryID = p.ProductCategoryID

JOIN [SalesLT].[SalesOrderDetail] AS od
    ON p.ProductID = od.ProductID

JOIN [SalesLT].[SalesOrderHeader] AS oh
    ON od.SalesOrderID = oh.SalesOrderID

JOIN [SalesLT].[Address] AS a
    ON a.AddressID = oh.ShipToAddressID

WHERE pc.[name] LIKE '%bikes%'
GROUP BY GROUPING sets (
(a.City), (a.City))
HAVING SUM([OrderQty]) > 10
ORDER BY Bikes DESC


____________________________________________________________
SELECT [SalesOrderNumber], [TotalDue]
    ,avg([TotalDue]) OVER()
    ,sign([TotalDue] - avg([TotalDue]) OVER())
FROM [SalesLT].[SalesOrderHeader]

_________________________________________________________
SELECT [TotalDue]
    ,[TotalDue]*100 / SUM([TotalDue]) OVER(partition BY [CustomerID])
    ,[TotalDue]*100 / SUM([TotalDue]) OVER()
FROM [SalesLT].[SalesOrderHeader]

______________________________________________________________

PODZAPYTANIA
ELECT [FirstName], [LastName]
FROM [SalesLT].[Customer]
WHERE [CustomerID] =

(SELECT [CustomerID]
FROM [SalesLT].[SalesOrderHeader]
WHERE [SalesOrderNumber] = 'SO71832')

__________________________________________________________

Stronnicowanie produktów
SELECT *
FROM
( SELECT [Name], [ListPrice], [Color]
    ,ntile(12) OVER (ORDER BY name) AS strona
FROM [SalesLT].[Product]) AS s
WHERE s.strona = 1