SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
S-28
Examples—SELECT

This 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.

This 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;

This 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);
These logical steps that determine the result of the previous query:
1. Join the tables.
EMPLOYEE Table JOB Table
EMPNUM ... JOBCODE ... SALARY JOBCODE JOBDESC
1 100 175500 100 MANAGER
1 100 175500 200 PROD SUPV
. .
1 100 175500 900 SECRETARY
568 300 39500 100 MANAGER
568 300 39500 200 PROD SUPV