CS386/586 Introduction to Databases
Fall 2011 Quarter

Assignment 3 (corrected) - SQL & Relational Algebra 2


Due: Thursday, 20 October 2011 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-list@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 equivalence 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 at hw11-03.pdf.]

 

Question 1 (5 points): (sofficers.club=¡¯sailing¡¯ (sofficers.role=¡¯VP¡¯(officers)) º
(sofficers.role=¡¯VP¡¯ (sofficers.club=¡¯sailing¡¯ (officers))

 

Question 2 (5 points): pofficers.pname(officers ¨C sofficers.role=¡¯VP¡¯(officers)) º
pofficers.pname(officers) ¨C pofficers.pname(sofficers.role=¡¯VP¡¯(officers))

 

Question 3 (5 points): plocation.room(sofficers.club=location.club(officers ´ location)) º plocation.room(location))

 

Question 4 (5 points): pofficers.pname,location.room(sofficers.club=location.club AND role=¡¯VP¡¯(officers ´ location)) º
pofficers.pname,location.room(sofficers.club=location.club (sofficers.role=¡¯VP¡¯(officers) ´ location))

 

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.

 

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

 

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

 

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.

 

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)

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 skill_count(agent_id, num_skills) that lists the number of skills for each agent.

 

Question 11 (10 points). Write an SQL query that find the average number of skills per agents, in each country. Use the skill-count view you defined in the previous question.