CS386/586 Introduction to
Databases
Winter
2009 Quarter
Assignment 1 - Basic SQL Queries
Due: January 13, 2009, 5:30pm.
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.
The main class web page is available: http://www.cs.pdx.edu/~lmd/cs386
This
assignment is based on the Spy relational database.
A description of this database is available, with other documentation, at: http://www.cs.pdx.edu/~lmd/cs386/dbsupport.htm
The online query page, where you can issue queries, is
available at: https://www.cat.pdx.edu/phpPgAdminIntroDb/
You can access the databases from a unix command line
as well. See the details at: http://www.cat.pdx.edu/web/database-services.html
Log in to this page with user name of "introdb_readonly"
with a password of "introdb".
Be sure to pay close attention to the description given for Spy database; you
will be expected to know how the schema works.
Part One
As an example of what we mean when we say “describe in English"
if the query is:
SELECT *
FROM Skill;
you should describe this query like this:
List all of the skills.
rather than something like this:
Return all rows in the Skill table.
In other words, describe what the query answer is in terms of the application. Don’t use database terminology in your explanation.
Describe in English what the following SQL queries do. Also list (at most) the first five rows of the result for each query, and the number of rows returned.
1.
SELECT name
FROM mission;
2.
SELECT lang_id
FROM languagerel;
3.
SELECT DISTINCT lang_id
FROM languagerel;
4. Explain why the query answer to query 2 is not identical to the query answer to query 3.
5.
SELECT count(*)
FROM team, teamrel
WHERE team.team_id = teamrel.team_id AND team.team_id = 7;
6. Modify the query in question 5 to compute the same information for team_id 22. (Write the SQL query; you don’t need to explain what it does in English.) Run the query and list the first 5 rows and the count of the tuples in the query answer.
7.
SELECT *
FROM language, skill;
8.
SELECT *
FROM language lang1, language lang2;
9.
SELECT first, last, skill
FROM agent, skillrel, skill
WHERE agent.agent_id = skillrel.agent_id AND skillrel.skill_id = skill.skill_id;
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 that you learned in the first lecture.
10. Produce a list of all skills showing the skill_id and the skill.
11. List mission names for missions that have an access_id of 4 or 6.
12. List the agent id, first name and last name of all agents that are on at least two teams.
13. List team names for teams that have at least one agent assigned that has the skill of ‘Sniper’ and at least one agent assigned that speaks ‘Cherokee’. (If a team has two different agents and one speaks ‘Cherokee’ and the other has the skill of ‘Sniper’ then that team should be in your query answer. If a team has one agent who speaks ‘Cherokee’ and the same agent has the skill of ‘Sniper’ that that team should also be in your query answer.)