SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
S-29
Examples—SELECT
568 300 39500 900 MANAGER
2. Drop rows with unequal job codes.
EMPLOYEE Table JOB Table
EMPNUM ... JOBCODE ... SALARY JOBCODE JOBDESC
1 100 175500 100 MANAGER
. . .
207 420 33000 420 ENGINEER
. . .
568 300 39500 300 SALESREP
3. Drop rows with job codes not 900, 300 or 420.
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
4. Process select list, leaving only four columns.
EMPLOYEE Table JOB Table
JOBDESC FIRST_NAME LAST_NAME SALARY
SALESREP TIM WALKER 32000
. . . .
SECRETARY JOHN CHOU 28000
. . . .
ENGINEER MARK FOLEY 33000
. . . .
SALESREP DESIREE EVANS 39500

This SQLCI example selects from three tables and groups the rows by job code
and (within job code) by department number. Only job codes 300, 420, and 900 are
selected. The minimum and maximum salary for the same job in each department
is computed, and the rows are ordered by maximum salary.
>> VOLUME $VOL.PERSNL;
>> SELECT E.JOBCODE, E.DEPTNUM, MIN (SALARY), MAX (SALARY)
+> FROM EMPLOYEE E, DEPT D, JOB J
+> WHERE E.DEPTNUM = D.DEPTNUM AND E.JOBCODE = E.JOBCODE
+> AND E.JOBCODE IN (900, 300, 420)
+> GROUP BY E.JOBCODE, E.DEPTNUM
+> ORDER BY 4;

This example presents two ways to select data about orders by customers from
California. The price for the total quantity ordered is computed for each order
number.
>> VOLUME $VOL.SALES;
>> SELECT ORDERNUM, SUM (QTY_ORDERED * PRICE)
+> FROM PARTS P, ODETAIL O