Oregon State University
Oregon State University Home Page

Up

Spring Term, 2006, BA271 Query Exam

This quiz is open-book, open network. So feel free to browse the class website or the Course Technology textbook for hints or pointers.

If you have questions about these ground rules, please discuss them with Dave Sullivan before continuing.

Instructions:

  • This link leads to the Access database you should use when practicing this query quiz.

  • Your queries should be named Answer1, Answer2, Answer3, and Answer4. They should have identical column headings and number formatting as the images shown and described below.
  • The data in your database will be different from the data in my database when I captured the images shown below. Thus, your queries should have the same column headings and formatting, but they will produce a different number of rows and different values.
Answer1: What products have Ann Devon bought from Davolio?
  • Product Name should come from the Products table.
  • Ann Devon is a ContactName in the Customer table, and Davolio is a LastName in the Employee table.
  • Sort in ascending order by Product Name.
  • Hide the ContactName and LastName fields so they do not appear in your answer.

Answer2: Management is concerned that too many large orders are being delivered late. Write a query that lists the Supplier table's Contact Name, Sales (calculated as Quantity times Unit Price) and Order Date for all orders whose Order Date was in the last three months of 1995 and where the Required Date minus the Shipped Date is negative.
  • Contact Name should come from the Suppliers table.
  • Limit the attention of the query to only those sales where the Order Date is in the last three months of 1995.
  • List only those transaction where the Required Date is less than the Shipped Date.
  • Sort the query in descending order based on Sales.

Answer3: Which Company has shipped the most items of Produce to Germany?
  • Category Name should come from the Category table.
  • Country should come from the Customer table.
  • Company Name should come from the Shipper table.
  • Sort the query in descending order based on the Count of Items Shipped.
  • Note: the query shown below counts how many items were shipped to France -- but your query should count how many items were shipped to Germany.

Answer4: What are the top 5 companies that have bought Produce?
  •  Company Name should come from the Customer table.
  •  Category Name should come from the Category table.
  • Total Net Sales should be calculated from the Order Details table as Unit Price times Quantity times 1 minus the Discount rate.
  • Sort the query in descending order by Total Net Sales.
  • Make sure you write a "top 5" query so that only the five best companies appear in your answer.
  • Note: the image below shows which companies bought the most Beverages, not which ones bought the most Produce.

 

This website was created and is maintained by Dave Sullivan.
[College of Business Home Page] [OSU Home Page] [OSU Disclaimer]