CS 386/586 Exercise 2 Suggested Answers

Teacher table:
Number	Name	Office	E-mail
1	Lois	O12	lmd@whatever.edu
2	James	O14	jterwill@whatever.edu
3	Sally	O55	sally@whatever.edu

Course table:
Number	Name	Description
386	Intro to Databases	Introduction to relational database management systems
161	Intro to Computer Science 1	Introduction to problem-solving, algorithm and program design

Can-teach table:
Course	Teacher
386	1
386	2
161	2

Write a relational algebra query that is equivalent to each of the following SQL queries:

1. SELECT           DISTINCT Name
FROM               Teacher
WHERE Number > 1;

π_Name(σ_Number>1Teacher)

2. SELECT *
   FROM Teacher, Can-teach;
   
   Teacher X Can-Teach
   
   
3. SELECT  *
   FROM Teacher, Can-teach
   WHERE         Teacher.Number = Can-teach.Teacher;
   
   This query has a join condition between Teacher and Can-teach. As will all joins, you can express it like this with a cross product followed by a select (that uses the join condition):
   
   σ_{Teacher.Number=Can-teach.Teacher}(Teacher X Can-Teach)
   
   or you can use the join operator directly:
   
   Teacher ⋈_{Teacher.Number=Can-teach.Teacher} Can-Teach
   
   Both of these answers are correct; that is, both relational algebra queries are equivalent to the SQL query shown.
   
   
4. SELECT *
   FROM Teacher, Can-teach
   WHERE Can-teach.Course < 300 AND Teacher.Number = Can-teach.Teacher;
   
   This query has a join condition as well as a select condition. You can use the cross product operator (followed by a select operator) or a join operator to represent the join condition. In either case, you need a select operator to represent the select condition in the WHERE clause.
   
   σ_{Can-teach.Course<300}(σ_{Teacher.Number=Can-teach.Teacher}(Teacher X Can-Teach))
   
   You can list the select clauses in either order. So you could write this query like this:
   
   σ_{Teacher.Number=Can-teach.Teacher}(σ_{Can-teach.Course<300} (Teacher X Can-Teach))
   
   Also, since the select condition only applies to the Can-teach table, you could write this query like this:
   
   σ_{Teacher.Number=Can-teach.Teacher}(Teacher X (σ_{Can-teach.Course<300}Can-Teach))
   
   or you can use the join operator directly:
   
   σ_{Can-teach.Course<300}(Teacher ⋈_{Teacher.Number=Can-teach.Teacher} Can-Teach)
   
   and with the join operator, you can also push the select operator down to the Can-teach table because it only involves the Can-teach table like this:
   
   (Teacher ⋈_{Teacher.Number=Can-teach.Teacher} (σ_{Can-teach.Course<300}Can-Teach))
   
   All of these queries are correct; they are all equivalent to the SQL query given above.
   
   
5. SELECT DISTINCT T.Name, C.Name
   FROM Teacher T, Can-teach CT, Course C
   WHERE T.Number = CT.Teacher AND CT.Course = C.Number;
   
   This query has two join conditions and also has a final project operation.
   
   π_{T.name, C.name}(Teacher ⋈_{Teacher.Number=Can-teach.Teacher} (Can-Teach ⋈ _{Can-teach.Course=Course.Number} Course))
   
   or you could put the join conditions with two select clauses with their corresponding cross products operators like this:
   
   π_{T.name, C.name}(σ_{Teacher.Number=Can-teach.Teacher}(Teacher X (σ_{Can-teach.Course=Course.Number}(Can-Teach X Course))))
   
   There are other equivalent and thus correct relational algebra queries as well.
   
   

Consider the following table that represents products sold at several local convenience stores.  For each of the following requests:

Figure out the answer, using the data provided

Write an SQL query that will compute the query answer.

Use the Store Inventory table shown below. 

1. For each store, how many products are sold?  (List the store name and the count of products.)

This query simply takes all the rows in the inventory table, groups them by store. With a group by query, the only attributes that you can list in the final query answer are the grouping attributes (store, in this case) plus any aggregate operators. Aggregate operators, if present, are computed for each group. Thus, we get the count of individual items (as determined by beverage/size/price) for each store.

SELECT store, count(*)
FROM inventory
GROUP BY store;

store	count
Plaid Pantry	6
7-11	9
Circle K	8
Safeway	5

2. For each store, which product has the highest price (at that store)?  Please ignore the size of the beverage, just look at price.  (List the store name and the high price.)

This query is very similar. We group by store and we compute the max price.

SELECT store, max(price)
FROM inventory
GROUP BY store;

store	max
Plaid Pantry	3.20
7-11	1.10
Circle K	4.10
Safeway	2.75

3. Which products (identified by name) are only sold by one store?  (List the products.)

In this case, we want to consider individual products … and the various places they are sold. So, for this query, we group by beverage. This query is challenging because the name of a product can be repeated (with different sizes/prices) for a single store. Here are some possible queries and the answers they give.

If we simply group by beverage and find groups with COUNT(*) = 1, then we find beverage (names) that are only offered once (with just one size and one price); that is we find beverage names that only appear in one tuple in the entire inventory table. We use the HAVING clause to eliminate any group (that is, any beverage) that has more than one row.

SELECT beverage
FROM inventory
GROUP BY beverage

HAVING COUNT(*) =1;

beverage

Pepsi

Chocolate Milk

Diet Caffeine-Free Cherry Low-Fizz Vitamin-Fortified Coke

The problem here is that we miss beverages that are sold in two or more sizes at one store only. For the data shown below, we miss “Skim Milk” even though it is only sold at Safeway. Here’s an alternative query where we first project out only the beverage and store attributes from the inventory table (in the subquery shown in the FROM clause), and then group by beverage.

SELECT beverage
FROM (SELECT DISTINCT beverage, store FROM inventory)
GROUP BY beverage

HAVING COUNT(*) =1;

beverage

Pepsi

Chocolate Milk

Diet Caffeine-Free Cherry Low-Fizz Vitamin-Fortified Coke

Skim Milk

4. What would it cost to buy one of each milk product sold at each store?  (List the store and the total cost.)

For this query, we want to limit our query to only milk products. This means that we should put a condition in the WHERE clause to make sure we keep only the rows that describe milk products. Then we want to group by store and compute an aggregate (with the SUM) over price.

SELECT store, SUM(price)
FROM inventory
WHERE beverage = ‘Whole Milk’ OR beverage = ‘2% Milk’

OR beverage = ‘Chocolate Milk’ or beverage = ‘Skim Milk’

GROUP BY store;

store	sum
Plaid Pantry	5.40
7-11	3.40
Circle K	8.20
Safeway	7.20

Store Inventory Table
Store	Beverage	Size	Price
Plaid Pantry	Whole Milk	16oz	1.15
Plaid Pantry	2% Milk	16oz	1.05
Plaid Pantry	Whole Milk	64oz	3.20
Plaid Pantry	Pepsi	12oz	.70
Plaid Pantry	Diet Pepsi	12oz	.70
Plaid Pantry	Pepsi	20 oz	.95
7-11	Whole Milk	8oz	.65
7-11	Chocolate Milk	8oz	.65
7-11	Whole Milk	16oz	1.10
7-11	2% Milk	16oz	1.00
7-11	Coke	12oz	.65
7-11	Diet Coke	12oz	.65
7-11	Coke	20oz	1.10
7-11	Diet Coke	20oz	1.10
7-11	Diet Caffeine-Free Cherry Low-Fizz Vitamin-Fortified Coke	20oz	1.10
Circle K	Whole Milk	8oz	.60
Circle K	Whole Milk	16oz	1.20
Circle K	Whole Milk	32oz	2.30
Circle K	Whole Milk	128oz	4.10
Circle K	Coke	20oz	1.15
Circle K	Diet Coke	20oz	1.15
Circle K	Coke	32oz	2.10
Circle K	Diet Coke	32oz	2.05
Safeway	Skim Milk	16oz	1.20
Safeway	Skim Milk	32oz	2.00
Safeway	2% Milk	16oz	1.25
Safeway	2% Milk	64oz	2.75
Safeway	Diet Pepsi	12oz	.60
Safeway	Diet Coke	12oz	.60