/********************************************************************************/
/*																				*/
/*	Kroenke, Auer, Vandenberg, and Yoder 			*/
/*	Database Concepts (8th Edition) Appendix E				*/
/*																				*/
/*  James River Jewelry Database Project Questions							*/
/*																				*/
/*	These are the Microsoft SQL Server 2014/2016 SQL code solutions				*/
/*																				*/
/********************************************************************************/

/*****   James_River_Jewelry CREATE TABLE statements   **************************/

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

/*****   James_River_Jewelry INSERT statements   ********************************/

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

USE JRJ
GO

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

SELECT * FROM CUSTOMER;

/*****   ITEM DATA  *************************************************************/

SELECT * FROM ITEM;

/*****   PURCHASE DATA   ********************************************************/

SELECT * FROM PURCHASE;

/*****   PURCHASE_ITEM   ********************************************************/

SELECT * FROM PURCHASE_ITEM;

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

SELECT * FROM CUSTOMER;

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

ALTER TABLE ITEM
	ADD ArtistHasBeenPaid	Varchar(25) NULL;

UPDATE ITEM
	SET ArtistHasBeenPaid = 'Paid'; 

UPDATE ITEM
	SET ArtistHasBeenPaid = 'In process' 
		WHERE ItemNumber IN (11,13,14,16); 

UPDATE ITEM
	SET ArtistHasBeenPaid = 'Waiting for invoice' 
		WHERE ItemNumber IN (15,17,18,19); 

ALTER TABLE ITEM
	ALTER COLUMN ArtistHasBeenPaid Varchar(25) NOT NULL;

/*****   Question D  ***********************************************************/

ALTER TABLE ITEM
	ADD CONSTRAINT ArtistPaidCheck CHECK
		(ArtistHasBeenPaid 
			IN ('Waiting for invoice', 'In process', 'Paid'));

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

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

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

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 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



/*****   Question I.1   *********************************************************/

CREATE VIEW CustomerPurchaseView AS
	SELECT		C.CustomerID, LastName, FirstName,
				InvoiceNumber, InvoiceDate, PreTaxAmount
	FROM 		CUSTOMER AS C JOIN PURCHASE AS P
			ON	C.CustomerID = P.CustomerID;


/*****   Question I.2   *********************************************************/

CREATE VIEW CustomerPurchaseFirstNameFirstView AS
	SELECT		C.CustomerID,
				dbo.FirstNameFirst(FirstName, LastName) AS CustomerName, 
				InvoiceNumber, InvoiceDate, PreTaxAmount
	FROM 		CUSTOMER AS C JOIN PURCHASE AS P
			ON	C.CustomerID = P.CustomerID;

/*****   Question I.3   *********************************************************/

CREATE VIEW PurchaseItemItemView AS
	SELECT		P.InvoiceNumber, PI.ItemNumber,
				ArtistLastName, ArtistFirstName,
				ItemDescription, Cost, RetailPrice
	FROM 		PURCHASE AS P JOIN PURCHASE_ITEM AS PI
		ON		P.InvoiceNumber = PI.InvoiceNumber
				JOIN ITEM AS I
		ON		PI.ItemNumber = I.ItemNumber;

/*****   Question I.4   *********************************************************/

CREATE VIEW PurchaseItemItemFirstNameFirstView AS
	SELECT		P.InvoiceNumber, PI.ItemNumber,
				dbo.FirstNameFirst(ArtistFirstName, ArtistLastName) AS ArtistName,
				ItemDescription, Cost, RetailPrice
	FROM 		PURCHASE AS P JOIN PURCHASE_ITEM AS PI
		ON		P.InvoiceNumber = PI.InvoiceNumber
				JOIN ITEM AS I
		ON		PI.ItemNumber = I.ItemNumber;

/*****   Question J.1   *********************************************************/

SELECT		*
FROM		CustomerPurchaseView
ORDER BY	LastName, FirstName;

/*****   Question J.2   *********************************************************/

SELECT		*
FROM		CustomerPurchaseFirstNameFirstView
ORDER BY	CustomerName;

/*****   Question J.3   *********************************************************/

SELECT		*
FROM		PurchaseItemItemView
ORDER BY	InvoiceNumber, ItemNumber;

/*****   Question J.4   *********************************************************/

SELECT		*
FROM		PurchaseItemItemFirstNameFirstView
ORDER BY	InvoiceNumber, ItemNumber;

/*****   Question J.5   *********************************************************/

SELECT		InvoiceNumber,
			SUM(Cost) AS TotalItemCost,
			SUM(RetailPrice) AS TotalRetailSales
FROM		PurchaseItemItemView
GROUP BY	InvoiceNumber;

/*****   Question K.1  *******************************************************/

/* NO DATABASE ACTIVITY IN THIS QUESTION */

/*****   Question K.2  *******************************************************/

ALTER TABLE APPRAISALS
			ALTER COLUMN ItemNumber INT NOT NULL;

ALTER TABLE APPRAISALS
			ALTER COLUMN AppraisalDate DateTime NOT NULL;

ALTER TABLE APPRAISALS
	ADD CONSTRAINT APPRAIALS_PK PRIMARY KEY (ItemNumber, 
				AppraisalDate);

SELECT * FROM APPRAISALS;

/*****   Question K.3 *******************************************************/

ALTER TABLE APPRAISALS
			ADD CONSTRAINT APPR_FK_ITEM FOREIGN KEY (ItemNumber)
				REFERENCES ITEM(ItemNumber);


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