Basic query used in the examples

The examples used in this section are based on the query described below, which uses tables from the SAMPLE database.

Purpose

This query lists the name, department, and earnings for all non-manager employees who earn more than 90% of the highest-paid manager's salary.

Source code

SELECT S.ID,S.NAME,O.DEPTNAME,SALARY+COMM
FROM ORG O, STAFF S
WHERE
  O.DEPTNUMB = S.DEPT AND
  S.JOB <> 'Mgr' AND
  S.SALARY+S.COMM > ALL( SELECT ST.SALARY*.9
                         FROM STAFF ST
                         WHERE ST.JOB='Mgr' )
ORDER BY S.NAME

Explanation

The query has two parts:

• The subquery (in parentheses at the end) produces rows of data that consist of 90% of each manager's salary.

  Because the subquery is qualified by ALL, only the largest value from this table is retrieved.

• The main query joins all rows in the ORG and STAFF tables where the department numbers are the same, JOB does not equal 'Mgr', and salary plus commission is greater than the value that was returned from the subquery.

The main query contains the following three predicates (comparisons):

1.   ORG.DEPTNUMB = STAFF.DEPT
2.   S.JOB <> 'Mgr'
3.   S.SALARY+S.COMM > ALL ( SELECT ST.SALARY*.9
                         FROM STAFF ST
                         WHERE ST.JOB='Mgr' )

These predicates represent, respectively:

1. A join predicate, which joins the ORG and STAFF tables where department numbers are equal
2. A local predicate on the JOB column of the STAFF table
3. A local predicate on the SALARY and COMM columns of the STAFF table that uses the result of the subquery.