Answers › Chapter 3 Answers

Chapter 3 Answers

Query 1

For each manager in table EMP, determine the number of staff reporting to the manager and the average salary of the staff members:

select mgrid, count(*) as &xq.number of staff',
  avg(salary) as &xq.average salary'
  from emp
  group by mgrid

                                   EMP REPORT
                                    mm/dd/yy

         MGRID         NUMBER OF STAFF                  AVERAGE SALARY

           0003                      8                        35375.00
           0007                      5                        29200.00
           0030                      6                       106833.33
     END OF REPORT

Query 2

List the number of jobs greater than or equal to 3 that are assigned to classes 10 and 50. Display the report in order by the number of jobs:

select count(*) as &xq.number of jobs', class
  from joblist
  where class between 10 and 50
  group by class
  having count(*) >= 3
  order by 1

                                 JOBLIST REPORT
                                    mm/dd/yy

                     NUMBER OF JOBS                    CLASS

                                  3                       11
                                  3                       33
                                  4                       12
                                  5                       42
                                  6                       21
                                  6                       43
     END OF REPORT

Query 3

For departments 6666, 4000, and 3100, list the minimum and maximum salaries within the department, provided the average departmental salary in greater than $37,800:

select deptid, min(salary) as &xq.minimum salary',
  max(salary) as &xq.maximum salary'
  from emp
  where deptid in (3100, 4000, 6666)
  group by deptid
  having avg(salary) > 37800

                                   EMP REPORT
                                    mm/dd/yy

          DEPTID         MINIMUM SALARY         MAXIMUM SALARY

            3100               20000.00               65000.00
            6666               76000.00              240000.00
     END OF REPORT

Query 4

List information about employees earning less than the average salary:

select lastname, salary
  from emp
  where salary <
     (select avg(salary)
      from emp) ! display

                                   EMP REPORT
                                    mm/dd/yy

                     LASTNAME                       SALARY
                  ───────────────                 ───────────
                  JENSEN                             37000.00
                  JACOBI                             55000.00
                  TYRO                               20000.00
                  DOUGH                              33000.00
                  HEAROWITZ                          33000.00
                  GRANGER                            34500.00
                  GALLWAY                            33000.00
                  LITERATA                           37500.00
                  ANGELO                             18000.00
                  MCDOUGALL                          18000.00
                  PENMAN                             39000.00
                  JACKSON                            34000.00
                  ZEDI                               37000.00
     END OF REPORT