NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
S-30
Examples—SELECT
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
•
The following 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;
•
The following 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
+> WHERE O.PARTNUM = P.PARTNUM AND ORDERNUM IN
+> (SELECT ORDERNUM FROM ORDERS O, CUSTOMER C
+> WHERE O.CUSTNUM = C.CUSTNUM AND STATE = "CALIFORNIA")
+> GROUP BY ORDERNUM;