CS386/586 Introduction to Databases
Fall 2011 Quarter
Assignment 2 - SQL & Relational Algebra 1
Due: Thursday, 13 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.)
Relational Algebra
Note: you won't be able to execute these queries, since we don't have an implemented system that supports user queries written in
relational algebra.
Write the following queries in Relation Algebra, using ONLY the select, project and cross product operators:
(a) Find the address and city for all agents with last name Li.
(b) List salary and last name of all agents with the Cryptographer skill.
(c) List the city, country and clearance description of all agents who speak Vietnamese.
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. You should be able to write
these SQL queries
using only the features covered in the first two sets of lecture notes.
(a) Find the agent id and salary in Euros for all agents whose country is France. Name the result columns France_ids and Euro_pay. (Assume the stored salary is in dollars.)
(b) Find the number of different skills
(c) Find the high, low and total salary for all agents with Top-Secret clearance.
(d) Find the team name for all teams with at least one agent with the French language.
Do this query two ways: Once using NATURAL JOIN and one without any JOIN operator in the FROM clause. (You only need to submit one copy of the result.)
(e) List the affiliations for each agent, but also include agents with no affiliations. The result should have first name, last name, affiliation strength and affiliation title.
(f) List the team name for each team that has an agent who can speak German and an agent who can speak Hebrew. Do this query twice: Once using INTERSECT and once without using that operator.
(g) Find all agents who can speak Hindi or who have the Pilot skill. Do this query twice: Once with UNION and once without using that operator.