CS 386/586 Winter 2012 Practice using Group BY with Having

These exercises are to reinforce concepts from the course, and are not to be turned in. You can work on them alone or in groups.

Consider the following table that represents products sold at several local convenience stores. 

Store (Store, Beverage, Size, Price)

Note this table has been added to the Postgresql introdb database. Here’s how you find it. Click on the “introdb_sailors” database. You’ll now see an extra schema listed (inside introdb_sailors) that is called “introdb_store”; the sailors schema is listed under “Public”. Once you click on the “introdb_store” schema, you’ll see one table called “pricelist”. It has the data that is shown on the bottom of this page.

For each of the following requests:

Figure out the answer, using the data provided

Write an SQL query that will compute the query answer.

Note: you can run your queries against the Store table, in the Sailors database on the phpPGAdminIntrodDb page:
https://www.cat.pdx.edu/phpPgAdminIntroDb/

It has the same data as shown down below.

For each of the following requests:

a) Figure out the answer, using the data provided

b) Write an SQL query that will compute the query answer.

Use the Store Inventory table shown below. Note that in-class students will be given a packet of “tuples” plus one “schema tuple” in a set. Out-of-class students can create the same resource by printing the table below and then cutting each row apart from the rest. Manipulating these rows, e.g., into groups, can be useful.

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

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.)

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

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

Store-Product-Price 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 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