/********************************************************************************/
/*																				*/
/*	Kroenke, Auer, Vandenberg, and Yoder 					*/
/*	Database Concepts (8th Edition) Chapter 03 	       		*/
/*																				*/
/*  Chapter 03, Appendix A												*/
/*																				*/
/*  Wedgewood Pacific [WP] Database Queries 			*/
/*												*/
/*												*/
/*	This is the Microsoft SQL Server 2014/2016 code							*/
/*																				*/
/********************************************************************************/
/*       NOTE: For consistency with the text, SQL keywords are NOT              */
/*             enclosed in square brackets [].                                  */

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

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

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

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

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

/******   DEPARTMENT Data   *****************************************************/

SELECT	* FROM DEPARTMENT;

/******   EMPLOYEE Data   *******************************************************/

SELECT	* FROM EMPLOYEE;

/******   PROJECT Data   ********************************************************/

SELECT	* FROM PROJECT;

/******   ASSIGNMENT Data   *****************************************************/

SELECT	* FROM ASSIGNMENT;


/******   SQL-QUERY-CH03-01   ***************************************************/
/******   Appendix A Name: MSSQL-SQLQuery01.sql *********************************/

SELECT	ProjectID, ProjectName, Department, MaxHours, StartDate, EndDate
FROM	PROJECT;

/******   SQL-QUERY-CH03-02   ***************************************************/
/******   Appendix A Name: MSSQL-SQLQuery02.sql *********************************/

SELECT	*
FROM	PROJECT;

/******   SQL-QUERY-CH03-03   ***************************************************/
/******   Appendix A Name: MSSQL-SQLQuery03.sql *********************************/

SELECT	ProjectName, Department, MaxHours
FROM	PROJECT;

/******   SQL-QUERY-CH03-04   ***************************************************/
/******   Appendix A Name: MSSQL-SQLQuery04.sql *********************************/

SELECT	ProjectName, MaxHours, Department
FROM	PROJECT;

/******   SQL-QUERY-CH03-05   ***************************************************/
/******   Appendix A Name: MSSQL-SQLQuery05.sql *********************************/

SELECT	Department
FROM	PROJECT;

/******   SQL-QUERY-CH03-06   ***************************************************/
/******   Appendix A Name: MSSQL-SQLQuery06.sql *********************************/

SELECT	DISTINCT Department
FROM	PROJECT;

/******   SQL-QUERY-CH03-07   ***************************************************/
/******   Appendix A Name: MSSQL-SQLQuery07.sql *********************************/

SELECT	*
FROM	PROJECT
WHERE	Department = 'Finance';

/******   SQL-QUERY-CH03-08   ***************************************************/
/******   Appendix A Name: MSSQL-SQLQuery08.sql *********************************/

SELECT	*
FROM	PROJECT
WHERE	StartDate = '05/10/2017';

/******   SQL-QUERY-CH03-09   ***************************************************/
/******   Appendix A Name: MSSQL-SQLQuery09.sql *********************************/

SELECT	*
FROM	PROJECT
WHERE	MaxHours > 135;

/******   SQL-QUERY-CH03-10   ***************************************************/
/******   Appendix A Name: MSSQL-SQLQuery10.sql *********************************/

SELECT	FirstName, LastName, Department, OfficePhone
FROM	EMPLOYEE
WHERE	Department = 'Accounting';

/******   SQL-QUERY-CH03-11   ***************************************************/
/******   Appendix A Name: MSSQL-SQLQuery11.sql *********************************/

SELECT	FirstName, LastName, Department, OfficePhone
FROM	EMPLOYEE
ORDER BY Department;

/******   SQL-QUERY-CH03-12   ***************************************************/
/******   Appendix A Name: MSSQL-SQLQuery12.sql *********************************/

SELECT	FirstName, LastName, Department, OfficePhone
FROM	EMPLOYEE
ORDER BY Department DESC;

/******   SQL-QUERY-CH03-13   ***************************************************/
/******   Appendix A Name: MSSQL-SQLQuery13.sql *********************************/

SELECT	FirstName, LastName, Department, OfficePhone
FROM	EMPLOYEE
ORDER BY Department DESC, LastName ASC;

/******   SQL-QUERY-CH03-14   ***************************************************/
/******   Appendix A Name: MSSQL-SQLQuery14.sql *********************************/

SELECT	FirstName, LastName, Department, OfficePhone
FROM	EMPLOYEE
WHERE	Department ='Accounting'
	AND OfficePhone = '360-285-8430';

/******   SQL-QUERY-CH03-15   ***************************************************/
/******   Appendix A Name: MSSQL-SQLQuery15.sql *********************************/

SELECT	FirstName, LastName, Department, OfficePhone
FROM	EMPLOYEE
WHERE	Department ='Accounting'
	OR	OfficePhone = '360-285-8410';

/******   SQL-QUERY-CH03-16   ***************************************************/
/******   Appendix A Name: MSSQL-SQLQuery16.sql *********************************/

SELECT	FirstName, LastName, Department, OfficePhone
FROM	EMPLOYEE
WHERE	Department ='Accounting'
	AND NOT OfficePhone = '360-285-8430';

/******   SQL-QUERY-CH03-17   ***************************************************/
/******   Appendix A Name: MSSQL-SQLQuery17.sql *********************************/

SELECT	FirstName, LastName, Department, OfficePhone
FROM	EMPLOYEE
WHERE	Department IN ('Administration','Finance','Accounting');

/******   SQL-QUERY-CH03-18   ***************************************************/
/******   Appendix A Name: MSSQL-SQLQuery18.sql *********************************/

SELECT	FirstName, LastName, Department, OfficePhone
FROM	EMPLOYEE
WHERE	Department NOT IN ('Administration','Finance','Accounting');

/******   SQL-QUERY-CH03-19   ***************************************************/
/******   Appendix A Name: MSSQL-SQLQuery19.sql *********************************/
	
SELECT	FirstName, LastName, Department, OfficePhone
FROM	EMPLOYEE
WHERE	EmployeeNumber >= 2
	AND EmployeeNumber <= 5;

/******   SQL-QUERY-CH03-20   ***************************************************/
/******   Appendix A Name: MSSQL-SQLQuery20.sql ********************************/

SELECT	FirstName, LastName, Department, OfficePhone
FROM	EMPLOYEE
WHERE	EmployeeNumber BETWEEN 2 AND 5;

/******   SQL-QUERY-CH03-21   ***************************************************/
/******   Appendix A Name: MSSQL-SQLQuery21.sql *********************************/

SELECT	*
FROM	PROJECT
WHERE	ProjectName LIKE '2017 Q_ Portfolio Analysis';

/******   SQL-QUERY-CH03-22   ***************************************************/
/******   Appendix A Name: MSSQL-SQLQuery22.sql *********************************/

SELECT	*
FROM	EMPLOYEE
WHERE	OfficePhone LIKE '360-287-88__';

/******   SQL-QUERY-CH03-23   ***************************************************/
/******   Appendix A Name: MSSQL-SQLQuery23.sql *********************************/

SELECT	*
FROM	EMPLOYEE
WHERE	OfficePhone LIKE '360-287-88%';

/******   SQL-QUERY-CH03-24   ***************************************************/
/******   Appendix A Name: MSSQL-SQLQuery24.sql *********************************/

SELECT	*
FROM	EMPLOYEE
WHERE	Department LIKE '%ing';

/******   SQL-QUERY-CH03-25   ***************************************************/
/******   Appendix A Name: MSSQL-SQLQuery25.sql *********************************/

SELECT	*
FROM	EMPLOYEE
WHERE	Department NOT LIKE '%ing';

/******   SQL-QUERY-CH03-26   ***************************************************/
/******   Appendix A Name: M6SSQL-SQLQuery26.sql *********************************/

SELECT	FirstName, LastName, Department, OfficePhone
FROM	EMPLOYEE
WHERE	OfficePhone IS NULL;

/******   SQL-QUERY-CH03-27   ***************************************************/
/******   Appendix A Name: MSSQL-SQLQuery27.sql *********************************/

SELECT	FirstName, LastName, Department, OfficePhone
FROM	EMPLOYEE
WHERE	OfficePhone IS NOT NULL;

/******   SQL-QUERY-CH03-28   ***************************************************/
/******   Appendix A Name: MSSQL-SQLQuery28.sql *********************************/

SELECT	COUNT(*)
FROM	PROJECT;

/******   SQL-QUERY-CH03-29   ***************************************************/
/******   Appendix A Name: MSSQL-SQLQuery29.sql *********************************/

SELECT	COUNT(*) AS NumberOfProjects
FROM	PROJECT;

/******   SQL-QUERY-CH03-30   ***************************************************/
/******   Appendix A Name: MSSQL-SQLQuery30.sql *********************************/

SELECT	COUNT(Department) AS NumberOfDepartments
FROM	PROJECT;

/******   SQL-QUERY-CH03-31   ***************************************************/
/******   Appendix A Name: MSSQL-SQLQuery31.sql *********************************/

SELECT	COUNT(DISTINCT Department) AS NumberOfDepartments
FROM	PROJECT;

/******   SQL-QUERY-CH03-32   ***************************************************/
/******   Appendix A Name: MSSQL-SQLQuery32.sql *********************************/

SELECT	SUM(MaxHours) AS TotalMaxHours,
	AVG(MaxHours) AS AverageMaxHours,
	MIN(MaxHours) AS MinmumMaxHours,
	MAX(MaxHours) AS MaximumMaxHours
FROM	PROJECT
WHERE	ProjectID <= 1200;

/******   Query 33 ************************************************************/
/******   Bad query - does NOT run - produces error message   *****************/

SELECT	ProjectName, COUNT(*)
FROM	PROJECT;

/******   SQL-QUERY-CH03-34   ***************************************************/
/******   Appendix A Name: MSSQL-SQLQuery34.sql *********************************/
/******   Bad query - does NOT run - produces error message   *******************/

SELECT	*
FROM	PROJECT
WHERE	MaxHours > AVG(MaxHours);

/******   SQL-QUERY-CH03-35   ***************************************************/
/******   Appendix A Name: MSSQL-SQLQuery35.sql *********************************/

SELECT	ProjectID, ProjectName, MaxHours,
		(24.50 * MaxHours) AS MaxProjectCost
FROM	PROJECT;

/******   SQL-QUERY-CH03-36   ***************************************************/
/******   Appendix A Name: MSSQL-SQLQuery36.sql *********************************/

SELECT		Department, Count(*) AS NumberOfEmployees
FROM		EMPLOYEE
GROUP BY	Department;

/******   SQL-QUERY-CH03-37   ***************************************************/
/******   Appendix A Name: MSSQL-SQLQuery37.sql *********************************/

SELECT		Department, Count(*) AS NumberOfEmployees
FROM		EMPLOYEE
GROUP BY	Department
HAVING		COUNT(*) > 1;

/******   SQL-QUERY-CH03-38   ***************************************************/
/******   Appendix A Name: MSSQL-SQLQuery38.sql *********************************/

SELECT		Department, Count(*) AS NumberOfEmployees
FROM		EMPLOYEE
WHERE		EmployeeNumber <= 10
GROUP BY	Department
HAVING		COUNT(*) > 1;

/******   SQL-QUERY-CH03-39   ***************************************************/
/******   Appendix A Name: MSSQL-SQLQuery39.sql *********************************/

SELECT	FirstName, LastName
FROM	EMPLOYEE
WHERE	EmployeeNumber IN (6, 10, 11, 16, 17);

/******   SQL-QUERY-CH03-40   ***************************************************/
/******   Appendix A Name: MSSQL-SQLQuery40.sql *********************************/

SELECT	DISTINCT EmployeeNumber
FROM	ASSIGNMENT
WHERE	HoursWorked > 50;

/******   SQL-QUERY-CH03-41   ***************************************************/
/******   Appendix A Name: MSSQL-SQLQuery41.sql *********************************/

SELECT	FirstName, LastName
FROM	EMPLOYEE
WHERE	EmployeeNumber IN
		(SELECT		DISTINCT EmployeeNumber
		 FROM		ASSIGNMENT
		 WHERE		HoursWorked > 50);

/******   SQL-QUERY-CH03-42   ***************************************************/
/******   Appendix A Name: MSSQL-SQLQuery42.sql *********************************/

SELECT	DISTINCT ProjectID
FROM	PROJECT
WHERE	Department = 'Accounting';

/******   SQL-QUERY-CH03-43   ***************************************************/
/******   Appendix A Name: MSSQL-SQLQuery43.sql *********************************/

SELECT	DISTINCT EmployeeNumber
FROM	ASSIGNMENT
WHERE	HoursWorked > 40
	AND ProjectID IN
		(SELECT	ProjectID
		 FROM	PROJECT
		 WHERE	Department = 'Accounting');
		
/******   SQL-QUERY-CH03-44   ***************************************************/
/******   Appendix A Name: MSSQL-SQLQuery44.sql *********************************/

SELECT	FirstName, LastName
FROM	EMPLOYEE
WHERE	EmployeeNumber IN
		(SELECT	DISTINCT EmployeeNumber
	  	 FROM	ASSIGNMENT
		 WHERE	HoursWorked > 40
		   AND	ProjectID IN
				(SELECT	ProjectID
				 FROM	PROJECT
				 WHERE	Department = 'Accounting'));

******   SQL-QUERY-CH03-45   ***************************************************/
/******   Appendix A Name: MSSQL-SQLQuery45.sql *********************************/

SELECT		FirstName, LastName, ProjectID, HoursWorked
FROM		EMPLOYEE, ASSIGNMENT;

/******   SQL-QUERY-CH03-46   ***************************************************/
/******   Appendix A Name: MSSQL-SQLQuery46.sql *********************************/

SELECT		FirstName, LastName, ProjectID, HoursWorked
FROM		EMPLOYEE, ASSIGNMENT
WHERE		EMPLOYEE.EmployeeNumber = ASSIGNMENT.EmployeeNumber;

/******   SQL-QUERY-CH03-47   ***************************************************/
/******   Appendix A Name: MSSQL-SQLQuery47.sql ********************************/

SELECT		FirstName, LastName, ProjectID, HoursWorked
FROM		EMPLOYEE, ASSIGNMENT
WHERE		EMPLOYEE.EmployeeNumber = ASSIGNMENT.EmployeeNumber
ORDER BY	EMPLOYEE.EmployeeNumber, ProjectID;

/******   SQL-QUERY-CH03-48   ***************************************************/
/******   Appendix A Name: MSSQL-SQLQuery48.sql *********************************/

SELECT		FirstName, LastName, HoursWorked
FROM		EMPLOYEE JOIN ASSIGNMENT
			ON	EMPLOYEE.EmployeeNumber = ASSIGNMENT.EmployeeNumber
ORDER BY	EMPLOYEE.EmployeeNumber, ProjectID;

/******   SQL-QUERY-CH03-49   ***************************************************/
/******   Appendix A Name: MSSQL-SQLQuery49.sql *********************************/

SELECT		FirstName, LastName,
			SUM(HoursWorked) AS TotalHoursWorked
FROM		EMPLOYEE AS E JOIN ASSIGNMENT AS A
	ON	E.EmployeeNumber = A.EmployeeNumber
GROUP BY	LastName, FirstName
ORDER BY 	LastName, FirstName;

/******   SQL-QUERY-CH03-50   ***************************************************/
/******   Appendix A Name: MSSQL-SQLQuery50.sql *********************************/

SELECT		FirstName, LastName, ProjectID, HoursWorked
FROM		EMPLOYEE AS E JOIN ASSIGNMENT AS A
	ON	E.EmployeeNumber = A.EmployeeNumber
WHERE		HoursWorked > 50
ORDER BY	LastName, FirstName, ProjectID;

/******   SQL-QUERY-CH03-51   ***************************************************/
/******   Appendix A Name: MSSQL-SQLQuery51.sql *********************************/

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

/******   SQL-INSERT-CH03-05   ***************************************************/
/******   Appendix A Name: MSSQL-SQLInsert05.sql *********************************/

INSERT INTO PROJECT(ProjectName, Department, MaxHours, StartDate)
	VALUES
	('2017 Q4 Tax Preparation', 'Accounting', 175.00, '10-DEC-17');	



/******   SQL-QUERY-CH03-52   ***************************************************/
/******   Appendix A Name: MSSQL-SQLQuery52.sql *********************************/

SELECT * FROM PROJECT;

/******   SQL-QUERY-CH03-53   ***************************************************/
/******   Appendix A Name: MSSQL-SQLQuery53.sql *********************************/

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

/******   Query 54 ************************************************************/

/*** EXAMPLE CODE - DO NOT RUN ***/

SELECT StudentPK, StudentName, LockerFK, LockerPK, LockerType
FROM STUDENT INNER JOIN LOCKER
	ON STUDENT.LockerFK = LOCKER.LockerPK
ORDER BY StudentPK;

/******   Query 55 ************************************************************/

/*** EXAMPLE CODE - DO NOT RUN ***/

SELECT StudentPK, StudentName, LockerFK, LockerPK, LockerType
FROM STUDENT LEFT OUTER JOIN LOCKER
	ON STUDENT.LockerFK = LOCKER.LockerPK
ORDER BY StudentPK;

/******   Query 56 ************************************************************/

/*** EXAMPLE CODE - DO NOT RUN ***/

SELECT StudentPK, StudentName, LockerFK, LockerPK, LockerType
FROM STUDENT RIGHT OUTER JOIN LOCKER
	ON STUDENT.LockerFK = LOCKER.LockerPK
ORDER BY LockerPK;

/******   SQL-QUERY-CH03-57   ***************************************************/
/******   Appendix A Name: MSSQL-SQLQuery57.sql *********************************/

SELECT * 
FROM EMPLOYEE
WHERE OfficePhone IS NULL;

/******   SQL-UPDATE-CH03-01   ***************************************************/

UPDATE		EMPLOYEE
	SET	OfficePhone = '360-287-8620'
	WHERE	EmployeeNumber = 13;

/*  TO UNDO THE UPDATE SEEN IN RESULTS OF SQL-QUERY-CH03-58:						*/
/*	Run the following command:													*/ 

UPDATE	EMPLOYEE
	SET		OfficePhone = NULL
	WHERE	EmployeeNumber = 13;

/******   SQL-QUERY-CH03-58   ***************************************************/
/******   Appendix A Name: MSSQL-SQLQuery58.sql *********************************/

SELECT  * 
FROM EMPLOYEE
WHERE EmployeeNumber = 13;

/******   SQL-UPDATE-CH03-02   ***************************************************/

/*  IF THE FOLLOWING COMMAND IS RUN, AN INTENTIONAL ERROR IS CREATED!			*/
/*	DO NOTE RUN UNLESS YOU WANT TO ILLUSTRATE THAT ERROR!						*/

UPDATE	EMPLOYEE
	SET	OfficePhone = '360-287-8620';

/******   SQL-QUERY-CH03-59   ***************************************************/
/******   Appendix A Name: MSSQL-SQLQuery59.sql *********************************/

SELECT  * FROM EMPLOYEE;

/*  TO UNDO THE INTENTIONAL ERROR IN QUERY 59:									*/
/*	Run the following set of commands:											*/

UPDATE	EMPLOYEE
SET	OfficePhone = '360-285-8110'
WHERE	EmployeeNumber = 1;

UPDATE	EMPLOYEE
SET	OfficePhone = '360-285-8120'
WHERE	EmployeeNumber = 2;

UPDATE	EMPLOYEE
SET	OfficePhone = '360-285-8210'
WHERE	EmployeeNumber = 3;

UPDATE	EMPLOYEE
SET	OfficePhone = '360-285-8310'
WHERE	EmployeeNumber = 4;

UPDATE	EMPLOYEE
SET	OfficePhone = '360-285-8320'
WHERE	EmployeeNumber = 5;

UPDATE	EMPLOYEE
SET	OfficePhone = '360-285-8410'
WHERE	EmployeeNumber = 6;

UPDATE	EMPLOYEE
SET	OfficePhone = '360-285-8420'
WHERE	EmployeeNumber = 7;

UPDATE	EMPLOYEE
SET	OfficePhone = '360-285-8430'
WHERE	EmployeeNumber = 8;

UPDATE	EMPLOYEE
SET	OfficePhone = '360-285-8440'
WHERE	EmployeeNumber = 9;

UPDATE	EMPLOYEE
SET	OfficePhone = '360-285-8510'
WHERE	EmployeeNumber = 10;

UPDATE	EMPLOYEE
SET	OfficePhone = '360-285-8520'
WHERE	EmployeeNumber = 11;

UPDATE	EMPLOYEE
SET	OfficePhone = '360-285-8610'
WHERE	EmployeeNumber = 12;

UPDATE	EMPLOYEE
SET	OfficePhone = NULL
WHERE	EmployeeNumber = 13;

UPDATE	EMPLOYEE
SET	OfficePhone = '360-285-8710'
WHERE	EmployeeNumber = 14;

UPDATE	EMPLOYEE
SET	OfficePhone = '360-285-8720'
WHERE	EmployeeNumber = 15;

UPDATE	EMPLOYEE
SET	OfficePhone = '360-285-8810'
WHERE	EmployeeNumber = 16;

UPDATE	EMPLOYEE
SET	OfficePhone = '360-285-8820'
WHERE	EmployeeNumber = 17;

UPDATE	EMPLOYEE
SET	OfficePhone = '360-285-8830'
WHERE	EmployeeNumber = 18;

UPDATE	EMPLOYEE
SET	OfficePhone = NULL
WHERE	EmployeeNumber = 19;

UPDATE	EMPLOYEE
SET	OfficePhone = NULL
WHERE	EmployeeNumber = 20;
 
SELECT  * FROM EMPLOYEE;

/******   SQL-UPDATE-CH03-03   ***************************************************/

UPDATE		EMPLOYEE
	SET		Department = 'Finance', OfficePhone = '360-285-8420'
	WHERE	EmployeeNumber = 9;

/*  TO UNDO SQL-UPDATE-CH03-03:												*/
/*	Run the following command:													*/ 

UPDATE	EMPLOYEE
SET		Department = 'Accounting', Phone = '360-285-8440'
WHERE	EmployeeNumber = 9;

/* *** EXAMPLE CODE – DO NOT RUN *** */
/* *** SQL-DELETE-CH03-01 *** */
DELETE
FROM PROJECT
WHERE Department = 'Sales and Marketing';

/* *** EXAMPLE CODE – DO NOT RUN *** */
/* *** SQL-DELETE-CH03-02 *** */
DELETE
FROM PROJECT;

/* *** EXAMPLE CODE – DO NOT RUN *** */
/* *** SQL-DELETE-CH03-03 *** */
DELETE
FROM EMPLOYEE
WHERE EmployeeNumber = 1;

/* *** EXAMPLE CODE – DO NOT RUN *** */
/* *** SQL-DROP-TABLE-CH03-01 *** */
DROP TABLE ASSIGNMENT;

/* *** EXAMPLE CODE – DO NOT RUN *** */
/* *** SQL-ALTER-TABLE-CH03-01 *** */
ALTER TABLE ASSIGNMENT DROP CONSTRAINT ASSIGN_EMP_FK;

/* *** EXAMPLE CODE – DO NOT RUN *** */
/* *** SQL-TRUNCATE-TABLE-CH03-01 *** */
TRUNCATE TABLE PROJECT;

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

