Operators


Arithmetic operator

(addition (+), Subtraction (-), multiplication (*), division (/), modulo (%))

SELECT ENAME, ((SAL*12+NVL (COMM*12, 0)-1000)) "ANNUAL SALARY" FROM DB_EMP;

Comparison operators

SELECT ENAME FROM DB_EMP WHERE SAL >= 1000;
SELECT DNAME, LOC FROM DB_DEPT WHERE LOC <> 'DEL';

SQL Operators


IS NULL

IS NULL operator is used to find null values.

SELECT EMPNO, ENAME FROM DB_EMP WHERE COMM IS NULL;

IN Operator

We can specify multiple values with this operator.

Syntax:
SELECT column1,column2,….
FROM TABLE_NAME;
WHERE COLUMN_NAME IN (value1, value2,.....);
SELECT EMPNO, ENAME FROM DB_EMP WHERE DEPTNO IN (100,200,500);
SELECT EMPNO, ENAME FROM DB_EMP WHERE DEPTNO NOT IN (100,200,500);

Between Operator

Select values within a range. The values can be numbers, text, or dates.

Syntax:
SELECT column1, column2, …
FROM TABLE_NAME
WHERE COLUMN_NAME BETWEEN value1 AND value2;
SELECT EMPNO, ENAME FROM DB_EMP WHERE HIREDATE BETWEEN '20-APR-14' AND '22-APR-14'
SELECT EMPNO, ENAME FROM DB_EMP WHERE HIREDATE NOT BETWEEN '20-APR-14' AND '22-APR-14'

Logical Operator

AND Operator
SELECT * FROM TABLE_NAME WHERE Condition1 AND Condition 2;
SELECT * FROM DB_EMP WHERE SAL>1000 AND DEPTNO=200;

OR Operator
SELECT * FROM TABLE_NAME WHERE Condition1 OR Condition 2;
SELECT * FROM DB_EMP WHERE SAL>1000 OR DEPTNO=200;

LIKE Operator

This Operator is used for wildcard search.

Wildcards symbols

Percent (%):

Percent sign represents zero or more characters.

Underscore ( _ ):

Underscore represents a single character.

SELECT * FROM TABLE_NAME WHERE column LIKE 'XXXX%';
OR 
SELECT * FROM TABLE_NAME WHERE column LIKE '%XXXX%';
OR
SELECT * FROM TABLE_NAME WHERE column LIKE '_XXXX%';
OR
SELECT * FROM TABLE_NAME WHERE column LIKE 'XXXX_';
OR
SELECT * FROM TABLE_NAME WHERE column LIKE '_XXXX';
OR
SELECT * FROM TABLE_NAME WHERE column LIKE '_%XXXX%';
SELECT * FROM DB_EMP WHERE ENAME LIKE '%AC%';
Footer with Map