Examples of SELECT statements

Examples of SELECT statements.

Introductory concepts
Example 1: Select all the rows from DSN8C10.EMP. 
   SELECT * FROM DSN8C10.EMP;
Example 2: Select all the rows from DSN8C10.EMP, arranging the result table in chronological order by date of hiring. 
   SELECT * FROM DSN8C10.EMP
      ORDER BY HIREDATE;
Example 3: Select the department number (WORKDEPT) and average departmental salary (SALARY) for all departments in the table DSN8C10.EMP. Arrange the result table in ascending order by average departmental salary. 
   SELECT WORKDEPT, AVG(SALARY)
     FROM DSN8C10.EMP
     GROUP BY WORKDEPT
     ORDER BY 2;
Example 4: Change various salaries, bonuses, and commissions in the table DSN8C10.EMP. Confine the changes to employees in departments D11 and D21. Use positioned updates to do this with a cursor named UP_CUR. Use a FOR UPDATE clause in the cursor declaration to indicate that all updatable columns are updated. Below is the declaration for a PL/I program. 
   EXEC SQL DECLARE UP_CUR CURSOR FOR
     SELECT WORKDEPT, EMPNO, SALARY, BONUS, COMM
       FROM DSN8C10.EMP
       WHERE WORKDEPT IN ('D11','D21')
       FOR UPDATE;
Beginning where the cursor is declared, all updatable columns would be updated. If only specific columns needed to be updated, such as only the salary column, the FOR UPDATE clause could be used to specify the salary column (FOR UPDATE OF SALARY).
Example 5: Find the maximum, minimum, and average bonus in the table DSN8C10.EMP. Execute the statement with uncommitted read isolation, regardless of the value of ISOLATION with which the plan or package containing the statement is bound. Assign 13 as the query number for the SELECT statement. 
   EXEC SQL
     SELECT MAX(BONUS), MIN(BONUS), AVG(BONUS)
       INTO :MAX, :MIN, :AVG
       FROM DSN8C10.EMP
       WITH UR
       QUERYNO 13;
If bind option EXPLAIN(YES) is specified, rows are inserted into the plan table. The value used for the QUERYNO column for these rows is 13.

Example 6: The cursor declaration shown below is in a PL/I program. In the query within the declaration, X.RMT_TAB is an alias for a table at some other Db2. Hence, when the query is used, it is processed using DRDA access. See Distributed relational databases.

The declaration indicates that no positioned updates or deletes will be done with the query's cursor. It also specifies that the access path for the query be optimized for the retrieval of at most 50 rows. Even so, the program can retrieve more than 50 rows from the result table, which consists of the entire table identified by the alias. However, when more than 50 rows are retrieved, performance could possibly degrade. 
   EXEC SQL DECLARE C1 CURSOR FOR
     SELECT * FROM X.RMT_TAB
     OPTIMIZE FOR 50 ROWS
     FOR READ ONLY;
The FETCH FIRST clause could be used instead of the OPTIMIZE FOR clause to ensure that only 50 rows are retrieved as in the following example: 
   EXEC SQL DECLARE C1 CURSOR FOR
     SELECT * FROM X.RMT_TAB
     FETCH FIRST 50 ROWS ONLY;
Example 7: Assume that table DSN8C10.EMP has 1000 rows and you want to see the first five EMP_ROWID values that were inserted into DSN8C10.EMP_PHOTO_RESUME. 
   EXEC SQL DECLARE CS1 CURSOR FOR
     SELECT EMP_ROWID
       FROM FINAL TABLE (INSERT INTO DSN8C10.EMP_PHOTO_RESUME (EMPNO)
                        SELECT EMPNO FROM DSN8C10.EMP)
       FETCH FIRST 5 ROWS ONLY;
All 1000 rows are inserted into DSN8C10.EMP_PHOTO_RESUME, but only the first five are returned.