Operators

Operators



>,<,=,!=,<>,<=,>=

The operators describe as Less then,Greater then,Equal to,Not Equal to,Not Equal to,Less then Equal to, Greater then Equal to..


IN/NOT IN – using this Operator we can check more then one value of a column.We always use IN operator when we are checking more then one value in a same column.

Ex: write a query to fetch the emp details whose deptno is 10 or 20
      Select * from emp where deptno in (10,20);
  
     write a query to fetch the emp details whose deptno is not 10 or 40
      Select * from emp where deptno not in (40,10);

OR -- this operator is used when we are checking more then one value on 2 different columns.


EX:
   Write a query to fetch the emp details whose deptno is 10 or 20
Select * from emp where deptno = 10 or deptno =20;
    
 Instead of using or condition in the above stmt we can use in operator bcz we are checking        multiple values on the same column. We can use or operator as per below example...
  
Write a query to fetch emp details whose dept is 10 or whose sal greater then 1000.
Select * from emp where deptno =10 or sal >1000;
   It returns the records if that record deptno is 10 or if it the sdal is greater then 1000.

BETWEEN/NOT BETWEEN-- These are used when we are checking the ranges...

EX:  Write a query to fetch emp details from emp table whose sal is between 1000 and 3000.

       Select * from emp where sal between 1000 and 3000;

LIKE/NOT LIKE – This operator is used when we are searching any value in the string.

Note: when ever we use like or not like operator we need to search the string value by using % operator.

EX: write a query to fetch the emp details from emp table whose ename start with a letter ‘S’
      Select * from emp where ename  = ‘S%’; -- this query is wrong
      Select * from emp where ename like ‘S%’;

AND—This operator is used  when both the conditions should be satisfied...

Ex: Write a query to fetch emp details whose sal>1000 and whose dept no is 10 or 20.

     Select 8 from emp where deptno in(10,20) and sal >1000.
IS--  This  operator is used when we need to fetch the null or not null values. Oracle has provided NULL  keyword to identify the null values and it has provided the NOT NULL keyword to identify the not null values...

NOTE: When ever we need to identify null column records or not null column records we need to use is operator. In oracle column_name = NULL will not work

EX: List emp details whose sal is null.
        Select  * from emp where comm  is null;

       List emp details whose sal is not null and deptno is 10 or whose job is ÇLERK
        Select  * from emp where comm  is null;
 
       List emp details whose sal is null and deptno is 10 or whose job is ÇLERK
      Select  * from emp
      Where comm. Is null
      And (deptno = 10 and job = ‘ÇLERK’);

   
Examples:

1)      List all employee details whose deptno is not 10 and 20.
Select * from emp where deptno not in (10,20);

2)      List all employee details for the deptno 10 and whose salary is greater then or equal to 1000.
Select *
 from emp
where deptno = 10
and sal >=1000;
3)      List all employee details whose ename ends with S.
 Select *
 from emp
where ename like ‘%S’;

4)      List all employee details whose ename contains letter S.
 Select *
 from emp
where ename like ‘%S%’;

5)      List all employee details whose ename contains letter S twice.
 Select *
 from emp
where ename like ‘%S%S%’;

6)      List all employee details whose ename contains letter R at 3 position.
 select * from emp
where ENAME LIKE '__R%';

7)      List all employee details whose ename contains letter T and ends with S
 Select *
 from emp
where ename like ‘%T%S’;

8)      List all employee details whose ename doesnot contain letter S
 Select *
 from emp
where ename not  like ‘%S%’;

9)      List all employee details whose deptno is 10 or whose job is MANAGER and whose sal is between 100 and 2000.
 Select *
 from emp
where ( deptno =10 or job = ‘MANAGER’)
and      sal between 100 and 2000;

10)  List all employee details whose ename whose job is not MANAGER and deptno is 10 or 20 and whose salary is not equal to 0.
 Select *
from emp
Where job != ‘MANAGER’
and deptno in (10,20)
and sal !=0;




   


1 comments :

Usually I never comment on blogs but your article is so convincing that I never stop myself to say something about it. You’re doing a great job Man learn Oracle SOA Online Training

Reply

Post a Comment