/********************************************************************************/
/*																				*/
/*	Kroenke, Auer, Vandenberg, and Yoder 					*/
/*	Database Concepts (8th Edition) Appendix E 	       		*/
/*																				*/
/*	Heather Sweeny Designs database	- Appendix E							*/
/*																				*/
/*	These are the Microsoft SQL Server 2014/2016 SQL code solutions				*/
/*																				*/
/********************************************************************************/

/*****   App E Case Question A   ************************************************/

/*** Add the column and foreign key and unique constraints ***/

ALTER TABLE CUSTOMER
	ADD ReferredBy Int NULL;

ALTER TABLE CUSTOMER 
	ADD CONSTRAINT CUST_FK_CUST FOREIGN KEY(ReferredBy) REFERENCES
		CUSTOMER (CustomerID);

/*** Populate the column ***/

UPDATE CUSTOMER
	SET ReferredBy = 1 WHERE CustomerID = 2;

UPDATE CUSTOMER
	SET ReferredBy = 3 WHERE CustomerID = 4;

UPDATE CUSTOMER
	SET ReferredBy = 4 WHERE CustomerID = 5;

UPDATE CUSTOMER
	SET ReferredBy = 7 WHERE CustomerID = 7;

UPDATE CUSTOMER
	SET ReferredBy = 6 WHERE CustomerID = 8;

UPDATE CUSTOMER
	SET ReferredBy = 9 WHERE CustomerID = 10;

UPDATE CUSTOMER
	SET ReferredBy = 2 WHERE CustomerID = 12;

/*****   App E Case Question B   ************************************************/

/*** Add the column as NULL allowed ***/

ALTER TABLE SEMINAR_CUSTOMER
	ADD Attended Varchar(20) NULL;

/*** Populate the column ***/

UPDATE SEMINAR_CUSTOMER
	SET Attended = 'Attended';

UPDATE SEMINAR_CUSTOMER
	SET Attended = 'Did not attend' WHERE SeminarID = 1 AND CustomerID = 2;

UPDATE SEMINAR_CUSTOMER
	SET Attended = 'Did not attend' WHERE SeminarID = 4 AND CustomerID = 7;

/*** ALTER column to NOT NULL ***/

ALTER TABLE SEMINAR_CUSTOMER 
	ALTER COLUMN Attended Varchar(20) NOT NULL;


/*****   App E Case Question D   ************************************************/

ALTER TABLE SEMINAR_CUSTOMER
	ADD CONSTRAINT AttendedCheck CHECK (Attended IN ('Attended','Did not attend'));

/*****   App E Case Question E   ************************************************/

ALTER TABLE SEMINAR_CUSTOMER
	ALTER COLUMN Attended Varchar(20) NULL;

/*****   App E Case Question F   ************************************************/

SELECT 	C.FirstName AS CustomerFirstName, C.LastName AS CustomerLastName, 
	R.FirstName AS ReferrerFirstName, R.LastName AS ReferrerLastName
FROM	CUSTOMER C JOIN CUSTOMER R
	ON C.ReferredBy = R.CustomerID;

/*****   App E Case Question G   ************************************************/

SELECT 	C.FirstName AS CustomerFirstName, C.LastName AS CustomerLastName, 
	R.FirstName AS ReferrerFirstName, R.LastName AS ReferrerLastName
FROM	CUSTOMER C LEFT JOIN CUSTOMER R
	ON C.ReferredBy = R.CustomerID;

/*****   App E Case Question H   ************************************************/
/******  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 concatentate name
	RETURN @FullName
END

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

SELECT		dbo.FirstNameFirst(FirstName, LastName) AS CustomerName, 
			Phone
FROM		CUSTOMER
ORDER BY	CustomerName;

/*****   App E Case Question I.1   **********************************************/

CREATE VIEW CustomerSeminarView AS
	SELECT	C.CustomerID, C.LastName, C.FirstName,
			C.EmailAddress, City, State, ZIP,
			S.SeminarDate, S.Location, S.SeminarTitle
	FROM	CUSTOMER C, SEMINAR_CUSTOMER SC, SEMINAR S
	WHERE	C.CustomerID = SC.CustomerID
		AND SC.SeminarID = S.SeminarID;

/*****   App E Case Question I.2   **********************************************/

CREATE VIEW CustomerFirstNameFirstSeminarView AS
	SELECT	C.CustomerID,
			dbo.FirstNameFirst(FirstName, LastName) AS CustomerName, 
			C.EmailAddress, City, State, ZIP,
			S.SeminarDate, S.Location, S.SeminarTitle
	FROM	CUSTOMER C, SEMINAR_CUSTOMER SC, SEMINAR S
	WHERE	C.CustomerID = SC.CustomerID
		AND SC.SeminarID = S.SeminarID;

/*****   App E Case Question I.3   **********************************************/

CREATE VIEW CustomerProductView AS
	SELECT	C.CustomerID, C.LastName, C.FirstName,
			C.EmailAddress,
			I.InvoiceNumber, I.InvoiceDate,
			P.ProductNumber, P.ProductDescription
	FROM	CUSTOMER C, INVOICE I, LINE_ITEM LI, PRODUCT P
	WHERE	C.CustomerID = I.CustomerID
		AND I.InvoiceNumber = LI.InvoiceNumber
		AND LI.ProductNumber = P.ProductNumber;

/*****   App E Case Question I.4   **********************************************/

CREATE VIEW CustomerFirstNameFirstProductView AS
	SELECT	C.CustomerID,
			dbo.FirstNameFirst(FirstName, LastName) AS CustomerName, 
			C.EmailAddress,
			I.InvoiceNumber, I.InvoiceDate,
			P.ProductNumber, P.ProductDescription
	FROM	CUSTOMER C, INVOICE I, LINE_ITEM LI, PRODUCT P
	WHERE	C.CustomerID = I.CustomerID
		AND I.InvoiceNumber = LI.InvoiceNumber
		AND LI.ProductNumber = P.ProductNumber;

/*****   App E Case Question J.1   **********************************************/

SELECT		*
FROM		CustomerSeminarView
ORDER BY	State DESC, City DESC, ZIP DESC;

/*****   App E Case Question J.2   **********************************************/

SELECT		*
FROM		CustomerFirstNameFirstSeminarView
ORDER BY	State DESC, City DESC, ZIP DESC;

/*****   App E Case Question J.3   **********************************************/

SELECT		*
FROM		CustomerSeminarView
ORDER BY	Location, SeminarDate, SeminarTitle;

/*****   App E Case Question J.4   **********************************************/

SELECT		*
FROM		CustomerFirstNameFirstSeminarView
ORDER BY	Location, SeminarDate, SeminarTitle;

/*****   App E Case Question J.5   **********************************************/

SELECT		*
FROM		CustomerProductView
ORDER BY	LastName, FirstName, InvoiceNumber, ProductNumber;

/*****   App E Case Question J.6   **********************************************/

SELECT		*
FROM		CustomerFirstNameFirstProductView
ORDER BY	CustomerName, InvoiceNumber, ProductNumber;

/*****   App E Case Question K.2   ********************************************/

SELECT * FROM EMPLOYEE_TEMP;

/*****   App E Case Question K.3   ********************************************/

CREATE TABLE EMPLOYEE (
	EmployeeNumber		Int		NOT NULL IDENTITY(1, 1),
	FirstName		CHAR(25)	NOT NULL,
	LastName		CHAR(25)	NOT NULL,
	Supervisor		Int		NULL,
	OfficePhone		CHAR(12)	NULL,
	EmailAddress		CHAR(100)	NOT NULL,
	CONSTRAINT 		EMP_PK 		PRIMARY KEY (EmployeeNumber),
	CONSTRAINT		EMP_EMP_FK	FOREIGN KEY (Supervisor) REFERENCES
				EMPLOYEE (EmployeeNumber)
);

/*****   App E Case Question K.4   ********************************************/

INSERT INTO EMPLOYEE
	(FirstName, LastName, Supervisor, OfficePhone, EmailAddress)
	SELECT FirstName, LastName, NULL, OfficePhone, EmailAddress
	FROM EMPLOYEE_TEMP;

SELECT * FROM EMPLOYEE;

/*****   App E Case Question K.5   ********************************************/

UPDATE EMPLOYEE 
	SET Supervisor = 1 WHERE EmployeeNumber = 2;

UPDATE EMPLOYEE 
	SET Supervisor = 1 WHERE EmployeeNumber = 3;

UPDATE EMPLOYEE 
	SET Supervisor = 4 WHERE EmployeeNumber = 5;

/*****   App E Case Question K.6   ********************************************/

SELECT E.FirstName AS EmployeeFirstName, E.LastName AS EmployeeLastName,
	S.FirstName AS SupervisorFirstName, S.LastName AS SupervisorLastName
FROM EMPLOYEE E LEFT JOIN EMPLOYEE S
	ON E.Supervisor = S.EmployeeNumber;


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

