Neoview SQL Reference Manual (R2.3)

600 1500 JIMMY SCHNEIDER 26000.00
900 2500 MIRIAM KING 18000.00
900 1000 SUE CRAMER 19000.00
. . .
In this example, because of READ UNCOMMITTED access, the query does not wait for
other concurrent processes to commit rows.
• Display selected rows grouped by job code in ascending order:
SELECT jobcode, AVG(salary)
FROM persnl.employee
WHERE jobcode > 500 AND deptnum <= 3000
GROUP BY jobcode
ORDER BY jobcode;
JOBCODE EXPR
------- ----------------------
600 29000.00
900 25100.00
--- 2 row(s) selected.
This select list contains only grouping columns and aggregate functions. Each row of the
output summarizes the selected data within one group.
• Select data from more than one table by specifying the table names in the FROM clause and
specifying the condition for selecting rows of the result in the WHERE clause:
SELECT jobdesc, first_name, last_name, salary
FROM persnl.employee E, persnl.job J
WHERE E.jobcode = J.jobcode AND
E.jobcode IN (900, 300, 420);
JOBDESC FIRST_NAME LAST_NAME SALARY
------------ ------------ --------------- -----------
SALESREP TIM WALKER 32000.00
SALESREP HERBERT KARAJAN 29000.00
...
ENGINEER MARK FOLEY 33000.00
ENGINEER MARIA JOSEF 18000.10
...
SECRETARY BILL WINN 32000.00
SECRETARY DINAH CLARK 37000.00
...
--- 27 row(s) selected.
This type of condition is sometimes referred to as a join predicate. The query first joins the
EMPLOYEE and JOB tables by combining each row of the EMPLOYEE table with each row
of the JOB table; the intermediate result is the Cartesian product of the two tables.
This join predicate specifies that any row (in the intermediate result) with equal job codes
is included in the result table. The WHERE condition further specifies that the job code must
be 900, 300, or 420. All other rows are eliminated.
The four logical steps that determine the intermediate and final results of the previous query
are:
1. Join the tables.
JOB TableEMPLOYEE Table
JOBDESCJOBCODESALARYJOBCODE ...EMPNUM ...
160 SQL Statements