We will be working with two tables. The DogBreeds table contains official inform

Question

We will be working with two tables. The DogBreeds table contains official information about the breed from the Pet Guardian Angels of America™website. The Dogs table contains information about a particular set of dogs.

Step 1

Create the Dogs and DogBreeds tables by running the following script:

DogsSetup.sql

Step 2

Write a single query that shows how many dogs are male and how many are female. Show one row for male, and one row for female.

Screenshot #1: Gender

Step 3

Write a single query that retrieves the following fields:

Breed Name

Number dogs for that breed

Average weight for that breed (from Dogs table)

Heaviest weight for a dog for that breed (from Dogs table)

There should be one line per breed . Do not display breeds or genders that do not exist in the Dogs table. Round the average weight to 1/10th of a pound. Be sure each column has a reasonable column name (use an alias if necessary). Order the output by gender and then breed name.

Screenshot #3: Breed / counts and weights

Notes

Do not use the AvgBreedWeight field in the DogBreeds table for any part of this exercise. Instead you should use the DogWeight field in the Dogs table as part of your calculation. The AvgBreedWeight field will be used in class next week.

When rounding do not worry about the extra zeros to the right (e.g. if rounding to two decimal places 25.150000 is acceptable).

Explanation / Answer

Step 2:

Select Gender,count(*) from Dogs group by Gender;

Step 3:

Select BreedName ,count(BreedID) as NumOfDogs , Round(DogWeight*0.1,2) as AverageWeight ,max(DogWeight) as HeaviestDog from Dogs,DogBreeds group by BreedName having Dogs.BreedID = DogBreeds.BreedID order by Gender,BreedName;

Do ask if any doubt. Please upvote.

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.