/******************************************************************************/
/*																				                                    */
/*	Kroenke, Auer, Vandenberg, and Yoder Database Concepts (8th Edition) Appendix E		*/
/*	Database Concepts (8th Edition) Appendix E				*/
/*																				                                    */
/*  James River Jewelry Database Project Questions				          			*/
/*																				                                    */
/*	These are the Oracle Express 11gR2 SQL code solutions	                    */
/*																				                                    */
/******************************************************************************/

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

/*       See the file:	 DBC-e08-ODB-JRJ-Create-Tables.sql 	                  */

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

/*       See the file:	 DBC-e08-ODB-JRJ-Insert-Data.sql	                  	*/

/*****   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
	MODIFY 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 OR REPLACE FUNCTION FirstNameFirst
	-- These are the input parameters
		(
		 varFirstName	IN Char,
		 varLastName	IN char
		)
	-- This is the variable that will hold the returned value
	RETURN 		Varchar
	IS varFullName	Varchar(60);

BEGIN

	-- SQL statement to concatenate the names in the proper order
	varFullName := (RTRIM(varFirstName) || ' ' || RTRIM(varLastName));
	-- Return the concatenated name
	RETURN varFullName;

END;
/


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

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

/*****   Question I.2   *****************************************************/
/* View name changed to be short enough for Oracle */

CREATE VIEW CustFirstNameFirstPurchaseView AS
      	 SELECT		 C.CustomerID, FirstNameFirst (FirstName, LastName) AS CustomerName,
				            InvoiceNumber, InvoiceDate, PreTaxAmount
	       FROM 		  CUSTOMER C JOIN PURCHASE 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 P JOIN PURCHASE_ITEM PI
					              ON P.InvoiceNumber = PI.InvoiceNumber
				            JOIN ITEM I
					              ON PI.ItemNumber = I.ItemNumber;

/*****   Question I.4   *****************************************************/
/* View name changed to be short enough for Oracle */

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

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

SELECT	 *
FROM		 CustomerPurchaseView
ORDER BY LastName, FirstName;


/*****   Question J.2  *****************************************************/
/* View name changed to be short enough for Oracle */

SELECT	 *
FROM		 CustFirstNameFirstPurchaseView
ORDER BY CustomerName;

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

SELECT	 *
FROM		 PurchaseItemItemView
ORDER BY InvoiceNumber, ItemNumber;


/*****   Question J.4   *****************************************************/
/* View name changed to be short enough for Oracle */

SELECT	 *
FROM		 PurItemItemFirstNameFirstView
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
			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);

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