CS 386 Introduction
to Databases
Spring 2006 Quarter
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. 10 points total, 2 for each
part (1 for explanation, 1 for results)
(a) SELECT * FROM Agent WHERE agent_id
= 55;
This query returns the values of all fields in the Agent table for the row with agent_id 55. Since agent_id is a primary key for this table, there can be only one such row. [Note that this is not "all of the information about the agent with id 55." The database contains lots of other information about each agent, such as languages spoken.]
| agent_id | first | middle | last | address | city | country | salary | clearance_id |
|---|---|---|---|---|---|---|---|---|
|
55
|
John | NULL | House | 35 28th Avenue | Seattle | USA |
54803
|
5
|
1 row
(b) SELECT DISTINCT city FROM Agent;
This query returns the range of city values in the Agent table. In other words, it returns the set of cities with at least one agent. [Note that the result has been sorted in the process of duplicate elimination.]
| city |
|---|
| Amsterdan |
| Athens |
| Atlanta |
| Baghdad |
| Bangalore |
(c) SELECT city FROM Agent;
This query returns the value of the city field for each row in the Agent table.
| city |
|---|
| Athens |
| Paris |
| New York |
| Athens |
| New York |
(d) SELECT first, last FROM Agent WHERE salary > 52000 and salary < 55000;
This query returns the first and last names of agents with salaries between 52,000 and 55,000.
| first | last |
|---|---|
| Andrew | James |
| John | Johnston |
| Jim | Kieburtz |
| George | Launchbury |
| George | McNamee |
106 rows
(e) SELECT sc_level, description FROM Securityclearance;
This query returns the name and description of each security clearance level.
| sc_level | description |
|---|---|
| Presidential | Highest level of access |
| Majestic | Second highest level of access |
| Magellon | Third highest level of access |
| Top Secret | Fourth highest level of access |
| Secret | Fifth highest level of access |
2. 15 points total, 5 for each
part (2 for explanation, 2 for result table, 1 for result count)
(a) SELECT name, access_id
FROM Mission WHERE mission_status='ongoing'
AND team_id=14;
This query returns the name and required security clearance level id for ongoing missions of the team with id 14.
| name | access_id |
|---|---|
| Deeping Stream |
6
|
| Isengard |
3
|
| Pillars of the King |
5
|
| Deeping Coomb |
5
|
4 rows
(b) SELECT name, access_id
FROM Mission M WHERE M.mission_status='ongoing'
AND M.team_id=14;
The meaning and results of this query are the same as those of the previous query. Here a range variable has been declared for the Mission table and used to clarify references in the WHERE clause.
(c) SELECT name, access_id FROM Mission WHERE Mission.mission_status='ongoing'
AND Mission.team_id=14;
This query also performs the same function as the previous two. Here the references in the WHERE clause are clarified by directly naming the table.
3. 10 points total, 5 for each
part (see above)
(a) SELECT * FROM Agent
A, SecurityClearance
S WHERE A.salary = 55262
AND S.sc_level
= 'Majestic';
This query returns the cross-product of a selection of rows from the Agent and SecurityClearance tables. Each row in Agent with salary value 55,262 will be prepended to each row in SecurityClearance with level name "Majestic". Since there is only one row in SecurityClearance with this value, each of the selected rows from Agent will only appear in one row of the result, whereas the SecurityClearance row will appear once for each row from Agent (i.e. in every row of the result). There is no necessary relationship in the result rows between the values in the fields from Agent and those in the fields from SecurityClearance.
| agent_id | first | middle | last | address | city | country | salary | clearance_id | sc_id | sc_level | description |
|---|---|---|---|---|---|---|---|---|---|---|---|
|
125
|
Bill | NULL | DiLiberty | 9-11 75th Avenue | New York | USA |
55262
|
4
|
2
|
Majestic | Second highest level of access |
|
247
|
Julien | NULL | Weber | 42 63rd Avenue | Madrid | Spain |
55262
|
1
|
2
|
Majestic | Second highest level of access |
|
425
|
Jim | NULL | Peroulis | 27 88th Avenue | Jerusalem | Israel |
55262
|
5
|
2
|
Majestic | Second highest level of access |
|
1088
|
Williams | NULL | Knoop | NULL | Madrid | Spain |
55262
|
2
|
2
|
Majestic | Second highest level of access |
4 rows
(b) SELECT * FROM Agent A, SecurityClearance S WHERE A.clearance_id = S.sc_id
AND A.salary = 55262 AND S.sc_level = 'Majestic';
This query returns the information in the Agent and SecurityClearance tables for agents with a salary of 55,262 and Majestic clearance. It does so by perfoming an equijoin of Agent and SecurityClearance on the security clearance level id field of each table. Unlike the previous query, here the result rows must share the same value in clearance_id and sc_id, and so we know that the agent described by the Agent values has the security clearance level described by the SecurityClearance values. [Note that the result must be contained in the result of the previous query.]
| agent_id | first | middle | last | address | city | country | salary | clearance_id | sc_id | sc_level | description |
|---|---|---|---|---|---|---|---|---|---|---|---|
|
1088
|
Williams | NULL | Knoop | NULL | Madrid | Spain |
55262
|
2
|
2
|
Majestic | Second highest level of access |
1 row
4. 10 points total, 5 for each
part (see above)
(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';
This query returns the name and location of each agent that speaks Spanish.
| first | last | city | country |
|---|---|---|---|
| John | Walpole | New York | USA |
| Pete | Consel | Athens | USA |
| Nicolas | Macon | San Francisco | USA |
| Tim | Tolmach | Athens | USA |
| George | van Santen | Paris | France |
92 rows
(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;
This query returns each pair of agents by first and last name that are in the same location and the first listed agent has both a higher security clearance (lower clearance level id) and the same or higher salary than the second listed.
| first | last | first | last |
|---|---|---|---|
| George | Riley | George | Carter |
| George | Riley | John | Luben |
| George | Riley | Mary | Debus |
| George | Riley | Mario | Silverman |
| George | Riley | Neithan | Vournas |
4866 rows
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. 10 points total (6 query, 3 result table, 1 result count)
SELECT city, country, salary FROM Agent WHERE first = 'Lilly' AND last = 'Li';
| city | country | salary |
|---|---|---|
| Athens | USA |
67893
|
1 row
[There was some ambiguity with the next few questions caused by the fact that agent and mission names are not unique. Answers that excluded or included the rows duplicating these names were accepted. The queries below show possible solutions that were acceptable.]
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.) 10
points (see above)
SELECT first, last FROM Agent A, SecurityClearance S WHERE A.clearance_id = S.sc_id AND (S.sc_level = 'Top Secret' OR S.sc_level = 'Secret');
| first | last |
|---|---|
| Alex | Acevedo |
| Alex | Doug |
| Alex | Loftus |
| Alex | Sage |
| Allon | Kuhn |
7. List the first and last names of all agents who speak at least two languages. 10 points (see above)
SELECT DISTINCT first, last from Agent A, Languagerel L1, Languagerel L2 WHERE A.agent_id = L1.agent_id AND A.agent_id = L2.agent_id AND L1.lang_id <> L2.lang_id;
| first | last |
|---|---|
| Alex | Acevedo |
| Alex | Hruby |
| Alex | Loftus |
| Alex | Richards |
| Alex | Sage |
538 rows (or 541 if repeated names are included --- note that queries returning all 541 were likely to either add another field to the result or use SQL features not covered in the first week of class, so points may have been taken off for those reasons)
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. 15 points total (9 query, 5
result table, 1 result count)
SELECT DISTINCT M.name, M.mission_status FROM Mission M, Teamrel T, Agent A, SecurityClearance S WHERE M.team_id = T.team_id AND T.agent_id = A.agent_id AND A.clearance_id = S.sc_id AND S.sc_level = 'Majestic';
| name | mission_status |
|---|---|
| Adaldrida | ongoing |
| Aglarond | success |
| Aldaron | failed |
| Aldor | success |
| Aman | failed |