In this exercise, you will practice creating a table of given specifications. You will then add and modify data:

Concepts you will need to know:

How to create a table using either the designer or by hand

Decide reasonable data types and if appropriate, lengths

Setting a primary key and identity column

Adding data constraints

Inserting, updating, and deleting data

In your own database, create a DogLicense table with the following structure. Use your own discretion for reasonable data types and lengths. If you use the Database Designer Tools, add a Check Constraint by right-clicking on the column in the designer and selecting Check Constraints... (7 Points)

The constraint is coded like the code following a WHERE in a SELECT (e.g. Age>=12 and Age<=21)

.

Table Name: DogLicense Structure:

License (assigned numeric computer assigned primary key starting at 1)

Expires (greater than 01/01/1990)

Sex (only M, F, NM, SF) (stands for Male, Female, Neutered Male, or Spayed Female)

PetName

Breed

OwnerLastName

OwnerFirstName

Address

Zip (99201-99212 only) (I would suggest non-numeric)

Phone (e.g. (555) 555-5555) Don't worry about the format. That is the interface programmer's responsibility.

I will give 1 extra credit points if you create the table using a CREATE TABLE command. Your work must include all design elements I described above in SQL. Include a copy of your CREATE TABLE command when you submit your work.

HINT: Do yourself a favor and use the IN statement rather than a string of OR statements in your constraints.

DATA MODIFICATIONS

Provide all the SQL Statements needed for the following. If a question asks you to change data you don't have (e.g. change fee from Male or Female dogs), create the statement as though the data does exist. You don't know how data will evolve.

Enter the following record first: (2 points)

License: 1 (<-doesn't matter)

Expires: 06/21/2023

Sex: NM

PetName: Rosco

Breed: St. Bernard

OwnerLastName: Freeman

OwnerFirstName: Mark

Address: 123 Meadow Ln.

Zip: 99207

Phone: (509) 555-1212

Add 6 more records to your table using INSERT. Include 3 examples of both INSERT methods (three with and three without field lists). (6 points)

After entering data, modify the phone number for your 4^th record, by a specific license number. Change the phone to read (509) 555-9897. Provide the modify command. (2 points)

Delete your third record by the owner's last name. (2 points)

There has been a Postal realignment. Change all zip codes that read 99207 to now read 99208. (2 points)

Mark Freeman has given his St. Bernard Rosco to his son-in-law Mason Reeves. Make the changes. (2 points)

Provide a list of all your existing records.* I will preview your structure in your database. (2 points)