/********************************************************************************/
/*																				*/
/*	Kroenke, Auer, Vandenberg, Yoder - DBC (8th Edition) Chapter 08				*/
/*																				*/
/*	The QACS-DW Database Query G and view H										*/
/*																				*/
/*	These are the Microsoft SQL Server 2016 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;
			
/* ******** Guestion H View Defintion  *********************************/

CREATE VIEW QACSDWVenodrDollarSalesView 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;


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