OPERATORS

O p e r a t o r s


The following operators can be used in the where clause to simplify the conditions.

Between … And
In
Like
Is Null

1) Between … And:

This operator is used to test whether the given value is present in given range of values.

Examples:
Find all employees whose Salary is more than 2000 and less than 4000.
Select * from EMP where Sal between 2000 and 4000;
Find all employees joined after 12 th june 1990, and before 31st December 1990.
Select * from EMP where Hiredate between ‘12-Jun-90’ and ‘31-Dec-90’;
Find all employees who are not joined after 12th June 1990, and before 31st December 1990.
Select * from EMP where Hiredate NOT Between ‘12-Jun-90’ and ‘31-Dec-90’;


2) In :

This operator is used to test whether the given value is present in a given list of values.

Examples:
Find all employees whose salary is either 1200 or 1500 or 1750.
Select * from EMP where Sal IN (1200, 1500, 1750);
Find all employees who are working either as a Manager or a Clerk.
Select * from EMP where Job IN (‘MANAGER’, ‘CLERK’);


3) Like:

One of the most powerful features of SQL is a marvelous pattern-matching operator called LIKE, which is able to search through the rows of a database column for values that look like a pattern you describe. It uses two special characters to denote which kind of matching to do : a percent sign, called wildcard, and an underscore, called position marker.

Examples:
Find all employees whose Name starts with ‘S’.
Select * from EMP where Ename LIKE ‘S%’;
Find all employees whose name ends with ‘E’.
Select * from EMP where Ename LIKE ‘%E’;
Find all employees whose name contains the letter ‘A’.
Select * from EMP where Ename LIKE ‘%A%’ ;
Find all employees whose name contains ‘L’ as second letter.
Select * from EMP where Ename LIKE ‘_L%’ ;
Find all employees whose name doesn’t contain the letter ‘A’.
Select * from EMP where Ename NOT LIKE ‘%A%’;
Find all employees whose name contains five letters.
Select * from EMP where Ename like ‘_ _ _ _ _’; (Underscores without space between them)


4) Is Null:

This operator is used to test for a null value in the given column.


Examples:
Find all employees who are not drawing any commission.
Select * from EMP where Comm IS NULL;
Find all employees who are drawing Commission.
Select * from EMP where Comm IS NOT NULL;