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 Logic.

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.

Marcus Schaefer
Last updated: October 2nd, 2007.