WHERE clause in ORACLE

• This clause is used to provide comparison of data, which restricts the rows from a table.
• A WHERE clause contains a condition that must be met and should directly follow the from clause.
• Comparison to date type data is not case sensitive.
• Character values are case sensate and Date values are format Sensitive (DD-MON-YY).
• The WHERE clause can compares..
        Ø  Values in columns
        Ø  Literal Values
        Ø  Arithmetic Expressions
        Ø  Function
  Examples :-
  
  
  Display all those employees, who are working as CLERK.
  
  
  Ans : Select * from emp where job = ‘CLERK’;
  
  
  Display all those employees, who are working in deptno 20.
  
  
  Ans : Select * from emp where deptno=20;
  
  
  Display all those employees, who have been hired on 3^rd-dec-81.
  
  
  Ans : Select * from emp where hiredate=’3^rd-dec-81’;
  
  
  Display empno,ename,job,sal,deptno for all those employees who are working in deptno 20 and as CLERK.
  
  
  Ans : Select empno, ename, job, sal, deptno from emp
  Where deptno=20 AND job=’CLERK’;
  
  
  Display ename,job,salary,deptno for all those employees who are not working as MANAGER.
  
  
  Ans : Select ename, job, sal, deptno from emp
            Where job !=’MANAGER’;
  
  
  Display ename, job, salary, deptno for all those employees whose salary is >=2000.
  
  
  Ans : Select ename, job, sal, deptno from emp
            Where sal>=2000;
  
  
  Display ename, job, salary for all those employees whose salary is >=1000 and <= 3000.
  
  
  Ans : Select ename, job, sal from emp
            Where sal>=1000 AND sal<=3000;
  
  
  (OR) sal BETWEEN 1000 AND 3000;
  
  
  Display ename, job, salary, deptno, hiredate fro all those employees who have been hired between 01-jan-81 and 31-dec-81 and they are working as clerk, salesman and salary between 1000 and 3000;
  
  
  Ans : Select ename, job, sal, deptno, hiredate
            From emp
            Where hiredate BETWEEN ’01-JAN-81’ AND ’31-DEC-81’
            AND
            Job IN (‘CLERK’ , ‘SALESMAN’)
            AND
            Sal BETWEEN 1000 AND 3000;
  
  
  Display ename, job for all those employees who are not working as clerk, analyst.
  
  
  Ans : select ename, job from emp
            Where job NOT IN (‘CLERK’ , ‘ANALYST’);