SQL/MX 2.x Reference Manual (G06.24+, H06.03+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—523725-004
2-205
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;
JOBCODE DEPTNUM (EXPR) (EXPR)
------- ------- ----------- -----------
900 1500 17000.00 17000.00
900 2500 18000.00 18000.00
...
300 3000 19000.00 32000.00
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