CS 386 Introduction to Databases
Spring 2006 Quarter

---

Assignment 1 - Basic SQL Queries

Note: Due date corrected.

Due: Thursday, 13 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 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 One

Describe in English what the following SQL queries do. Also include (at most) the first
five rows of the result for each query, and the total number of rows returned.

1.
(a) SELECT * FROM Agent WHERE agent_id = 55;
(b) SELECT DISTINCT city FROM Agent;
(c) SELECT city FROM Agent;
(d) SELECT first, last FROM Agent WHERE salary > 52000 and salary < 55000;
(e) SELECT sc_level, description FROM Securityclearance;

2.
(a) SELECT name, access_id FROM Mission WHERE mission_status='ongoing'
AND team_id=14;
(b) SELECT name, access_id FROM Mission M WHERE M.mission_status='ongoing'
AND M.team_id=14;
(c) SELECT name, access_id FROM Mission WHERE Mission.mission_status='ongoing'

AND Mission.team_id=14;

3.
(a) SELECT * FROM Agent A, SecurityClearance S WHERE A.salary = 55262
AND S.sc_level = 'Majestic';
(b) SELECT * FROM Agent A, SecurityClearance S WHERE A.clearance_id = S.sc_id

AND A.salary = 55262 AND S.sc_level = 'Majestic';

4.
(a) SELECT A.first, A.last, A.city, A.country FROM Agent A, Languagerel LR, Language L

WHERE A.agent_id = LR.agent_id AND LR.lang_id = L.lang_id AND L.language = 'Spanish';
(b) SELECT A1.first, A1.last, A2.first, A2.last FROM Agent A1, Agent A2

WHERE A1.city = A2.city AND A1.country = A2.country
AND A1.clearance_id < A2.clearance_id AND A1.salary >= A2.salary;

Part Two
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 week.

5. List the city, country and salary for agent Lilly Li.

6. List the first and last names of all agents with Secret or Top Secret clearance.
(Your query shouldn’t depend on what clearance IDs are used for these clearance levels.)

7. List the first and last names of all agents who speak at least two languages.

8. List the name and status of all missions that have at least one agent with Majestic clearance.
Don’t repeat missions in your result.