Home‎ > ‎CIS 54‎ > ‎

Lab 9: Queries and Updates

In this lab you'll practice more queries and manipulate data using update and delete.  

Introduction

Most SQL you write will be queries. But a database is useless unless you can add, modify and remove data from it. In this lab you'll make a personal copy of the Garden Glory database and manipulate the data in it. In this lab you will create one SQL file. The file will accomplish all of the tasks in the lab in the proper order. To grade your lab I'll run the SQL file and test the output for correctness.  

Pet Database Queries

In this part you will use the pet database (public_pets). Write SQL to answer the following questions:
  1. Write a join query that joins PET_3 and BREED to output a table with the following attributes: 
    1. PET_3.PetName,
    2. PET_3.PetBreed, 
    3. PET_3.PetDOB,
    4. PET_3.PetWeight
    5. BREED.MinWeight
    6. BREED.MaxWeight
    7. BREED.AverageLifeExpectancy
  2. Update query #1 to include the pets with an unknown breed. 
  3. Update query #1 to include a calculated column that is the average weight of the breed as defined by (MinWeight + MaxWeight) / 2. 
  4. Filter query #3 to show only pets that are above average weight. 

Copy the Garden Glory Schema

Create your own schema for this lab then download and execute the SQL file below. It will create the Garden Glory tables and insert the data. 


Once you have completed this part you should have SQL statements that copy the Garden Glory database. 

Changing Staff

Garden Glory is successful and is hiring. Add the following employees:
  • Bill LaBill 
    • Phone: 555-555-1212
    • Level: Junior
  • Dilbert Smilbert
    • Phone: 555-555-2222
    • Level: Junior
  • Boots Hamburger
    • No phone
    • Level: Senior
Employees are on the move too! The following employees quit: 
  • Sam Smith
  • Dale Murray
Can you remove them from the database? If not how can you change their records to reflect that they are former employees? Describe how you will do that and update their records to show that they have quit.

New Properties

Sam Douglas bought a new apartment building. The new building is:
  • Name: Cabrillo Manor
  • Type: Apartments
  • Street: 123 Cabrillo Way
  • City: Aptos
  • State: CA
  • ZIP: 95062
Sam needs the lawn mowed right away which will take about four hours. Assign an employee to do it and make sure it gets done today!

Project Planning

In the project you will design a database for a business, web or personal use. The database should have at least six interrelated tables. More details will follow, but start thinking about your project and tell me what your idea for your database is. Please submit one paragraph that describes your plan and what enties and tables you'll need. 

Turn In

  1. The exercises from class
  2. A single SQL file with all of your statements.
  3. A paragraph describing your project idea.
Submit your homework on canvas.

Grading

  • 5 points for exercises
  • 10 points for your queries
  • 5 points for your paragraph
ċ
GardenGlory.sql
(5k)
Michael Matera,
Apr 13, 2016, 7:36 PM
Comments