/* ******** Exercise 8.52.N.5 View Defintion  *********************************/

/*  NOTE:  Need to rename view to meet Oracle's 30-character limit on identifiers */

CREATE VIEW HSDDWProdSalesPayTypeView AS
	SELECT	C.CustomerID, C.CustomerName, C.City,
				PT.PaymentType,
				P.ProductNumber, P.ProductName,
				SUM(PS.Total) AS TotalDollarSales
	FROM		CUSTOMER C, PRODUCT_SALES PS,
				PRODUCT P, PAYMENT_TYPE PT
	WHERE		C.CustomerID = PS.CustomerID
		AND	P.ProductNumber = PS.ProductNumber
		AND	PT.PaymentTypeID = PS. PaymentTypeID
	GROUP BY	C.CustomerID, C.CustomerName, C.City,
				P.ProductNumber, P.ProductName, PT.PaymentType;



				
/* ******** Exercise 8.48.N.5 SELECT FROM View *************************************/

SELECT * FROM HSDDWProdSalesPayTypeView;
