--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 [Rok] Rok, SUM([LiczbaT]) [Liczba transakcji]
FROM (
	SELECT YEAR(TransactionDate) [Rok], COUNT(TransactionID) [LiczbaT]
	FROM Production.TransactionHistory
	GROUP BY YEAR(TransactionDate)
	UNION
	SELECT YEAR(TransactionDate) [Rok], COUNT(TransactionID) [LiczbaT]
	FROM Production.TransactionHistoryArchive
	GROUP BY YEAR(TransactionDate)
	) [Transakcje]
GROUP BY [Transakcje].[Rok]



UNION SELECT COUNT(*) AS 'Liczba transakcji', YEAR([TransactionDate]) FROM [Production].[TransactionHistoryArchive]
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
order by 2;

--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