/********************************************************************************/
/*                                                                              */
/*  Kroenke, Auer, Vandenberg & Yoder                                           */
/*  Database Concepts (8th Edition) Chapter 08                                  */
/*                                                                              */
/*  Marcia's Dry Cleaning Data Warehouse [MDC-DW] Case questions                */
/*                                                                              */
/*  Oracle Database XE code solutions                                    */
/*                                                                              */
/********************************************************************************/

/***  Chapter 8 - MDC-Problems   ************************************************/

/* QUESTION I */

SELECT * FROM INVOICE_ITEM;

SELECT		S.ServiceID, S.ServiceDescription,
			T.MonthText, T.QuarterText, T.Year,
			SUM(II.ExtendedPrice) AS TotalGrossIncome
FROM		INVOICE_ITEM II, SERVICE S, TIMELINE T
WHERE		II.ServiceID = S.ServiceID
	AND		II.TimeID = T.TimeID
GROUP BY	S.ServiceID, S.ServiceDescription, T.MonthText, T.QuarterText, T.Year
ORDER BY	S.ServiceID, S.ServiceDescription, T.MonthText, T.QuarterText, T.Year;

/* QUESTIONS J and K */

/*  Note: must limit view name to 30 characters in Oracle */

CREATE VIEW MDCDWServiceGrossIncomeView AS
	SELECT		S.ServiceID, S.ServiceDescription,
				T.MonthText, T.QuarterText, T.Year,
				SUM(II.ExtendedPrice) AS TotalGrossIncome
	FROM		INVOICE_ITEM II, SERVICE S, TIMELINE T
	WHERE		II.ServiceID = S.ServiceID
		AND		II.TimeID = T.TimeID
	GROUP BY	S.ServiceID, S.ServiceDescription, T.MonthText, T.QuarterText, T.Year;

/* QUESTION M:  Using the view to display data that will then be exported to create the OLAP report */

SELECT		*
FROM		MDCDWServiceGrossIncomeView
ORDER BY	ServiceID, ServiceDescription, MonthText, QuarterText, Year;



