NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
S-28
Examples—SELECT
•
The following SQLCI example displays selected rows grouped by job code in
ascending order. The select-list contains only grouping columns and
functions because each group results in one row.
>> 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 27125.00
--- 2 row(s) selected.
•
The following SQLCI example uses the HAVING clause to accomplish the same
result as the previous example in an alternative but equally efficient way:
>> SELECT JOBCODE, AVG (SALARY)
+> FROM PERSNL.EMPLOYEE
+> WHERE DEPTNUM <= 3000
+> GROUP BY JOBCODE
+> HAVING JOBCODE > 500
+> ORDER BY JOBCODE;
•
The following example selects 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. The condition is called a join predicate.
This query joins the EMPLOYEE and JOB tables by combining each row of the
EMPLOYEE table with each row of the JOB table; the result is the Cartesian
product of the two tables. The join predicate specifies that any row with equal job
codes is included in the result table. All other rows are eliminated.
>> VOLUME $VOL1.PERSNL;
>> SELECT JOBDESC, FIRST_NAME, LAST_NAME, SALARY
+> FROM EMPLOYEE, JOB
+> WHERE EMPLOYEE.JOBCODE = JOB.JOBCODE AND
+> EMPLOYEE.JOBCODE IN (900, 300, 420);
The logical steps that determine the result of the previous query are as follows: