22. ROW NUMBER

ROW NUMBER:

·        Select * from emp where rownum=1; à1 record.                N and N àN

·        Select * from emp where rownum > 1; àno rows.                 N and F  àF

·        Select * from emp where rownum<5 span=""> à5 rows.                   N and T àN  

   

·        Select * from emp where rownum!=5; à4 rows.                N  or  N àN   

     

·        Select * from emp where rownum=5; àno rows.                  N or  F  àN

·        Select rownum,emp.* from emp; à14 rows.                          N  or T  àT  

·        Select * from emp where rownum <=5;à5 rows.      NULL is NULL àT

·        Select * from emp where rownum > 5;àno rows.      NULL = NULLàF

·        Select * from emp where rownum in (1,2,3);à3 rows.

·        Select * from emp where rownum in (3,1,2);à3 rows.

·        Select * from emp where rownum in (5,4,1);à3 rows.

·        Select * from emp where rownum not in (5,4,1);àno rows.

·        Select * from emp where rownum +3<-1 span="">;àno rows.

·        Select * from emp where decode(rownum,rownum,1) >= 0;à14 rows.

·   Select * from (select rownum r,emp.* from emp) where r=(select count(*) from emp);à(n-1) rows.

·        Select * from (select rownum r,emp.* from emp) where r between 1 and 6;

  

·      Select * from (select rownum r,emp.* from emp) where r in (1,(select count(*) from emp));à1,14 rows.

·        Select * from (select rownum r,emp.* from emp) where r=2;à1 row.

·        Select * from (select rownum r,emp.* from emp) where r not in(1,(select count(*) from emp));à12 rows.

·        Select * from (select rownum r,emp.* from emp) where rownum=2;

àno rows.

·        Select * from (select rownum r,emp.* from emp) where r > 5;à 5 rows.

·        Select * from (select rownum r,emp.* from emp) where r=&n;

·        Select * from (select rownum r,emp.* from emp) where r!=&n;

·    Select * from (select rownum r,emp.* from emp) where r=(select count(*) from emp);à1 row.

·        Select * from (select rownum r,emp.* from emp) where r=(select count(*)-1 from emp);à1 row.

·        Select * from (select rownum r,emp.* from emp) where r not between 6 and 10;à9 rows.

·        Select * from (select rownum r,emp.* from emp) where r in(1,14);à2 rows.

           INSERT TOPIC: Ampersand (&) is used to prompt the user to enter

the value. It takes values from user.

Example:

·        Select &col from emp;

·        Select * from &n;

·        Enter value for n:emp

·        We get 14 rows.

·       How come the SQL * PLUS allows to provide our own values as a (by using ampersand).

·        By using double (&&) ampersand we can provide a value only once for N of occurrences throw out the session.

·        All of the SQL elements can replace with &.

  

Examples:

·        Select * from emp where &col;àenter value for col:sal > 3000.

·        Select &&col from emp order by &col;àenter value for col:deptno.

Define:  It is used to list out the variables and also to define the variables

this defined variables last for the session.

          Example:

·        Select * from (&n);

Enter value for n: select * from emp

·        Select &n;

Enter value for n:* from emp

·        Select * from emp where deptno=&x;

Enter value for x:10

    MULTI ROW OPERATORS: -    IN, SOME/ANY, ALL,EXISTS.

·        IN: Search the list of values.

·        ANY: In any the given value become true any one of the value.

·        ALL: The given value has to be the true with all of the listed values.

·    ANY & ALL: It will always come with relational operators. It won’t exist individually.

Example:

·     Select * from  emp where deptno=(10,20,30);àerror.

·     Select * from  emp where deptno in(10,20,30);

·     Select * from  emp where deptno > any(10,20);à11 rows.

·     Select * from  emp where deptno !=any(10,20);à14 rows.

·     Select * from  emp where deptno =any(10,20);

·     Select * from  emp where deptno

·     Select * from  emp where deptno !=all(10,20);

·     Select * from  emp where deptno > all(10,20);

·     Select * from  emp where deptno =all(10,20);

·     Select * from  emp where deptno =all(10);

·     Select * from  emp where deptno =all(10,null);

·     Select * from  emp where deptno >=all(10,20);

·     Select * from  emp where deptno >=all(20,20);à11 rows.

·     Select * from  emp where deptno >=all((20,30);

  

EXISTS: If records are found returns to else false.

·     We use exist for record existence.

Examples:

·     Select * from emp where deptno=10 and exists (select * from emp where deptno=20 and job=’MANAGER’);à3 rows.

Select * from emp where deptno=10 and not exists (select * from emp where deptno=20 and job=’MANAGER’);

  HIERARCHICAL SUB QUERIES:

·     We use hierarchical sub queries to display data in hierarchical.

1. Start with

2. Connect by

3. Prior

4. Level

5. Sys_connect_by_path ------- 10g

6. Connect_by_iscycle --------- 10g 

7. Connect_by_isleaf ----------- 10g

8. Connect_by_root ------------  9i

9. Sibillings ---------------------- 9i

----------------------------------------------------------------------------

1) Start with:

·     Start with specifies root record, in the absence of start with each and every record will be treated as a root record.

·     To the root record level provides 1 and for the subsequent child records provides 2,3,4….and so on.

------------------------------------------------------------------------

 2) Connect by:

·     This clause specifies relation between parent and child records.

-------------------------------------------------------------------------

 3) Prior:

·     Represents the prior record with respect to current record.

-------------------------------------------------------------------------

     4) Level:

·     Level is pseudo column which provides number values to that root subsequent child records. It supports maximum of 255 values.

-------------------------------------------------------------------------

 5) Sys_connect_by_path:

·     It is a hierarchical function. It results the path from root to current node.

------------------------------------------------------------------------

 6) Connect_by_isleaf:

·     To the leaf  record provides 1,the other provides 0.

Examples:

·     Select level,empno,ename,prior empno,prior ename from emp

start with mgr is null connect by prior empno=mgr;

·     Select level,max(sal) from emp where level <=3  connect by prior sal>sal;àerror.

·  Select level,max(sal) from emp where level <=3  connect by prior sal > sal group by level;

1          400

2          300

3          200

·     Select level,min(sal) from emp where level <=3  connect by prior sal > sal group by level;

1         100

2         100

3         100

·    Select level, max(sal) from emp where level <=3,connect by prior sal

---------------------------------------------------------------------------------

Q) Write a query to display all the managers to the smith?

·     Select level,empno,ename from emp  start with ename=’SMITH’

connect by prior mgr=empno;

·     Select level,empno,ename,prior ename from emp start with ename=’SMITH’

connect by prior mgr=empno;

·     Select level,empno,ename,prior ename from emp start with ename=’JONES’

connect by prior empno=mgr;

·    Select level,empno,ename,prior ename,sys_connect_by_path(ename,’>’) path from emp start with ename=’JONES’ connect by prior empno=mgr;

 Select level, empno, ename, prior ename, sys_connect_by_path(ename,’>’) path connect_by_isleaf from emp start with ename=’JONES’ connect by prior empno=mgr;

·     Select level, empno, ename, connect_by_isleaf from emp where connect by_isleaf=1 or 0 start with ename =’KING’ connect by prior empno=mgr;à8 rows.

  SCALAR SUBQUERIES:

·     Using sub queries instead of column names is said to be scalar queries.

·     Scalar queries have to return only one value for each of the outer query record.

·     Scalar query contains only one column.

     Example:

   Select ename, (select loc from dept where dept.deptno=emp.deptno), job from emp;

Note:

  § It is a special case of single row sub query.

  § It is not possible to provide the order by clause, which are used in  

where and having clauses of outer queries.

 Select   -   SQ

 From    -   SQ   - order by

            Where   -  SQ   - not possible to order by clause

            Having  -  SQ  - not possible to order by clause

  § We can use scalar sub queries as a correlated sub queries.

  § We can use ANY and ALL an alternatives to a group functions particularly minimum and maximum.

 Examples:

·  Select * from emp where sal > (select max(sal) from emp where deptno=10);

·  Select * from emp where sal > all(select sal from emp where deptno=10);

Note:

 = ALL

 < ALL(Smaller than the Lesser)

 > ALL(More than the Greater)

 > ANY(More than the Lesser)

 < ANY(Smaller than the Lesser)

           = ANY

Example:

·        Create table ‘space tab’ (sno number(5));

        #,/,-,$,char,number are allow in table name.

        Table name space allow specifying with in double coats.

·        Select * from  “space tab”;

·     Sub query value cannot be used as a default value. Only we have to use literals or functions.

    USING INLINE VIEW DELETE CLAUSE:   

     ·     Select * from sam1

     ·     Delete (select sno s from sam1) where s<30 span="">à3 rows.      

     

     ·     Update (select sname m from sam1) set m=’D’; à2 rows. 

   Example:      

                                                                                       

  Where           [[start with condition1]

  Group by       connect by condition2]

  Having

  Order by

     ·    Delete from emp where empid in(select empid from emp mstart with ename=’Blake’ connect by prior empid=mid;

-----------------------------------------------------------------------------------------------------

    Example:

·        Select level, lpad(‘ ‘, 2*(level-1))||ename xname from emp 

Start with mgr is null

Connect by prior empno=mgr

Order by siblings by ename;

 ------------------------------------------------------------------------------------------------

SIBILINGS: (9i):

       ·        It place the child nodes there parent nodes while preserving hierarchy.

       ·        In the absence of siblings hierarchy get disturbed.

       ·        Example is given above.

     Select sum (sal) from emp start with ename=’BLAKE’ connect by prior mpno=mgr;à9400.

  ------------------------------------------------------------------------------

     Select empno,ename,sal from emp

start with mgr is null

connect by prior empno=mgr;à6 rows.

 -------------------------------------------------------------------------------

     Select max(level) from emp

start with mgr is null connect by prior empno=mgr;à4 rows.

--------------------------------------------------------------------------------

      Select x.ename,x.sal,(select sum(sal) from emp y

start with y.ename=x.ename

connect by prior by y.empno=y.mgr), sum(sal) from emp x;

 -------------------------------------------------------------------------------

      Select level,ename,deptno from emp

start with mgr is null

connect by prior empno=mgr and

prior   deptno!=deptno;àKING,JONES,BLAKE.

 ------------------------------------------------------------------------------

      Select level,count(empno) from emp

start with mgr is null

connect by prior empno=mgr group by level;

 -----------------------------------------------------------------------------

 Select level,count(empno) from emp

start with mgr is null

connect by prior empno=mgr group by level

having level=(select max(level from emp

start with mgr is null

connect by prior empno=mgr);

------------------------------------------------------------------------------ 

·        Select level,ename,hiredate,prior ename,prior hiredate from emp

start with hiredate=(select min(hiredate) from emp

connect by prior empno=mgr);

 ----------------------------------------------------------------------------

·        Select level,ename,hiredate,prior ename,prior hiredate from emp

start with hiredate=(select max(hiredate) from emp

connect by prior empno=mgr);

 ---------------------------------------------------------------------------

·        Select * from emp where ename=’BLAKE’

start with ename=’JONES’ connect by prior mgr=empno;

 --------------------------------------------------------------------------

·        Select * from emp where ename=’JONES’ start with ename=’KING’

connect by prior empno=mgr group by level;