Friday, 9 June 2017

Operators in Oracle SQL

Where Clause :- Where clause is used as a record filter. Records will be filter on where condition/conditions.
 

Operator :-Operators are used to make a condition. Operators are used between operands
Types of Operator:- Below are the category of Operators
    1) Arithmetic Operators (+,-,*,/)
    2) Logical Operators (AND, OR & NOT)
    3) Relational Operators (>,>=,=,<,<=,<>,!=)
    4) Special Operators (BETWEEN,IN,LIKE,ANY,ALL,EXISTS,IS)
    5) Set Operators (UNION,UNION ALL,INTERSCET,MINUS)
 

Relational Operator :- These operators are used compare the values. Below are the relational Operator.
            a) >  (is greater than)
            b) >= (is greater than or equal to)
            c) <  (is less than)
            d) <= (is less than or equal to)
            e) =  (is equal to)
Note:- (>,>=,<,<=) are not used with string.           
Examples:- a) Relational operators with numeric values
select * from student where id>103;
select * from student where id<103;
select * from student where id>=103;
select * from student where id<=103;
select * from student where id<>103;
select * from student where id!=103;
           b) Relational operators with date values
select * from student where dob>'12-JAN-1981';
select * from student where dob<'12-JAN-1980';
            c) Relational operators with string values
select * from student where name='Mahesh';
select * from student where name!='Mahesh';

Logical Operator (AND,OR,NOT):- Logical operators (AND,OR) are used to join 2 or more conditions. Logical operator (NOT) is used to negate the conditions

Example:- a) NOT logical Operator
select * from student where not id>103;
select * from student where not id<103;
select * from student where not id>=103;
select * from student where not id<=103;
select * from student where not id<>103;
select * from student where not id!=103;
select * from student where not dob>'12-JAN-1981';
select * from student where not name='Mahesh';
select * from student where not name!='Mahesh';
         b) AND & OR logical Operator
select * from student where id>102 and course='BCA';
select * from student where id>102 OR course='BCA';
select * from student where id>102 OR course='BCA' or DOB>'12-JAN-1981';
select * from emp where deptno=10 or deptno=20 ;
select * from student where id>102 AND ( course='BCA' or DOB>'12-JAN-1981');

Special Operators :-Below are the special operator in SQL
                   a) BETWEEN & NOT BETWEEN
                   b) IN             & NOT IN
                   c) LIKE        & NOT LIKE
                   d) ANY         & NOT ANY
                   e) ALL          & NOT ALL
                   f) EXISTS   & NOT EXISTS
                   g) IS             & IS NOT

Examples:- a) IS Operator (Oracle provide "IS" and "IS NOT" operator for comparing null values)

select * from emp where comm is null;
select * from emp where comm is not null;
           b) IN Operator
select * from emp where deptno in (10,20);
select * from emp where deptno in (10,20,30);
select * from emp where deptno not in (10,20);
           c) Like Operator
select * from emp where job like '_A%';
select * from emp where job not like '_A%';
select * from emp where job like '_AN%';
select * from emp where job like '%N';
select * from emp where empno like '76%';
           d) Between Operator
select * from emp where sal>=1000 and sal<=3000;
select * from emp where sal between 1000 and 3000;
select * from student where dob between '12-JUL-82' and '12-JUL-87';
select * from emp where sal not between 1000 and 3000;
           e) Any Operator
select * from emp where sal>any(500,1500,2000);
select * from emp where sal<any(500,1500,2000);
select * from emp where not sal<any(500,1500,2000);
           f) All Operator
select * from emp where sal>all(500,1500,2000);
select * from emp where sal<all(500,1500,2000);
select * from emp where not sal<all(500,1500,2000);
 

Arithmetic Operators:- (+,-,*,/) :-select empno,ename,sal,sal*12 annual_sal from emp;
select empno,ename,sal,comm,sal+comm mysal from emp;
select empno,ename,sal,comm,sal+nvl(comm,0) mysal from emp;
select sum(comm) from emp;
select sum(sal),min(sal),max(sal) from emp;

Set Operator :- Set Operators are used to combine the result of two or more queries. Below are the set operators.
                1) UNION
                2) UNION ALL
                3) INTERSECT
                4) MINUS


Rules for queries with Set Operator:-
Below are the rules for queries with set operators.
                a) All queries with set operators should have same number of columns
                b) Datatypes of columns should be sequentially matched in all queries
                c) Oreder by clause should be placed in last query only 

a) UNION Operator                Example
select * from emp where deptno=10
union
select * from emp where deptno=20;

select empno,ename,job,deptno from emp where deptno in (10,20)
union
select empno,ename,job,deptno from emp where deptno=20
order by deptno;

Note:- If datatype of columns is not matched the below error will be generated
select id,name,course,dob from student where course='BCA'
union
select regno,name,dob,course from student3 where course='MCA';

ORA-01790: expression must have same datatype as corresponding expression;

Note:- If order by clause is placed other than last query then below error will be generated
select id,name,dob,course from student where course='BCA' order by id
union
select regno,name,dob,course from student3 where course='MCA';

ORA-00933: SQL command not properly ended;
 

b) UNION ALL Operatorselect empno,ename,job,deptno from emp where deptno in (10,20)
union all
select empno,ename,job,deptno from emp where deptno=20
order by deptno;


 c) INTERSECT Operatorselect empno,ename,job,deptno from emp where deptno in (10,20)
INTERSECT
select empno,ename,job,deptno from emp where deptno=20
order by deptno;


d) MINUS Operatorselect empno,ename,job,deptno from emp where deptno in (20)
MINUS
select empno,ename,job,deptno from emp where deptno in (10,20)
order by deptno;

select empno,ename,job,deptno from emp where deptno in (20,30)
minus
select empno,ename,job,deptno from emp where deptno in (10,20)
order by deptno;
 


1 comment:

  1. Thanks for the given information you are providing content very usefull to users.
    Learn How to Work With Ref Cursor in ORACLE 12C For Beginner Actual cost of this course is 9,999/- But now Kuncham Software Solutions Pvt ltd is giving you Free access. Limited period offer Hurry-up Enroll Now at Udemy.


    Hurry-Up Enroll Now and get Free Access to the Entire Course


    ReplyDelete

Cloning database with RMAN

Below are the steps to clone production database(primedb) to UAT database(clonedb)   STEP1 :- Check the both servers are able to connect ...