/********************************************************************************/
/*																				*/
/*	Kroenke, Auer, Vandenberg, and Yoder 										*/
/*	Database Concepts (8th Edition) Appendix E 						       		*/
/*																				*/
/*	Wedgewood Pacific [WP] in-text SQL statements for Appendix E				*/
/*																				*/
/*	These are the MySQL 5.7 SQL code solutions									*/
/*																				*/
/********************************************************************************/

/***************************/
/*** ALTER TABLE SECTION ***/
/***************************/
/* Run these in the correct order at the right time, see App E for details */
/* *** SQL-ALTER-TABLE-AppE-02 *** */
ALTER TABLE PRODUCTION_ITEM
	ADD ApprovalDate DATE NULL;

/* *** SQL-UPDATE-AppE-01 *** */
UPDATE PRODUCTION_ITEM
	SET ApprovalDate = '2014-8-30'
	WHERE SKU = 150102001;
/* *** SQL-UPDATE-AppE-02 *** */
UPDATE PRODUCTION_ITEM
	SET ApprovalDate = '2014-8-30'
	WHERE SKU = 150102005;
/* *** SQL-UPDATE-AppE-03 *** */
UPDATE PRODUCTION_ITEM
	SET ApprovalDate = '2014-9-30'
	WHERE SKU = 150201001;
/* *** SQL-UPDATE-AppE-04 *** */
UPDATE PRODUCTION_ITEM
	SET ApprovalDate = '2014-9-30'
	WHERE SKU = 150201005;
/* *** SQL-UPDATE-AppE-05 *** */
UPDATE PRODUCTION_ITEM
	SET ApprovalDate = '2014-11-30'
	WHERE SKU = 150303001;
/* *** SQL-UPDATE-AppE-06 *** */
UPDATE PRODUCTION_ITEM
	SET ApprovalDate = '2014-11-30'
	WHERE SKU = 150303005;
/* *** SQL-UPDATE-AppE-07 *** */
UPDATE PRODUCTION_ITEM
	SET ApprovalDate = '2015-9-30'
	WHERE SKU = 160103001;
/* *** SQL-UPDATE-AppE-08 *** */
UPDATE PRODUCTION_ITEM
	SET ApprovalDate = '2015-9-30'
	WHERE SKU = 160103005;
/* *** SQL-UPDATE-AppE-09 *** */
UPDATE PRODUCTION_ITEM
	SET ApprovalDate = '2015-9-30'
	WHERE SKU = 160202001;
/* *** SQL-UPDATE-AppE-10 *** */
UPDATE PRODUCTION_ITEM
	SET ApprovalDate = '2015-9-30'
	WHERE SKU = 160202005;
/* *** SQL-UPDATE-AppE-11 *** */
UPDATE PRODUCTION_ITEM
	SET ApprovalDate = '2015-11-30'
	WHERE SKU = 160304001;
/* *** SQL-UPDATE-AppE-12 *** */
UPDATE PRODUCTION_ITEM
	SET ApprovalDate = '2015-11-30'
	WHERE SKU = 160304005;

/* *** SQL-ALTER-TABLE-AppE-03 *** */
ALTER TABLE PRODUCTION_ITEM
	MODIFY COLUMN ApprovalDate DATE NOT NULL;

/* *** SQL-ALTER-TABLE-AppE-06 *** */
/* MySQL does not enforce CHECK constraint but runs the command OK */
ALTER TABLE PRODUCTION_ITEM
	ADD CONSTRAINT CheckProductionDate CHECK
			(ApprovalDate < ProductionStartDate);

/* *** SQL-ALTER-TABLE-AppE-08 *** */
ALTER TABLE CATALOG_SKU_2016
	ADD CONSTRAINT CAT16_PROD_ITEM_FK FOREIGN KEY(SKU)
		REFERENCES PRODUCTION_ITEM(SKU)
			ON UPDATE NO ACTION
			ON DELETE NO ACTION;

/*************/
/*** MERGE ***/
/*************/

/*  The SQL to create and populate the PRODUCTION_ITEM_DATA table can be found in:  */
/* 	DBC-e08-MySQL-WP-Create-AppE-Tables.sql                     */
/* 	DBC-e08-MySQL-WP-Insert-Data-AppE.sql                     */

/* *** SQL-MERGE-AppE-01 *** */
/* MySQL DOES NOT SUPPORT MERGE, it has a similar INSERT ... ON DUPLICATE KEY UPDATE ... clause though */
/*
MERGE INTO PRODUCTION_ITEM AS PROI USING PRODUCTION_ITEM_DATA AS PROID
	ON PROI.SKU = PROID.SKU
	WHEN MATCHED THEN
		UPDATE SET PROI.ProductionEndDate = PROID.ProductionEndDate
	WHEN NOT MATCHED THEN
		INSERT (SKU, SKU_Description,
			ProductionStartDate, ProductionEndDate,
			QuantityOnHand, QuantityInProduction,
			ApprovalDate)
		    VALUES (PROID.SKU, PROID.SKU_Description,
			PROID.ProductionStartDate, PROID.ProductionEndDate,
			PROID.QuantityOnHand, PROID.QuantityInProduction,
			PROID.ApprovalDate);
*/
/******************************************/
/*** EXTENSIONS TO SQL QUERY TECHNIQUES ***/
/******************************************/
/* for MySQL we will simulate the merge with updates and inserts */

UPDATE PRODUCTION_ITEM
	SET ProductionEndDate = '2016-10-31'
	WHERE SKU = 160103001;

UPDATE PRODUCTION_ITEM
	SET ProductionEndDate = '2016-10-31'
	WHERE SKU = 160103005;
    
INSERT INTO PRODUCTION_ITEM 
VALUES( 170104001, 'Alpha IV, Black', '2016-11-15', NULL, 0, 200, '2016-10-15');
 
INSERT INTO PRODUCTION_ITEM 
VALUES( 170104005, 'Alpha IV, White', '2016-11-15', NULL, 0, 200, '2016-10-15');

/* *** EXAMPLE CODE - DO NOT RUN - USED IN CHAPTER 3*** */
/* *** SQL-INSERT-CH03-05 *** */
INSERT INTO PROJECT
	(ProjectName, Department, MaxHours, StartDate)
	VALUES('2017 Q4 Tax Preparation', 'Accounting', 175.00, '2017-12-10');

/****** SQL-Query-AppE-01 ******/
SELECT * FROM PROJECT;

/* *** SQL-Query-CH03-53 *** */
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-Query-AppE-02 *** */
SELECT ProjectName, EmployeeNumber, HoursWorked
FROM PROJECT LEFT JOIN ASSIGNMENT
	ON PROJECT.ProjectID = ASSIGNMENT.ProjectID
ORDER BY PROJECT.ProjectID;

/* *** SQL-Query-AppE-03 *** */
SELECT ProjectName, FirstName, LastName, HoursWorked
FROM (PROJECT AS P JOIN ASSIGNMENT AS A
		ON P.ProjectID = A.ProjectID)
	RIGHT JOIN EMPLOYEE AS E
		ON A.EmployeeNumber = E.EmployeeNumber
ORDER BY P.ProjectID, A.EmployeeNumber;

/* *** SQL-Query-AppE-04 *** */
SELECT E.FirstName, E.lastName
FROM EMPLOYEE AS E
WHERE EmployeeNumber IN
	(SELECT A.EmployeeNumber
	FROM ASSIGNMENT AS A
	WHERE A.ProjectID = 1000);

/* *** SQL-Query-AppE-05 *** */
SELECT E1.EmployeeNumber, E1.FirstName, E1.LastName
FROM EMPLOYEE AS E1
WHERE E1.LastName IN
	(SELECT E2.LastName
	FROM EMPLOYEE AS E2
	WHERE E1.LastName = E2.LastName
		AND E1.EmployeeNumber <> E2.EmployeeNumber);

/* *** SQL-Query-AppE-06 *** */
SELECT E1.EmployeeNumber, E1.FirstName, E1.LastName
FROM EMPLOYEE AS E1
WHERE E1.EmployeeNumber IN
	(SELECT E2.EmployeeNumber
	FROM EMPLOYEE AS E2
	WHERE E1.LastName = E2.LastName
		AND E1.EmployeeNumber <> E2.EmployeeNumber);

/* *** SQL-Query-AppE-07 *** */
SELECT E1.EmployeeNumber, E1.FirstName, E1.LastName
FROM EMPLOYEE AS E1
WHERE EXISTS
	(SELECT E2.LastName
	FROM EMPLOYEE AS E2
	WHERE E1.LastName = E2.LastName
		AND E1.EmployeeNumber <> E2.EmployeeNumber);

/* *** SQL-Query-AppE-08 *** */
SELECT EmployeeNumber, FirstName, LastName, Supervisor
FROM EMPLOYEE
ORDER BY EmployeeNumber;

/* *** SQL-Query-AppE-09 *** */
SELECT S.FirstName AS SupervisorFirstName,
	S.LastName AS SupervisorLastName,
	E.FirstName AS EmployeeFirstName,
	E.LastName AS EmployeeLastName
FROM EMPLOYEE S JOIN EMPLOYEE E
	ON S.EmployeeNumber = E.Supervisor
ORDER BY S.EmployeeNumber;

/* *** SQL-Query-AppE-10 *** */
SELECT SKU, CatalogDescription, CatalogPage, DateOnWebSite
FROM CATALOG_SKU_2015
	UNION
SELECT SKU, CatalogDescription, CatalogPage, DateOnWebSite
FROM CATALOG_SKU_2016;

/*************/
/*** VIEWS ***/
/*************/

/****** SQL-CREATE-VIEW-AppE-02   ***********************************************/
					
CREATE VIEW EmployeePhoneView AS
	SELECT	FirstName, LastName, OfficePhone AS EmployeePhone
	FROM	EMPLOYEE;

/******   SQL-QUERY-AppE-11   ***************************************************/

SELECT		*
FROM		EmployeePhoneView
ORDER BY	LastName, FirstName;

/****** SQL-CREATE-VIEW-AppE-03********************************************/

CREATE VIEW BasicDepartmentDataView AS
	SELECT	DepartmentName, DepartmentPhone
	FROM	DEPARTMENT;

/******   SQL-QUERY-AppE-12   ***************************************************/
SELECT		*
FROM		BasicDepartmentDataView
ORDER BY	DepartmentName;

/****** SQL-CREATE-VIEW-AppE-04   ***********************************************/

CREATE VIEW MarketingDepartmentProjectView AS
	SELECT	ProjectID, ProjectName, MaxHours,
			StartDate, EndDate
	FROM	PROJECT
	WHERE	Department = 'Sales and Marketing';

/******   SQL-QUERY-AppE-13   ***************************************************/

SELECT		*
FROM		MarketingDepartmentProjectView
ORDER BY	ProjectID;

/****** SQL-CREATE-VIEW-AppE-05   ***********************************************/

CREATE VIEW	ProjectHoursToDateView AS
	SELECT		PROJECT.ProjectID,
				ProjectName,
				MaxHours AS ProjectMaxHours,
				SUM(HoursWorked) AS ProjectHoursWorkedToDate
	FROM		PROJECT JOIN ASSIGNMENT 
	ON		PROJECT.ProjectID = ASSIGNMENT.ProjectID
	GROUP BY	PROJECT.ProjectID, ProjectName, MaxHours;

/******   SQL-QUERY-AppE-14   ***************************************************/

SELECT		*
FROM		ProjectHoursToDateView
ORDER BY	ProjectID;

/* *** SQL-Query-CH03-51 *** */
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-CREATE-VIEW-AppE-06   ***********************************************/

CREATE VIEW EmployeeProjectHoursWorkedView AS
	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;

/******   SQL-QUERY-AppE-15   ***************************************************/

SELECT * 
FROM EmployeeProjectHoursWorkedView
ORDER BY LastName, FirstName;

/******   SQL-QUERY-AppE-16   ***************************************************/

SELECT		ProjectID, ProjectName, ProjectMaxHours,
			ProjectHoursWorkedToDate
FROM		ProjectHoursToDateView
WHERE		ProjectHoursWorkedToDate > ProjectMaxHours
ORDER BY	ProjectID;

/****** SQL-CREATE-VIEW-AppE-07   ***********************************************/

CREATE VIEW ProjectsOverAllocatedMaxHoursView AS
	SELECT		ProjectID, ProjectName, ProjectMaxHours,
				ProjectHoursWorkedToDate
	FROM		ProjectHoursToDateView
	WHERE		ProjectHoursWorkedToDate > ProjectMaxHours;

/******   SQL-QUERY-AppE-17   ***************************************************/

SELECT		ProjectID, ProjectName, ProjectMaxHours,
			ProjectHoursWorkedToDate,
			(ProjectHoursWorkedToDate - ProjectMaxHours)
				AS HoursOverMaxAllocated
FROM		ProjectsOverAllocatedMaxHoursView
ORDER BY	ProjectID;

/***************/
/*** SQL/PSM ***/
/***************/		

/* *** SQL-Query-AppE-18 *** */

SELECT CONCAT(RTRIM(LastName),', ',RTRIM(FirstName))  AS EmployeeName, Department, OfficePhone, EmailAddress
FROM EMPLOYEE
ORDER BY EmployeeName;	

DELIMITER //
CREATE FUNCTION NameConcatenation 

-- These are the input parameters
(
	FirstName		CHAR(25),
	LastName		CHAR(25)
)
RETURNS VARCHAR(60) DETERMINISTIC

BEGIN
	-- This is the variable that will hold the value to be returned
	DECLARE FullName VARCHAR(60);

	-- SQL statements to concatenate the names in the proper order
	SET FullName = CONCAT(LastName,', ', FirstName);
	
	-- Return the concatentated name
	RETURN FullName;
END
//
DELIMITER ;


/* *** SQL-Query-AppE-19 *** */

SELECT NameConcatenation(FirstName, LastName) AS EmployeeName,
		Department, OfficePhone, EmailAddress
FROM EMPLOYEE
ORDER BY EmployeeName;			

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


