Accessing Multiple Tables › Scenarios

Scenarios

Create the appropriate statements online to retrieve the needed data:

1. Management would like to see which employees are involved in which projects. Write a SELECT statement to retrieve this information by joining the EMPLOYEE and PROJECT tables that contain the data. Display the information by project description.
2. The Human Resources department needs a list of employees and their remaining vacation time. This information is contained in the EMPLOYEE and BENEFITS tables. Display employee ID and last name as well as the vacation time remaining for fiscal year 2000. Order your report by employee ID.
3. More statistics are being gathered on vacation hours. You have been asked to produce a report of average vacation hours taken for each department. Display department ID and average vacation taken for fiscal year 1999. Order the report by department ID.
4. The budget committee needs a list of job titles, names of employees holding those jobs, and current salaries of those employees. They are interested only in jobs offering salaries of more than $55,000. Order your list by job title and include the job ID.

   Note: You need to join three tables to get this information.Specify the three tables in the FROM clause. Join two tables at a time in the WHERE clause. Use aliases to qualify column names.

5. Employee 2004 has just had a review and is due to get a pay increase. The increase is stored as REVIEW_PERCENT in the BENEFITS table. Employee 2004's manager has asked you to show her how much the increase is in dollar amount. To get this information, you need to multiply the current salary for fiscal year 2000 by the review percent. Show the employee ID, current salary, percent increase, and increase as a dollar amount.

   Note: You need to perform a calculation involving columnsfrom two different tables.

To check your answers, see Review Answers for Chapter 6.