Answers to Exercises › Review Answers for Chapter 5

Review Answers for Chapter 5

Exercise 5-1 Answer

This is the answer for Exercise 5-1.

select avg(num_dependents)
       from coverage;

Exercise 5-2 Answer

This is the answer for Exercise 5-2.

select avg(vac_accrued)
       from benefits
       where fiscal_year = 1999;

Exercise 5-3 Answer

This is the answer for Exercise 5-3.

select count(*)
       from skill;

Exercise 5-4 Answer

This is the answer for Exercise 5-4.

select count(phone)
       from employee
       where dept_id = 5200;

Exercise 5-5 Answer

This is the answer for Exercise 5-5.

select max(salary_amount)
       from position
       where job_id = 3333;

Exercise 5-6 Answer

This is the answer for Exercise 5-6.

select min(sick_taken)
       from benefits;

Exercise 5-7 Answer

This is the answer for Exercise 5-7.

select sum(vac_taken)
       from benefits;

Exercise 5-8 Answer

This is the answer for Exercise 5-8.

select count(distinct proj_id)
       from consultant;

Exercise 5-9 Answer

This is the answer for Exercise 5-9.

select dept_id, count(emp_id)
       from employee
       group by dept_id;

Exercise 5-10 Answer

This is the answer for Exercise 5-10.

select job_id, sum(salary_amount)
       from position
       group by job_id;

Exercise 5-11 Answer

This is the answer for Exercise 5-11.

select city, count(emp_id)
       from employee
       group by city
       having count(emp_id) > 2;

Exercise 5-12 Answer

This is the answer for Exercise 5-12.

select job_id, avg(salary_amount)
       from position
       group by job_id
       having avg(salary_amount) > 25000;

Exercise 5-13 Answer

This is the answer for Exercise 5-13.

select job_id, avg(salary_amount) as "Average Salary"
       from position
       group by job_id
       having avg(salary_amount) > 25000;

Review Answers

These are the answers for Review.

1. You use aggregate functions to perform calculations within a SELECT statement.
2. An aggregate function can be used instead of a column name with SELECT or in the HAVING clause.
3. When the aggregate function AVG encounters a null value, it ignores the row.
4. The HAVING clause acts as a search condition with an aggregate function.
5. You rename an aggregate function column heading by using AS and the heading you want.

Scenario Answers

These are the answers for Scenario.

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.)

   select dept_id, count(emp_id)
          from employee
          group by dept_id;
   

   DEPT_ID       (EXPR)
   -------       ------
      1100            3
      1110            2
      1120            4
      2200            5
      2210            8
      3510            2
      3520            1
      3530            2
      4500            3
      4600            9
      5000            3
      5100            2
      5200            5
      6200            6
   
   14 rows processed
   

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.)

   select job_id, min(salary_amount), max(salary_amount)
          from position
          group by job_id;
   

    JOB_ID          (EXPR)          (EXPR)
    ------          ------          ------
      2051          <null>          <null>
      2053          <null>          <null>
      2077        18001.00        29536.00
      3333         2200.00        30680.00
      4012        28601.80        44001.40
      4023        74776.00        74776.00
      4025        43888.00        43888.00
      4123        49921.76        49921.76
      4130        45241.94        45241.94
      4560          <null>          <null>
      4666        85280.00        85280.00
      4700        47009.34        59488.00
      4734        53665.00        57824.50
      5110        56977.80        56977.80
      5555        36400.00        76961.00
      5890        41600.00        68016.00
      6004       110448.00       110448.00
      6011        94953.52        94953.52
      6021       111593.00       111593.00
      8001       117832.68       117832.68
      9001       146432.00       146432.00
   
    21 rows processed
   

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.

   select manager_id, count(emp_id)
          from employee
          group by manager_id;
   

    MANAGER_ID       (EXPR)
    ----------       ------
          1003            5
          1034            3
          1234            1
          1765            1
          2004            2
          2010            6
          2096            3
          2180            3
          2209            1
          2246            2
          2448            1
          2461            2
          2466            6
          2894            7
          3082            1
          3222            2
          3778            1
          3991            1
          4321            1
          4358            1
        <null>            5
   
    21 rows processed
   

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.)

   select count(emp_id)
          from expertise
          where skill_id = 3333
            and skill_level > '02';
   

        (EXPR)
        ------
             2
   
   1 row processed
   

5. The Human Resources department is conducting research into salaries. They have asked you for a screen showing:
   • Job ID
   • Average salary by job
   • Minimum salary by job
   • Maximum salary by job

   They need this screen only for current 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 screen makes sense.

   select job_id as "Job",
          avg(salary_amount) as "Average Salary",
          min(salary_amount) as "Minimum Salary",
          max(salary_amount) as "Maximum Salary"
          from position
          where job_id < 4000
          group by job_id
          having avg(salary_amount) < 25000;
   

    Job                    Average Salary  Minimum Salary  Maximum Salary
    ---                    --------------  --------------  --------------
   2077                          23672.56        18001.00        29536.00
   3333                          23130.05         2200.00        30680.00
   2 rows processed
   

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 screen 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 screen should list a skill only if there are more than two employees that fit that category.

   select skill_id, count(emp_id)
          from expertise
          where (skill_id = 3333 and skill_level >= '02')
             or (skill_id = 4444 and skill_level = '04')
          group by skill_id
          having count(emp_id) > 2;
   

    SKILL_ID       (EXPR)
    --------       ------
        3333            3
   
    1 row processed