CPSC-110 Spring 2009
Lab 4
Thursday, February 12, 2009
Professor Heidi Ellis

Be sure to hand in your print out before leaving!!!

Objectives:

In this lab we will gain fluency using SQL join queries. Remember that the result of an SQL query on a relation is another relation. A join is an SQL query that combines columns from two or more tables in the resulting relation. Tables that are to be combined are typically related to each other via keys.

A primary key is the column or combination of columns that uniquely identifies each row in a table. The values of primary keys must be unique. Lets consider an example of authors and books where authors write one or more books. The figure below shows the author relation. In this relation, the author_id is the primary key as each author must have a unique identification number.


A foreign key is the occurrence of the column or combination of columns that makes up the primary key in one relation (e.g., author_id), in another relation. For instance, in the book relation shown below, the author_id refers to the primary key author_id in the author relation. The author_id in the book relation is a foreign key to the author relation. In other words, the book relation contains a foreign key into the author relation.


Joins typically combine data from two relations based on this primary/foreign key relationship. The join is accomplished by setting the keys in the two relations equal to each other. For instance, to retrieve the information about the book and the author of the book, the SQL query would be: 
select *
from book, author
where book.author_id = author.author_id
Note that since both the book and the author table have an author_id attribute, you must specifically tell SQL the table from which to take the attribute. You do this by prefixing the attribute name with the name of the table followed by a period followed by the attribute name. For example book.author_id tells SQL to take the author_id from the book table. You may prefix any attribute with the table name (e.g., book.title. However, you are only required to use the prefix of the table name in cases where two tables have attributes with the same name. The results of this query are shown below.


There are times when table names are long and unwieldy and it is easier to use a shorter name for a table. This is known as aliasing. In order to provide an alias for a table, simply provide the alias after the table name in the from clause and then use the alias to refer to the table: 
select *
from book b, author a
where b.author_id = a.author_id
This query is equivalent to the one above.

Pair Programming:
  1. Locate your partner and introduce yourself:
    2. Abby AldermanAlyssa Cockrell 
    Richard CurtinRobert Perryman 
    Cory DiBenedettoPreston Maddock 
    Briggs ElwellBen McDanielMatt Vibert
    Elise GalboWhitney Merrill 
    Brooke HaynesRyan Piacentini 
    Nikki LustigShanice Smith 
    Brenna FoxAndrea Titone 
  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 lab4.txt. Put both team members' names in the document.
  5. 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 lab4.txt file.
  6. Email the completed file to the partner in whose account you were not working so that they have a copy.
  7. Print the lab4.txt file and hand it in at the end of lab.
 Queries
We will be using two different databases for this lab. (Note that not all queries are join queries.)

Banking Database

First we will work with the Banking database. Double click on the Banking database and view the various tables in the database. When you are familiar with the database, answer the following queries:
  1. Show all information for borrowers and the loans belonging to the borrowers.
  2. Show all information for borrowers and the loans belonging to the borrowers. Use an alias for both tables.
  3. Show the customer name, loan number, branch name, and amount for borrowers and their loans.
  4. Show the customer name, street, city, and loan number for borrowers who live in the city of Rye.
  5. Show the customer name and loan amount for loans given at the Downtown branch with a loan amount greater than $1200.
  6. Show the customer name, account number, branch name, and balance for all depositor accounts.
  7. Show the customer name, street, city, account number, branch name, and balance for all depositor accounts. (Hint: 3-way join on customer, depositor, and account tables)
  8. Show the customer name, customer's city, account number, branch name, and balance for all depositor accounts with a balance of more than $500.
  9. Write your own query that uses at least three tables in the database.

GadgetCadge Database

Now open the GadgetCadge database by double clicking on the appropriate database. Explore the tables until you are comfortable with the database. Note that the order_item table holds information on the products and quantity ordered while the g_order table relates the actual order item with the customer that placed the order. Answer the following queries:
  1. Show the order number and product name for all products that have been ordered.
  2. Show the product name and manufacturer for all taxable products.
  3. Show unit cost and delivery date of all products ordered by customer with id c222.
  4. Show the order ids and quantity ordered for orders that contain a Slingbox.
  5. Show the customer id, customer names, product names and unit costs of all products that have been ordered. Hint: this requires a multi-table join.
  6. Show the names, email addresses and order ids of all customers who have placed an order. Sort the results based on customer name. Try sorting in decreasing order.
  7. Show the customer name, order id, product name, quantity and unit cost for all orders.
  8. Repeat the query above and add a column that shows the item cost (multiply quantity and unit cost). Label the new column "ItemCost".
  9. Show the category name and average unit cost for all taxable products that have been ordered. Sort by average unit cost. Hint: You will have to alias the average unit cost.
  10. Challenger: Show the order ids, customer names, and total of the orders for all orders. Hint: you can do math right in the select statement by saying something like: sum(quantity * unit_cost).
  11. Write your own query that uses at least three tables in the database.
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 Ellis