/******************************************************************************/
/*																				                                    */
/*	Kroenke, Auer, Vandenberg, and Yoder 					*/
/*	Database Concepts (8th Edition) Appendix E 	       		*/
/*																				                                    */
/*  Garden Glory Database Views and SQL Queries							             		  */
/*																				                                    */
/*	These are the Oracle Express 11g R2 SQL code solutions	                    */
/*																				                                    */
/******************************************************************************/

/*****   Garden_Glory CREATE TABLE statements   *******************************/

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

/*****   Garden_Glory INSERT statements   *************************************/

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

/*****   OWNER DATA   *********************************************************/

SELECT * FROM OWNER;

/*****   EMPLOYEE DATA   ******************************************************/

SELECT * FROM EMPLOYEE;

/*****   PROPERTY DATA   ****************************************************/

SELECT * FROM OWNED_PROPERTY;

/*****   SERVICE DATA   *****************************************************/

SELECT * FROM GG_SERVICE;

/*****   PROPERTY SERVICE DATA   *****************************************************/

SELECT * FROM PROPERTY_SERVICE;

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

ALTER TABLE EMPLOYEE
	ADD Supervisor Int NULL;

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

UPDATE EMPLOYEE
	SET Supervisor = 5 WHERE EmployeeID = 2;

UPDATE EMPLOYEE
	SET Supervisor = 1 WHERE EmployeeID = 3;

UPDATE EMPLOYEE
	SET Supervisor = 1 WHERE EmployeeID = 4;

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

ALTER TABLE EMPLOYEE
	ADD Apprenticeship	Varchar(20) NULL;

UPDATE EMPLOYEE
	SET Apprenticeship = 'Completed' WHERE EmployeeID = 1 OR EmployeeID = 4;

UPDATE EMPLOYEE
	SET Apprenticeship = 'In process' WHERE EmployeeID = 2 OR EmployeeID = 5;

UPDATE EMPLOYEE
	SET Apprenticeship = 'Not started' WHERE EmployeeID = 3;

ALTER TABLE EMPLOYEE
	MODIFY Apprenticeship Varchar(20) NOT NULL;

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

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

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

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

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

SELECT E1.EmployeeID, E1.FirstName, E1.LastName
FROM EMPLOYEE E1
WHERE EXISTS (SELECT *
	      FROM EMPLOYEE E2
	      WHERE E1.FirstName = E2.FirstName AND E1.LastName = E2.LastName
		AND E1.EmployeeID <> E2.EmployeeID);



/*****   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 OwnerPropertyView AS
	       SELECT		O.OwnerID, OwnerName, OwnerType,
				          PropertyID, PropertyName, Street, City, State, Zip
       	 FROM 		OWNER O JOIN OWNED_PROPERTY P
					             ON O.OwnerID = P.OwnerID;

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

CREATE VIEW PropertyServiceView AS
SELECT	P.PropertyID, PropertyName, Street, City, State, ZIP,
			ServiceDate, FirstName, LastName, HoursWorked
FROM 		OWNED_PROPERTY P JOIN PROPERTY_SERVICE PS
		ON P.PropertyID = PS.PropertyID
			JOIN EMPLOYEE E
				ON E.EmployeeID = PS.EmployeeID;

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

/* Renamed view to adhere to Oracle's 30-character limit on
	identifiers */

CREATE VIEW PropServiceFirstNameFirstView AS
		SELECT	P.PropertyID, PropertyName, Street, City, State, ZIP,
					ServiceDate,
					FirstNameFirst(FirstName, LastName) AS EmployeeName, 
					HoursWorked
		FROM 		OWNED_PROPERTY P JOIN PROPERTY_SERVICE PS
				ON		P.PropertyID = PS.PropertyID
				JOIN EMPLOYEE E
				ON		PS.EmployeeID = E.EmployeeID;


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

SELECT	 *
FROM		 OwnerPropertyView
ORDER BY OwnerName;

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

SELECT	 *
FROM		 PropertyServiceView
ORDER BY Zip, State, City;

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

SELECT	 *
FROM		 PropServiceFirstNameFirstView
ORDER BY Zip, State, City;

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

/* NO DATABASE ACTIVITY IN THIS QUESTION */

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

ALTER TABLE TOOLS
	ADD CONSTRAINT TOOLS_PK PRIMARY KEY (ToolID);

ALTER TABLE TOOL_USE
	ADD EmployeeID Int;

UPDATE TOOL_USE
	SET EmployeeID = 2 WHERE UsedBy = 'John Evanston';

UPDATE TOOL_USE
	SET EmployeeID = 4 WHERE UsedBy = 'Jerry Murphy';

UPDATE TOOL_USE
	SET EmployeeID = 5 WHERE UsedBy = 'Joan Fontaine';

UPDATE TOOL_USE
	SET EmployeeID = 1 WHERE UsedBy = 'Sam Smith';

ALTER TABLE TOOL_USE
	ADD CONSTRAINT TOOL_USE_PK PRIMARY KEY (ToolID, EmployeeID, Date_Used);

ALTER TABLE TOOL_USE
	DROP COLUMN UsedBy;


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

ALTER TABLE TOOL_USE
	ADD CONSTRAINT TU_TOOL_FK FOREIGN KEY (ToolID) 
			REFERENCES TOOLS (ToolID);

ALTER TABLE TOOL_USE
	ADD CONSTRAINT TU_EMP_FK FOREIGN KEY (EmployeeID) 
			REFERENCES EMPLOYEE (EmployeeID);

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