/********************************************************************************/
/*																				*/
/*	Kroenke, Auer, Vandenberg, and Yoder 					*/
/*	Database Concepts (8th Edition) Chapter 03 	       		*/
/*																				*/
/*  Garden Glory Database Queries												*/
/*																				*/
/*	These are the Microsoft SQL Server 2016 SQL code solutions				*/
/*																				*/
/********************************************************************************/

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

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

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

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

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

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

SELECT		LastName, FirstName, CellPhone
FROM		EMPLOYEE
WHERE		ExperienceLevel = 'Master';

/*****   Question F   ***********************************************************/

SELECT		LastName, FirstName, CellPhone
FROM		EMPLOYEE
WHERE		ExperienceLevel = 'Master'
	AND		FirstName LIKE 'J%';

/*****   Question G   ***********************************************************/

SELECT		LastName, FirstName, CellPhone
FROM		EMPLOYEE
WHERE		EmployeeID IN
			(SELECT EmployeeID
			 FROM 	PROPERTY_SERVICE
			 WHERE 	PropertyID IN
						(SELECT	PropertyID
						 FROM	OWNED_PROPERTY
						 WHERE	City = 'Seattle'));

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

SELECT		E.LastName, E.FirstName, E.CellPhone
FROM		EMPLOYEE as E JOIN PROPERTY_SERVICE as PS
	ON 		E.EmployeeID = PS.EmployeeID
			JOIN OWNED_PROPERTY as OP
	ON 		PS.PropertyID = OP.PropertyID
WHERE		OP.City = 'Seattle';


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

SELECT		LastName, FirstName, CellPhone
FROM		EMPLOYEE
WHERE		EmployeeID IN
			(SELECT 	EmployeeID
			 FROM 		PROPERTY_SERVICE
			 WHERE 		PropertyID IN
						(SELECT		PropertyID
						 FROM		OWNED_PROPERTY
						 WHERE		OwnerID IN
									(SELECT		OwnerID
									 FROM		OWNER
									 WHERE		OwnerType = 'Corporation')));

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

SELECT		DISTINCT E.LastName, E.FirstName, E.CellPhone
FROM		EMPLOYEE as E JOIN PROPERTY_SERVICE as PS
	ON		E.EmployeeID = PS.EmployeeID
			JOIN OWNED_PROPERTY as OP
	ON		PS.PropertyID = OP.PropertyID
			JOIN OWNER as O
	ON		OP.OwnerID = O.OwnerID
WHERE		O.OwnerType = 'Corporation';

/*****   Question K   ***********************************************************/

SELECT		LastName, FirstName, CellPhone, SUM(HoursWorked) AS TotalHoursWorked
FROM		EMPLOYEE JOIN PROPERTY_SERVICE
	ON		EMPLOYEE.EmployeeID = PROPERTY_SERVICE.EmployeeID
GROUP BY	LastName, FirstName, CellPhone;

/*****   Question L   ***********************************************************/

SELECT		ExperienceLevel, SUM(HoursWorked) AS TotalHoursWorked
FROM		EMPLOYEE JOIN PROPERTY_SERVICE
	ON		EMPLOYEE.EmployeeID = PROPERTY_SERVICE.EmployeeID
GROUP BY	ExperienceLevel
ORDER BY	ExperienceLevel DESC;


/*****   Question M   ***********************************************************/

SELECT		OwnerType, SUM(HoursWorked) AS TotalHoursWorked
FROM		EMPLOYEE JOIN PROPERTY_SERVICE
	ON		EMPLOYEE.EmployeeID = PROPERTY_SERVICE.EmployeeID
			JOIN OWNED_PROPERTY
	ON		PROPERTY_SERVICE.PropertyID = OWNED_PROPERTY.PropertyID
			JOIN OWNER
	ON		OWNED_PROPERTY.OwnerID = OWNER.OwnerID
WHERE		ExperienceLevel <> 'Junior'
GROUP BY	OwnerType;

/*****   Question N   ***********************************************************/

SELECT * FROM EMPLOYEE;

ALTER TABLE EMPLOYEE DROP CONSTRAINT EXPERIENCE_LEVEL_CHECK;

UPDATE		EMPLOYEE
SET			ExperienceLevel = 'SuperMaster'
WHERE		ExperienceLevel = 'Master';

SELECT * FROM EMPLOYEE;

/*       To undo:                                                               */

UPDATE		EMPLOYEE
SET			ExperienceLevel = 'Master'
WHERE		ExperienceLevel = 'SuperMaster';

SELECT * FROM EMPLOYEE;

ALTER TABLE EMPLOYEE ADD CONSTRAINT EXPERIENCE_LEVEL_CHECK
	CHECK (EXPERIENCELEVEL IN ('Master','Senior','Junior'));

/*****   Question O  ***********************************************************/

SELECT * FROM EMPLOYEE;

ALTER TABLE EMPLOYEE DROP CONSTRAINT EXPERIENCE_LEVEL_CHECK;

UPDATE	 EMPLOYEE
SET			 ExperienceLevel = 'temp'
WHERE		 ExperienceLevel = 'Senior';

UPDATE	 EMPLOYEE
SET			 ExperienceLevel = 'Senior'
WHERE		 ExperienceLevel = 'Junior';

UPDATE	 EMPLOYEE
SET			 ExperienceLevel = 'Junior'
WHERE		 ExperienceLevel = 'temp';

SELECT * FROM EMPLOYEE;

/*       To undo:                                                               */

UPDATE	 EMPLOYEE
SET			 ExperienceLevel = 'temp'
WHERE		 ExperienceLevel = 'Junior';

UPDATE	 EMPLOYEE
SET			 ExperienceLevel = 'Junior'
WHERE		 ExperienceLevel = 'Senior';

UPDATE	 EMPLOYEE
SET			 ExperienceLevel = 'Senior'
WHERE		 ExperienceLevel = 'temp';

ALTER TABLE EMPLOYEE ADD CONSTRAINT EXPERIENCE_LEVEL_CHECK
	CHECK (EXPERIENCELEVEL IN ('Master','Senior','Junior'));

SELECT * FROM EMPLOYEE;

/*****   Question P  ***********************************************************/

/*       DO NOT RUN THE SOLUTION TO QUESTION P!	(It is commented out intentionally!)					         */

/* DELETE FROM PROPERTY_SERVICE;
DELETE FROM OWNED_PROPERTY;
DELETE FROM EMPLOYEE;
DELETE FROM GG_SERVICE;
DELETE FROM OWNER; */

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