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


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

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

/******   SQL-Query-CH03-01 ***************************************************/
/******   Appendix B Name: ODB-SQL-Query-01.sql *******************************/

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

/******   SQL-Query-CH03-02 ***************************************************/
/******   Appendix B Name: ODB-SQL-Query-02.sql *******************************/

SELECT	*
FROM	  PROJECT;

/******   SQL-Query-CH03-03 ***************************************************/
/******   Appendix B Name: ODB-SQL-Query-03.sql *******************************/

SELECT	ProjectName, Department, MaxHours
FROM	  PROJECT;

/******   SQL-Query-CH03-04 ***************************************************/
/******   Appendix B Name: ODB-SQL-Query-04.sql *******************************/

SELECT	ProjectName, MaxHours, Department
FROM	  PROJECT;

/******   SQL-Query-CH03-05 ***************************************************/
/******   Appendix B Name: ODB-SQL-Query-05.sql *******************************/

SELECT	Department
FROM	  PROJECT;

/******   Query 06 ************************************************************/
/******   Appendix B Name: ODB-SQL-Query06.sql ********************************/

SELECT	DISTINCT Department
FROM	PROJECT;

/******   Query 07 ************************************************************/
/******   Appendix B Name: ODB-SQL-Query07.sql ********************************/

SELECT	*
FROM	PROJECT
WHERE	Department = 'Finance';

/******   Query 08 ************************************************************/
/******   Appendix B Name: ODB-SQL-Query08.sql ********************************/

/* Using Oracle's default date format, different from SQL Server */

SELECT	*
FROM	PROJECT
WHERE	StartDate = '10-MAY-2017';

/******   Query 09 ************************************************************/
/******   Appendix B Name: ODB-SQL-Query09.sql ********************************/

SELECT	*
FROM	PROJECT
WHERE	MaxHours > 135;

/******   Query 10 ************************************************************/
/******   Appendix B Name: ODB-SQL-Query10.sql ********************************/

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

/******   Query 11 ************************************************************/
/******   Appendix B Name: ODB-SQL-Query11.sql ********************************/

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

/******   Query 12 ************************************************************/
/******   Appendix B Name: ODB-SQL-Query12.sql ********************************/

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

/******   Query 13 ************************************************************/
/******   Appendix B Name: ODB-SQL-Query13.sql ********************************/

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

/******   Query 14 ************************************************************/
/******   Appendix B Name: ODB-SQL-Query14.sql ********************************/

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

/******   Query 15 ************************************************************/
/******   Appendix B Name: ODB-SQL-Query15.sql ********************************/

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

/******   Query 16 ************************************************************/
/******   Appendix B Name: ODB-SQL-Query16.sql ********************************/

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

/******   Query 17 ************************************************************/
/******   Appendix B Name: ODB-SQL-Query17.sql ********************************/

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

/******   Query 18 ************************************************************/
/******   Appendix B Name: ODB-SQL-Query18.sql ********************************/

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

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

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

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

/******   Query 21 ************************************************************/
/******   Appendix B Name: ODB-SQL-Query21.sql ********************************/

/* Since ProjectName is a CHAR field (not VARCHAR) it is padded with blanks on the right.  
The first version of this query will thus not work.  
The second, correct, version below will trim the trailing blanks before doing the comparison. */

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

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

/******   Query 22 ************************************************************/
/******   Appendix B Name: ODB-SQL-Query22.sql ********************************/

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

/******   Query 23 ************************************************************/
/******   Appendix B Name: ODB-SQL-Query23.sql ********************************/

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

/******   Query 24 ************************************************************/
/******   Appendix B Name: ODB-SQL-Query24.sql ********************************/

/* Since Department is a CHAR field (not VARCHAR) it is padded with blanks on the right.  
The first version of this query will not work.  The second, 
correct, version below will trim the trailing blanks before doing the comparison. */

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

SELECT	*
FROM	EMPLOYEE
WHERE	RTRIM(Department) LIKE '%ing';

/******   Query 25 ************************************************************/
/******   Appendix B Name: ODB-SQL-Query25.sql ********************************/

/* Since Department is a CHAR field (not VARCHAR) it is padded with blanks on the right.  
The first version of this query will not work.  
The second, correct, version below will trim the trailing blanks before doing the comparison. */

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

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

/******   Query 26 ************************************************************/
/******   Appendix B Name: ODB-SQL-Query26.sql ********************************/

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

/******   Query 27 ************************************************************/
/******   Appendix B Name: ODB-SQL-Query27.sql ********************************/

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

/******   Query 28 ************************************************************/
/******   Appendix B Name: ODB-SQL-Query28.sql ********************************/

SELECT	COUNT(*)
FROM	PROJECT;

/******   Query 29 ************************************************************/
/******   Appendix B Name: ODB-SQL-Query29.sql ********************************/

SELECT	COUNT(*) AS NumberOfProjects
FROM	PROJECT;

/******   Query 30 ************************************************************/
/******   Appendix B Name: ODB-SQL-Query30.sql ********************************/

SELECT	COUNT(Department) AS NumberOfDepartments
FROM	PROJECT;

/******   Query 31 ************************************************************/
/******   Appendix B Name: ODB-SQL-Query31.sql ********************************/

SELECT	COUNT(DISTINCT Department) AS NumberOfDepartments
FROM	PROJECT;

/******   Query 32 ************************************************************/
/******   Appendix B Name: ODB-SQL-Query32.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: ODB-SQLQuery34.sql *********************************/
/******   Bad query - does NOT run - produces error message   *******************/

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

/******   Query 35 ************************************************************/
/******   Appendix B Name: ODB-SQL-Query35.sql ********************************/

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

/******   Query 36 ************************************************************/
/******   Appendix B Name: ODB-SQL-Query36.sql ********************************/

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

/******   Query 37 ************************************************************/
/******   Appendix B Name: ODB-SQL-Query37.sql ********************************/

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

/******   Query 38 ************************************************************/
/******   Appendix B Name: ODB-SQL-Query38.sql ********************************/

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

/******   Query 39 ************************************************************/
/******   Appendix B Name: ODB-SQL-Query39.sql ********************************/

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

/******   Query 40 ************************************************************/
/******   Appendix B Name: ODB-SQL-Query40.sql ********************************/

SELECT	DISTINCT EmployeeNumber
FROM	  ASSIGNMENT
WHERE	  HoursWorked > 50;

/******   Query 41 ************************************************************/
/******   Appendix B Name: ODB-SQL-Query41.sql ********************************/

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

/******   Query 42 ************************************************************/
/******   Appendix B Name: ODB-SQL-Query42.sql ********************************/

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

/******   Query 43 ************************************************************/
/******   Appendix B Name: ODB-SQL-Query43.sql ********************************/

SELECT	DISTINCT EmployeeNumber
FROM	  ASSIGNMENT
WHERE	  HoursWorked > 40
	AND   ProjectID IN
		    (SELECT	ProjectID
		     FROM	  PROJECT
		     WHERE	Department = 'Accounting');
		
/******   Query 44 ************************************************************/
/******   Appendix B Name: ODB-SQL-Query44.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'));

/******   Query 45 ************************************************************/
/******   Appendix B Name: ODB-SQL-Query45.sql ********************************/

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

/******   Query 46 ************************************************************/
/******   Appendix B Name: ODB-SQL-Query46.sql ********************************/

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

/******   Query 47 ************************************************************/
/******   Appendix B Name: ODB-SQL-Query47.sql ********************************/

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

/******   Query 48 ************************************************************/
/******   Appendix B Name: ODB-SQL-Query48.sql ********************************/

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

/******   Query 49 ************************************************************/
/******   Appendix B Name: ODB-SQL-Query49.sql ********************************/

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

/******   Query 50 ************************************************************/
/******   Appendix B Name: ODB-SQL-Query50.sql ********************************/

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

/******   Query 51 ************************************************************/
/******   Appendix B Name: ODB-SQL-Query51.sql ********************************/

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

/******   Query 52 AND SQL-INSERT-CH03-05************************************************************/

INSERT INTO PROJECT(ProjectID, ProjectName, Department, MaxHours, StartDate)
	VALUES(seqPID.NextVal, '2017 Q4 Tax Preparation', 'Accounting',
	175.00, TO_DATE('12/10/2017', 'MM/DD/YYYY'));	

SELECT * FROM PROJECT;

/******   Query 53 ************************************************************/

SELECT	  ProjectName, FirstName, LastName, HoursWorked
FROM	    EMPLOYEE E JOIN ASSIGNMENT A
	ON	    E.EmployeeNumber = A.EmployeeNumber
			JOIN PROJECT 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;

/******   Query 57 ************************************************************/

SELECT * 
FROM EMPLOYEE
WHERE OfficePhone IS NULL;

/******   Query 58 AND SQL-UPDATE-CH03-01************************************************************/

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

SELECT  * 
FROM EMPLOYEE
WHERE EmployeeNumber = 13;

/******   Query 59 and 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';

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;

/*** THE FOLLOWING CODE FROM BOOK FOR DELETE, DROP, ALTER TABLE, and TRUNCATE TABLE examples IS PROVIDED ***/

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

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



