Neoview SQL Reference Manual (R2.4)

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.
• A FULL OUTER JOIN combines the results of both left and right outer joins. These joins
show records from both tables and fill in NULLs for missing matches on either side:
SELECT *
FROM employee
FULL OUTER JOIN
department
ON employee.DepartmentID = department.DepartmentID;
LastName DepartmentID DepartmentName DepartmentID
------- ------------ -------------- ------------
Smith 34 Clerical 34
Jones 33 Engineering 33
Robinson 34 Clerical 34
Jasper 36 NULL NULL
Steinberg 33 Engineering 33
Rafferty 31 Sales 31
NULL NULL Marketing 35
• 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 Statement 181