Answers to Exercises › Review Answers for Chapter 7

Review Answers for Chapter 7

Exercise 7-1 Answer

This is the answer for Exercise 7-1.

Exercises

select distinct dept_id
       from employee
       where emp_id in
             (select emp_id
                     from benefits
                     where (vac_accrued - vac_taken) > 80)
                            and fiscal_year = 1999;

Exercise 7-2 Answer

This is the answer for Exercise 7-2.

select emp_id, num_dependents
       from coverage
       where num_dependents >
             (select avg(num_dependents)
                     from coverage);

Exercise 7-2b Answer

This is the answer for Exercise 7-2b.

select distinct emp_id, num_dependents
       from coverage
       where num_dependents >
             (select avg(num_dependents)
                     from coverage);

Exercise 7-3 Answer

This is the answer for Exercise 7-3.

select distinct dept_id
       from employee
       where exists
             (select *
                     from position
                     where salary_amount > 50000
                       and employee.emp_id = "position".emp_id);

Or,

select distinct dept_id
       from employee
       where exists
             (select *
                     from position p
                     where salary_amount > 50000
                       and employee.emp_id = p.emp_id);

Review Answers

These are the answers for Review.

1. A nested SELECT statement is also known as a subquery.
2. A subquery is located in a WHERE clause.
3. A subquery must be enclosed in parentheses.
4. You use the EXISTS predicate to retrieve rows based on the existence of rows in another table.
5. When using an EXISTS predicate, the outer SELECT statement and the subquery are linked by matching columns in the WHERE clause in the subquery.
6. You can use an asterisk (*) in the subquery if you are using the EXISTS keyword.

Scenario Answers

These are the answers for Scenarios.

1. For tax purposes, the Accounting department needs to keep track of all jobs for which employees earn more than $65,000. A list of job titles is sufficient. (Use the JOB and POSITION tables.)

   select job_title
          from job
          where job_id in
                (select job_id from position
                        where salary_amount > 65000);
   

   JOB_TITLE
   ---------
   Accountant
   Appraisal Spec
   Manager - Acctng
   Manager - HR
   Manager - Mktng
   President
   Salesperson
   Sr Mechanic
   Vice President
   
   9 rows processed
   

2. Upper management is concerned about the equality of salaries within Commonwealth Auto. They need to have a list by name of all jobs for which at least one employee earns less than $35,000. (Use the JOB and POSITION tables.)

   select job_title
          from job
          where job_id in
                (select job_id from position
                        where salary_amount < 35000);
   

    JOB_TITLE
    ---------
    Admin Asst
    Purch Clerk
    Sales Trainee
   
    3 rows processed
   

3. Over the years, lots of department information has been added to the database. The Human Resources department is responsible for this portion of the database and knows that there are some departments still listed for which there are no longer any associated employees. They have asked you for a list showing these departments. Order the list by department ID. (Use the DEPARTMENT and EMPLOYEE tables.)

   select dept_id
          from department
          where not exists
                (select *
                        from employee
                        where employee.dept_id = department.dept_id)
          order by dept_id;
   

   DEPT_ID
   -------
      4200
      4900
      6000
   
   3 rows processed