Chapter 2 Answers

Query 1

List the salary range for each job class:

select class, maxsalary - minsalary as &xq.salary range'
  from jobclass

CLASS REPORT mm/dd/yy CLASS SALARY RANGE 11 6000.00 21 2000.00 33 2000.00 42 2000.00 43 4500.00 51 19000.00 63 10000.00 71 10000.00 72 14000.00 81 20000.00 93 50000.00 END OF REPORT

Query 2

List all employees who have both a:

Manager with ID 0007 or 0003

Job that begins with the number 3

select lastname, mgrid, jobid
  from emp
  where mgrid in (0003, 0007) and
    jobid like &xq.3___'

EMP REPORT mm/dd/yy LASTNAME MGRID JOBID JENSEN 0003 3025 JACOBI 0003 3011 TYRO 0003 3027 DOUGH 0003 3025 HEAROWITZ 0003 3025 GRANGER 0003 3025 GALLWAY 0003 3025 LITERATA 0003 3031 END OF REPORT

Query 3

Identify all employees whose sex code was entered incorrectly (that is, is not M or F):

select firstname, lastname, sex
  from emp
  where sex not in (&xq.m', &xq.f')

EMP REPORT mm/dd/yy FIRSTNAME LASTNAME SEX THEMIS PAPAZEUS N END OF REPORT

Query 4

List the number of years employees have from January 1, 1988 until they reach retirement age (65):

select firstname, lastname, birthdate,
  (65 - next-int-eqlo(datedif(880101,birthdate)/365))
  as &xq.years until retirement'
  from emp

EMP REPORT mm/dd/yy FIRSTNAME LASTNAME BIRTHDATE YEARS UNTIL RETIREMENT JOHN RUPEE 330219 11 JENNIFER GARFIELD 450818 23 MONTE BANK 500101 27 JULIE JENSEN 480730 26 JAMES JACOBI 401101 18 RALPH TYRO 551225 33 JANE DOUGH 510329 29 VLADIMIR HEAROWITZ 560425 34 PERCY GRANGER 580222 36 JAMES GALLWAY 471006 25 HENRIETTA HENDON 331006 11 LARRY LITERATA 550430 33 MICHAEL ANGELO 570405 35 CAROL MCDOUGALL 590304 37 - 1 -