CIS 451/551 Database Processing

Fall 2007

Assignment 3

due Friday, October 26

Write SQL queries answering the following:

On the stores7 database

1.  Find all customers who have spent at least $10 on items made by the company 'Anza'.

2.  For each customer, add up the total amount spent purchasing items made by the company 'Anza'.  Include those customers who have bought nothing (with $0 next to their name).

3.  For each customer, add up the total amount spent by that customer.  Include the shipping charge.  You do not need to include those who have ordered nothing.

4.  For each manufacturer, list the names of those customers who have purchased everything made by that manufacturer.  (Note: you'll only get 'Norge', with two customers.)

On the company database

5.  For each project, find the total and average of the working hours allocated to that project.

6.  Determine which project has the maximum number of allocated hours.  Only return one project name

(more than one OK if there are ties).

7.  Find the employee whose salary is closest to the average salary.

8.  (CIS 551 only) List the dependents of the 4 lowest paid employees.