--lista1
--zad1
SELECT COUNT(*) AS 'Ilość produktów' FROM [Production].[Product];
SELECT COUNT(*) AS 'Ilość kategorii' FROM [Production].[ProductCategory];
SELECT COUNT(*) AS 'Ilość podkategorii' FROM [Production].[ProductSubcategory];

--zad2
SELECT * FROM [Production].[Product]
WHERE [Color] IS NULL;

--zad 3
SELECT YEAR([OrderDate]), SUM([SubTotal]) FROM [Sales].[SalesOrderHeader]
GROUP BY YEAR([OrderDate]);

--zad 4
SELECT COUNT(*) AS 'Liczba klientów' FROM [Sales].[Customer];
SELECT COUNT(*) AS 'Liczba sprzedawców' FROM [Sales].[SalesPerson];

--zad 5
SELECT COUNT(*) AS 'Liczba transakcji', YEAR([TransactionDate]) FROM [Production].[TransactionHistory]
GROUP BY YEAR([TransactionDate]);

--zad 6
SELECT pc.Name, pp.Name FROM [Production].[Product] pp
JOIN [Sales].[SalesOrderDetail] ss ON ss.[ProductID] != pp.[ProductID]
JOIN [Production].[ProductSubcategory] ps ON ps.[ProductSubcategoryID] = pp.ProductSubcategoryID
JOIN [Production].[ProductCategory] pc ON pc.ProductCategoryID = ps.ProductCategoryID
GROUP BY pc.Name, pp.Name;

--zad 7

SELECT ps.name AS 'Produkt', MAX(ss.UnitPrice*ss.UnitPriceDiscount) AS 'Maksymalny rabat',MIN(ss.UnitPrice*ss.UnitPriceDiscount) 'Minimalny rabat' FROM [Production].[Product] pp
JOIN [Sales].[SalesOrderDetail] ss ON ss.[ProductID] = pp.[ProductID]
JOIN [Production].[ProductSubcategory] ps ON ps.[ProductSubcategoryID] = pp.ProductSubcategoryID
JOIN [Production].[ProductCategory] pc ON pc.ProductCategoryID = ps.ProductCategoryID
WHERE ss.UnitPrice*ss.UnitPriceDiscount>0
GROUP BY ps.Name;

--zad 8
SELECT [Name] AS 'Produkt',[ListPrice] AS 'Cena' FROM [Production].[Product]
where [ListPrice] > (SELECT AVG([ListPrice]) FROM [Production].[Product])
GROUP BY [Name],[ListPrice]
order by ListPrice desc;

 --zad9
SELECT
SUM(sod.OrderQty)/COUNT(distinct YEAR(soh.OrderDate))   as 'Średnia produktów',
MONTH(soh.OrderDate) as 'Miesiąc'
FROM Sales.SalesOrderHeader soh JOIN Sales.SalesOrderDetail sod ON soh.SalesOrderID=sod.SalesOrderID
GROUP BY MONTH(soh.OrderDate)

--zad10
SELECT
AVG(DATEDIFF(dd,soh.OrderDate,soh.DueDate)) as 'Ile dni czeka klient',
st.Name AS 'Państwo'
FROM Sales.SalesOrderHeader soh JOIN Sales.SalesTerritory st ON soh.TerritoryID=st.TerritoryID
GROUP BY st.Name






SELECT Production.ProductCategory.Name Kategoria
    ,COUNT(Production.Product.ProductID) [Liczba produktów]
FROM Production.Product
LEFT JOIN Production.ProductSubcategory ON Production.ProductSubcategory.ProductSubcategoryID = Production.Product.ProductSubcategoryID
LEFT JOIN Production.ProductCategory ON Production.ProductCategory.ProductCategoryID = Production.ProductSubcategory.ProductCategoryID
WHERE Production.Product.ProductID NOT IN (
        SELECT Sales.SalesOrderDetail.ProductID
        FROM Sales.SalesOrderDetail
        )
GROUP BY Production.ProductCategory.Name

SELECT Production.ProductCategory.Name
    ,Production.Product.*
FROM Production.Product
LEFT JOIN Production.ProductSubcategory ON Production.ProductSubcategory.ProductSubcategoryID = Production.Product.ProductSubcategoryID
LEFT JOIN Production.ProductCategory ON Production.ProductCategory.ProductCategoryID = Production.ProductSubcategory.ProductCategoryID
WHERE Production.Product.ProductID NOT IN (
        SELECT sod.ProductID
        FROM Sales.SalesOrderDetail sod
        )
ORDER BY 1