Homework is due by classtime on Thursday. Submit either a hardcopy (in class, or drop it off at the 4th floor front desk), or email it to me.

We have talked more about SQL (see new presentation and chapter 7).

1. [Pine Valley SQL]

   Write SQL for the following queries, and list the output:

• [10pt] List all customers (by name) and, if we have that information, what territories they do business in.
• [10pt] List all product lines and the average price of products in that product line.
• [10pt] List all orders and the total price of each order (just accounting for the products in the order, no shipping or handling). Hint: In a first step assume that each product item gets ordered once, when totaling the order. In a second step, modify what you are summing up by taking the quantity of each item into account.

2. [Company SQL]

   Write SQL for the following queries, and list the output:

• [15pt] List all supervisors (full name and SSN) that do not have any dependents.
• [15pt] List all employees that have a dependent spouse of the same sex and a dependent child of the opposite sex.

3. [Extra Credit]

    Write SQL for the following queries in the Pine Valley Furniture world:

• Extend the display from your third query in 1. to include a 7% sales tax, and $3.00 shipping for each item in the total.
• List the names of employees that work on all department 5 projects.

Marcus Schaefer
Last updated: October 9th, 2007.