Previous Topic: ReviewNext Topic: Accessing Multiple Tables

Using Aggregate Functions › Scenarios

Scenarios

Create the appropriate statements online to retrieve the needed data:

  1. In order to plan for the Christmas party for Commonwealth Auto, the Human Resources department needs a count of employees by department. (The EMPLOYEE table contains this information.)
  2. As part of its salary research, the Human Resources department needs to know the minimum and maximum salaries being earned for each job ID in the company. (Use the POSITION table.)
  3. Upper management needs to know how many subordinate employees there are for each manager in order to evaluate the span of control within the company. The EMPLOYEE table contains this information.
  4. A project is coming up that requires project members having the skill ID 3333 (body work). The project leader needs to find out how many employees have a skill level greater than 02 for this skill to see whether he needs to hire consultants to staff the project. Keep in mind that the SKILL_LEVEL column contains character data. (Use the EXPERTISE table.)

    Note: Use a WHERE clause with more than one predicate.

  5. The Human Resources department is conducting research into salaries.They have asked you for a report showing:

    They need this report only for positions with a job ID less than 4000 (indicating training and clerical positions) where the average salary is less than $25,000. Use the POSITION table and rename the column headings so that the report makes sense.

  6. The training group is concerned that there are few people in the company who have certain crucial skills. They have asked you to give them a report listing the number of employees who have either a medium level of competence (02 or above) for skill 3333 (body work) or a high level of competence (04) for skill 4444 (assembly). The report should list a skill only if there are more than two employees that fit that category.

    Note: Hint: The column SKILL_LEVEL is a character column.

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