SQL Operators

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 ‘\’;