--1.
SELECT Orders.CustomerID, Customers.ContactName, COUNT(*)
FROM dbo.Orders INNER JOIN dbo.Customers ON Customers.CustomerID = Orders.CustomerID
WHERE YEAR(ShippedDate) = 1997
GROUP BY Orders.CustomerID, Customers.ContactName;

--2.
SELECT ProductName, ProductID, (
	SELECT ROUND(SUM(([UnitPrice] * [Quantity]) * (1 - [Discount])), 0)
	FROM dbo.[Order Details]
	WHERE Products.ProductID = [Order Details].ProductID
)
FROM dbo.Products

--4.
SELECT Categories.CategoryID, Categories.CategoryName
FROM dbo.Categories INNER JOIN (
	SELECT CategoryID, COUNT(*) As Amount
	FROM dbo.Products
	WHERE Products.UnitPrice > 10
	GROUP BY Products.CategoryID
) t ON t.Amount > 10 AND Categories.CategoryID = t.CategoryID

--3.
SELECT Products.ProductID, Products.ProductName, Orders.ShipCountry
FROM dbo.Products INNER JOIN dbo.[Order Details] ON [Order Details].ProductID = Products.ProductID
INNER JOIN dbo.Orders ON Orders.OrderID = [Order Details].OrderID
GROUP BY Products.ProductID, Products.ProductName, Orders.ShipCountry
--HAVING COUNT(Products.ProductID) > 15

--5.
SELECT Orders.EmployeeID, Employees.FirstName, COUNT(*)
FROM dbo.Orders INNER JOIN dbo.Employees ON Orders.EmployeeID = Employees.EmployeeID
WHERE ShipCity = 'Paris'
GROUP BY Orders.EmployeeID, Employees.FirstName