|
WHERE clause
A WHERE clause is an optional part of a SelectExpression, DELETE statement, or UPDATE statement.
The WHERE clause lets you select rows based on a boolean expression. Only
rows for which the expression evaluates to TRUE are returned in the result,
or, in the case of a DELETE statement, deleted, or, in the case of an UPDATE
statement, updated.
Syntax WHERE Boolean expression
Boolean expressions
are allowed in the WHERE clause. Most of the general expressions listed in Table of general expressions, can result in a
boolean value.
In addition, there are the more common boolean expressions.
Specific boolean operators listed in Table 10, take one or more operands;
the expressions return a boolean value.
Example
-- find the flights where no business-class seats have
-- been booked
SELECT *
FROM FlightAvailability
WHERE business_seats_taken IS NULL
OR business_seats_taken = 0
-- 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 SAMP.EMP_ACT.*, LASTNAME
FROM SAMP.EMP_ACT, SAMP.EMPLOYEE
WHERE EMP_ACT.EMPNO = EMPLOYEE.EMPNO
-- 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 new-column-name specified in the AS clause
-- which is outside the fullselect (DINFO)
-- in order to get the AVGSALARY and EMPCOUNT columns,
-- as well as 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
|
