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

Explanation / Answer

SELECT
COUNT(case when Gender = 'Male' then 1 end) as male_count,
COUNT(case when Gender = 'Female' then 1 end) as female_count
FROM Dogs;

SELECT BreedName, count(Dogs.BreedID), AvgBreedWeight as Average_Weight, max(Dogs.DogWeight) FROM DogBreeds INNER JOIN Dogs ON DogBreeds.BreedID = Dogs.BreedID GROUP BY Dogs.BreedName;

**Comment for any further queries.

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

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