/********************************************************************************/
/*																				*/
/*	Kroenke, Auer, Vandenberg, Yoder - DBC (8th Edition) Chapter 08				*/
/*																				*/
/*  JRJ-DW Database Case Questions G & H														*/
/*																				*/
/*	These are the Microsoft SQL Server 2016 SQL code solutions					*/
/*																				*/
/********************************************************************************/
-- cut and paste to run them separately
/* ******** 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;

/* ******** Guestion H 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;
	

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