Examples of subselect queries
The following examples illustrate the susbelect query.
- Example 1 - Select all columns and rows from the EMPLOYEE table.
SELECT * FROM EMPLOYEE
- Example 2 - Join the EMP_ACT and EMPLOYEE tables, select all the columns from the EMP_ACT table
and add the employee's surname (LASTNAME) from the EMPLOYEE table to each row of the result.
SELECT EMP_ACT.*, LASTNAME FROM EMP_ACT, EMPLOYEE WHERE EMP_ACT.EMPNO = EMPLOYEE.EMPNO
- Example 3 - Join the EMPLOYEE and DEPARTMENT tables, select the employee number (EMPNO),
employee surname (LASTNAME), department number (WORKDEPT in the EMPLOYEE table, and DEPTNO in the
DEPARTMENT table) and department name (DEPTNAME) of all employees who were born (BIRTHDATE) earlier
than 1955.
SELECT EMPNO, LASTNAME, WORKDEPT, DEPTNAME FROM EMPLOYEE, DEPARTMENT WHERE WORKDEPT = DEPTNO AND YEAR(BIRTHDATE) < 1955
- Example 4 - Select the job (JOB) and the minimum and maximum salaries (SALARY) for each group of
rows with the same job code in the EMPLOYEE table, but only for groups with more than one row and
with a maximum salary greater than or equal to 27000.
SELECT JOB, MIN(SALARY), MAX(SALARY) FROM EMPLOYEE GROUP BY JOB HAVING COUNT(*) > 1 AND MAX(SALARY) >= 27000
- Example 5 - Select all the rows of EMP_ACT table for employees (EMPNO) in department (WORKDEPT)
'E11'. (Employee department numbers are shown in the EMPLOYEE table.)
SELECT * FROM EMP_ACT WHERE EMPNO IN (SELECT EMPNO FROM EMPLOYEE WHERE WORKDEPT = 'E11')
- Example 6 - From the EMPLOYEE table, select the department number (WORKDEPT) and
maximum departmental salary (SALARY) for all departments whose maximum salary is less than the
average salary for all employees.
The subquery in the HAVING clause is run once in this example.SELECT WORKDEPT, MAX(SALARY) FROM EMPLOYEE GROUP BY WORKDEPT HAVING MAX(SALARY) < (SELECT AVG(SALARY) FROM EMPLOYEE)
- Example 7 - Using the EMPLOYEE table, select the department number (WORKDEPT) and
maximum departmental salary (SALARY) for all departments whose maximum salary is less than the
average salary in all other departments.
SELECT WORKDEPT, MAX(SALARY) FROM EMPLOYEE EMP_COR GROUP BY WORKDEPT HAVING MAX(SALARY) < (SELECT AVG(SALARY) FROM EMPLOYEE WHERE NOT WORKDEPT = EMP_COR.WORKDEPT)
In contrast to Example 6, the subquery in the HAVING clause is run for each group.
- Example 8 - Determine the employee number and salary of sales representatives along
with the average salary and head count of their departments.This query must first create a nested table expression (DINFO) to get the AVGSALARY and EMPCOUNT columns, and the DEPTNO column that is used in the WHERE clause.
SELECT THIS_EMP.EMPNO, THIS_EMP.SALARY, DINFO.AVGSALARY, DINFO.EMPCOUNT FROM EMPLOYEE THIS_EMP, (SELECT OTHERS.WORKDEPT AS DEPTNO, AVG(OTHERS.SALARY) AS AVGSALARY, COUNT(*) AS EMPCOUNT FROM EMPLOYEE OTHERS GROUP BY OTHERS.WORKDEPT ) AS DINFO WHERE THIS_EMP.JOB = 'SALESREP' AND THIS_EMP.WORKDEPT = DINFO.DEPTNO
Using a nested 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. Because of the context of the rest of the query, only the rows for the department of the sales representatives are considered by the view.
- Example 9 - Display the average education level and salary for five random groups of
employees.This query requires the use of a nested table expression to set a random value for each employee so that it can later be used in the GROUP BY clause.
SELECT RANDID , AVG(EDLEVEL), AVG(SALARY) FROM ( SELECT EDLEVEL, SALARY, INTEGER(RAND()*5) AS RANDID FROM EMPLOYEE ) AS EMPRAND GROUP BY RANDID
- Example 10 - Query the EMP_ACT table and return those project numbers that have an
employee whose salary is in the top 10 of all
employees.
SELECT EMP_ACT.EMPNO,PROJNO FROM EMP_ACT WHERE EMP_ACT.EMPNO IN (SELECT EMPLOYEE.EMPNO FROM EMPLOYEE ORDER BY SALARY DESC FETCH FIRST 10 ROWS ONLY)
- Example 11 - Assuming that PHONES and IDS are two SQL variables with array values of the same
cardinality, turn these arrays into a table with three columns (one for each array and one for the
position), and one row per array
element.
SELECT T.PHONE, T.ID, T.INDEX FROM UNNEST(PHONES, IDS) WITH ORDINALITY AS T(PHONE, ID, INDEX) ORDER BY T.INDEX