/********************************************************************************/
/*												*/
/*	Kroenke, Auer, Vandenberg, and Yoder 					*/
/*       Database Concepts (8th Edition) AppE						*/
/*																				*/
/*  The Queen Ann Curiosity Shop [QACS] Database Views and Queries				*/
/*																				*/
/*	These are the Microsoft SQL Server 2014/2016 SQL code solutions				*/
/*																				*/
/********************************************************************************/

/*****   QACS CREATE TABLE statements   *****************************************/

/*       See the files:	 DBC-e08-MSSQL-QACS-Create-Tables.sql					*/

/*****   QACS INSERT statements   ***********************************************/

/*       See the files:	 DBC-e08-MSSQL-QACS-Insert-Data.sql						*/

USE QACS
GO

/*****   Question A   ***********************************************************/

/*** Add the column and foreign key constraint ***/

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 = 2 WHERE CustomerID = 4;

UPDATE CUSTOMER
	SET ReferredBy = 3 WHERE CustomerID = 5;

UPDATE CUSTOMER
	SET ReferredBy = 4 WHERE CustomerID = 7;

UPDATE CUSTOMER
	SET ReferredBy = 5 WHERE CustomerID = 8;

UPDATE CUSTOMER
	SET ReferredBy = 6 WHERE CustomerID = 9;

UPDATE CUSTOMER
	SET ReferredBy = 7 WHERE CustomerID = 10;

/*****   Question B   ***********************************************************/

/*** Add the column and foreign key constraint ***/

ALTER TABLE EMPLOYEE
	ADD Supervisor Int NULL;

ALTER TABLE EMPLOYEE
	ADD CONSTRAINT EMP_EMP_FK FOREIGN KEY (Supervisor)
		REFERENCES EMPLOYEE (EmployeeID);

/*** Populate the column ***/

UPDATE EMPLOYEE
	SET Supervisor = 1 WHERE EmployeeID = 2 OR EmployeeID = 3;

UPDATE EMPLOYEE
	SET Supervisor = 3 WHERE EmployeeID = 4 OR EmployeeID = 5;

/*****   Question C   ***********************************************************/

ALTER TABLE EMPLOYEE
	ADD Training	Varchar(20) NULL;

UPDATE EMPLOYEE
	SET Training = 'Completed' WHERE EmployeeID BETWEEN 1 AND 3; 

UPDATE EMPLOYEE
	SET Training = 'In process' WHERE EmployeeID BETWEEN 4 AND 5; 

ALTER TABLE EMPLOYEE
	ALTER COLUMN Training Varchar(20) NOT NULL;

/*****   Question E   ***********************************************************/

ALTER TABLE EMPLOYEE
	ADD CONSTRAINT TrainingCheck CHECK
		(Training IN ('Completed', 'In process', 'Not started'));

/*****   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;

/*****   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;

/*****   Question H   ***********************************************************/

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

/*****   Question I   ***********************************************************/

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.EmployeeID;

/*****   Question J   ***********************************************************/

SELECT C1.CustomerID, C1.FirstName, C1.LastName
FROM CUSTOMER C1
WHERE EXISTS (SELECT *
	      FROM CUSTOMER C2
	      WHERE C1.FirstName = C2.FirstName AND 
				C1.LastName = C2.LastName AND 
				C1.CustomerID <> C2.CustomerID);

/*****   Question K   ***********************************************************/
/******  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;

/*****   Question L.1   *********************************************************/

CREATE VIEW BasicCustomerView AS
	SELECT		CustomerID, LastName, FirstName, Phone, EmailAddress
	FROM 		CUSTOMER;

/*****   Question L.2   *********************************************************/

CREATE VIEW BasicCustomerFirstNameFirstView AS
	SELECT		CustomerID,
				dbo.FirstNameFirst(FirstName, LastName) AS CustomerName, 
				Phone, EmailAddress
	FROM 		CUSTOMER;

/*****   Question L.3   *********************************************************/

CREATE VIEW SaleItemItemView AS
	SELECT		S.SaleID, SI.SaleItemID, SI.ItemID, SaleDate,
				ItemDescription, ItemCost,
				I.ItemPrice AS ListItemPrice,
				SI.ItemPrice AS ActualItemPrice
	FROM 		SALE AS S JOIN SALE_ITEM AS SI
					ON S.SaleID = SI.SaleID
				JOIN ITEM AS I
					ON SI.ItemID = I.ItemID;

/*****   Question M.1   *********************************************************/

SELECT		*
FROM		BasicCustomerView
ORDER BY	LastName, FirstName;

/*****   Question M.2   *********************************************************/

SELECT		*
FROM		BasicCustomerFirstNameFirstView
ORDER BY	CustomerName;

/*****   Question M.3   *********************************************************/

SELECT		*
FROM		SaleItemItemView
ORDER BY	SaleID, SaleItemID;

/*****   Question M.4   *********************************************************/

SELECT		SaleID,
			SUM(ActualItemPrice) AS TotalPretaxRetailSales
FROM		SaleItemItemView
GROUP BY	SaleID;

/*****   Question N.1  *******************************************************/

/* NO DATABASE ACTIVITY IN THIS QUESTION */

/*****   Question N.2  *******************************************************/

ALTER TABLE MERCHANDISE
	ALTER COLUMN ItemNumber INT NOT NULL;

ALTER TABLE MERCHANDISE
	ALTER COLUMN VendorID INT NOT NULL;

ALTER TABLE MERCHANDISE
	ALTER COLUMN QuantityOnHand INT NOT NULL;

ALTER TABLE MERCHANDISE
	ALTER COLUMN QuantityOnOrder INT NOT NULL;

ALTER TABLE MERCHANDISE
	ADD CONSTRAINT MERCH_PK PRIMARY KEY (ItemNumber);

/*****   Question N.3 *******************************************************/

ALTER TABLE MERCHANDISE
	ADD CONSTRAINT MERCH_FK_VENDOR FOREIGN KEY (VendorID)
		REFERENCES VENDOR(VendorID);

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