Question
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:
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 4th 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)
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started