OPERATORS

Logical operators

There are three logical operators:
· AND
· OR
· NOT

AND Operator==List the details of the employees who do the job of salesman and belong to the department 10.
SQL> select * from emp where job = ‘MANAGER’ and deptno = 20;

OR Operator==List the names of the employees who do the job clerk or salesman.
SQL> select ename, job from emp where job = ‘CLERK’ or job = “SALESMAN’.

NOT Operator==List employee number of the employees whose don’t have the name of ‘FORD’, ‘JAMES ‘or ‘JONES’;
SQL> select empno from emp where ename not in (‘FORD’,’JAMES’,’JONES’);


Set Operators
Set operators are used to combine information of similar type from one of more than one table. While performing the set operations it should be remembered that the corresponding columns must be the same .The set operators allow us to combine two or more queries into one result. The different type of SET operators in Oracle is:
· UNION
· INTERSECT
· MINUS
Union clause

The union clause merges the output of two or more queries in a single set of rows or columns .It eliminates the duplicate values between two or more queries. The syntax of Union operator is:
Select
UNION
Select
[order by clause ]
Here the statements 1 and 2 are valid select statements and the order by clause is optional. Both the queries are executed independently, but the output of both the queries is merged.

Example:Display the different jobs in departments 20 and 30.
SQL>Select job from emp Where deptno =20
Union
Select job from emp Where deptno =30;


Intersect operator
Another operator, which can be used to get the combined output of multiple queries, is the Intersect operator. It gives only those rows as output from both the queries, which have common records among them.
The syntax of the intersect operator is:

SELECT
INTERSECT
SELECT
[ORDER by clause ];


Example:

· List the Jobs common to department 20 and 30.
SQL> select job from emp where deptno =20
INTERSECT
select job from emp where deptno =30;



Minus operator

The Minus operator returns the rows, which are unique to the first query. It must be remembered that in case of minus A-B is different from B-A. That is why the minus operator does not follow the property of commutative unlike UNION and INTERSECT operators.

The syntax is:

SELECT
MINUS
SELECT
[ORDER BY clause];

Example :List the jobs, which are common to department 20 only,
SQL> select job from emp where deptno =20
MINUS
select job from emp where deptno =30
MINUS
select job from emp where deptno =10;


Arithmetic operator

ADD
SQL>Select add(2,2) from dual;

SUBTRACT
SQL> Select sub(5,3) from dual;

MULTIPLY
SQL>Select mul(3,2) from dual;

DIVIDE
SQL>Select div(4,2) from dual;

MODULUS
SQL>Select mod(10,3) as first from dual;