CPSC-110 Spring 2009
Lab 6
Thursday, March 12, 2009
Professor Heidi Ellis
Be sure to hand in your print out before leaving!!!
Objectives:
- Understand how subqueries work in SQL.
- Understand the filtering use of subqueries.
In this lab we will continue our understanding of SQL queries by exploring subqueries.
Subqueries
As queries grow more complex, results may require creation of intermediate results in the form of relations. These temporary relations are created via a subquery.
A subquery is simply a query that is written as part of another query's where clause. Typically subqueries do one of the following:
- Test membership of a row in a set.
- Perform set comparison using one of the standard set operations (e.g., union).
- Determine the cardinality of a set.
Subqueries are structured so that it is possible to isolate each part of a statement. Subqueries also provide an alternative to complex join queries. In addition, subqueries may be more readable and more understandable. In fact, it was the innovation of subqueries that gave people the idea of calling SQL "Structured Query Language".
The GadegeCadge Database.
We will be running our subqueries against the GadgetCadge database. The figure below shows an overview of the tables in the GadgetCadge database and how the tables are related:
Lets look at a simple query: "Find all product ids for products that are both listed in an order and contained in a shopping cart." In order to complete this query, we need to know two things:
- Which product ids are listed in an order, and
- Which product ids are contained in a shopping cart
We need to know which product ids are contained in both the set of product ids listed in an order and the product ids that are contained in a shopping cart. To determine this, we will retreive the set of product ids that are in the shopping cart and then retrieve any ids from that set that are listed in an order. In order to break the problem down into pieces, we start by framing each of these things as an independent query:
- select product_id from order_item
- select product_id from s_cart
Execute each of these and compare the results of the two queries. Postulate the set of product ids that are in the intersection of the two sets.
Next we will construct a subquery that retrieves the product ids that are in the order_item table that are also in the s_cart table. In order to do this, we make the query to retrieve product ids from the shopping cart a subquery:
select product_id
from order_item
where product_id in
(select product_id
from s_cart);
The system will first execute the subquery (select product_id from s_cart) which will result in a temporary relation containing a single column of the product ids that are contained in the s_cart relation. Next the system will select the product ids from the order_item and will compare these ids with the product ids in the temporary relation containing the ids that are in the s_cart relation. If a product id is found in both the order_item and the s_cart relations, the product id will be added to the result relation (i.e., the relation that is displayed.)
Pair Programming:
- Locate your partner and introduce yourself:
| Abby Alderman | Shanice Smith | |
| Richard Curtin | Matt Vibert | |
| Cory DiBenedetto | Alyssa Cockrell | |
| Briggs Elwell | Robert Perryman | |
| Elise Galbo | Preston Maddock | Ryan Piacentini |
| Brooke Haynes | Ben McDaniel | |
| Nikki Lustig | Whitney Merrill | |
| Brenna Fox | Andrea Titone | |
- Select one person's account in which to work.
- Select one person to start as "driver". Change drivers every five queries.
- Using KWrite (or another text editor), create a new file called lab6.txt. Put both team members' names in the document.
- 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 lab6.txt file.
- Email the completed file to the partner in whose account you were not working so that they have a copy.
- Print the lab6.txt file and hand it in at the end of lab.
Queries
For each of the statements below, write an SQL query that uses a subquery and an SQL query that uses a join. Break the problem down into the independent queries that retrieve the desired information from the individual tables. Then modify and combine these independent queries into a larger one that retrieves the desired information. Copy your SQL queries into a text file and hand in.
- Retrieve the names of products that have been ordered.
- Retrieve the names of products that are non-taxable.
- Retrieve the customer names for all customers who have placed an order.
- Retrieve the names and order ids of customers who have placed an order. Note that this query cannot be done easily using subqueries. Why not?
- Retrieve the names of any products that have not been ordered. Note: you will need to use the not in clause instead of the clause. Also note that this query cannot be done using joins. Why not?
- Retrieve the names and category ids of any products that have not been ordered. Use a single query. Note that this query uses both a subquery and a join.
- Retrieve the names of all products not in a shopping cart nor in an order. Note, no join needed.
You're done!! Be sure to have the professor or TA check off your work before you leave!!