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

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—540440-003
2-203
MXCI Examples of SELECT
•
Suppose that the JOB_CORPORATE table has been created from the JOB table
by using the CREATE LIKE statement. Form the union of these two tables:
SELECT * FROM job UNION SELECT * FROM job_corporate;
JOBCODE JOBDESC
------- ------------------
100 MANAGER
200 PRODUCTION SUPV
250 ASSEMBLER
300 SALESREP
400 SYSTEM ANALYST
420 ENGINEER
450 PROGRAMMER
500 ACCOUNTANT
600 ADMINISTRATOR
900 SECRETARY
100 CORP MANAGER
300 CORP SALESREP
400 CORP SYSTEM ANALYS
500 CORP ACCOUNTANT
600 CORP ADMINISTRATOR
900 CORP SECRETARY
--- 16 row(s) selected.
•
Present two ways to select the same data submitted by customers from California.
The first way:
SELECT OD.ordernum, SUM (qty_ordered * price)
FROM sales.parts P, sales.odetail OD
WHERE OD.partnum = P.partnum AND OD.ordernum IN
(SELECT O.ordernum
FROM sales.orders O, sales.customer C
WHERE O.custnum = C.custnum AND state = 'CALIFORNIA')
GROUP BY OD.ordernum;
ORDERNUM (EXPR)
---------- ---------------------
200490 1030.00
300350 71025.00
300380 28560.00
--- 3 row(s) selected.
The second way:
SELECT OD.ordernum, SUM (qty_ordered * price)
FROM sales.parts P, sales.odetail OD
WHERE OD.partnum = P.partnum AND OD.ordernum IN
(SELECT O.ordernum
FROM sales.orders O
WHERE custnum IN
(SELECT custnum
FROM sales.customer
WHERE state = 'CALIFORNIA'))