CSE 544

University of Washington

Spring 2000

Homework 1

In this assignment you will write SQL queries against a relational database. The script creating the relations can be found here.

We encourage you to use SQL Server (or PostgresQL) to test your solutions. Help with getting started with either of these is available here. You can, of course, use any relational product you like (including none), but you do this at your own risk. Finally, beware of one thing: It is possible to write an SQL query that gives the correct answer (for these relation instances) but is not, in general, the right query.

You do not need to remove duplicates unless explicitly requested to do so. What you should turn in for each question is the SQL query and the result it produced. This can be handwritten or typeset. The query and the result for each question should be together (i.e., don't give us all the queries then, separately, all the results). If there is a query that you cannot write in SQL explain why and propose an extension to SQL to accomodate for it. Any of these queries which can be posed in SQL return non-empty results from the sample instance provided.

Write SQL queries for the following queries:

a. Retrieve the first and last names of all employees earning more than $30000.

b. Retrieve the names of the departments which have employees, without duplicates.

c. Retrieve the first and last names of all employees earning an above average salary.

d. Retrieve the department names and their average salaries, sorted (from maximum to minimum).

e. Retrieve the first and the last names of all employees who are working on a project in Sugarland and in Stafford. Also return the names of the projects they work on in Sugarland and Stafford.

f. Retrieve the first and the last names of all employees who are working on every project in two different cities, without duplicates.

g. Find the most senior manager (the person to whom everyone report to - perhaps indirectly).

h. Retrieve the first and last names of all employees who work on the maximum number of projects.

i. Find the first and the last names and the addresses of all employees whose department has no location in Houston but who work on at least one project located in Houston, without duplicates.