## Expressions and operators

Introduction:

Ø An operator and any of its operands taken together constitute an expression

Ø Expression is the combination of formulas and constants or variables.

Operators and its types:

1.    Arithmetic operators

2.    Character operator

3.    Comparison or relational operators

4.    Logical operators

5.    SET operators

Arithmetic operators:

It performs Arithmetic operations.

 Operators Description Example + Addition Sal + 200 - Subtraction Sal - 200 * Multiplication Sal * 200 / Division Sal / 10

Arithmetic operator in SELECT statement,

1. Select sal + 200 from emp;
2.  Select sal - 100 from emp;
3. Select comm * 0.5 from emp;
4. Select sal / 2 from emp;

Character operator:

Concatenation operator ( || ) is known as character operator.

Example:

Select ename || ‘ ‘ || ‘is working as a developer’ from emp;

Output will be,

Thara is working as a developer.

Here,

|| ‘ ‘ || is used for space

Relational operator:

 Operators Description Example = Equal to Where deptno = 10 != OR <> Not equal to Where deptno <>20 < Less than Where sal < 10000 > Greater than Where sal < 50000 <= Less or equal to Where sal <= 20000 >= Greater than or equal to Where sal >= 60000

Examples:

1.         Select  * from emp where deptno = 10;
2.         Select  * from emp where deptno  <> 50;
3.         Select ename from emp where comm < 2000:
4.                    Select ename from emp where comm > 2000;
5.         Select ename from emp where sal <= 20000;
6.         Select ename from emp where sal >= 50000;

Logical operators:

1.    AND

2.    OR

3.    NOT

AND operator:

Ø When we want to retrieve the data which should be satisfied one or more conditions, for that case AND operator is used.

Example:

Select  * from emp where deptno = 10 AND sal =10000;

OR operator:

Ø Any one of the condition should be satisfied the corresponding rows will be displayed.

Example:

Select  * from emp where comm = 2000 OR sal =20000;

NOT operator:

Ø NOT operator returns TRUE if the enclosed condition evaluates FALSE and returns FALSE if the enclosed condition evaluates TRUE.

Example:

Select * from emp where NOT (mgr is not null);

SET operators:

Introduction:

Ø Set operators are used to combine the results of two queries into a single result.

Ø Queries contain set operators are called compound queries.

Ø The individual queries are called component queries.

SET operators are,

1.    Union

2.    Union all

3.    Intersect

4.    Minus

Union

Ø Union operator is used to combine two select statements.

Ø   Ø  It returns all distinct rows selected by the component queries and it does  not  return duplicate values.

Ø  SELECT statement within union must have the same number of columns

Example:

1. Select empno,ename,sal,comm from emp1

union

select empno,ename,sal,comm from emp2;

Union all

Ø  It returns all rows selected by the component queries including duplicate rows.

Example:

1. Select empno, ename, sal, comm from emp1

union

select empno, ename, sal, comm from emp2;

Intersect

Ø  Intersect operator is used to combine two SELECT statements, but returns rows only from the first SELECT statement.

Ø Ø  This means it returns only common rows in the both select statement.

Example:

1. Select empno, ename, sal, comm from emp1

Intersect

select empno, ename, sal, comm from emp2;

Minus

Ø The minus operator will retrieve all records from the first data set and then remove from the results all records from second data set

Example:

1. Select empno, ename, sal, comm from emp1

minus

select empno, ename, sal, comm from emp2;

## Service Support

Need our help to Learn or Post New Concepts Contact me

## Service Support

Need our help to Learn or Post New Concepts Contact me