/********************************************************************************/
/*                                                                              */
/*  Kroenke, Auer, Vandenberg, Yoder - DBC (8th Edition) Chapter 08             */
/*                                                                              */
/*  JRJ-DW Database Case Questions G & H                                                        */
/*                                                                              */
/*  These are the Oracle Database XE SQL code solutions                  */
/*                                                                              */
/********************************************************************************/

/* ******** Question G Query  ******************************************/

SELECT		A.ArtistName,
			I.ItemType, I.ItemMaterial,
			SUM(PP.PurchasePrice) AS TotalDollarSales
FROM		ARTIST A, PURCHASE_PRICE PP, ITEM I
WHERE		A.ArtistID = PP.ArtistID
	AND		I.ItemID = PP.ItemID
GROUP BY	A.ArtistName,
			I.ItemType, I.ItemMaterial
ORDER BY	A.ArtistName,
			I.ItemType, I.ItemMaterial;

/* ******** Questions H, I View Defintion  *********************************/

CREATE VIEW JRJDWProductDollarSalesView AS
	SELECT		A.ArtistName,
				I.ItemType, I.ItemMaterial,
				SUM(PP.PurchasePrice) AS TotalDollarSales
	FROM		ARTIST A, PURCHASE_PRICE PP, ITEM I
	WHERE		A.ArtistID = PP.ArtistID
		AND		I.ItemID = PP.ItemID
	GROUP BY	A.ArtistName,
				I.ItemType, I.ItemMaterial;

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

SELECT * FROM JRJDWProductDollarSalesView
ORDER BY ArtistName, ItemType, ItemMaterial;