/******************************************************************************/
/*																				                                    */
/*	Kroenke, Auer, Vandenberg, and Yoder 					*/
/*	Database Concepts (8th Edition) Chapter 03 	       		*/
/*																				                                    */
/*  Wedgewood Pacific [WP] Database Queries Chapter Exercises	  */
/*																			                                      */
/*	These are the Oracle Express 11g Release 2 SQL code solutions            */
/*																				                                    */
/******************************************************************************/


/*****   Exercise Questions  3.58   *******************************************/


/*****   WP Database CREATE TABLE statements   *******************************/

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

/*****   WPC Database INSERT statements   *************************************/

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

/*****   Exercise Questions  3.59  *******************************************/

/*****   Question A - SQLQuery-AWE-3-1-A.sql  *********************************/
/*****   Appendix B Name: ODB-SQLQuery-AWE-3-1-A.sql **************************/

SELECT * FROM PROJECT;

/*****   Question B - SQLQuery-AWE-3-1-B.sql  *********************************/
/*****   Appendix B Name: ODB-SQLQuery-AWE-3-1-B.sql **************************/

SELECT	 ProjectID, ProjectName, StartDate, EndDate
FROM 		 PROJECT;

/*****   Question C - SQLQuery-AWE-3-1-C.sql  *********************************/
/*****   Appendix B Name: ODB-SQLQuery-AWE-3-1-C.sql **************************/

SELECT 	*
FROM 		PROJECT
WHERE		StartDate < TO_DATE('08/01/2017', 'MM/DD/YYYY');

/*****   Question D - SQLQuery-AWE-3-1-D.sql  *********************************/
/*****   Appendix B Name: ODB-SQLQuery-AWE-3-1-D.sql **************************/

SELECT 	*
FROM 		PROJECT
WHERE		EndDate IS NULL;

/*****   Question E - SQLQuery-AWE-3-1-E.sql   ********************************/
/*****   Appendix B Name: ODB-SQLQuery-AWE-3-1-E.sql **************************/

SELECT	 ProjectID, E.EmployeeNumber, LastName, FirstName, OfficePhone
FROM		 ASSIGNMENT A JOIN EMPLOYEE E
			     ON A.EmployeeNumber=E.EmployeeNumber;

/*****   Question F - SQLQuery-AWE-3-1-F.sql   ********************************/
/*****   Appendix B Name: ODB-SQLQuery-AWE-3-1-F.sql **************************/

SELECT	 P.ProjectID, ProjectName, P.Department,
         E.EmployeeNumber, LastName, FirstName, OfficePhone
FROM		 ASSIGNMENT A JOIN EMPLOYEE E
				   ON A.EmployeeNumber=E.EmployeeNumber
			  JOIN PROJECT P
				  ON A.ProjectID=P.ProjectID;

/*****   Question G - SQLQuery-AWE-3-1-G.sql   ********************************/
/*****   Appendix B Name: ODB-SQLQuery-AWE-3-1-G.sql **************************/

SELECT	  P.ProjectID, ProjectName, D.DepartmentName, DepartmentPhone,
  		    E.EmployeeNumber, LastName, FirstName, OfficePhone
FROM		  ASSIGNMENT A JOIN EMPLOYEE E
				    ON A.EmployeeNumber=E.EmployeeNumber
			    JOIN PROJECT P
				    ON A.ProjectID=P.ProjectID
			    JOIN DEPARTMENT D
				    ON P.Department=D.DepartmentName
ORDER BY	P.ProjectID;

/*****   Question H - SQLQuery-AWE-3-1-H.sql   ********************************/
/*****   Appendix B Name: ODB-SQLQuery-AWE-3-1-H.sql **************************/


SELECT		P.ProjectID, ProjectName, D.DepartmentName,
			DepartmentPhone,
			E.EmployeeNumber, LastName, FirstName,
			OfficePhone
FROM		ASSIGNMENT A JOIN EMPLOYEE E
				ON A.EmployeeNumber=E.EmployeeNumber
			JOIN PROJECT P
				ON A.ProjectID=P.ProjectID
			JOIN DEPARTMENT D
				ON P.Department=D.DepartmentName
WHERE		DepartmentName='Sales and Marketing'
ORDER BY	P.ProjectID;

/*****   Question I - SQLQuery-AWE-3-1-I.sql   ********************************/
/*****   Appendix B Name: ODB-SQLQuery-AWE-3-1-I.sql **************************/

SELECT		COUNT(*) AS NumberOfMarketingDeptProjects
FROM		  PROJECT
WHERE		 Department='Sales and Marketing';

/*****   Question J - SQLQuery-AWE-3-1-J.sql   ********************************/
/*****   Appendix B Name: ODB-SQLQuery-AWE-3-1-J.sql **************************/

SELECT		SUM(MaxHours) AS TotalMaxHrsForMKTGDeptProjects
FROM		  PROJECT
WHERE		 Department='Sales and Marketing';

/*****   Question K - SQLQuery-AWE-3-1-K.sql  *********************************/
/*****   Appendix B Name: ODB-SQLQuery-AWE-3-1-K.sql **************************/

SELECT		AVG(MaxHours) AS AvgMaxHrsForMKTGDeptProjects
FROM		  PROJECT
WHERE		 Department='Sales and Marketing';

/*****   Question L - SQLQuery-AWE-3-1-L.sql  *********************************/
/*****   Appendix B Name: ODB-SQLQuery-AWE-3-1-L.sql **************************/

SELECT		Department, COUNT(*) AS NumberOfDeptProjects
FROM		  PROJECT
GROUP BY	Department;


/*****   Exercise Questions  3.60  *******************************************/


/*****   Question A   *********************************************************/
*****   WP Database CREATE TABLE statements   ********************************/

/*       See the file:	 DBC-e08-ODB-WP-Create-New-Tables-CH03-Exercise.sql	*/

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

/*****   WP Database INSERT statements   *************************************/

/*       See the file:	 DBC-8-ODB-WP-Insert-New-Data-CH03-Exercises.sql   */

/*****   COMPUTER DATA  *******************************************************/

SELECT	  *
FROM		  COMPUTER
ORDER BY	Make DESC, SerialNumber ASC;

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

/*****   WP Database CREATE TABLE statements   *******************************/

/*       See the file:	 DBC-e08-ODB-WP-Create-New-Tables-CH03-Exercise.sql	*/

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

/*****   WP Database INSERT statements   *************************************/

/*       See the file:	 DBC-e08-ODB-WP-Insert-New-Data-CH03-Exercises.sql	*/

/*****   COMPUTER_ASSIGNMENT DATA  ********************************************/

SELECT		*
FROM		  COMPUTER_ASSIGNMENT
ORDER BY	DateAssigned ASC, SerialNumber ASC;

/*****   Question E - SQLQuery-AWE-3-3-E.sql  *********************************/
/*****   Appendix B Name: ODB-SQLQuery-AWE-3-3-E.sql **************************/

SELECT		C.SerialNumber, Make, Model, E.EmployeeNumber, LastName, FirstName, Department, OfficePhone
FROM		COMPUTER C JOIN COMPUTER_ASSIGNMENT CA
				ON C.SerialNumber=CA.SerialNumber
			JOIN EMPLOYEE E
				ON CA.EmployeeNumber=E.EmployeeNumber
WHERE		DateReassigned IS NULL
ORDER BY	Department, LastName;




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