Home‎ > ‎CIS 54‎ > ‎

Lab 8: Multiple Queries

In this lab you'll practice multiple queries and joins. 

Introduction

Normalization is needed to avoid modification problems. To get useful information from a normalized database you have to use more complicated queries. In this lab you'll use multi-part queries to answer questions. 

Queen Anne Queries

The Queen Anne Curiosity Shop want's to use their database to better understand their sales. Refer to the book to understand the structure of the tables and the meaning of the data (we worked on this data in Lab 5). The data can be found in the database public_queen_anne. 
  1. Write a query that joins SALE and CUSTOMER to output a table with the following attributes:
    1. CUSTOMER.FirstName
    2. CUSTOMER.LastName
    3. SALE.Total
  2. Update query #1 to instead show how much money each customer has spent in total. Order your query from the most to least money spent. 
  3. Write a query that joins SALE_ITEM, ITEM and VENDOR  to output a table with the following attributes. Your table should only contain items that have been sold (therefore exist in SALE_ITEM):
    1. ITEM.ItemDescription
    2. VENDOR.CompanyName
    3. VENDOR.ContactLastName
    4. ITEM.ItemCost
    5. ITEM.ItemPrice
  4. Update query #3 to determine how much profit has been made from sales of items from each vendor. 
  5. Write a query that shows which items in ITEM have not been sold.

Turn In

  1. The exercise queries from the lecture.
  2. Queries 1 through 5 for part 2. You should have one query for each part.
Submit your homework on canvas.

Grading

  • 5 points for the exercises 
  • 15 points for the Queene Anne Queries
Comments