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 …
Ex:
- Select Ename, Sal, Job From Emp
where Sal BETWEEN 1000 AND 1500;
- Select Ename, Sal, Job From Emp
where Sal Not between 1000 and 1500;
- Select Ename, Sal, Job From Emp
where Job Between ’MANAGER’ And ’SALESMAN’;
- Select Ename, Sal, Job From Emp
where Job NOT between ’MANAGER’ and ’SALESMAN’;
- Select Ename, Sal, Job,
Hiredate From Emp where Hiredate between ’17-FEB-1981’ and ’20-JUN-1983’;
- Select Ename, Sal, Job,
Hiredate From Emp where Hiredate NOT between ’17-FEB-1981’ and
’20-JUN-1983’;
IN Operator:
Ø This 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.
Ex:
- Select Ename, Sal, Job From Emp
where Ename IN (’FORD’,’ALLEN’);
- Select Ename, Sal, Job From Emp
where Ename NOT IN (’FORD’,’ALLEN’);
- Select Ename, Sal, Job From Emp
where Ename IN(10,30);
- Select Ename, Sal, Job,
Hiredate From Emp where hiredate IN (’20-FEB-1981’,’09-JUN-1981’);
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.
Ex:
- Select Ename, Deptno, Comm From
Emp where Comm IS NULL;
- Select Ename, Deptno, Job, Mgr
From Emp where mgr is null;
- Select Ename, Deptno, comm From
Emp where comm Is not null;
- Select Ename, Deptno, comm, mgr
From Emp where mgr is not null;
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 cards in oracle
are:
o
%
à used to represent any sequences of
zero or more characters.
o
_
à 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.
Ex:
- Select Ename, Job From Emp
where Ename LIKE ’S%’;
- Select Ename,Job From Emp where
Ename not like ’S%’;
- Select Ename,Job From Emp where
ename like ’%S%’;
- Select Ename, Job From Emp
where ename like ’_A%’;
- Select Ename, Job From Emp
where ename not like ’_A%’;
- Select ename, job from emp
where ename = ’SM%’; (not valid)
- Select ename, job from emp
where ’SM%’ like Ename; (not valid)
- Select Ename, Hiredate From emp
where hiredate like ’%-FEB-81’;
- Select Ename, Hiredate From emp
where hiredate like ’03-%-81’;
- Select * from dept where Dname like ‘%\_%’ ESCAPE ‘\’;