Answers to Exercises › Review Answers for Chapter 4

Review Answers for Chapter 4

Exercise 4-1 Answer

This is the answer for Exercise 4-1.

select emp_id, emp_fname, emp_lname
       from employee
       where emp_id = 5103;

Exercise 4-2 Answer

This is the answer for Exercise 4-2.

select emp_id, job_id, salary_amount
       from position
       where salary_amount > 100000;

Exercise 4-3 Answer

This is the answer for Exercise 4-3.

select emp_id, emp_fname, emp_lname, city
       from employee
       where city = 'Boston'
       order by emp_id;

Exercise 4-4 Answer

This is the answer for Exercise 4-4.

select emp_id, emp_fname, emp_lname, city
       from employee
       where not city = 'Boston';

Exercise 4-5 Answer

This is the answer for Exercise 4-5.

select emp_id
       from position
       where bonus_percent is null;

Exercise 4-6 Answer

This is the answer for Exercise 4-6.

select emp_id, emp_fname, emp_lname, phone
       from employee
       where phone is not null;

Exercise 4-7 Answer

This is the answer for Exercise 4-7.

select job_id, emp_id, salary_amount
       from position
       where salary_amount between 20000 and 35000;

Exercise 4-8 Answer

This is the answer for Exercise 4-8.

select emp_id, salary_amount
       from position
       where salary_amount in (41600, 45240, 50440);

Exercise 4-9 Answer

This is the answer for Exercise 4-9.

select dept_id, dept_name from department
       where dept_name like '%NEW CARS%';

Exercise 4-10 Answer

This is the answer for Exercise 4-10.

select dept_id, dept_name from department
       where dept_name not like '%NEW CARS%';

Exercise 4-11 Answer

This is the answer for Exercise 4-11.

select proj_id, est_man_hours - act_man_hours
       from project
       where est_man_hours - act_man_hours > 0;

Exercise 4-12 Answer

This is the answer for Exercise 4-12.

select emp_id, city, phone
       from employee
       where city in ('Camden', 'Brookline', 'Canton')
          or phone is not null;

Review Answers

These are the answers for Review.

1. The clause that allows the user to specify search conditions that filter the rows to be selected is:
   • a. The WHERE clause
2. What are the components of the WHERE clause?
   • b. The keyword WHERE
   • c. A search condition
3. You can compare a character column to a
   • b. Mask
4. Masks are used with
   • c. The LIKE predicate
5. Which of the following are mask characters?
   • b. _
   • d. %
6. The IS NULL predicate causes:
   • b. The retrieval of rows where a column contains no value
7. Parentheses are used to:
   • a. Set up the sequence of arithmetic evaluation
   • b. Set up the sequence of evaluation of AND and OR in a compound WHERE clause

Scenario Answers

These are the answers for Scenarios.

1. Periodically, a list is published giving divisions and their departments. A new department was recently added to division D09, so a new list for that division is needed. Use the DEPARTMENT table and show division code, department ID, and department name. Order by department ID.

   select div_code, dept_id, dept_name
          from department
          where div_code = 'D09'
          order by dept_id;
   

   DIV_CODE  DEPT_ID  DEPT_NAME
   --------  -------  ---------
   D09          4500  HUMAN RESOURCES
   D09          4900  MIS
   D09          5000  CORPORATE ACCOUNTING
   D09          5200  CORPORATE MARKETING
   D09          6000  LEGAL
   D09          6200  CORPORATE ADMINISTRATION
   
   6 rows processed
   

2. All Commonwealth Auto employees whose last names begin with L and M are due to have flu shots. The medical office needs to have the complete names of these individuals and the department to which each is assigned. Sort the list by last name. (Use the EMPLOYEE table.)

   select emp_lname, emp_fname, dept_id
          from employee
          where emp_lname like 'L%' or emp_lname like 'M%'
          order by emp_lname;
   

   EMP_LNAME             EMP_FNAME             DEPT_ID
   ---------             ---------             -------
   Loren                 Martin                   4600
   Lowe                  Frank                    2200
   Lynn                  David                    2200
   MacGregor             Bruce                    2200
   Mills                 Thomas                   6200
   Moore                 Richard                  2200
   
   6 rows processed
   

3. The Marketing department has a large project coming up and needs employees who have at least a medium level of competence (greater than 02) in skill 3333. Display employee ID and level of competence for each employee using the EXPERTISE table.

   select emp_id, skill_level
          from expertise
          where skill_id = 3333
            and skill_level > '02';
   

   EMP_ID  SKILL_LEVEL
   ------  -----------
     2437  04
     3288  04
   
   2 rows processed
   

4. In order to identify employees involved in media projects, the Human Resources department needs a list of employees associated with a project ID that begins with P (indicating media-related). Order the list by employee ID. (Use the ASSIGNMENT table to find this information.)

   select emp_id, proj_id
          from assignment
          where proj_id like 'P%'
          order by emp_id;
   

   EMP_ID  PROJ_ID
   ------  -------
     2894  P634
     3411  P634
   
   2 rows processed
   

5. The budget group needs a list of employees who hold a position that pays less than $25,000. Show employee ID and salary.

   select emp_id, salary_amount
          from position
          where salary_amount < 25000;
   

    EMP_ID   SALARY_AMOUNT
    ------   -------------
      3338        22048.84
      3767         2200.00
      4660        24000.00
      1765        18001.00
      2180        19000.10
      2106        23920.00
      3704        22880.00
      4008        24441.00
      4703        24857.00
   
    9 rows processed