10. OPERATORS

OPERATORS

•           Arthimatic
•          Concatenation
•          Relational or Comparison
•          Special
•          Logical
•          Row Operators
•          Hierarchical Operators
•          Set Operators
•          Table Operators

    

    ORDER OF PRECEDENCE:

UNIARY OPERATORES:  >1>2>3>4>5

1) ARITHEMATIC:    *,    /     ,   +      ,   -

2) CONCATINATION:    ||      (JOIN STRINGS)

3) RELATIONAL:     =,>, <,>=, <=,!=,<>,^=,~=

4) SPECIAL  IS,IN,LIKE(  _  ,%),BETWEEN(AND),ANY/SOME,ALL,EXISTS,

5) ROW: DISTINCT, ALL, PRIOR

6) LOGICAL: NOT, AND, OR;

7) HIERARCHICAL: CONNECT_BY_ROOT, PIROR

8) TABLE: THE;

 NOTE:  We can override the order of precedence or we can divert the order                   of precedence by interpreting parantasis (  )

  ARTHIMATIC OPERATORES:

EG:    SELECT 2*100+10, 2*(100+10) FROM DUAL;

 SELECT SAL, SAL+SAL/10 FROM EMP;

     SELECT * FROM EMP WHERE DEPTNO>-1;

   CONCATINATION OPERATOR: To merge up or join the strings we use

Concatenation operators.

EG:   SELECT ‘WELL’||’COME’ FROM DUAL;

SELECT EMPNO||’EMPLOYEE NAME’||ENAME FROM EMP;

SELECT ‘HELLO’|| NULL FROM DUAL;

SELECT ‘HELLO’||NULL||’WORLD’ FROM DUAL;

SELECT ‘HELLO’||’NULL’||’WORLD’ FROM DUAL;

SELECT ‘HELLO’||’ WELCOME’ FROM DUAL;

SELECT ‘123’+4||5 FROM DUAL;

SELECT ‘123’+5||-5 FROM DUAL;

  

  RELATIONAL OR COMPARISON :

EG:    SELECT * FROM EMP WHERE DPETNO=10 AND SAL>1000;

         SELECT * FROM EMP WHERE DEPTNO<>10;

         SELECT * FROM EMP WHERE DEPTNO!=10;

         SELECT * FROM EMP WHERE ENAME =’KING’;

         SELECT * FROM EMP WHERE ENAME >’KING’;

 SPECIAL OPERATORS:

IS: TO HANDLE NULL VALUES

EG: SELECT * FROM EMP WHERE COMM=NULL; NO ROWS;

      SELECT * FROM EMP WHERE COMM IS NULL;

      SELECT * FROM EMP WHERE COMM IS NOT NULL;

IN: TO PROVIDE THE LIST OF VALUES

   SYN: [NOT] IN (LIST VALUES)  ,  IN=ANY;  NOT IN !=ALL;

EG:   SELECT * FROM EMP WHERE DEPTNO IN(10,20);

        SELECT * FROM EMP WHERE DEPTNO NOT IN (10,20);

        SELECT * FROM EMP WHERE DEPETNO IN(10,NULL,20);

        SELECT * FROM EMP WHERE DEPTNO NOT IN(10,NULL,20);

LIKE: To search the patterns we use ‘like’ operator with the help of wild card

Characters

%    ANY OR NO NUMBER OF CHARACTERS

_  SINGLE CHARACTERS COMPARISION

EG:    SELECT * FROM EMP WHERE ENAME LIKE ‘S%’;

          SELECT * FROM EMP WHERE ENAME LIKE ‘_A%’;

          SELECT * FROM EMP WHERE ENAME LIKE ‘%E_’;

          SELECT * FROM EMP WHERE ENAME LIKE ‘%A%’;

          SELECT * FROM EMP WHERE ENAME LIKE ‘S%’ OR ENAME LIKE ‘A%’;

          SELECT * FROM EMP WHERE ENAME REGEXP_LIKE (ENAME,’^[SA]’);

          SELECT * FROM EMP WHERE ENAME NOT LIKE ‘S%’;

          SELECT * FROM EMP WHERE ENAME LIKE ‘KING’;

NOTE: like works as equal operator in the absence of wild card characters;

4) BETWEEN:

•          To provide range of values.
•          Always lower limit must be less than upper limit if not Condition becomes false.
•          Range includes the boundary values also.

EG:  SELECT * FROM EMP WHERE SAL BETWEEN 1000 AND 2000;

       SELECT * FROM EMP WHERE SAL NOT BETWEEN 1000 AND 3000;

       SELECT * FROM EMP WHERE SAL BETWEEN 5000 AND 1000; --- NO ROWS

       SELECT * FROM EMP WHERE SAL BOT BETWEEN 5000 AND 1000;

         (ALL THE ROWS ;)

5) ANY/SOME: 

       In any, the given value has to become true with any of the list of values

6)  ALL: 

       In all, the given value hat to become true with all of the list values.

7) EXISTS: 

       Returns true if record is found else false.

NOTE:  We have to use any & all along with relational operators.

            They can’t exist individually.

 SELECT * FROM EMP WHERE DEPTNO>ANY(10,20);

 SELECT * FROM EMP WHERE DEPTNO

 SELECT * FROM EMP WHERE DEPTNO!=ANY(10,20);

 SELECT * FROM EMP WHERE DEPTNO!=ANY(10,NULL,20);

 SELECT * FROM EMP WHERE DEPTNO!=ALL(10,NULL,20);

 SELECT * FROM EMP WHERE DEPTNO=ANY(10,NULL,20);

 SELECT * FROM EMP WHERE DEPTNO=ALL(10,NULL,20);

Arithmetic operators & order of precedence:

Arithmetic operators:

Level	Operator
High	( )
Mediumn	/
Medium	*
Low	+
Low	-

Ex:-    =>     3 * 100/5 + 20/10 – 5

          =>     300/5 + 20/10 – 5

          =>     60 + 20/10 – 5

          =>     60+2-5

          =>     62-5

          =>     57

Order of presidence:

Level	Operators
1	Unary (+, -, ~, connect_by_root)
2	Arithmetic
3	||
4	=, >, <, >=, <=
5	[not] like, is [not], [not] in
6	[not] between
7	!=, <>, ^=
8	Not
9	And
10	Or

COLUMN AND TABLE ALIASES

•          They are the alternate names to the table and columns
•         They are the temporary names only to that query
•         Which area used to identify the columns
•         We use table aliases to avoid uncertainty and also to increase performance

TABLE ALIASES

•          To qualify the columns
•          We use it to increase the performance

EG:  SELECT DEPTNO FROM EMP, DEPT; ------- ERROR (AMBIGUOUS)

       SELECT E.DEPTNO, D.LOC FROM EMP E,DEPT D  WHERE E.DEPTNO=D.DEPTNO;

EG:  SELECT SAL “SALARY OF” FROM EMP;

        SELECT 2*2 EXP FROM DUAL;