/*
DIVISION, ANY and ALL

*/

/*
To identify certain segments of each market for an
upcoming promotion, marketing requested a list of 
customers who have ordered ALL products from a 
given product category. For example
category 4 (Dairy Products)

There are at least two ways to do this.
1. Compute a DISTINCT count for the ALL attribute
   and filter with a HAVING clause
2. Nested NOT EXISTS clauses

*/
USE Northwind

/*
In the first case, we would GROUP BY on 
customerID and filter on WHERE CategoryID=4.
We would join Orders, [Order Details], and Products
We need Products to get the CategoryID (which is a Fk) 

First the join
*/

SELECT CustomerID, OrderDetail.ProductID
FROM Orders, [Order Details] AS OrderDetail, Products
WHERE Orders.OrderID=OrderDetail.OrderID AND
		Products.ProductID=OrderDetail.ProductID AND
		Products.CategoryID = 4
ORDER BY CustomerID
/*
Now, replace the ORDER BY with GROUP BY and add the
COUNT() function.
*/
SELECT CustomerID, COUNT(DISTINCT [Order Details].ProductID) as ProductCount
FROM Orders, [Order Details], Products
WHERE Orders.OrderID=[Order Details].OrderID AND
		Products.ProductID=[Order Details].ProductID AND
		Products.CategoryID = 4
GROUP BY CustomerID

/*
Once we have that, we can add a HAVING clause that 
only shows rows where the customer has ordered ALL
the products. 
The COUNT(DISTINCT...) function is copied into the 
HAVING clause.
*/
COUNT(*)
COUNT(OrderID)
COUNT(DISTINCT OrderID)


SELECT CustomerID, COUNT(DISTINCT [Order Details].ProductID) as ProductCount
FROM Orders, [Order Details], Products
WHERE Orders.OrderID=[Order Details].OrderID AND
		Products.ProductID=[Order Details].ProductID AND
		Products.CategoryID = 4
GROUP BY CustomerID
HAVING COUNT(DISTINCT [Order Details].ProductID) = (subquery that returns the number of products in category 4)

--The subquery would be...
SELECT COUNT(DISTINCT ProductID) 
FROM Products 
WHERE CategoryID = 4

--Insert the subquery

SELECT CustomerID, COUNT(DISTINCT [Order Details].ProductID) as ProductCount
FROM Orders, [Order Details], Products
WHERE Orders.OrderID=[Order Details].OrderID AND
		Products.ProductID=[Order Details].ProductID AND
		Products.CategoryID = 4
GROUP BY CustomerID
HAVING COUNT(DISTINCT [Order Details].ProductID) = (SELECT COUNT(DISTINCT ProductID) 
                                                    FROM Products 
                                                    WHERE CategoryID = 4)
/*
The nested NOT EXISTS solution is given below.
This is very difficult to test incrementally...
*/

SELECT *
FROM Customers --SUPPLIER
WHERE NOT EXISTS
     (SELECT ProductID --Products 
      FROM Products
      WHERE CategoryID = 4 AND 
				NOT EXISTS 
           (SELECT [Order Details].ProductID
            FROM Orders, [Order Details]
            WHERE Orders.OrderID = [Order Details].OrderID AND
                  Orders.CustomerID = Customers.CustomerID AND 
                  [Order Details].ProductID = Products.ProductID))


/*
SELECT Customer info
FROM Customers
WHERE there are no products returned by subquery
	SELECT Product info for products in category 4
	FROM Products 
	WHERE the product does not appear on an order
		SELECT Product info
		FROM Order, [Order Details]
		WHERE the product appears on an order 

*/

/*
Marketing wants to mail out a survey to assess
satisfaction with its shippers. Marketing only
wants to send the mailing out to those customers
who have had an order shipped by all shippers.
*/

SELECT CustomerID, COUNT(DISTINCT shipvia)
FROM Orders
GROUP BY CustomerID
HAVING COUNT(DISTINCT shipvia) = (SELECT COUNT(*) FROM Shippers)

SELECT CustomerID, CompanyName, Address 
FROM Customers
WHERE NOT EXISTS
     (SELECT ShipperID 
      FROM Shippers
      WHERE NOT EXISTS 
           (SELECT ShipVia
            FROM Orders
            WHERE Orders.CustomerID = Customers.CustomerID AND 
                  Orders.ShipVia = Shippers.ShipperID))

/*

List product information for products whose unit price is greater than 
ALL unitprices for
products in category 7 (produce).

List product information for products whose unit price is greater than 
ANY unitprices for
products in category 7 (produce).
*/

SELECT ProductID, ProductName, SupplierID, CategoryID, UnitPrice
FROM Products
WHERE UnitPrice > ALL (SELECT UnitPrice 
                       FROM Products 
                       WHERE CategoryID = 7)

--OR using MAX(UnitPrice)...
SELECT ProductID, ProductName, SupplierID, CategoryID, UnitPrice
FROM Products
WHERE UnitPrice > (SELECT MAX(UnitPrice) 
                   FROM Products 
                   WHERE CategoryID = 7)

SELECT ProductID, ProductName, SupplierID, CategoryID, UnitPrice
FROM Products
WHERE UnitPrice > ANY (SELECT UnitPrice 
                       FROM Products 
                       WHERE CategoryID = 7)

--OR using MIN(UnitPrice)...
SELECT ProductID, ProductName, SupplierID, CategoryID, UnitPrice
FROM Products
WHERE UnitPrice > (SELECT MIN(UnitPrice) 
                   FROM Products 
                   WHERE CategoryID = 7)