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 retrieves the following columns for dogs with "retriever" in the breed name. Order the rows by the dogs weight.

DogName

BreedName

Gender

DogWeight

Screenshot #1: Retrievers

Step 3

Write a single query that retrieves the following columns:

Dog's Name

Breed Name

Dog's Weight

Show only rows for female dogs over 50 pounds. Order the rows by the dog's weight, with the heaviest dog first.

Screenshot #2: Large Females