/***************************************************************************/
/*																				                                    */
/*	Kroenke, Auer, Vandenberg, and Yoder 					*/
/*	Database Concepts (8th Edition) Appendix E 	       		*/
/*																				                                    */
/*  Art_Course_Database Views and SQL Queries			                   					*/
/*																				                                    */
/*	These are the Oracle Express 11g R2 SQL code solutions	     		        	*/
/*																			                                    	*/
/******************************************************************************/

/*****   Art Course Database CREATE TABLE statements   *******************************/

/*       See the file:	 DBC-e0-ODB-Art-Course-Database-Create-Tables.sql	  */

/*****   Art Course Database INSERT statements   *************************************/

/*       See the file:	 DBC-e08-ODB-Art-Course-Database-Insert-Data.sql		*/

/*****   CUSTOMER DATA   ******************************************************/

SELECT * FROM CUSTOMER;

/*****   COURSE DATA   ********************************************************/

SELECT * FROM COURSE;

/*****   ENROLLMENT DATA   ****************************************************/

SELECT * FROM ENROLLMENT;

/*****   Question E.44   ******************************************************/

CREATE VIEW CourseView AS
	SELECT 	 CourseNumber, Course, Fee
	FROM 		 COURSE;

/*****   Question E.45   ******************************************************/

SELECT 		 *
FROM 		   CourseView
ORDER BY	  CourseNumber;

/*****   Question E.46   ******************************************************/

CREATE VIEW CourseEnrollmentView AS
	SELECT 	  CO.CourseNumber, Course, CourseDate,
				    CU.CustomerNumber, CustomerLastName, CustomerFirstName, Phone
	FROM 		  COURSE CO JOIN ENROLLMENT E
					    ON CO.CourseNumber = E.CourseNumber
				    JOIN CUSTOMER CU
					    ON E.CustomerNumber = CU.CustomerNumber;

/*****   Question E.47   ******************************************************/

SELECT 		 *
FROM 		   CourseEnrollmentView
WHERE		   Course = 'Adv Pastels'
	AND		   CourseDate = '01-OCT-17'
ORDER BY	  CustomerLastName;

/*****   Question E.48  *****************************************************/

SELECT 		 *
FROM 		   CourseEnrollmentView
WHERE		   Course = 'Beg Oils'
	AND		   CourseDate = '15-OCT-17'
ORDER BY	  CustomerLastName;

/*****   Question E.49  *****************************************************/

CREATE VIEW CourseFeeOwedView AS
	SELECT 	 CO.CourseNumber, Course, CourseDate,
				    CU.CustomerNumber, CustomerLastName, CustomerFirstName, Phone,
				    Fee, AmountPaid, (Fee- AmountPaid) AS AmountOwed
	FROM 		 COURSE CO JOIN ENROLLMENT E
					    ON CO.CourseNumber = E.CourseNumber
				    JOIN CUSTOMER CU
					    ON E.CustomerNumber = CU.CustomerNumber;

/*****   Question E.50  ******************************************************/

SELECT 		  *
FROM 		    CourseFeeOwedView
ORDER BY	  CustomerLastName;

/*****   Question E.51  ******************************************************/

SELECT		  *
FROM		    CourseFeeOwedView
WHERE		    AmountOwed > 0
ORDER BY	  CustomerLastName;

/*****   Question E.52  ******************************************************/

CREATE OR REPLACE FUNCTION FirstNameFirst
	-- These are the input parameters
		(
		 varFirstName	IN Char,
		 varLastName	IN char
		)
	-- This is the variable that will hold the returned value
	RETURN 		Varchar
	IS varFullName	Varchar(60);

BEGIN

	-- SQL statement to concatenate the names in the proper order
	varFullName := (RTRIM(varFirstName) || ' ' || RTRIM(varLastName));
	-- Return the concatenated name
	RETURN varFullName;

END;
/

/*****   Question E.53  ******************************************************/

/* Oracle prohibits identifiers over 30 characaters long, so
	view is renamed */

CREATE VIEW CourseEnrollFirstNameFirstView AS
	SELECT CO.CourseNumber, Course, CourseDate,
		CU.CustomerNumber, FirstNameFirst (CustomerFirstName,
		CustomerLastName) AS CustomerName, Phone
	FROM COURSE CO, CUSTOMER CU, ENROLLMENT E
	WHERE CO.CourseNumber = E.CourseNumber AND
		E.CustomerNumber = CU.CustomerNumber;

/*****   Question E.54  ******************************************************/

/* Oracle prohibits identifiers over 30 characaters long, so
	view is renamed */

SELECT * 
FROM CourseEnrollFirstNameFirstView
ORDER BY CustomerName, CourseNumber;

/******************************************************************************/