CS386 Introduction to Databases
Spring 2006 Quarter
Assignment 2 - SQL
& Relational Algebra 1
Due:
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.)
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 city and country for all agents with last name Li.
(b) List first and last name of all agents with the
Pilot skill.
(c) List the country, salary 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
(b) Find the number of
different language skills
(c) Find the high, low and
total salary for all agents with Majestic clearance.
(d) Find the team name for
all teams with at least one agent with the Cryptographer skill.
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, including agents with no affiliations. The result should have first
name, last name and affiliation description.
(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 Biologist skill. Do this query twice: Once with