Chapter 6 Answers

Query 1

List employees in the Boston and Springfield offices, including commuters. Display the report in order of last name:

select * from boston
union
select * from springfield
  order by 2

BOSTON REPORT mm/dd/yy EMPID LASTNAME HIREDATE OFFICECODE TOWN ───── ─────────────── ──────── ────────── ─────────────── 0466 ANDALE 061582 002 BOSTON 0120 ANGELO 090879 002 BOSTON 0457 ARM 012365 002 BOSTON 0007 BANK 043078 001 SPRINGFIELD 0007 BANK 043078 002 BOSTON 0069 BLOOMER 050580 001 SPRINGFIELD 0069 BLOOMER 050580 002 BOSTON 0119 BOWER 121477 002 BOSTON 0467 BREEZE 022980 001 SPRINGFIELD 0004 CRANE 051477 001 SPRINGFIELD 0024 DOUGH 080876 001 SPRINGFIELD 0032 FERNDALE 090979 001 SPRINGFIELD 0329 FINN 061679 001 SPRINGFIELD - 1 -

Query 2

List the average salaries of employees in the Boston and Springfield offices:

select avg(salary) as &xq.average salary', town as city
  from boston, emp
  where boston.empid = emp.empid
  group by town
union
select avg(salary), city
  from springfield, emp
  where springfield.empid = emp.empid
  group by city ! display

BOSTON/EMP REPORT mm/dd/yy AVERAGE SALARY CITY ────────────── ─────────────── 49500.00 SPRINGFIELD 74142.85 BOSTON END OF REPORT

Query 3

List all the employees in the Springfield and Weston offices whose jobid is 3001 or 5001:

select lastname, city
  from weston, emp
  where weston.empid=emp.empid
     and jobid in (3001, 5001)
union
select lastname, city
  from springfield, emp
  where springfield.empid=emp.empid
     and jobid in (3001, 5001) ! display

EMP/WESTON REPORT mm/dd/yy LASTNAME JOBID CITY ─────────────── ────── ─────────────── GARFIELD 3001 SPRINGFIELD PAPAZEUS 5001 WESTON RUPEE 3001 SPRINGFIELD END OF REPORT