Lab 3 - Sub Queries:  All must be answered using a Sub Query.

Due: 2/21/2007

1.  List First & Last name, Total Amount Purchased of all customers ordered by total amount purchased that have a total amount purchased greater than the average of all purchases.

        HINT:  See section on using sub-queries in the HAVING clause.

        The output should look as follows:

FIRST_NAME LAST_NAME  TOTAL_PURCHASED
---------- ---------- ---------------
Gail       Black                49.95
Cynthia    Green                 69.9
Steve      White                75.94
John       Brown               109.95

2.  List Employee Name, Title, Salary, Average Salary by Title, Employee Salary minus Average Salary by Title for all employees. 

        HINT:  See section on using sub-queries in the FROM clause.  Also remember that you can join on any column, such as title, not just a key column.

The output should look as follows:

FIRST_NAME LAST_NAME  TITLE                    SALARY TITLE_SALARY SALARY-TITLE_SALARY
---------- ---------- -------------------- ---------- ------------ -------------------
James      Smith      CEO                      800000       800000                   0
Ron        Johnson    Sales Manager            600000       600000                   0
Fred       Hobbs      Salesperson              150000       325000             -175000
Susan      Jones      Salesperson              500000       325000              175000

3.  List Employee Name, Title, Salary whose current salary is not an exact match for the High Salary column in the salary_grades table.

4.  List All employees Name & Title whose first and last name are an exact match for the Last and First name in our customers table.

5.  List all customers that have not yet made a purchase.  Most Efficient Sub Query.

6.  Use a nested sub query to List all customers that have purchased a product_type of 3.