/*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;