/* ******** Exercise 8.52.I Query  ******************************************/

SELECT		C.CustomerID, C.CustomerName, C.City,
			P.ProductNumber, P.ProductName,
			SUM(PS.Total) AS TotalDollarSales
FROM		CUSTOMER C, PRODUCT_SALES PS, PRODUCT P
WHERE		C.CustomerID = PS.CustomerID
	AND		P.ProductNumber = PS.ProductNumber
GROUP BY	C.CustomerID, C.CustomerName, C.City,
			P.ProductNumber, P.ProductName
ORDER BY	C.CustomerID, P.ProductNumber;

/* ******** Exercise 8.52.J View Defintion  *********************************/

CREATE VIEW HSDDWProductDollarSalesView AS
	SELECT		C.CustomerID, C.CustomerName, C.City,
				P.ProductNumber, P.ProductName,
				SUM(PS.Total) AS TotalDollarSales
	FROM		CUSTOMER C, PRODUCT_SALES PS, PRODUCT P
	WHERE		C.CustomerID = PS.CustomerID
		AND		P.ProductNumber = PS.ProductNumber
	GROUP BY	C.CustomerID, C.CustomerName, C.City,
				P.ProductNumber, P.ProductName;
				
/* ******** Exercise 8.52.J View Query  *********************************/

SELECT * FROM HSDDWProductDollarSalesView
ORDER BY CustomerID, ProductNumber;

