/********************************************************************************/
/*																				*/
/*	Kroenke, Auer, Vandenberg, and Yoder 					*/
/*	Database Concepts (8th Edition) Chapter 03 	       		*/
/*																				*/
/*  Pet_Database Queries														*/
/*																				*/
/*	These are the Microsoft SQL Server 2016 SQL code solutions				*/
/*																				*/
/********************************************************************************/

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

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

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

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

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

USE Pet_Database;
GO

SELECT * FROM PET_OWNER;

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

SELECT * FROM BREED;

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

SELECT * FROM PET;

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

SELECT * FROM PET_3;

/*****   Question 3.15   ********************************************************/

SELECT		PetID, PetName, PetType, PetBreed, PetDOB, OwnerID
FROM		PET;

/*****   Question 3.16   ********************************************************/

SELECT		*
FROM		PET;

/*****   Question 3.17   ********************************************************/

SELECT		PetBreed, PetType
FROM		PET;

/*****   Question 3.18   ********************************************************/

SELECT		PetBreed, PetType, PetDOB
FROM		PET
WHERE		PetType = 'Dog';

/*****   Question 3.19   ********************************************************/

SELECT		PetBreed
FROM		PET;

/*****   Question 3.20   ********************************************************/

SELECT		DISTINCT PetBreed
FROM		PET;

/*****   Question 3.21   ********************************************************/

SELECT		PetBreed, PetType, PetDOB
FROM		PET
WHERE		PetType = 'Dog' AND PetBreed = 'Std. Poodle';

/*****   Question 3.22   ********************************************************/

SELECT		PetName, PetBreed, PetType
FROM		PET
WHERE		PetType NOT IN ('Dog', 'Cat', 'Fish');

/*****   Question 3.23  ********************************************************/

SELECT		PetID, PetName, PetBreed, PetType
FROM		PET
WHERE		RTRIM(PetName) LIKE 'K___';

/*	Note:  There are three underlines after the letter K in the WHERE clause. */

/*****   Question 3.24   ********************************************************/

SELECT		OwnerLastName, OwnerFirstName, OwnerEmail
FROM		PET_OWNER
WHERE		OwnerEmail LIKE '%somewhere.com';

/*****   Question 3.25   ********************************************************/

SELECT		OwnerLastName, OwnerFirstName, OwnerEmail
FROM		PET_OWNER
WHERE		OwnerPhone IS Null;

/*****   Question 3.26   ********************************************************/

SELECT		PetName, PetBreed
FROM		PET
ORDER BY	PetName;

/*****   Question 3.27   ********************************************************/

SELECT		PetName, PetBreed
FROM		PET
ORDER BY	PetBreed ASC, PetName DESC;

/*****   Question 3.28   ********************************************************/

SELECT		COUNT(*) AS NumberOfPets
FROM		PET;


/*****   Question 3.29   ********************************************************/

SELECT		COUNT(DISTINCT PetBreed) AS NumberOfBreeds
FROM		PET;

/*****   Question 3.30   ********************************************************/

/*       See the files:	 DBC-e08-MSSQL-Pet-Database-Create-Tables.sql			*/
/*		DBC-e08-MSSQL-Pet-Database-Insert-Data.sql 	  		*/

SELECT		* 
FROM		PET_3;


/*****   Question 3.31   ********************************************************/

SELECT		MIN(PetWeight) AS MinPetWeight,
			MAX(PetWeight) AS MaxPetWeight,
			AVG(PetWeight) AS AvgPetWeight
FROM		PET_3
WHERE		PetType = 'Dog';

/*****   Question 3.32   ********************************************************/

SELECT		PetBreed, AVG(PetWeight) AS AvgBreedWeight
FROM		PET_3
GROUP BY	PetBreed;

/*****   Question 3.33   ********************************************************/

SELECT		PetBreed, AVG(PetWeight) AS AvgBreedWeight
FROM		PET_3
GROUP BY	PetBreed
HAVING 		COUNT(*) > 1;

/*****   Question 3.34   ********************************************************/

SELECT		PetBreed, AVG(PetWeight) AS AvgBreedWeight
FROM		PET_3
WHERE		PetBreed <> 'Unknown'
GROUP BY	PetBreed
HAVING 		COUNT(*) > 1;

/*****   Question 3.35   ********************************************************/

SELECT		OwnerLastName, OwnerFirstName, OwnerEmail
FROM		PET_OWNER
WHERE		OwnerID IN
			(SELECT	OwnerID
			 FROM	PET_3
			 WHERE	PetType = 'Cat');

/*****   Question 3.36   ********************************************************/

SELECT		OwnerLastName, OwnerFirstName, OwnerEmail
FROM		PET_OWNER
WHERE		OwnerID IN
			(SELECT		OwnerID
			 FROM		PET_3
			 WHERE		PetName= 'Teddy' AND PetType = 'Cat');

/*****   Question 3.37   ********************************************************/

/*       See the files:	 DBC-e08-MSSQL-Pet-Database-Create-Tables.sql			*/
/*			DBC-e08-MSSQL-Pet-Database-Insert-Data.sql 			*/

SELECT		* FROM BREED;

SELECT		OwnerLastName, OwnerFirstName, OwnerEmail
FROM		PET_OWNER
WHERE		OwnerID IN
			(SELECT		OwnerID
			 FROM		PET_3
			 WHERE		PetBreed IN
						(SELECT		BreedName
						 FROM		BREED
						 WHERE		AverageLifeExpectancy > 15));

/*****   Question 3.38   ********************************************************/

SELECT		OwnerLastName, OwnerFirstName, OwnerEmail
FROM		PET_OWNER as PO INNER JOIN PET as P
	ON		PO.OwnerID = P.OwnerID
WHERE		PetType = 'Cat';

SELECT		DISTINCT OwnerLastName, OwnerFirstName, OwnerEmail
FROM		PET_OWNER as PO INNER JOIN PET as P
	ON		PO.OwnerID = P.OwnerID
WHERE		PetType = 'Cat';


/*****   Question 3.39   ********************************************************/

SELECT		OwnerLastName, OwnerFirstName, OwnerEmail
FROM		PET_OWNER JOIN PET_3
		ON	PET_OWNER.OwnerID = PET_3.OwnerID
WHERE		PetName = 'Teddy' AND PetType = 'Cat';


/*****   Question 3.40   ********************************************************/


SELECT	DISTINCT OwnerLastName, OwnerFirstName, OwnerEmail
FROM	(PET_OWNER as PO JOIN PET_3 as P
	ON	PO.OwnerID = P.OwnerID)
			JOIN BREED as B
				ON P.PetBreed = B.BreedName
WHERE	AverageLifeExpectancy > 15;



/*****   Question 3.41   ********************************************************/

SELECT		OwnerLastName, OwnerFirstName, PetName, PetType, PetBreed, AverageLifeExpectancy
FROM		PET_OWNER JOIN PET_3
				ON PET_OWNER.OwnerID = PET_3.OwnerID
			JOIN BREED
				ON PET_3.PetBreed = BREED.BreedName
WHERE		PetBreed <> 'Unknown';


/*****   Question 3.42   ********************************************************/

INSERT INTO PET_OWNER
	VALUES ('Rogers', 'Jim','555-232-3456', 'Jim.Rogers@somewhere.com');
INSERT INTO PET_OWNER
	VALUES ('Keenan', 'Mary', '555-232-4567', 'Mary.Keenan@somewhere.com');
INSERT INTO PET_OWNER
	VALUES ('Melnik', 'Nigel', '555-232-5678', 'Nigel.Melnik@somewhere.com');
	
SELECT * FROM PET_OWNER;

/*****   Question 3.43   ********************************************************/

INSERT INTO PET_OWNER (OwnerLastName, OwnerFirstName, OwnerPhone)
	VALUES ('Mayberry', 'Jenny','555-454-1243');
INSERT INTO PET_OWNER (OwnerLastName, OwnerFirstName, OwnerPhone)
	VALUES ('Roberts', 'Ken','555-454-2354');
INSERT INTO PET_OWNER (OwnerLastName, OwnerFirstName, OwnerPhone)
	VALUES ('Taylor', 'Sam','555-454-3465');
	
SELECT * FROM PET_OWNER;

/*****   Question 3.44   ********************************************************/

/*		 Show current contents of tables										*/

SELECT * FROM BREED;
SELECT * FROM PET_3;

/*		 Make change to BREED, which cascades to PET							*/

UPDATE		BREED
	SET		BreedName = 'Poodle, Std.'
	WHERE	BreedName = 'Std. Poodle';

/*		 Show revised contents of tables										*/

SELECT * FROM BREED;
SELECT * FROM PET_3;

/*	     TO UNDO if necessary:																*/

UPDATE		BREED
	SET		BreedName = 'Std. Poodle'
	WHERE	BreedName = 'Poodle, Std.';

SELECT * FROM BREED;
SELECT * FROM PET_3;

/*****   Question 3.45   ********************************************************/
/*	     No SQL statement														*/

/*****   Question 3.46   ********************************************************/

DELETE
FROM		PET_3
WHERE		PetType = 'Anteater';

/*****   Question 3.47   ********************************************************/

SELECT * FROM PET;

ALTER TABLE PET 
	ADD PetWeight Numeric(4,1) NULL;
	
SELECT * FROM PET;

/*****   Question 3.47   ********************************************************/

SELECT	PetID, PetWeight
FROM	PET_3;


UPDATE		PET
	SET		PetWeight = 25.5
	WHERE	PetID = 1;
UPDATE		PET
	SET		PetWeight = 10.5
	WHERE	PetID = 2;
UPDATE		PET
	SET		PetWeight = 28.5
	WHERE	PetID = 3;
UPDATE		PET
	SET		PetWeight = 20.0
	WHERE	PetID = 4;
UPDATE		PET
	SET		PetWeight = 9.5
	WHERE	PetID = 5;
UPDATE		PET
	SET		PetWeight = 9.5
	WHERE	PetID = 6;
UPDATE		PET
	SET		PetWeight = 25.0
	WHERE	PetID = 7;

SELECT * FROM PET;

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