Lab Assignment 2 (due April 27, 16:00pm)

• General Description
• Assignments
• Submission Summary
• Useful Links
• Sample Solutions

General Description

In this lab assignment, you will practise writing SQL queries to answer real world questions regarding the beer sales management DB. You will need to know SQL SELECT (including nested SELECT clause), natual join, DISTINCT keyword, ORDER BY (to order the query results), aggregation functions like AVG (to get average value for values of one column), MIN/MAX (to get mininum/maximum value for one column), COUNT (to count the number of values of one column). In some advanced questions, you need to know GROUP BY, HAVING clauses as well. Postgresql Searchable Online Manual and the Useful Links are the places you should visit when you have problems.

The DB schema consists of five relations: Frequents, Bar, Serves, Likes and Drinker. (This part is the same as in lab 1, you may skip it if you want. I put here just for your quick reference.)

//-- Frequents Relation --
//drinker: name of the drinker
//barid: name for the bar
//day: date on which a drinker goes to the bar
Frequents(drinker, barid, day)

//-- Bar Relation --
//barid: name for the bar
//address: address of the bar
//capacity: maximum number of people the bar can host
//owner: name of the person who owns the bar
Bar(barid, address, capacity, owner)

//-- Serves Relation --
//barid: name for the bar
//beer: brand name for a beer
//price: price for a bottle of beer in a bar
Serves(barid, beer, price)

//-- Likes Relation --
//drinker: name of the drinker
//beer: brand name for a beer that the drinker drank on a particular day
//day: date
Likes(drinker, beer, day)

//-- Drinker Relation --
//drinker: name of the drinker
//age: age of the drinker
//occupation: drinker's occupation
//salary: yearly salary of the drinker
Drinker(drinker, age, occupation, salary)

Assumptions about the schema:

1) The primary keys are underlined, note that in some cases they consists of several attributes. A primary key on table A should be defined as a foreign key on table B if it appears as an attribute in table B, e.g. drinker is a primary key on the Drinker table and a foreign key on the Likes table.

3) Assume each bar has only one owner and same owner may have multiple bars.

4) Salary is the yearly salary for the drinker.

5) Data type definition:

• capacity, age are non-negative integers; day is an integer between 1 and 7 (including 1 and 7).
• drinker, owner, beer, occupation, barid and address are of string type.
• price and salary are of float type and they are non-negative.

6) All data fields should be not null.

Assignments

This assignment consists of 8 questions, for each question, you are asked to submit a script file that includes the SQL query and a log file that contains the session when you run your query.

In order to simplify the evaluation process, we provide the standard DB schema and populate the tables with a set of standardized script files. Please drop the tables you have created in the first assignment and run these script files first. The following table shows the steps to run the script files. In case you need to remove the tuples from the five tables, use del_tables_content.script.

Query 1: Find drinker names and occupations of drinkers whose age is below 21.

Query 2 : Find the name, occupation and salary of drinkers who like Bud Light: show your result in ascending order of salary, duplicate tuples are ok.

Query 3 : Find bar names and address information for those bars that sell Bud Light at the cheapest price.

Query 4 : Find bar names, address information, and bar owner for the bars that Tom frequents on Friday, e.g. day 5.

Query 5 : Find the number of bars that Tom frequents, make sure you don't count the same bar multiple times.

Query 6 : Find the minimum and maximum age of the drinkers who frequent the bar Watering Hole.

Query 7 : Find the name and occupation for the youngest and oldest drinkers who frequents Watering Hole, no duplicates. Hint: use two SELECT queries and merge the query of subquery results with UNION.

Query 8 : Find the age, occupation, and salary of drinkers who frequent bars more than 3 times a week.

Submission Summary

Query style: it is always a good habit to write SQL queries using nice styles, just when you write code in C or Java. Here are some important points for your submission style:

• Use UPPER case for the keywords
• Use appropriate indentation to make query structure easy to read
• Use parenthesis where needed

How to debug your query: some queries involve sub-select clauses and a better way to debug is to start from small pieces and make sure the sub-selects are returning correct answers first. Then you can iterate this debug process until the final results are returned.

Items to submit	Description
query#.script (# is 1-8), e.g. query1.script	Eight script files, each for a query question.
query#.log (# is 1-8), e.g. query1.log	Eight log files, each for a session that records the execution of a query script.

You should use submit program to submit the assignments. If you dont know how to use submit, please read "how to use submit" for details. Sample submit commands for this lab assignment:

submit cmps180-wt.s04 lab2 query1.script //to submit query1.script

submit cmps180-wt.s04 lab2 query2.script query2.log //to submit two files together

peek cmps180-wt.s04 lab2 query1.script //to examine the query1.script file you submitted

Useful Links

• Practical Postgresql by John Worsley and Joshua Drake, edited by Andrew Brookins and Michael Holloway.
• Use the Safari Tech Books Online (only accessible from UCSC campus) to find DB or Postgresql related resources.
• Philip Greenspun's "SQL for Web Nerds"

Sample Solutions

• Here is the sample solutions for 8 queries.