Write an SQL statement to display all columns of all rows of PET. Do not use theasterisk (*) notation.

• 3.2 Write an SQL statement to display the breed, type, and DOB of all pets having the type Dog.
• 3.3 Write an SQL statement to display the PetBreed column of PET. Do not show duplicates.
• 3.4 Write an SQL statement to display the breed, type, and DOB for all pets having the type Dog and the breed Std. Poodle.
• 3.5 Write an SQL statement to display the name, breed, and type for all pets that are not of type Cat, Dog, or Fish.
• 3.6 Write an SQL statement to display the last name, first name, and email of all owners who have an email address ending with somewhere.com. Assume that email account names can be any number of characters. Note that the RTRIM function will be needed in the solution that uses a CHAR column, but not for one that uses a VARCHAR column.
• 3.7 Write an SQL statement to display the last name, first name and email of any owner who has a NULL value for OwnerPhone.
• 3.8 Write an SQL statement to display the name and breed of all pets, sorted by PetName.
• 3.9 Write an SQL statement to display the name and breed of all pets, sorted by PetBreed in ascendin order and by PetName in descending order within PetBreed.
• 3.10 Write an SQL statement to count the number of pets.
• 3.11 Write an SQL statement to display the minimum, maximum, and average weight of dogs.
• 3.12 Write an SQL statement to group the data by PetBreed and display the average weight per breed, but do not consider any pet having the breed of Unknown
• 3.13 Answer question 3.12, but consider only breeds for which two or more pets are included in the database.
• 3.14 Write an SQL statement to display the last name, first name, and email of any owners of cats. Use a subquery.
• 3.15 Write an SQL statement to display the OwnerLastName, OwnerFirstName, PetName,PetType, PetBreed, and AverageLifeExpectancy for pets with a known PetBreed.

Use the following tables for your answers to the questions

PET (PetID, PetName, PetType, PetBreed, PetDOB, OwnerID)

PET_3 (PetID, PetName, PetType, PetBreed, PetDOB, PetWeight, OwnerID)

PET_OWNER (OwnerID, OwnerLastName, OwnerFirstName, OwnerPhone, OwnerEmail)

BREED (BreedName, MinWeight, MaxWeight, AverageLifeExpectancy)

For each SQL statement you write, show the results based on these data.

 

Figure 3-26: PET_OWNER Data OwnerID 1 2 3 Figure 3-27: PET Data PetID 1 2 3 4 5 6 7 PET 3 Data PetID 1 2 3 4 5 6 7 Figure 3-29: BREED Data Collie Mix Std. Poodle Unknown OwnerLastName Downs James Frier Trent PetName King Teddy Fido AJ Cedro Wooley Buster PetName PetType Dog Cat BreedName Border Collie Cashmere King Teddy Fido Dog Dog Cat Wooley Cat Buster Dog AJ Cedro OwnerFirstName Owner Phone 555-537-8765 555-537-7654 555-537-6543 Marsha Richard Liz Miles PetType Dog Cat Dog Dog Cat Dog PetBreed Std. Poodle Cashmere Std. Poodle Collie Mix PetBreed Std. Poodle Min Weight 15.0 10.0 17.5 22.5 Cashmere Std. Poodle Collie Mix Unknown Unknown Border Collie Unknown Unknown Border Collie PetDOB 27-Feb-16 01-Feb-17 17-Jul-15 05-May-16 06-Jun-14 11-Dec-13 Max Weight 22.5 15.0 25.0 30.0 OwnerEmail Marsha Downs@somewhere.com Richard James@somewhere.com Liz Frier@somewhere.com Miles.Trent@somewhere.com PetDOB 27-Feb-16 01-Feb-17 17-Jul-15 05-May-16 06-Jun-14 11-Dec-13 PetWeight 25.5 10.5 28.5 20.0 95 95 25.0 1 3 2 2 OwnerID 4 1 213~~T OwnerID 1 2 4 AverageLifeExpectancy 20 12 18 18