We will be working with two tables. The DogBreeds table contains official information about the breed from the Pet Guardian Angels of Americawebsite. 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

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

 * DogsSetup.sql

 * CIS-2725

 * 

 * Builds and populates DogBreeds and Dogs tables 

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

DROP TABLE Dogs;

DROP TABLE DogBreeds;

CREATE TABLE DogBreeds

(

 BreedID INT PRIMARY KEY,

 BreedName NVARCHAR(30),

 AvgBreedWeight DECIMAL(3,0),

 HairType NVARCHAR(10)

);

CREATE TABLE Dogs

(

 DogID INT PRIMARY KEY,

 DogName VARCHAR(30),

 BreedID INT,

 Gender VARCHAR(6),

 DogWeight DECIMAL(5,2)

);

ALTER TABLE Dogs

ADD CONSTRAINT fk_Dogs_DogBreeds

FOREIGN KEY (BreedID)

REFERENCES DogBreeds (BreedID);

ALTER TABLE Dogs

ADD CONSTRAINT chkDogs_Gender CHECK (Gender IN ('Male','Female'));

-- Breed data from: http://www.pgaa.com/CANINE/GENERAL/size.html

INSERT INTO DogBreeds VALUES (1, 'Labrador Retriever', 73, 'Short');

INSERT INTO DogBreeds VALUES (2, 'Pomeranian', 5, 'Long');

INSERT INTO DogBreeds VALUES (3, 'Portuguese Water Dog', 55, 'Thick');

INSERT INTO DogBreeds VALUES (4, 'Shih Tzu', 13, 'Short');

INSERT INTO DogBreeds VALUES (5, 'Chihuahua', 4, 'Mixed');

INSERT INTO DogBreeds VALUES (6, 'Boxer', 70, 'Short');

INSERT INTO DogBreeds VALUES (7, 'Great Dane', 140, 'Short');

INSERT INTO DogBreeds VALUES (8, 'Bulldog', 50, 'Short');

INSERT INTO DogBreeds VALUES (9, 'German Shepherd', 82, 'Medium');

INSERT INTO DogBreeds VALUES (10, 'Golden Retriever', 70, 'Medium');

INSERT INTO Dogs VALUES ( 1, 'Toby', 1, 'Male', 72);

INSERT INTO Dogs VALUES ( 2, 'Maggie', 8, 'Female', 44);

INSERT INTO Dogs VALUES ( 3, 'Max', 9, 'Male', 98);

INSERT INTO Dogs VALUES ( 4, 'Molly', 1, 'Female', 65);

INSERT INTO Dogs VALUES ( 5, 'Lady', 2, 'Female', 4.5);

INSERT INTO Dogs VALUES ( 6, 'Buddy', 9, 'Male', 82);

INSERT INTO Dogs VALUES ( 7, 'Sam', 8, 'Male', 65);

INSERT INTO Dogs VALUES ( 8, 'Sadie', 9, 'Female', 55);

INSERT INTO Dogs VALUES ( 9, 'Lucy', 10, 'Female', 67);

INSERT INTO Dogs VALUES (10, 'Duke', 1, 'Male', 82);

INSERT INTO Dogs VALUES (11, 'Daisy', 3, 'Female', 122);

INSERT INTO Dogs VALUES (12, 'Cody', 8, 'Male', 50);

INSERT INTO Dogs VALUES (13, 'Ginger', 5, 'Female', 3.5);

INSERT INTO Dogs VALUES (14, 'Abby', 2, 'Female', 3.75);

INSERT INTO Dogs VALUES (15, 'Harley', 1, 'Male', 78);

INSERT INTO Dogs VALUES (16, 'Sparky', 3, 'Male', 165);

INSERT INTO Dogs VALUES (17, 'Sasha', 9, 'Female', 62);

INSERT INTO Dogs VALUES (18, 'Jake', 10, 'Male', 73);

INSERT INTO Dogs VALUES (19, 'Sandy', 3, 'Female', 142);

INSERT INTO Dogs VALUES (20, 'Winston', 8, 'Male', 54);

INSERT INTO Dogs VALUES (21, 'Dakota', 1, 'Female', 82);

INSERT INTO Dogs VALUES (22, 'Tasha', 6, 'Female', 68);

INSERT INTO Dogs VALUES (23, 'Coco', 5, 'Female', 4.5);

INSERT INTO Dogs VALUES (24, 'Rocky', 1, 'Male', 68);

INSERT INTO Dogs VALUES (25, 'Rusty', 2, 'Male', 5.25);

INSERT INTO Dogs VALUES (26, 'Chelsea', 6, 'Female', 71);

INSERT INTO Dogs VALUES (27, 'Princess', 5, 'Female', 5);

INSERT INTO Dogs VALUES (28, 'Barney', 6, 'Male', 75);

INSERT INTO Dogs VALUES (29, 'Missy', 6, 'Female', 70);

INSERT INTO Dogs VALUES (30, 'Murphy', 6, 'Male', 66);

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).