--1
SELECT COUNT(ProductID)
FROM Production.Product;

SELECT COUNT(ProductCategoryID)
FROM Production.ProductCategory;

SELECT COUNT(ProductSubcategoryID)
FROM Production.ProductSubcategory;

--2
SELECT *
FROM Production.Product
	WHERE Color IS NULL;

--3
SELECT DISTINCT YEAR(OrderDate), SUM(TotalDue)
FROM Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate)
ORDER BY YEAR(OrderDate);

 --4
SELECT COUNT(*) AS customer_number FROM Sales.Customer;

SELECT COUNT(*) AS employee_number FROM HumanResources.Employee;

 --5
 SELECT COUNT(*) FROM (
 SELECT * FROM Production.TransactionHistory
 UNION
 SELECT * FROM Production.TransactionHistoryArchive) as transactions
 GROUP BY Year(transactions.TransactionDate);

 --6
SELECT Production.Product.ProductID, Production.Product.Name AS product_name, Production.ProductCategory.Name AS category_name
FROM Production.Product
JOIN Production.ProductSubcategory ON Product.ProductSubcategoryID = Production.ProductSubcategory.ProductSubcategoryID
JOIN Production.ProductCategory ON Production.ProductSubcategory.ProductCategoryID = Production.ProductCategory.ProductCategoryID
LEFT JOIN Sales.SalesOrderDetail ON Production.Product.ProductID = Sales.SalesOrderDetail.ProductID
LEFT JOIN Sales.SalesOrderHeader ON Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID
LEFT JOIN Sales.Customer ON Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID
WHERE Sales.Customer.CustomerID IS NULL
GROUP BY Production.ProductCategory.Name, Production.Product.Name, Production.Product.ProductID;

--7
SELECT MAX(DiscountPct*ListPrice)
FROM Production.Product
	JOIN Sales.SpecialOfferProduct ON Product.ProductID=SpecialOfferProduct.ProductID
	JOIN Sales.SpecialOffer ON SpecialOfferProduct.SpecialOfferID = SpecialOffer.SpecialOfferID
	GROUP BY ProductSubcategoryID;
--8
SELECT ProductID
FROM Production.Product
	GROUP BY ProductID, ListPrice
	HAVING (SELECT AVG(ListPrice) FROM Production.Product)<ListPrice;

--9
SELECT AVG(Product.ProductID), MONTH(SalesOrderHeader.OrderDate)
FROM Sales.SalesOrderDetail
JOIN Production.Product ON SalesOrderDetail.ProductID = Product.ProductID
JOIN Sales.SalesOrderHeader ON SalesOrderHeader.SalesOrderID = SalesOrderDetail.SalesOrderID
GROUP BY MONTH(SalesOrderHeader.OrderDate)
ORDER BY MONTH(SalesOrderHeader.OrderDate);

--10
SELECT
territory.Name 'Country',
AVG(Datediff(day, OrderDate, ShipDate)/1.0) 'Average shipment time'
FROM Sales.SalesOrderHeader header
inner join Sales.SalesTerritory territory on header.TerritoryID = territory.TerritoryID
group by territory.Name;