/********************************************************************************/
/*                                                                              */
/*  Kroenke, Auer, Vandenberg, Yoder - DBC (8th Edition) Chapter 08             */
/*                                                                              */
/*  The QACS-DW Database Query G and view H                                     */
/*                                                                              */
/*  These are the Oracle Database XE SQL code solutions                  */
/*                                                                              */
/********************************************************************************/
/* ******** Question G Query  ******************************************/

SELECT		C.CustomerName,
			C.City, C.State, C.Zip,
			I.ItemDescription, V.VendorName,
			SUM(SI.ItemSalePrice) AS TotalDollarSales
FROM		CUSTOMER C, SALE_ITEM SI, ITEM I, VENDOR V
WHERE		C.CustomerID = SI.CustomerID
	AND		I.ItemID = SI.ItemID
	AND		V.VendorID = SI.VendorID
GROUP BY	C.CustomerName,
			C.City, C.State, C.Zip,
			I.ItemDescription, V.VendorName
ORDER BY	C.CustomerName,
			C.City, C.State, C.Zip,
			I.ItemDescription, V.VendorName;
			
/* ******** Questions H, I View Defintion  *********************************/

CREATE VIEW QACSDWVendorDollarSalesView AS
	SELECT		C.CustomerName,
				C.City, C.State, C.Zip,
				I.ItemDescription, V.VendorName,
				SUM(SI.ItemSalePrice) AS TotalDollarSales
	FROM		CUSTOMER C, SALE_ITEM SI, ITEM I, VENDOR V
	WHERE		C.CustomerID = SI.CustomerID
		AND		I.ItemID = SI.ItemID
		AND		V.VendorID = SI.VendorID
	GROUP BY	C.CustomerName,
				C.City, C.State, C.Zip,
				I.ItemDescription, V.VendorName;


/* Using the view to help create the OLAP report: */

SELECT * FROM QACSDWVendorDollarSalesView
ORDER BY CustomerName, City, State, Zip, ItemDescription, VendorName;
	
			