/********************************************************************************/
/*																				*/
/*	Kroenke, Auer, Vandenberg, and Yoder 					*/
/*	Database Concepts (8th Edition) Chapter 03 	       		*/
/*																				*/
/*  Art_Course_Database Queries													*/
/*																				*/
/*	These are the Microsoft SQL Server 2014/2016 SQL code solutions				*/
/*																				*/
/********************************************************************************/

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

/*       See the files:	 DBC-e08-MSSQL-Art-Course-Database-Create-Tables.sql	*/

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

/*       See the files:	 DBC-e08-MSSQL-Art-Course-Database-Insert-Data.sql		*/

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

SELECT * FROM CUSTOMER;

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

SELECT * FROM COURSE;

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

SELECT * FROM ENROLLMENT;

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

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

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

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

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

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

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

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


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











