/******************************************************************************/
/*																				                                    */
/*	Kroenke, Auer, Vandenberg, and Yoder 					*/
/*	Database Concepts (8th Edition) Appendix E 	       		*/
/*																				                                    */
/*  Pet_Database Views and SQL Queries							                    	 	  */
/*																				                                    */
/*	These are the Oracle Express 11g Release 2 SQL code solutions	                    */
/*																				                                    */
/******************************************************************************/

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

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

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

/*       See the files:	 DBC-e08-ODB-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, P.PetName, PetType,
				             O.OwnerID, OwnerLastName, OwnerFirstName,
                     OwnerPhone, OwnerEmail
	       FROM 		   PET_3 P JOIN PET_OWNER 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, P.PetName, PetType, PetBreed, PetDOB
	       FROM 		   PET_OWNER O JOIN PET_3 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 OR REPLACE FUNCTION FirstNameFirst
	-- These are the input parameters
		(
		 varFirstName	IN Char,
		 varLastName	IN char
		)
	-- This is the variable that will hold the returned value
	RETURN 		Varchar
	IS varFullName	Varchar(60);

BEGIN

	-- SQL statement to concatenate the names in the proper order
	varFullName := (RTRIM(varFirstName) || ' ' || RTRIM(varLastName));
	-- Return the concatenated name
	RETURN varFullName;

END;
/

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

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

/*****   Question E.36 ******************************************************/

CREATE VIEW PetOwnerFirstNameFirstView AS
	SELECT PetID, PetName, PetType, O.OwnerID, 
		FirstNameFirst (OwnerFirstName, OwnerLastName) AS PetOwnerName,
		OwnerPhone, OwnerEmail
	FROM PET_OWNER O, PET_3 P
	WHERE O.OWNERID = P.OWNERID;

/*****   Question E.37  ******************************************************/

SELECT *
FROM PetOwnerFirstNameFirstView 
ORDER BY PetOwnerName, PetName;

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