CS386 Introduction to Databases
Spring 2006 Quarter

---

Assignment 3 - SQL & Relational Algebra 2
Due: Thursday, 27 April 2006 at the beginning of class

You may do this assignment individually or you may work with one partner.  That is, this assignment is to be completed by individuals or by teams of two students.  If you work with a partner, then you should turn in one assignment paper, with both of your names on the paper. You should only talk to the instructor, the TA and your partner about this assignment. You may also post questions to the course mailing list, cs386@cs.pdx.edu.

Please turn in your completed assignments on paper. Put your last name, first name, the assignment number in that order in the first line of your assignment.  List last name and first name for your partner, if you have one, on the second line of your assignment. (If you are working with a partner, turn in one assignment paper.)

This assignment is again based on the Spy relational database. Information about this database and information how to access it is on the Database Info Page. Note that the database you want is called ‘introdb_spy’.
Part I: Relational Algebra

The algebraic expressions below are for the database with schema officers(club, role, pname) and location(club, room) used in Quiz 2. For each equation below, say whether it is true or false in general. Whenever your answer is “false”, give an example instance of the database where the two expressions are not equal (and show the value of the expressions for your database instance). [Note: If the algebra looks funny in your browser, there is a PDF version of this HW at hw06-03.pdf.]

Question 1 (5 points): (s_{officers.club=’biking’} (s_{officers.role=’Treasurer’(}officers)) =
(s_{officers.role=’Treasurer’} (s_{officers.club=’biking’} (officers))

True.

Question 2 (5 points): p_{location.room}(s_{officers.club=location.club}(officers ´ location)) = p_{location.room}(location))

False. Suppose the officers relation instance is empty, and the location relation instance is as in Quiz 2. Then p_{location.room}(s_{officers.club=location.club}(officers ´ location)) is empty, but p_{location.room}(location)) contains SH118 and NH312.

Question 3 (5 points): p_{officers.pname,location.room}(s_{officers.club=location.club AND role=’Treasurer’}(officers ´ location)) =
p_{officers.pname,location.room}(s_{officers.club=location.club} (s_{officers.role=’Treasurer’}(officers) ´ location))

True.

Question 4 (5 points): p_{officers.pname}(officers – s_{officers.role=’Treasurer’}(officers)) =
p_{officers.pname}(officers) – p_{officers.pname}(s_{officers.role=’Treasurer’}(officers))

True.

Part II: Group-by and subqueries

Write the SQL for the following queries. Show (at most) the first five rows of the result for
each query and the number of rows returned.

Question 5 (10 points): List the highest and lowest salary for agents in each country.

SELECT country, MAX(salary) as highest_salary, MIN(salary) as lowest_salary

FROM Agent

GROUP BY country;

country	highest_salary	lowest_salary
Luxembourg	78945	58093
Iraq	128956	50008
Russia	57984	50665
Japan	90745	50320
England	89645	54152

22 rows

Question 6 (10 points): List the highest and lowest salary for agents in each country with at least 10 cities.

SELECT country, MAX(salary) as highest_salary, MIN(salary) as lowest_salary

FROM Agent

GROUP BY country

HAVING COUNT(DISTINCT city) >= 10;

country	highest_salary	lowest_salary
USA	366962	50171

1 row

Question 7 (10 points): List the number of agents who speak each language.

SELECT language, COUNT(*)

FROM Language NATURAL JOIN Languagerel

GROUP BY language;

language	count
English	12
Korean	112
Spanish	92
Vietnamese	111
Bengali	89

20 rows

Question 8 (15 points): List the agents (id, first, last) who have been on a ‘Secret’ mission that failed. Write this query two ways, once using EXISTS and once using IN.

SELECT DISTINCT agent_id, first, last

FROM Agent NATURAL JOIN Teamrel NATURAL JOIN Mission M

WHERE mission_status = ‘failed’ AND EXISTS (

SELECT *

FROM Securityclearance SC

WHERE SC.sc_level = ‘Secret’ AND SC.sc_id = M.access_id );

SELECT DISTINCT agent_id, first, last

FROM Agent NATURAL JOIN Teamrel

WHERE team_id IN (

SELECT team_id

FROM Mission JOIN Securityclearance ON access_id = sc_id

WHERE mission_status = ‘failed’ AND sc_level = ‘Secret’ );

agent_id	first	last
1	Nick	Black
5	George	Fairley
20	George	Jones
43	Jim	Novick
48	Bill	Bellegarde

105 rows

Question 9 (15 points): Return the country for which the agents have the highest average salary, along with that average salary.

Useful information: You can use subqueries in the FROM clause and the HAVING clause.

Example subquery in the FROM clause:

SELECT MAX(cityaverages.avsal)
FROM (SELECT AVG(salary) AS avsal FROM agent GROUP BY city) AS cityaverages

Example subquery in the HAVING clause:

SELECT city, avg(salary)
FROM agent
GROUP BY city
HAVING 2*COUNT(agent_id) > ALL (SELECT COUNT(agent_id) FROM agent GROUP BY city)

[Here are two sample solutions of different styles.]

SELECT country, AVG(salary) as avg_salary

FROM Agent

GROUP BY country

ORDER BY avg_salary DESC

LIMIT 1;

SELECT country, AVG(salary) as avg_salary

FROM Agent

GROUP BY country

HAVING AVG(salary) >= ALL (SELECT AVG(salary) FROM Agent GROUP BY country);

country	avg_salary
USA	103376.768025078370

1 row

Part III: Views

For this part, you only need to write SQL statements. You don’t need to execute them.

Question 10 (10 points). Write an SQL view definition for a table lang_count(agent_id, num_langs) that lists the number of languages for each agent.

CREATE VIEW lang_count AS

SELECT agent_id, COUNT(DISTINCT language) AS num_langs

FROM Agent NATURAL JOIN Languagerel NATURAL JOIN Language

GROUP BY agent_id;

Question 11 (10 points). Write an SQL query that finds the average number of languages spoken by agents, in each country. Use the lang_count view you defined in the previous question.

SELECT country, AVG(num_langs) as avg_num_langs

FROM Agent NATURAL JOIN lang_count

GROUP BY country;