This clause specifies the criteria for selecting rows. The WHERE clause, if present, must occur immediately after the FROM clause.
Operators in the WHERE clause can be of two kinds:
- logical operators
- SQL operators
Logical Operators
Character data and strings in the WHERE clause must be enclosed in single quotes. In comparing the marks Oracle distinguishes between lowercase and uppercase letters.
To select the name, occupation and number of the department for all employees as a CLERK, you would type:
SELECT ENAME, JOB, DEPTNO
FROM EMP
WHERE JOB = 'CLERK';
To select all employees after 01.01.1982 we write:
SELECT ENAME, JOB, DEPTNO
FROM EMP
WHERE HireDate > '82 / 01/01 '
In the WHERE clause can compare values for each row of two columns. For example, to select those who receive a higher commission than salary, we write:
SELECT ENAME, JOB, DEPTNO
FROM EMP
WHERE COMM > SAL;
SQL Operators
There are four operators, SQL, running on all types of data:
- BETWEEN ... AND
- IN (list)
- LIKE
- IS NULL
BETWEEN ... AND operator
BETWEEN ... AND operator is used to check whether the value is in this range (including the ends of the interval). The upper limit must be followed at the bottom.
To select all, who earn from 1000 to 1500, including those who earn 1000 and 1500, we write:
SELECT ENAME, JOB, DEPTNO
FROM EMP
WHERE SAL BETWEEN 1000 AND 1500;
The IN operator
The IN operator is used to check whether the value is on the specified list. To select subordinates people with IDs 7839 and 7902, we write:
SELECT ENAME, JOB, DEPTNO
FROM EMP
MGR WHERE IN (7839.7902);
Character data appearing on the list should be included in single quotes.
LIKE operator
The LIKE operator is used to select the value corresponding to that given pattern. The pattern consists of two special symbols:
- % (percent sign) - matches any string of characters
- _ (underscore) - corresponds to one any character.
To select a person who, as the second letter in the name to the letter "A", write:
SELECT ENAME, JOB, DEPTNO
FROM EMP
WHERE ENAME LIKE '_A%';
To select a person whose name is composed of five letters, we write:
SELECT ENAME, JOB, DEPTNO
FROM EMP
WHERE ENAME LIKE '_____';
IS NULL Operator
IS NULL operator to search for NULL values.
To select data for people who do not have a boss, we write:
SELECT ENAME, JOB, DEPTNO
FROM EMP
WHERE MGR IS NULL;
You can not write in the WHERE clause condition MGR = NULL, because then Oracle does not find any values.
Operators of negation
Negation operators are used to deny the conditions in the WHERE clause.
- != - is not equal to (VAX, UNIX, PC)
- ^=, _= - is not equal (IBM)
- <> - is not equal (all operating systems)
- NOT column = - is not equal
- NOT column > - is not greater
- NOT BETWEEN - is in this range
- NOT IN - is not in the set
- NOT LIKE - is not compatible with mask
- IS NOT NULL - is not NULL
To select all the people who do not earn more than 2000, type:
SELECT ENAME, SAL
FROM EMP
NOT WHERE SAL > 2000;
To select the people who do not receive a commission, we write:
SELECT ENAME, JOB, COMM
FROM EMP
WHERE COMM IS NOT NULL
No comments:
Post a Comment