Homework 3 (due 10/9)
We have talked more about SQL (see new presentations and chapter 7), and will
continue to do so next week. If you need a refresher in logic, check Boolean
If you want to test out the SQL queries using set operations you need a
database other than Access (which only supports union, but not intersection or
set difference). For that, try the H2 Database Engine
(free download). You will need SQL code to create the
company database. After you have started H2, copy/paste the SQL code into the
database window and run it. For some info on how to use H2, see
notes on H2.
1. [Pine Valley SQL]
Write SQL for the following queries, and list the output:
- [5pt] List the names of all products ordered before 1/1/2003 by
customers located in Florida.
- [5pt] List employees who do not have a supervisor assigned.
- [5pt] List all employees that can use a 12in band saw (you did that last time,
use a nested query now).
- [5pt] List all employees who cannot use a 12in band saw. Hint: modify
the previous nested query.
- [10pt] List all customers who bought both the 8-drawer desk and the computer
desk. Hint: write a query to get IDs of customers who bought the
8-drawer desk, and another query for customers who bought the computer desk.
Combine using set operations (in which case you can't run it in Access), or
by using two IN in the WHERE clause.
2. [Company SQL]
Write SQL for the following query, and list the output:
- [10pt] List all employees who do not manage a department. Hint: it's
much easier to find employees who do manage a department.
- [10pt] Find all employees in the company database who work on none of
the Stafford projects.
Last updated: October 2nd, 2007.