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;

 

 

 

    

 

 

  

 

Share: