CPSC-110 Computers, Information and Society
Lab 2
Thursday, January 29, 2009
Professor Heidi Ellis

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

Objectives:

  1. Understand the structure of SQL queries.
  2. Execute a series of queries against a database.
  3. Use the KWrite editor to save your queries and print.

Part 1: SQL Structure
The first objective of this lab is to understand the structure of SQL squeries. We will be using the FuelEconomy database to run examples so you should start up the MySQL query browser. You might want to review lab 1 to refresh your memory on how to open the query browser.

Structured Query Language (SQL) is used to access and modify data in a relational database. We will be using the SQL select statement to retrieve data from the fuel table. The select statement has the following syntax: 
select <columnname1, columnname2,...> 
from <table names> 
where <conditions>;
Note that the where clause is optional.

SQL may retreive a subset of the columns of a table, called projection, a subset of the rows of a table, called selection, or a subset of both rows and columns, called combination. An example of a projection is the query to return the IDs for all entries in the fuel table: 
select ID 
from 'fuel' f;
This query returns a single column with all rows of data present. Multiple columns may be retrieved by separating column names by commas in the query. For example, a query to retrieve the ID and manufacturer for all entries in the fuel table appears like this: 
select ID, Mfr 
from 'fuel' f;
The columns in the output will appear in the order in which you specify the column names in the query. In the query above, the ID will be shown as the first column on the left. The names of the columns displayed in the result can be labeled by providing the display name after the column name. For instance, a query to retrieve the ID and manufacturer for all entries in the fuel table where the Mfr column is labeled "Manufacturer" appears like this: 
select ID, Mfr Manufacturer 
from 'fuel' f;
Note that there is no comma separating the Mfr column name and the display name of Manufacturer. This is because we are renaming the Mfr column name to be Manufacturer. There is however a comma between the ID column name and the Mfr column name as the comma is the required delimiter (separator) between column names.

A selection query uses the where clause to select a subset of the rows in a table. The subset is selected based on whether a row meets the criteria specified in the where clause. For instance, the query to retrieve all information for all 4-cycle vehicles is: 
select * 
from 'fuel' f
where NumCycles=4;
This query retrieves all columns where the data in the NumCycles column equals 4.

In a combination query, the query uses a mix of column names, possibly display names and selection clauses. For example, the query to retrieve only the IDs and manufacturers for 4-cycle vehicles is: 
select ID, Mfr 
from 'fuel' f
where NumCycles=4;
What about capitalization? The capitalization rules vary across DBMSs. To be safe, follow these rules: Do I need the semicolon at the end of the query? The semicolon is not necessary when executing queries within the MySQL query browser. However, I suggest that you get used to using them as semicolons at the end of each query will be imporant when we do stored procedures later in the semester.

Pair Programming:
  1. Locate your partner and introduce yourself:
    2. Abby AldermanPreston Maddock 
    Richard CurtinBen McDaniel 
    Cory DiBenedettoWhitney MerrillRobert Perryman
    Briggs ElwellRyan Piacentini 
    Elise GalboShanice Smith 
    Brooke HaynesAndrea Titone 
    Nicki LustigMatt Vibert 
    Brenna FoxAlyssa Cockrell 
  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 lab2.txt. Put all 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 lab2.txt file.
  8. Also indicate whether each query is a projection, selection, or combination.
  9. Email the completed file to the partner in whose account you were not working so that they have a copy.
  10. Print the lab2.txt file and hand it in at the end of lab. To print from Kwrite:
    1. Select File/Print
    2. Select the options tab
    3. Change “Print system currently used” to "CUPS from Generic"
    4. On the pull-down menu for printers, select syslabprn
Queries:
  1. Show all information in the fuel table.
  2. Show the car class for all entries in the fuel table.
  3. Show the highway miles per gallon and the annual cost for all entries in the fuel table.
  4. Show the number of cycles, manufacturer and car line for all entries in the fuel table. The data should be shown in the order specified (i.e., number of cycles shown first followed by manufacturer, then car line).
  5. Show the manufacturers for all entries in the fuel table. Make sure that each manufacturer is only listed once. (Hint: You will need to use the distinct key word.)
  6. Show the manufacturer, transmission, and city miles per gallon for all entries in the fuel table. Title the columns "Manufacturer", "Transmission", and "CityMPG".
  7. Show the combined MPG, car line, and manufacturer for all entries in the fuel table. These columns should be in the following order with the following titles: "Combined MPG", "Car Line", "Manufacturer". (Hint: You may have to put the column names in quotes.)
  8. Show all information for all vehicles manufactured by Chevrolet.
  9. Show all information for all vehicles that have a city MPG greater than 20 miles per gallon.
  10. Show all information for vehicles that have rear wheel drive.
  11. Show the manufacturer and car line for all vehicles that are two seaters.
  12. Show car line and manufacturer (in that order) for all vehicles with turbo engines.
  13. Show all information for gas guzzlers.
  14. Show the displacements for vehicles with annual fuel costs in excess of $1800.
  15. Show the manufacturer, transmission, and city miles per gallon for vehicles that have three valves per cylinder. Title the columns "Manufacturer", "Transmission", and "CityMPG".
  16. Show the combined MPG, car line, manufacturer, displacement and drive system for all MAZDAs. These columns should be in the following order with the following titles: "Combined MPG", "Car Line", "Manufacturer", "Displacement", and "DriveSys".
  17. Show the combined MPG, car line, and manufacturer for vehicles with annual fuel costs less than $1900. These columns should be in the following order with the following titles: "Combined MPG", "Car Line", "Manufacturer".
  18. Show the combined MPG for your favorite vehicle. Show both the make and model in the results.
  19. Create your own unique query that returns four or more columns and uses a where clause.
  20. Create your own query that returns four or more columns labeled with names other than the column names and uses a where clause.
Hand in the sheet with your SQL queries for lab credit.

If You Have Time

Find another database in MySQL and try several queries against the data. Good candidate databases include gadgetcadge and information_schema.

Hand in your sheet listing the SQL queries. Include the names of all partners.
You're done!!