/********************************************************************************/
/*																				*/
/*	Kroenke, Auer, Vandenberg, and Yoder 										*/
/*	Database Concepts (8th Edition) Appendix E					 	       		*/
/*																				*/
/*  Pet_Database Views and SQL Queries											*/
/*																				*/
/*	These are the MySQL 5.7 SQL code solutions									*/
/*																				*/
/********************************************************************************/

/*****   Pet_Database CREATE TABLE statements   *********************************/

/*       See the files:	 DBC-e08-MySQL-Pet-Database-Create-Tables.sql			*/

/*****   Pet_Database INSERT statements   ***************************************/

/*       See the files:	 DBC-e08-MySQL-Pet-Database-Insert-Data.sql 			*/



/*****   PET_OWNER DATA   *******************************************************/

SELECT * FROM PET_OWNER;

/*****   BREED DATA   ***********************************************************/

SELECT * FROM BREED;

/*****   PET DATA   *************************************************************/

SELECT * FROM PET;

/*****   PET_3 DATA   ***********************************************************/

SELECT * FROM PET_3;

/*****   Question E.21   *********************************************************/

CREATE VIEW OwnerPhoneView AS
	SELECT 		OwnerLastName, OwnerFirstName, OwnerPhone
	FROM 		PET_OWNER;

/*****   Question E.22   *********************************************************/

SELECT 		*
FROM 		OwnerPhoneView
ORDER BY	OwnerLastName;

/*****   Question E.23   *********************************************************/

CREATE VIEW DogBreedView AS
	SELECT 		PetID, PetName, PetBreed, PetDOB
	FROM 		PET_3
	WHERE		PetType = 'Dog';

/*****   Question E.24   *********************************************************/

SELECT 		*
FROM 		DogBreedView
ORDER BY	PetName;

/*****   Question E.25   *********************************************************/

CREATE VIEW CatBreedView AS
	SELECT 		PetID, PetName, PetBreed, PetDOB
	FROM 		PET_3
	WHERE		PetType = 'Cat';

/*****   Question E.26   ********************************************************/

SELECT 		*
FROM 		CatBreedView
ORDER BY	PetName;

/*****   Question E.27   ********************************************************/

CREATE VIEW PetOwnerView AS
	SELECT 		PetID, PetName, PetType,
				O.OwnerID, OwnerLastName, OwnerFirstName, OwnerPhone, OwnerEmail
	FROM 		PET_3 AS P JOIN PET_OWNER AS O
				ON P.OwnerID = O.OwnerID;

/*****   Question E.28   ********************************************************/

SELECT 		*
FROM 		PetOwnerView
ORDER BY	OwnerLastName, PetName;

/*****   Question E.29   ********************************************************/

CREATE VIEW OwnerPetView AS
	SELECT 		O.OwnerID, OwnerLastName, OwnerFirstName,
				PetID, PetName, PetType, PetBreed, PetDOB
	FROM 		PET_OWNER AS O JOIN PET_3 AS P 
				ON O.OwnerID = P.OwnerID;

/*****   Question E.30   ********************************************************/

SELECT 		*
FROM 		OwnerPetView
ORDER BY	OwnerLastName, PetName;

/*****   Question E.31   ********************************************************/

CREATE VIEW PetCountView AS
	SELECT 		PetType, COUNT(PetType) AS NumberOfPets
	FROM 		PET_3
	GROUP BY	PetType;

/*****   Question E.32   ********************************************************/

SELECT 		*
FROM 		PetCountView
ORDER BY	PetType;

/*****   Question E.33   ********************************************************/

CREATE VIEW DogBreedCountView AS
	SELECT 		PetBreed, COUNT(PetBreed) AS NumberOfDogs
	FROM 		PET_3
	WHERE		PetType = 'Dog'
	GROUP BY	PetBreed;

/*****   Question E.34   ********************************************************/

SELECT 		*
FROM 		DogBreedCountView
ORDER BY	PetBreed;

/*****   Question E.35   ********************************************************/
/******  Create Function  *******************************************************/
DELIMITER //
CREATE FUNCTION FirstNameFirst 

-- 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(FirstName,' ', LastName);
	
	-- Return the concatentated name
	RETURN FullName;
END
//
DELIMITER ;


/******  Test Function  *********************************************************/

SELECT		FirstNameFirst(OwnerFirstName, OwnerLastName) AS PetOwnerName, 
			OwnerPhone, OwnerEmail
FROM		PET_OWNER
ORDER BY	PetOwnerName;

/*****   Question  E.36   *******************************************************/
/******  Create View  ***********************************************************/

CREATE VIEW	PetOwnerFirstNameFirstView AS
	SELECT		PetID, PetName, PetType,
				PO.OwnerID,
				FirstNameFirst(OwnerFirstName, OwnerLastName) AS PetOwnerName,
				OwnerPhone, OwnerEmail
	FROM 		PET_OWNER AS PO JOIN PET_3 AS P
		ON		PO.OwnerID = P.OwnerID;
				
/*****   Question  E.37   *******************************************************/
/******  Query View *************************************************************/

SELECT		*
FROM		PetOwnerFirstNameFirstView
ORDER BY	PetOwnerName, PetName;

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