Select all columns and rows from the EMPLOYEE table.
SELECT * FROM EMPLOYEE
Join the EMPPROJACT and EMPLOYEE tables, select all the columns from the EMPPROJACT table and add the employee's surname (LASTNAME) from the EMPLOYEE table to each row of the result.
SELECT EMPPROJACT.*, LASTNAME FROM EMPPROJACT, EMPLOYEE WHERE EMPPROJACT.EMPNO = EMPLOYEE.EMPNO
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 1930.
SELECT EMPNO, LASTNAME, WORKDEPT, DEPTNAME FROM EMPLOYEE, DEPARTMENT WHERE WORKDEPT = DEPTNO AND YEAR(BIRTHDATE) < 1930
This subselect could also be written as follows:
SELECT EMPNO, LASTNAME, WORKDEPT, DEPTNAME FROM EMPLOYEE INNER JOIN DEPARTMENT ON WORKDEPT = DEPTNO WHERE YEAR(BIRTHDATE) < 1930
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
Select all the rows of EMPPROJACT table for employees (EMPNO) in department (WORKDEPT) 'E11'. (Employee department numbers are shown in the EMPLOYEE table.)
SELECT * FROM EMPPROJACT WHERE EMPNO IN (SELECT EMPNO FROM EMPLOYEE WHERE WORKDEPT = 'E11')
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.
SELECT WORKDEPT, MAX(SALARY) FROM EMPLOYEE GROUP BY WORKDEPT HAVING MAX(SALARY) < (SELECT AVG(SALARY) FROM EMPLOYEE)
The subquery in the HAVING clause would only be executed once in this example.
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 would need to be executed for each group.
Join the EMPLOYEE and EMPPROJACT tables, select all of the employees and their project numbers. Return even those employees that do not have a project number currently assigned.
SELECT EMPLOYEE.EMPNO, PROJNO FROM EMPLOYEE LEFT OUTER JOIN EMPPROJACT ON EMPLOYEE.EMPNO = EMPPROJACT.EMPNO
Any employee in the EMPLOYEE table that does not have a project number in the EMPPROJACT table will return one row in the result table containing the EMPNO value and the null value in the PROJNO column.
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.