Chapter 5 Answers

Query 1

List the jobs for which employees earn more than $65,000:

select *
  from joblist
  where exists (select *
                  from emp
                  where emp.jobid=joblist.jobid and
                  salary > 65000)

JOBLIST REPORT mm/dd/yy EMPID CLASS JOBID TITLE 0003 71 3001 MGR INTERNL SOFTWARE 0007 72 4001 MGR PUBLIC RELATIONS 0015 72 5001 MGR BRAINSTORMING 0471 72 5001 MGR BRAINSTORMING 0030 93 9001 PRESIDENT 0472 81 9005 DIR CORP CONFUSION END OF REPORT

Query 2

List openings for jobs that command salaries in the range $35,000 to $40,000:

select class, jobid, title
  from joblist
  where not exists
    (select * from emp where emp.jobid=joblist.jobid)
    and exists
      (select * from jobclass where jobclass.class=joblist.class
       and minsalary >= 35000 and maxsalary <= 40000)

JOBLIST REPORT mm/dd/yy CLASS JOBID TITLE 43 1023 RECRUITER/INTERVWR 33 4023 PHOTOGRAPHER 43 5023 RAINDANCE CONSULTANT 43 5035 HUMIDITY CONTROL CLK END OF REPORT

Query 3

List all employees in the Springfield office who have this job title: &xq.Programmer/Analyst':

select * from springfield where exists
    (select * from employee where employee.empid = springfield.empid
    and exists
        (select * from joblist where employee.empid = joblist.empid and
        joblist.title = &xq.programmer/analyst'))

SPRINGFIELD REPORT mm/dd/yy EMPID LASTNAME STARTYEAR OFFICECODE CITY ───── ─────────────── ───────── ────────── ─────────────── 0024 DOUGH 080876 001 SPRINGFIELD 0029 GALLWAY 101081 001 SPRINGFIELD 0028 GRANGER 052780 001 SPRINGFIELD 0027 HEAROWITZ 090981 001 SPRINGFIELD END OF REPORT

Query 4

List all departments that have no associated employee:

select *
from dept
where not exists
  (select * from emp
   where emp.deptid = dept.deptid) ! display

DEPT REPORT mm/dd/yy DEPTID DEPTNAME MGRID 1000 PERSONNEL 0013 2000 ACCOUNTING AND PAYROLL 0011 3200 COMPUTER OPERATIONS 0004 5100 BRAINSTORMING 0015 5200 THERMOREGULATION 0349 5300 BLUE SKIES 0321 END OF REPORT