/********************************************************************************/
/*																				*/
/*	Kroenke, Auer, Vandenberg, and Yoder 					*/
/*	Database Concepts (8th Edition) Appendix E 	       		*/
/*																				*/
/*  Pet_Database Views and SQL Queries											*/
/*																				*/
/*	These are the Microsoft SQL Server 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 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 AS CO JOIN ENROLLMENT AS E
					ON CO.CourseNumber = E.CourseNumber
				JOIN CUSTOMER AS 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 AS CO JOIN ENROLLMENT AS E
					ON CO.CourseNumber = E.CourseNumber
				JOIN CUSTOMER AS 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 Function  *******************************************************/

CREATE FUNCTION dbo.FirstNameFirst 

-- These are the input parameters
(
	@FirstName		CHAR(25),
	@LastName		CHAR(25)
)
RETURNS VARCHAR(60)
AS
BEGIN
	-- This is the variable that will hold the value to be returned
	DECLARE @FullName VARCHAR(60)

	-- SQL statements to concatenate the names in the proper order
	SELECT @FullName = RTRIM(@FirstName) + ' ' + RTRIM(@LastName);
	
	-- Return the concatentated name
	RETURN @FullName
END

/******  Test Function  *********************************************************/

SELECT		dbo.FirstNameFirst(CustomerFirstName, CustomerLastName) AS CustomerName, 
			Phone
FROM		CUSTOMER
ORDER BY	CustomerName;


/*****   Question E.53   ********************************************************/
/******  Create View  ***********************************************************/

CREATE VIEW	CourseEnrollmentFirstNameFirstView AS
	SELECT		CR.CourseNumber, Course, CourseDate,
				C.CustomerNumber,
				dbo.FirstNameFirst(CustomerFirstName, CustomerLastName) AS CustomerName, 
				Phone
	FROM		CUSTOMER AS C JOIN ENROLLMENT AS E
		ON		C.CustomerNumber = E.CustomerNumber
				JOIN COURSE as CR
		ON		E.CourseNumber = CR.CourseNumber;
	
	
/*****   Question E.54   ********************************************************/
/******  Query View *************************************************************/

SELECT		*
FROM		CourseEnrollmentFirstNameFirstView
ORDER BY	CustomerName, CourseNumber;

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