DB2 10.5 for Linux, UNIX, and Windows

Examples of select-statement queries

The following examples illustrate the select-statement query.

Example 1: Select all columns and rows from the EMPLOYEE table.
```
  SELECT * FROM EMPLOYEE
```
Example 2: Select the project name (PROJNAME), start date (PRSTDATE), and end date (PRENDATE) from the PROJECT table. Order the result table by the end date with the most recent dates appearing first.
```
  SELECT PROJNAME, PRSTDATE, PRENDATE
   FROM PROJECT          
   ORDER BY PRENDATE DESC
```
Example 3: Select the department number (WORKDEPT) and average departmental salary (SALARY) for all departments in the EMPLOYEE table. Arrange the result table in ascending order by average departmental salary.
```
  SELECT WORKDEPT, AVG(SALARY)
     FROM EMPLOYEE   
     GROUP BY WORKDEPT 
     ORDER BY 2
```
Example 4: Declare a cursor named UP_CUR to be used in a C program to update the start date (PRSTDATE) and the end date (PRENDATE) columns in the PROJECT table. The program must receive both of these values together with the project number (PROJNO) value for each row.
```
  EXEC SQL  DECLARE UP_CUR CURSOR FOR
              SELECT PROJNO, PRSTDATE, PRENDATE
                FROM PROJECT
                FOR UPDATE OF PRSTDATE, PRENDATE;
```

Example 5: This example names the expression SAL+BONUS+COMM as TOTAL_PAY

   SELECT SALARY+BONUS+COMM AS TOTAL_PAY
     FROM EMPLOYEE
     ORDER BY TOTAL_PAY

Example 6: Determine the employee number and salary of sales representatives along with the average salary and head count of their departments. Also, list the average salary of the department with the highest average salary.
Using a common table expression for this case saves the processing resources of creating the DINFO view as a regular view. During statement preparation, accessing the catalog for the view is avoided and, because of the context of the rest of the fullselect, only the rows for the department of the sales representatives are considered by the view.
```
 WITH
    DINFO (DEPTNO, AVGSALARY, EMPCOUNT) AS
        (SELECT OTHERS.WORKDEPT, AVG(OTHERS.SALARY), COUNT(*)
          FROM EMPLOYEE OTHERS
          GROUP BY OTHERS.WORKDEPT
        ),
    DINFOMAX AS
        (SELECT MAX(AVGSALARY) AS AVGMAX FROM DINFO)
 SELECT THIS_EMP.EMPNO, THIS_EMP.SALARY,
        DINFO.AVGSALARY, DINFO.EMPCOUNT, DINFOMAX.AVGMAX
  FROM EMPLOYEE THIS_EMP, DINFO, DINFOMAX
  WHERE THIS_EMP.JOB = 'SALESREP'
  AND THIS_EMP.WORKDEPT = DINFO.DEPTNO
```

Example 7: Given two tables, EMPLOYEE and PROJECT, replace employee SALLY with a new employee GEORGE, assign all projects lead by SALLY to GEORGE, and return the names of the updated projects.

  WITH
    NEWEMP AS (SELECT EMPNO FROM NEW TABLE
                (INSERT INTO EMPLOYEE(EMPNO, FIRSTNME)
                    VALUES(NEXT VALUE FOR EMPNO_SEQ, 'GEORGE'))),
    OLDEMP AS (SELECT EMPNO FROM EMPLOYEE WHERE FIRSTNME = 'SALLY'),
    UPPROJ AS (SELECT PROJNAME FROM NEW TABLE
                (UPDATE PROJECT
                    SET RESPEMP = (SELECT EMPNO FROM NEWEMP)
                    WHERE RESPEMP = (SELECT EMPNO FROM OLDEMP))),
    DELEMP AS (SELECT EMPNO FROM OLD TABLE
                (DELETE FROM EMPLOYEE
                    WHERE EMPNO = (SELECT EMPNO FROM OLDEMP)))
  SELECT PROJNAME FROM UPPROJ;

Example 8: Retrieve data from the DEPT table. That data will later be updated with a searched update, and will be locked when the query executes.

  SELECT DEPTNO, DEPTNAME, MGRNO
    FROM DEPT
    WHERE ADMRDEPT ='A00'
    FOR READ ONLY WITH RS USE AND KEEP EXCLUSIVE LOCKS

Example 9: Select all columns and rows from the EMPLOYEE table. If another transaction is concurrently updating, deleting, or inserting data in the EMPLOYEE table, the select operation will wait to get the data until after the other transaction is completed.
```
  SELECT * FROM EMPLOYEE WAIT FOR OUTCOME 
```