CPSC-110 Spring 2009
Lab 3
Thursday, February 5
Professor Heidi Ellis

Be sure that you hand in the print out of your work before leaving!!!

Objectives:

  1. Execute a series of complex queries against a database.
  2. Execute queries that use the built-in functions.
In this lab we will gain fluency using SQL queries. We will be investigating queries that use built-in functions, the in clause, the between clause and queries that use more than one condition in the where clause.

Pair Programming:
  1. Locate your partner and introduce yourself:
    2. Abby AldermanRobert Perryman 
    Richard CurtinPreston MaddockAlyssa Cockrell
    Cory DiBenedettoBen McDaniel 
    Briggs ElwellWhitney Merrill 
    Elise GalboRyan Piacentini 
    Brooke HaynesShanice Smith 
    Nikki LustigAndrea Titone 
    Brenna FoxMatt Vibert 
  2. Select one person's account in which to work.
  3. Select one person to start as "driver". Change drivers every five queries.
  4. Using KWrite (or another text editor), create a new file called lab3.txt. Put both team members' names in the document.
  5. Select the fuel_economy database and the fuel table.
  6. Take a minute to review the columns and rows in the table to familiarize yourself with the data. You need to understand the columns and their data in order to answer the queries.
  7. In the query area in the top, middle portion of the query browser, construct and execute an SQL query to answer the statements listed below. For each statement, write the SQL query that provides the correct response to the query in your lab3.txt file.
  8. Email the completed file to the partner in whose account you were not working so that they have a copy.
  9. Print the lab3.txt file and hand it in at the end of lab.
Queries:
  1. Show the class and displacement for all 8-cycle vehicles. Make sure that the result set contains no duplicates.
  2. Show the manufacturer and car line for all vehicles that get less than 20 MPG highway and have a displacement greater than 4. Label the column headings "Manufacturer" and "Line". Make sure that the result set contains no duplicates.
  3. Show the car line and combined miles per gallon for vehicles where the annual fuel cost is greater than $1500 or the combined miles per gallon is less than 25. Label the miles per gallon column "Combined Miles Per Gallon". Make sure that the result set contains no duplicates.
  4. Show the car lines for Toyotas that do not use premium fuel.
  5. Show the manufacturer, car line, and transmission for BMWs and Audis that get greater than 16 MPG city and Lexus' that get greater than 20 MPG city. Provide English-readable column names.
  6. Show the manufacturers of vehicles that have a displacement that is not between 2 and 3 and a rear wheel drive system. Make sure that the result set contains no duplicates.
  7. Show the manufacturers that start with "ch". Make sure not to include duplicate manufacturer names.
  8. Show the class and car line for all coupe vehicles that have an automatic transmission and get less then 25 MPG in the city.
  9. Show the car line and annual fuel cost for vehicles that use diesel fuel and vehicles that have a manual transmission and have an annual fuel cost less than $1100.
  10. Show the class and car line for automatic vehicles with an annual fuel cost of less than $1500 and manual vehicles with an annual fuel cost of less than $2000.
  11. Show the annual fuel cost and class for all vehicles that are any sort of a compact vehicle.
  12. Show an alphabetized list of manufacturers for all manufacturers of vehicles with automatic transmissions.
  13. Show the car line, manufacturer, and class of vehicles that have transmissions of type Auto(S6), Auto(L7), or Auto(S5). Use the "in" clause.
  14. Show the combined MPG and car line for all vehicles that are not Volvos, Mercedes, and Mazdas. Use the in operator. Make sure not to include duplicates in your result set.
  15. Show the car line, manufacturer, and class of vehicles that have average highway mileage between 20 and 30 MPG . Use the "between" clause.
  16. Show the average combined MPG for all compact vehicles.
  17. How many vehicles have more than 5 cycles?
  18. What is the minimum number of cycles for all vehicles?
  19. What is the minimum city MPG for all GMC vehicles that have a displacement between 3 and 4.
  20. What is the maximum number of cycles for all vehicles?
  21. How many vehicles made by Chevrolet or Ford have a highway MPG between 30 and 40?
  22. What is the average highway MPG for vehicles that are not within the range of 6 to 10 cycles?
Hand in the sheet containing the queries. Make sure that both partner's names are on the sheet.

You're done!! Be sure to have the professor or TA check off your work before you leave!!

Copyright © Heidi J. C. Ellis