/********************************************************************************/
/*																				*/
/*	Kroenke, Auer, Vandenberg, Yoder - DBC (8th Edition) Chapter 03				*/
/*																				*/
/*  Wedgewood Pacific [WP] Database Queries Chapter Exercises					*/
/*																				*/
/*	These are the MySQL 5.7						SQL code solutions				*/
/*																				*/
/********************************************************************************/


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


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

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

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

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

/*****   DEPARTMENT DATA  *******************************************************/

SELECT * FROM DEPARTMENT;

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

SELECT * FROM EMPLOYEE;

/*****   PROJECT DATA  **********************************************************/

SELECT * FROM PROJECT;

/*****   ASSIGMENT DATA  ********************************************************/

SELECT * FROM ASSIGNMENT;

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

/*****   Question A - SQLQuery-AWE-3-1-A.sql  ***********************************/
/*****   Appendix C Name: MySQL-SQLQuery-AWE-3-1-A.sql **************************/

SELECT * FROM PROJECT;

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

SELECT		ProjectID, ProjectName, StartDate, EndDate
FROM 		PROJECT;

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

SELECT 		*
FROM 		PROJECT
WHERE		StartDate <'2017-08-01';

/*****   Question D - SQLQuery-AWE-3-1-D.sql  ***********************************/
/*****   Appendix C Name: MySQL-SQLQuery-AWE-3-1-D.sql **************************/

SELECT 		*
FROM 		PROJECT
WHERE		EndDate IS NULL;

/*****   Question E - SQLQuery-AWE-3-1-E.sql   **********************************/
/*****   Appendix C Name: MySQL-SQLQuery-AWE-3-1-E.sql **************************/

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

/*****   Question F - SQLQuery-AWE-3-1-F.sql   **********************************/
/*****   Appendix A Name: MySQL-SQLQuery-AWE-3-1-F.sql **************************/

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

/*****   Question G - SQLQuery-AWE-3-1-G.sql   *********************************/
/*****   Appendix A Name: MySQL-SQLQuery-AWE-3-1-G.sql *************************/

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

/*****   Question H - SQLQuery-AWE-3-1-H.sql   **********************************/
/*****   Appendix A Name: MySQL-SQLQuery-AWE-3-1-H.sql **************************/

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

/*****   Question I - SQLQuery-AWE-3-1-I.sql   **********************************/
/*****   Appendix A Name: MySQL-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 A Name: MySQL-SQLQuery-AWE-3-1-J.sql **************************/

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

/*****   Question K - SQLQuery-AWE-3-1-K.sql  ***********************************/
/*****   Appendix A Name: MySQL-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 A Name: MySQL-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-MySQL-WP-Create-New-Tables-CH03-Exercise.sql	*/

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

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

/*       See the file:	 DBC-e08-MySQL-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-MySQL-WP-Create-New-Tables-CH03-Exercise.sql	*/

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

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

/*       See the file:	 DBC-e08-MySQL-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 A Name: MySQL-SQLQuery-AWE-3-1-E.sql *************************/

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



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