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;
No comments:
Post a Comment