Chapter 4 Answers

Table Query 1

List the average salary for employees in the Boston office:

select count(*) as &xq.number of boston employees', avg(salary)
      as &xq.average salary'
  from emp, boston
  where emp.empid = boston.empid

EMPLOYEE/BOSTON REPORT mm/dd/yy NUMBER OF BOSTON EMPLOYEES AVERAGE SALARY ────────────────────────── ────────────── 7 74142.85 END OF REPORT

Table Query 2

List all employees in the Springfield office who are programmer/analysts:

select emp.lastname, joblist.title, springfield.city
  from emp, joblist, springfield
  where emp.empid = springfield.empid and
  emp.empid = joblist.empid and
  joblist.title = &xq.programmer/anaylst'

JOBLIST/EMPLOYEE/SPRINGFIELD REPORT mm/dd/yy LASTNAME TITLE CITY ─────────────── ──────────────────── ─────────────── DOUGH PROGRAMMER/ANALYST SPRINGFIELD HEAROWITZ PROGRAMMER/ANALYST SPRINGFIELD GRANGER PROGRAMMER/ANALYST SPRINGFIELD GALLWAY PROGRAMMER/ANALYST SPRINGFIELD END OF REPORT

Table Query 3

List the average salary of the managers in table DEPT:

select avg(salary) as &xq.average manager salary'
  from emp, dept
  where emp.empid = dept.mgrid

EMP/DEPARTMENT REPORT mm/dd/yy AVERAGE MANAGER SALARY 128333.33 END OF REPORT

Table Query 4

List all employees who are either programmer/analysts, paste-up artists, or a brainstorming manager:

select emp.lastname, joblist.title
  from emp, joblist
  where joblist.title in (&xq.programmer/analyst', &xq.paste-up artist',
     &xq.mgr brainstorming') and
     joblist.empid = emp.empid

JOBLIST/EMPLOYEE REPORT mm/dd/yy LASTNAME TITLE ─────────────── ──────────────────── DOUGH PROGRAMMER/ANALYST HEAROWITZ PROGRAMMER/ANALYST GRANGER PROGRAMMER/ANALYST GALLWAY PROGRAMMER/ANALYST ANGELO PASTE-UP ARTIST MCDOUGALL PASTE-UP ARTIST PAPAZEUS MGR BRAINSTORMING END OF REPORT

Record Query 1

List each employee's job title and salary:

select employee.emp-last-name-0415, job.job-title-0440,
  emposition.salary-amount-0420
  from employee, emposition, job
  where emp-emposition and job-emposition

JOB/EMPOSITION/EMPLOYEE REPORT mm/dd/yy EMP-LAST-NAME-0415 TITLE-0440 SALARY-AMOUNT-0420 ────────────────── ──────────────────── ────────────────── O'HEARN PROGRAMMER TRAINEE 38000.00 TYRO PROGRAMMER TRAINEE 20000.00 WILCO MGR THERMOREGULATION 80000.00 GARFIELD MGR INTERNL SOFTWARE 65000.00 RUPEE MGR INTERNL SOFTWARE 76000.00 JOHNSON PERSONNEL CLERK 13500.00 FITZHUGH PERSONNEL CLERK 13000.00 BLOOMER PAYROLL CLERK 15000.00 ARM STURM/DRANG ADMIN 46000.00 WAGNER STURM/DRANG ADMIN 47000.00 ORGRATZI RECRUITER/INTERVWR 39000.00 BANK MGR PUBLIC RELATIONS 80000.00 ANDALE SNOWBLOWER 33500.00 - 1 -

Record Query 2

For each job assigned to more than one employee, list the number of employees assigned to the job and their average salaries:

select job.title-0440, count(*) as &xq.number of jobs',
      avg(emposition.salary-amount-0420) as &xq.average salary'
  from employee, emposition, job
  where emp-emposition and job-emposition
  group by job.title-0440
  having count(*) > 1

JOB/EMPOSITION REPORT mm/dd/yy TITLE-0440 NUMBER OF JOBS AVERAGE SALARY ──────────────────── ────────────── ────────────── COMPUTER OPERATOR 3 20333.33 DATA ENTRY CLERK 4 13937.50 DATABASE ADMIN. 2 55000.00 DOCUMENTATION SPEC 2 41250.00 MGR BRAINSTORMING 3 83333.33 MGR COMPUTER OPS 3 68333.33 MGR INTERNL SOFTWARE 2 70500.00 PASTE-UP ARTIST 2 17500.00 PERSONNEL CLERK 2 13250.00 PR WRITER 2 38000.00 PROGRAMMER TRAINEE 2 29000.00 PROGRAMMER/ANALYST 6 35500.00 RAINMAKER 3 46166.66 - 1 -

Record Query 3

List each employee's manager:

select worker.last-name-0415 as &xq.staff', supervisor.last-name-0415
  as &xq.project leader'
  from employee worker, employee supervisor, structure
  where reports-to.worker and manages.supervisor
  order by worker.emp-last-name-0415

EMPLOYEE/EMPLOYEE REPORT mm/dd/yy STAFF PROJECT LEADER ─────────────── ─────────────── ANDALE MAKER ANGELO BANK ARM MAKER BANK BOWER BANK WILDER BLOOMER JENSON BOWER BANK BREEZE MAKER CLOTH WILCO CLOUD WILDER CLOUD MOON CRANE RUPEE CROW MAKER - 1 -