SQL*PLUS Operators  

1. BETWEEN...AND...Operator
2. IN Operator
3. IS NULL Operator
4. LIKE Operator

1. BETWEEN...AND...Operator :

• This operator is used to display rows based on a range of values.
• The declared range is inclusive.
• The lower limit should be declared first
• The negation of this operator is NOT BETWEEN...AND...

Examples :

1.
SELECT Ename, Sal, Job
FROM Emp
WHERE Sal BETWEEN 1000 AND 1500;
2.
SELECT Ename, Sal, Job
FROM Emp
WHERE Sal  NOT BETWEEN 1000 AND 1500;

3.
SELECT Ename, Sal, Job
FROM Emp
WHERE Job BETWEEN 'MANAGER'  AND 'SALESMAN' ;

4.
SELECT Ename, Sal, Job
FROM Emp
WHERE Job NOT BETWEEN 'MANAGER'  AND 'SALESMAN' ;

5.
SELECT Ename, Sal, Job,Hiredate
FROM Emp
WHERE Hiredate BETWEEN '18-FEB-1981'  AND '20-JUN-1983' ;
6.

SELECT Ename, Sal, Job,Hiredate
FROM Emp
WHERE Hiredate NOT BETWEEN '18-FEB-1981'  AND '20-JUN-1983' ;

2. IN Operator :

The operator is used to test for values in a specified list.
The operator can be used upon any data type.
The negation of the operator is NOT IN.

Examples :

1.
SELECT Ename, Sal, Job
FROM Emp
WHERE Ename IN('FORD' , 'ALLEN');

2.
SELECT Ename, Sal, Job
FROM Emp
WHERE Ename NOT IN('FORD' , 'ALLEN');

3.
SELECT Ename, Sal, Deptno
FROM Emp
WHERE Deptno IN(10 , 30 );

4.
SELECT Ename, Sal, Deptno
FROM Emp
WHERE Deptno NOT IN(10 , 30 );

5.
SELECT Ename, Sal, Hiredate
FROM Emp
WHERE Hiredate IN( '20-FEB-1981' , '09-JUN-1981' );

6.

SELECT Ename, Sal, Hiredate
FROM Emp
WHERE Hiredate NOT IN( '20-FEB-1981' , '09-JUN-1981' );

3. IS NULL Operator :

• The operator tests for NULL values.
• It is the only operator that can be used to test for NULL'S.
  
• The negation is IS NOT NULL.

Examples :

1.
SELECT Ename,Deptno, Comm
FROM Emp
WHERE Comm IS NULL;
2.

SELECT Ename, Deptno, Job, MGR
FROM Emp
WHERE MGR IS NULL;

3.

SELECT Ename, Deptno, Comm

FROM Emp
WHERE Comm IS NOT NULL;
4.

SELECT Ename, Deptno, Job, MGR
FROM Emp
WHERE MGR IS NOT NULL;

4. LIKE Operator :

• The LIKE operator is used to search for a matching character patterns.
• The character pattern matching operation is referred as a WILD CARD SEARCH.
• The available WILD CARD in oracle are      

•                     % - Used to represent any sequence of zero or more characters.       
•                    _ -  Represents any single character, only at that position.

• The WILD CARD symbols can be used in any combination with literal characters.
• For finding exact match for '%' and '_' the ESCAPE option has to be used along with '\' symbol.

Examples :

1.
SELECT Ename, Job
FROM Emp
WHERE Ename LIKE 'S%';

2.
SELECT Ename, Job
FROM Emp
WHERE Ename NOT LIKE 'S%';

3.
SELECT Ename, Job
FROM Emp
WHERE Ename LIKE '_A%';

4.
SELECT Ename, Job
FROM Emp
WHERE Ename NOT LIKE '_A%';

5.
SELECT Ename, Job
FROM Emp
WHERE Ename LIKE 'SM%';
6.
SELECT Ename, Job
FROM Emp
WHERE Ename NOT LIKE 'SM%';

7.
SELECT Ename, Job
FROM Emp
WHERE  'SM%' LIKE Ename;

8.
SELECT Ename,Hiredate
FROM Emp
WHERE Hiredate LIKE '%-FEB-1981';

9.
SELECT Ename,Hiredate
FROM Emp
WHERE Hiredate LIKE '03-%-1981';

10.
SELECT *
FROM Dept
WHERE Dname LIKE '%\_%' ESCAPE '\';