SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—540440-003
2-201
MXCI Examples of SELECT
3. Drop rows with job codes not equal to 900, 300, or 420.
4. Process the select list, leaving only four columns.
The final result is shown in the output:
JOBDESC FIRST_NAME LAST_NAME SALARY
------------ ------------ --------------- -----------
SALESREP TIM WALKER 32000.00
...
SECRETARY JOHN CHOU 28000.00
...
•
Select from three tables, group the rows by job code and (within job code) by
department number, and order the groups by the maximum salary of each group:
SELECT E.jobcode, E.deptnum, MIN (salary), MAX (salary)
FROM persnl.employee E,
persnl.dept D, persnl.job J
WHERE E.deptnum = D.deptnum AND E.jobcode = J.jobcode
AND E.jobcode IN (900, 300, 420)
GROUP BY E.jobcode, E.deptnum
ORDER BY 4;
207 420 33000 420 ENGINEER
. . . . . . . . . . . . . . .
568 300 39500 300 SALESREP
EMPLOYEE Table JOB Table
EMPNUM ... JOBCODE ... SALARY JOBCODE JOBDESC
75 300 32000 300 SALESREP
. . . . . . . . . . . . . . .
178 900 28000 900 SECRETARY
. . . . . . . . . . . . . . .
207 420 33000 420 ENGINEER
. . . . . . . . . . . . . . .
568 300 39500 300 SALESREP
JOBDESC FIRST_NAME LAST_NAME SALARY
SALESREP TIM WALKER 32000
. . . . . . . . . . . .
SECRETARY JOHN CHOU 28000
. . . . . . . . . . . .
ENGINEER MARK FOLEY 33000
. . . . . . . . . . . .
SALESREP JESSICA CRINER 39500
EMPLOYEE Table JOB Table