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