/*Mazzuola || Ercolini*/

/*
Elenco prodotti con prezzo superiore alla media
 */
SELECT ProductName FROM Products WHERE UnitPrice > (SELECT avg(UnitPrice) FROM
Products);

/*
Nome del prodotto/i con prezzo massimo
*/
SELECT ProductName FROM Products WHERE UnitPrice = (SELECT MAX(UnitPrice) FROM
Products);

/*
Elenco articoli che non sono mai stati ordinati
*/
SELECT ProductName FROM Products
WHERE Products.ProductID NOT IN (
SELECT [Order Details].ProductID FROM [Order Details]);

/*
Elenco clienti che hanno effettuato almeno un ordine con piu di 5 prodotti
*/

SELECT CompanyName, Customers.CustomerID, COUNT([Order Details].OrderID) AS Num FROM Customers,Orders,[Order Details]
WHERE Orders.CustomerID = Customers.CustomerID
AND Orders.OrderID = [Order Details].OrderID
GROUP BY [Order Details].OrderID, CompanyName, Customers.CustomerID HAVING COUNT([Order Details].OrderID) > 5;

/*
In quale paese/stati abbiamo piu clienti
*/

SELECT TOP 1 Country, COUNT(CompanyName) as Num FROM Customers
WHERE Country != ''
GROUP BY Country
ORDER BY Num DESC;

/*
Quale categoria ha meno prodotti
*/

SELECT TOP 1 CategoryName, COUNT(ProductID) as Num FROM Categories, Products
WHERE Products.CategoryID = Categories.CategoryID
GROUP BY CategoryName
ORDER BY Num ASC;

/*
Cliente che hanno fatto piu ordini
*/

SELECT TOP 1 CompanyName, COUNT(*) as Num FROM Customers, Orders
WHERE Orders.CustomerID = Customers.CustomerID
GROUP BY CompanyName
ORDER BY Num DESC;

/*
Clienti che hanno ordinato piu di 30 prodotti diversi (anche piu ordini)
*/

drop table t555 /* si cancella la tabelle temporanea t555*/
/* si estraggono */
SELECT DISTINCT dbo.[Order Details].ProductID as prodotto, dbo.Orders.CustomerID, dbo.customers.companyname as cliente into t555
FROM dbo.Orders INNER JOIN
	dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order Details].OrderID
						inner join dbo.customers on dbo.Orders.customerID=dbo.customers.customerID
GROUP BY dbo.Orders.CustomerID, dbo.customers.companyname, dbo.[Order Details].ProductID

select customerID,cliente,count(prodotto)
from t555 
group by customerID, cliente
having count(prodotto)>30
order by count(prodotto)desc;