Review Answers for Chapter 6

Answers to Exercises › Review Answers for Chapter 6

Review Answers for Chapter 6

Exercise 6-1 Answer

This is the answer for Exercise 6-1.

select div_code, div_name, emp_id, emp_lname, emp_fname
       from employee, division
       where emp_id=div_head_id;

Exercise 6-2 Answer

This is the answer for Exercise 6-2.

select expertise.emp_id, emp_lname,
       emp_fname, skill_id
       from demoproj.expertise, demoempl.employee
       where expertise.emp_id = employee.emp_id;

Exercise 6-3 Answer

This is the answer for Exercise 6-3.

select expertise.emp_id, emp_lname, emp_fname, skill_id
       from demoproj.expertise, demoempl.employee
       where employee.emp_id = expertise.emp_id
       order by emp_lname desc, emp_fname desc;

Exercise 6-4 Answer

This is the answer for Exercise 6-4.

select distinct consultant.manager_id, emp_lname, emp_fname
       from demoproj.consultant, demoempl.employee
       where consultant.manager_id = employee.emp_id;

Review Answers

These are the answers for Review.

Statement	Term
1. Needed to join two or more tables	b. Common columns
2. Resolves the problem of joining table columns that have the same name	f. Aliases
3. Where the joining is specified	a. The WHERE clause
4. Where an alias is identified	d. The FROM clause
5. Used to append one table to another	c. The UNION clause

Scenario Answers

These are the answers for Scenarios.

Management would like to see which employees are involved in which projects. Write a SELECT statement to retrieve this information by joining the ASSIGNMENT and PROJECT tables that contain the data. Display the information by project description.

select assignment.proj_id, proj_desc, emp_id
       from assignment, project
       where project.proj_id = assignment.proj_id
       order by proj_desc;

PROJ_ID PROJ_DESC EMP_ID ------- --------- ------ C203 Consumer study 2894 C240 Service study 4358 D880 Systems analysis 2466 D880 Systems analysis 9388 P634 TV ads - WTVK 3411 5 rows processed

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 in fiscal year 2000. Order your screen by employee ID.

select benefits.emp_id, emp_lname,
      (vac_accrued - vac_taken)
      from benefits, employee
      where benefits.emp_id = employee.emp_id
           and fiscal_year = 2000
      order by benefits.emp_id;

EMP_ID EMP_LNAME (EXPR) ------ --------- ------ 1003 Baldwin 108.00 1034 Gallway 36.50 1234 Mills 68.00 1765 Alexander 76.50 2004 Johnson 68.50 2010 Parker 92.75 2096 Carlson 28.50 2106 Widman 76.50 2174 Zander 60.00 2180 Albertini 108.50 2209 Smith 76.50 2246 Hamel 36.50 2424 Wilder 60.50 2437 Thompson 76.00 2448 Lynn 55.50 2461 Anderson 36.00 2466 Bennett 68.50 2598 Jacobs 60.00 2781 Thurston 16.00 2894 Griffin 76.00 3082 Brooks 24.00 3118 Wooding 68.00 3222 Voltmer 76.00 3288 Sampson 20.00 3294 Johnson 60.00 3338 White 76.00 3341 Smith 43.50 3411 Williams 8.00 3433 Crane 36.00 3449 Taylor 20.00 3704 Moore 28.00 3764 Park -4.00 3767 Lowe 8.00 3769 Donelson 76.00 3778 Ferndale 36.00 3841 Cromwell 76.00 3991 Wilkins 8.00 4001 Thompson 36.00 4002 Roy 36.00 4008 Clark 76.00 4027 Courtney 36.00 4321 Bradley 28.00 4358 Robinson 76.00 4456 Thompson 36.00 4660 MacGregor 20.00 4703 Halloran 38.75 4773 Dexter 8.00 4962 White 60.00 5008 Fordman 14.50 5090 Wills 54.00 5103 Ferguson 54.00 51 rows processed

More statistics are being gathered on vacation hours. You have been asked to produce a screen of average vacation hours taken for each department. Display department ID and average vacation taken for fiscal 1999. Order the screen by department ID.

select dept_id, avg(vac_taken)
       from benefits, employee
       where benefits.emp_id = employee.emp_id
            and fiscal_year = 1999
       group by dept_id
       order by dept_id;

DEPT_ID (EXPR) ------- ------ 1100 106.66 1110 160.00 1120 133.33 2200 120.00 2210 115.00 3510 100.00 3520 120.00 3530 120.00 4500 133.33 4600 99.42 5000 84.00 5100 120.00 5200 100.00 6200 86.66 14 rows processed

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.

select j.job_id, job_title, emp_lname,
       emp_fname, salary_amount
       from job j, position p, employee e
       where j.job_id = p.job_id
         and p.emp_id = e.emp_id
         and salary_amount > 55000
       order by job_title;

JOB_ID JOB_TITLE EMP_LNAME EMP_FNAME SALARY_AMOUNT ------ --------- --------- --------- ------------- 4023 Accountant Taylor Cynthia 74776.00 5890 Appraisal Spec Smith Michael 66144.00 5890 Appraisal Spec Brooks John 68016.00 5110 CUST SER MGR Bradley George 56977.80 6011 Manager - Acctng Bennett Patricia 94953.52 6004 Manager - HR Voltmer Louise 110448.00 6021 Manager - Mktng Griffin William 111593.00 4734 Mktng Admin Robinson Judith 57824.50 9001 President Baldwin James 146432.00 4700 Purch Agnt Hamel Marylou 59488.00 4700 Purch Agnt Johnson Eleanor 59280.00 5555 Salesperson Albertini Joan 76961.00 5555 Salesperson Parker Cora 76440.00 5555 Salesperson Lynn David 70720.00 4666 Sr Mechanic Carlson Thomas 85280.00 8001 Vice President Mills Thomas 117832.68 16 rows processed

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 by the review percent. Show employee ID, current salary, percent increase, and increase as a dollar amount.
```
select position.emp_id, salary_amount,
       review_percent, (review_percent * salary_amount)
       from benefits, position
       where "position".emp_id = benefits.emp_id
         and "position".emp_id = 2004
         and fiscal_year = 2000
         and finish_date is null;
```

Notes

POSITION is the table name and an SQL keyword; therefore, when the POSITION table name is used as an identifier, it must be enclosed in double quotation marks.

As an alternative, you can use an alias for the table name. For example:

select position.emp_id, salary_amount,
       review_percent, (review_percent * salary_amount)
       from benefits b,  position p
       where p.emp_id = b.emp_id
         and p.emp_id = 2004
         and fiscal_year = 2000
         and finish_date is null;

EMP_ID SALARY_AMOUNT REVIEW_PERCENT (EXPR) ------ ------------- -------------- ------ 2004 59280.00 0.030 1778.40000 1 row processed