Learning SQL (SQL Lab)
In-class Activity (please post discussion in Blackboard)
Assignment: (adapted and updated with approval from a project designed by Dr. Wilcox, 1996)
This assignment is designed to gain hands on experience with Access by creating tables and designing SQL statements. Please use the posted PowerPoint Presentation in order to better understand SQL and get some hints for the below tasks. Your project is based on the tables with a similar design and similar SQL questions.
Task #1: Create the 3 below tables (open Access and then a blank database; call your database “yourlastnameSQLLab”; click on the Table tab and then design view; you can now create the ANIMAL table; the fields are AnimalName (text), AnimalType (text), Weight (number), DateOfBirth (text), and FavoriteFood (text); now you can populate the table in the datasheet; do the same thing for the FeedingSchedule and KeeperName tables; RULE – do not use spaces for table or column names – you can either use _ or Caps/Lower cases as above; an Access tutorial is available in the Course Documents section of the classroom):
Animal
|
AnimalName
|
AnimalType
|
Weight
|
DateOfBirth
|
FavoriteFood
|
|
Happy
|
Elephant
|
3000
|
1/20/02
|
Oranges
|
|
Go
|
Chimpanzee
|
40
|
4/6/04
|
Popcorn
|
|
Lucky
|
Tiger
|
250
|
5/5/06
|
Raw Rabbit
|
|
Stripes
|
Tiger
|
200
|
3/23/01
|
Lamb
|
FeedingSchedule
|
Day
|
AnimalName
|
KeeperName
|
NumberOfFeedings
|
|
Monday
|
Happy
|
Jena
|
5
|
|
Monday
|
Go
|
Jena
|
3
|
|
Monday
|
Lucky
|
Jena
|
3
|
|
Monday
|
Stripes
|
Paul
|
1
|
|
Tuesday
|
Happy
|
Jena
|
5
|
|
Tuesday
|
Go
|
Kevin
|
5
|
|
Tuesday
|
Lucky
|
Kevin
|
3
|
|
Tuesday
|
Stripes
|
Paul
|
1
|
|
Wednesday
|
Happy
|
Kevin
|
2
|
|
Wednesday
|
Go
|
Kevin
|
3
|
|
Wednesday
|
Lucky
|
Paul
|
2
|
|
Wednesday
|
Stripes
|
Jena
|
1
|
Keeper
|
KeeperName
|
City
|
Telephone
|
FavoriteFood
|
Age
|
|
Jena
|
Bellevue
|
553-4567
|
Oranges
|
26
|
|
Paul
|
Omaha
|
555-9880
|
Oranges
|
22
|
|
Kevin
|
Lincoln
|
567-4512
|
Popcorn
|
18
|
|
Linda
|
Nebraska City
|
647-1122
|
Oranges
|
35
|
Task #2: In Access, write SQL queries to do the following (do not use the Wizard or Design view within Access…click on Design View and a pop-up window will appear; click the red X, and you will see the letter SQL in the top left of the program; a PowerPoint tutorial is available under Course Documents which relates to the below queries):
- List the ages and telephone numbers of all keepers.
- List the names of all keepers younger than 25.
- List the names of keepers who feed Lucky.
- List the name and age of all keepers who feed Lucky. Use the IN sub query construct (nested SELECTS – see hints in the PowerPoint presentation).
- List the name and age of all keepers who feed Tigers. Use the IN sub query construct (nested SELECTS – see hints in the PowerPoint presentation).
- Change Kevin’s age to “19”
- Insert a new record into the Keepers table with the values “Jimmy”, “Council Bluffs”,”788-0012” (use an SQL statement)
- Delete the record you inserted into the Keepers table in #7 (use a SQL statement).
Task #3: Once all of the above queries are functioning properly, email a Word document with your queries to your instructor…copy and paste each query to a Word document. You can discuss this lab in the discussion board using names and the actual SQL code.
Your project will be posted next week and you will not be able to discuss the project in the classroom, so please use the discussion forum this week and next for questions pertaining to the above lab.
