Homework 1
CS122, Summer 2007


Due: Tuesday, July 24

Please put your queries in an SQL script file hw1.sql and upload it to CSNS. Note that file uploading will be disabled automatically after 11:59PM of the due date, so please turn in your work on time.

[SQL] (27pt) Complete the following queries using the Human Resource Database. For all the queries that ask for "names of the employees", the results should have the employee names in one column in the form of "last name, first name".

  1. List the names of the employees whose annual salary is above $100,000 (excluding commissions).
  2. List the names of the employees who are managers, i.e. their job title has the word "Manager" in it.
  3. List the names of the employees who are not department managers but earn more than $100,000 per year (excluding commissions).
  4. List the average employee salary in each department. The results should include department name and average salary, and ordered by average salary in descending order.
  5. List the average employee salary in each region. The results should include region name and average salary, and ordered by average salary in descending order. For regions with no employees, display -1 as the average salary.
  6. List the names of the employees who were hired in the last three years.
  7. List the names of the top 10 employees ranked by years of service. "Years of service" can be calculated as current year minus the year when the employee was hired.
  8. Find the phone number of Ki Gee's manager.
  9. Find the the names of the employees who have worked for more than one department.