question archive The tables included in the questions below were in form of a snip
Subject:AccountingPrice: Bought3
The tables included in the questions below were in form of a snip. So I copied the exactly as they appeared vertically and horizontally in the original sniped tables. Please treat them as if they are tables.
CITD 120 -- SQL Concepts
Homework Assignment 2 (60 points)
Your Name: ______________________________________
You will now be directed to use the
online SQL Emulator to accomplish the following Tasks. You have
been sent an email providing you with the URL for the Online SQL Course and your unique login
ID/Password for access to this site. If you have not received this email, then contact your instructor.
Using the SQL Emulator
- prepare SQL statements to perform the following tasks. Each task is worth
10 points. For all tasks
show a screenshot of what the emulator provided (i.e., The Red Box). Place
each screenshot within a single Word document indicating what task you are answering. If you are
unclear as to what is required, please review the "Answer Example" provided.
For Tasks 3-6, please provide BOTH the SQL used to make the change (a screenshot of the Red Box)
and Confirm the change by showing the resultant Table (another screenshot of the Red Box).
Tasks:
1. (10 points) Using the SQL Emulator Create a Table named "Type" (case counts). Use the
Table below for the column specifications. Your answer should include a screenshot of
what the emulator provided (The Red Box).
Column Name Column Type Primary Key Not Null Comment
TypeID Integer Yes Yes Pet type identifier. The
highest type id will be 99.
Food Maximum of 20 No Yes Type of food
characters
CageSize Exactly No Yes Size of cage for this
1Character pet type
WaterBowlSize Exactly No Yes Size of water bowl
1 Character
FoodBowlSize Exactly No Yes Size of food bowl
1 Character
NeedsWalk Exactly No Yes Y if this type of pet
1 Character needs to be walked
NeedsLitter Exactly No Yes Y if this type of
pet needs litter
1 Character
NeedsLiner Exactly No Yes Y if this type of pet
1 Character needs a cage liner
2. (10 points) Using the SQL Emulator Create a Table named "Pet" (case counts). Use the
Table below for the column's specifications. Your answer should include a screenshot of
what the emulator provided (The Red Box).
Column Name Column Type Primary Key Not Null Comment
PetID Integer Yes Yes The highest pet id will be
9999
OwnerId Exactly 7 Yes Yes The id of the pet's owner
characters
TypeID Integer Yes Yes References the
Type Table
PetName Maximum 30 No No
characters
Description Maximum of 50 No No
characters
Birthday Date No No
LastVisit Date No No
3.(10 points) Enter the following record (row) into the Pet Table. Show your SQL and
Confirm record addition: Please note the date format, namely CCYY-MM-DD (ISO
8601:1988).
PetID OwnerId TypeID PetName Description Birthday LastVisit
150 53127 2.5 Baby Yorkie 2009-12-5 2019-1-15
4.
(10 points)
Enter the following record (row) into the Pet Table. Show SQL and Confirm
record addition:
PetID OwnerID TypeID PetName Description Birthday LastVisit
450 45123 4.6 Spike Bulldog 2012-10-2 2018-3-28
At this point, if you have done everything correctly, you should be able to review your work online.
Using the Online
SQL
Course site select 'Table Set 1 (Questions)'" and review 'Type". That will show
you that the 'Type" Table exist but contains no records. Selecting the Table "Pet" will show the two
records you entered.
In the next two questions, you are going to be asked to drop Tables you created from your Database.
Please note, dropping Tables can be dangerous. DO NOT DROP any Tables other than what you are
directed to drop. Please note, you will need all the Tables noted in your account. If something goes
wrong and you accidentally did something you should not have, please notify your instructor.
5.
(10 points) Using the SQL Emulator Delete the Table entitled "Type". This is the Table
you created in answering question 3. Provide a screenshot of what the emulator provided
(The Red Box). Confirm the Table no longer exists.
6.
(10 points) Using the SQL Emulator Delete the Table entitled "Pet". This is the Table
you created in answering question 4. Provide a screenshot of what the emulator provided
(The Red Box). Confirm the Table no longer exists.
At this point, if you have done everything correctly, you should be able to review the Tables
"Type" and "Pet" and see they no longer exist in your database.