Neoview SQL Reference Manual (R2.3)

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
900 2000 32000.00 32000.00
...
300 3200 22000.00 33000.10
420 4000 18000.10 36000.00
...
--- 16 row(s) selected.
Only job codes 300, 420, and 900 are selected. The minimum and maximum salary for the
same job in each department are computed, and the rows are ordered by maximum salary.
• Select from two tables that have been joined by using an INNER JOIN on matching part
numbers:
SELECT OD.*, P.*
FROM sales.odetail OD INNER JOIN sales.parts P
ON OD.partnum = P.partnum;
Order/Num Part/Num Unit/Price Qty/Ord Part/Num Part Description PRICE Qty/Avail
---------- -------- ------------ ---------- --------
------------------ ------------ -----------
400410 212 2450.00 12 212
PCSILVER, 20 MB 2500.00 3525
500450 212 2500.00 8 212
PCSILVER, 20 MB 2500.00 3525
100210 244 3500.00 3 244
PCGOLD, 30 MB 3000.00 4426
800660 244 3000.00 6 244
PCGOLD, 30 MB 3000.00 4426
... ... ... ... ...
... ... ...
--- 72 row(s) selected.
• Select from three tables and display them in employee number order. Two tables are joined
by using a LEFT JOIN on matching department numbers, then an additional table is joined
on matching jobcodes:
SELECT empnum, first_name, last_name, deptname, location, jobdesc
FROM employee e LEFT JOIN dept d ON e.deptnum = d.deptnum
LEFT JOIN job j ON e.jobcode = j.jobcode
ORDER BY empnum;
• 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;
162 SQL Statements