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

/* EXERCISE 3.52:  */

/*****   Art_Course_Database CREATE TABLE statements   *******************************/

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

/* EXERCISE 3.53:  */

/*****   Art_Course_Database INSERT statements   *************************************/

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


/*****   Question 3.54   ******************************************************/

SELECT	 *
FROM		 COURSE
WHERE		Course = 'Adv Pastels';

/*****   Question 3.55   ******************************************************/

SELECT	 CU.CustomerNumber, CustomerLastName, CustomerFirstName,
         Phone, CO.CourseNumber, AmountPaid 
FROM		 CUSTOMER CU JOIN ENROLLMENT EN
				   ON CU.CustomerNumber=EN.CustomerNumber
			   JOIN COURSE CO
				   ON EN.CourseNumber = CO.CourseNumber;

/*****   Question 3.56   ******************************************************/

SELECT	 Course, CourseDate, Fee,
          CustomerLastName, CustomerFirstName, Phone
FROM		 CUSTOMER CU JOIN ENROLLMENT EN
				   ON CU.CustomerNumber=EN.CustomerNumber
			   JOIN COURSE CO
				   ON EN.CourseNumber = CO.CourseNumber
WHERE		Course = 'Adv Pastels'
	AND		CourseDate = '01-OCT-2017';

/*****   Question 3.57   ********************************************************/

SELECT	 Course, CourseDate,
          CustomerLastName, CustomerFirstName, Phone, Fee, AmountPaid
FROM		 CUSTOMER CU JOIN ENROLLMENT EN
  				 ON CU.CustomerNumber=EN.CustomerNumber
	  		 JOIN COURSE CO
				   ON EN.CourseNumber = CO.CourseNumber
WHERE		Course = 'Adv Pastels'
	AND		CourseDate = '01-OCT-2017';



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








