/*
CIS 324

JOINS

pp. 74 (relational operation)
Natural Join

pp. 264-268, 293-300

JOINS with GROUP BY HAVING

*/

USE Northwind

/*
Beverages: Units Ordered Report
How many units of each product have been ordered for CategoryID 1 
for the entire database?
Only show products for which more than 1,000 units have been ordered.
*/
--Includes products in ALL categories
SELECT *
FROM [Order Details] AS OrderDetail
ORDER BY ProductID

SELECT ProductID, SUM(Quantity) AS QtyOrdered
FROM [Order Details] AS OrderDetail
GROUP BY ProductID

--Now JOIN OrderDetails to the PRODUCTS table to get CategoryID for the WHERE condition
--We need to look in the Products table
SELECT *
FROM Products

SELECT ProductID, SUM(Quantity) AS QtyOrdered
FROM [Order Details] AS OrderDetail, Products
WHERE Products.ProductID=OrderDetail.ProductID
GROUP BY ProductID

SELECT Products.ProductID, SUM(Quantity) AS QtyOrdered
FROM [Order Details] AS OrderDetail, Products
WHERE Products.ProductID=OrderDetail.ProductID
GROUP BY Products.ProductID


--Now add Products.CategoryID = 1 to the WHERE clause
SELECT Products.ProductID, SUM(Quantity) AS QtyOrdered
FROM [Order Details] AS OrderDetail, Products
WHERE Products.ProductID=OrderDetail.ProductID AND
      Products.CategoryID=1
GROUP BY Products.ProductID

--Filter groups
SELECT Products.ProductID, SUM(Quantity) AS QtyOrdered
FROM [Order Details] AS OrderDetail, Products
WHERE Products.ProductID=OrderDetail.ProductID AND
      CategoryID=1
GROUP BY Products.ProductID
HAVING SUM(Quantity) > 1000

SELECT Products.ProductID, SUM(Quantity) AS QtyOrdered
FROM [Order Details] AS OrderDetail, Products
WHERE Products.ProductID=OrderDetail.ProductID AND
      CategoryID=1
GROUP BY Products.ProductID
HAVING QtyOrdered > 1000

SELECT Products.ProductID, SUM(Quantity) AS QtyOrdered
FROM [Order Details] AS OrderDetail, Products
WHERE Products.ProductID=OrderDetail.ProductID AND
      CategoryID=1
GROUP BY Products.ProductID
HAVING SUM(Quantity) > 1000
ORDER BY QtyOrdered

/*
USA: order count report
List the count of orders placed by customers in the USA.
Only show customer counts where the customer has placed more 
than 10 orders.
*/
SELECT *
FROM Orders

SELECT Orders.CustomerID, COUNT(*) AS OrderCnt
FROM Orders
GROUP BY Orders.CustomerID

--How do we know the country?
SELECT *
FROM Customers
WHERE Customers.Country='USA'

--JOIN first
SELECT Customers.CustomerID, COUNT(*) AS OrderCnt
FROM Orders, Customers
WHERE Customers.CustomerID=Orders.CustomerID
GROUP BY Customers.CustomerID


--Add the USA WHERE filter
SELECT Orders.CustomerID, COUNT(*) AS OrderCnt
FROM Orders, Customers
WHERE Customers.CustomerID=Orders.CustomerID AND
      Customers.Country='USA'
GROUP BY Orders.CustomerID

--Add the GROUP BY

--Add the HAVING filter
SELECT Orders.CustomerID, COUNT(*) AS OrderCnt
FROM Orders, Customers
WHERE Customers.CustomerID=Orders.CustomerID AND
      Customers.Country='USA'
GROUP BY Orders.CustomerID
HAVING COUNT(*) > 10