WALKUP TREES AND INLINE VIEW

WALKUP TREES

Using hierarchical queries, you can retrieve data based on a natural hierarchical relationship between rows in a table. However, where a hierarchical relationship exists between the rows of a table, a process called tree walking enables the hierarchy to be constructed.

Ex:

    SQL> select ename || '==>' ||  prior ename, level from emp start with ename = 'KING'

            connect by prior empno=mgr;

ENAME||'==>'||PRIORENAM      LEVEL

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

KING==>                                1

JONES==>KING                     2

SCOTT==>JONES                   3

ADAMS==>SCOTT                  4

FORD==>JONES                     3

SMITH==>FORD                     4

BLAKE==>KING                      2

ALLEN==>BLAKE                    3

WARD==>BLAKE                    3

MARTIN==>BLAKE                 3

TURNER==>BLAKE                 3

JAMES==>BLAKE                    3

CLARK==>KING                      2

MILLER==>CLARK                  3

In the above

Start with clause specifies the root row of the table.

Level pseudo column gives the 1 for root , 2 for child and so on.

Connect by prior clause specifies the columns which has parent-child relationship.

INLINE VIEW OR TOP-N ANALYSIS

In the select statement instead of table name, replacing the select statement is known as inline view.

Ex:

     SQL> Select ename, sal, rownum rank from (select *from emp order by sal);

ENAME             SAL       RANK

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

SMITH             800          1

JAMES             950          2

ADAMS            1100        3

WARD             1250         4

MARTIN           1250        5

MILLER           1300         6

TURNER           1500        7

ALLEN            1600          8

CLARK            2450          9

BLAKE            2850         10

JONES            2975         11

SCOTT            3000         12

FORD             3000          13

KING             5000          14

SUBQUERIES AND EXISTS

SUBQUERIES

Nesting of queries, one within the other is termed as a subquery.

A statement containing a subquery is called a parent query.

Subqueries are used to retrieve data from tables that depend on the values in the table itself.

TYPES

Ø  Single row subqueries

Ø  Multi row subqueries

Ø  Multiple subqueries

Ø  Correlated subqueries

SINGLE ROW SUBQUERIES

In single row subquery, it will return one value.

Ex:

    SQL> select * from emp where sal > (select sal from emp where empno = 7566);

     EMPNO   ENAME      JOB        MGR    HIREDATE    SAL   COMM     DEPTNO

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

      7788         SCOTT   ANALYST    7566   19-APR-87   3000                    20

      7839         KING     PRESIDENT            17-NOV-81  5000                    10

      7902         FORD    ANALYST     7566   03-DEC-81   3000                    20

MULTI ROW SUBQUERIES

In multi row subquery, it will return more than one value. In such cases we should include operators like any, all, in or not in between the comparision operator and the subquery.

Ex:

     SQL> select * from emp where sal > any (select sal from emp where sal between 2500 and

             4000);

     EMPNO    ENAME      JOB      MGR     HIREDATE   SAL   COMM     DEPTNO

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

      7566         JONES   MANAGER   7839 02-APR-81   2975                   20

      7788         SCOTT   ANALYST    7566 19-APR-87    3000                   20

      7839         KING     PRESIDENT          17-NOV-81   5000                   10

      7902         FORD    ANALYST     7566 03-DEC-81    3000                   20

       SQL> select * from emp where sal > all (select sal from emp where sal between 2500 and  

              4000);

  

     EMPNO      ENAME    JOB       MGR     HIREDATE     SAL    COMM  DEPTNO

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

      7839         KING     PRESIDENT            17-NOV-81  5000                    10

MULTIPLE SUBQUERIES

There is no limit on the number of subqueries included in a where clause. It allows nesting of a query within a subquery.

Ex:

     SQL> select * from emp where sal = (select max(sal) from emp where sal < (select

              max(sal) from emp));

     EMPNO      ENAME   JOB      MGR       HIREDATE   SAL   COMM     DEPTNO

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

      7788         SCOTT   ANALYST  7566    19-APR-87   3000                    20

     7902          FORD    ANALYST   7566    03-DEC-81   3000                    20

CORRELATED SUBQUERIES

A subquery is evaluated once for the entire parent statement where as a correlated subquery is evaluated once for every row processed by the parent statement.

Ex:

     SQL> select distinct deptno from emp e where 5 <= (select count(ename) from emp where

             e.deptno = deptno);

    DEPTNO

    ----------

        20

        30

EXISTS

Exists function is a test for existence. This is a logical test for the return of rows from a query.

Ex:

     Suppose we want to display the department numbers which has more than 4 employees.

     SQL> select deptno,count(*) from emp group by deptno having count(*) > 4;

   DEPTNO   COUNT(*)

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

       20             5

       30             6

     From the above query can you want to display the names of employees?

      SQL> select deptno,ename, count(*) from emp group by deptno,ename having count(*) > 4;

     no rows selected

     The above query returns nothing because combination of deptno and ename never return     

     more than one count.

     The solution is to use exists which follows.

      

      SQL> select deptno,ename from emp e1 where exists (select * from emp e2

             where e1.deptno=e2.deptno group by e2.deptno having count(e2.ename) > 4) order by

             deptno,ename;

 

    DEPTNO   ENAME

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

        20            ADAMS

        20            FORD

        20            JONES

        20            SCOTT

        20            SMITH

        30            ALLEN

        30            BLAKE

        30            JAMES

        30            MARTIN

        30            TURNER

        30            WARD

NOT EXISTS

SQL> select deptno,ename from emp e1 where not exists (select * from emp e2

        where e1.deptno=e2.deptno group by e2.deptno having count(e2.ename) > 4) order by

        deptno,ename;

   DEPTNO ENAME

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

       10             CLARK

       10             KING

       10             MILLER

JOINS

The purpose of a join is to combine the data across tables.

A join is actually performed by the where clause which combines the specified rows of tables.

If a join involves in more than two tables then oracle joins first two tables based on the joins condition and then compares the result with the next table and so on.

TYPES

       Equi join

       Non-equi join

       Self join

       Natural join

       Cross join

       Outer join

Ø  Left outer

Ø  Right outer

Ø  Full outer

       Inner join

       Using clause

       On clause

Assume that we have the following tables.

SQL> select * from dept;

    DEPTNO DNAME      LOC

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

        10            mkt        hyd

        20            fin        bang

        30            hr         bombay

SQL> select * from emp;

       EMPNO   ENAME      JOB       MGR     DEPTNO

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

       111         saketh     analyst           444         10

       222         sudha     clerk                333         20

       333         jagan      manager         111         10

       444         madhu    engineer         222         40

EQUI JOIN

A join which contains an ‘=’ operator in the joins condition.

Ex:

     SQL> select empno,ename,job,dname,loc from emp e,dept d where e.deptno=d.deptno;

          EMPNO     ENAME      JOB    DNAME      LOC

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

            111           saketh    analyst    mkt        hyd

            333           jagan      manager  mkt        hyd

            222           sudha      clerk        fin        bang

USING CLAUSE

SQL> select empno,ename,job ,dname,loc from emp e join dept d using(deptno);

          EMPNO     ENAME      JOB    DNAME      LOC

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

            111           saketh    analyst    mkt        hyd

            333           jagan      manager  mkt        hyd

            222           sudha      clerk        fin        bang

ON CLAUSE

SQL>  select empno,ename,job,dname,loc from emp e join dept d on(e.deptno=d.deptno);

          EMPNO     ENAME      JOB    DNAME      LOC

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

            111           saketh    analyst    mkt        hyd

            333           jagan      manager  mkt        hyd

            222           sudha      clerk        fin        bang

NON-EQUI JOIN

A join which contains an operator other than ‘=’ in the joins condition.

Ex:

     SQL> select empno,ename,job,dname,loc from emp e,dept d where e.deptno > d.deptno;

          EMPNO     ENAME    JOB      DNAME      LOC

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

       222    sudha      clerk          mkt        hyd

       444    madhu     engineer   mkt        hyd

       444    madhu     engineer   fin          bang

       444    madhu     engineer   hr           bombay

SELF JOIN

Joining the table itself is called self join.

Ex:

     SQL> select e1.empno,e2.ename,e1.job,e2.deptno from emp e1,emp e2 where

             e1.empno=e2.mgr;

     EMPNO     ENAME    JOB      DEPTNO

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

       111          jagan      analyst         10

       222          madhu      clerk           40

       333          sudha      manager      20

       444          saketh     engineer      10

NATURAL JOIN

Natural join compares all the common columns.

Ex:

     SQL> select empno,ename,job,dname,loc from emp natural join dept;

     EMPNO   ENAME      JOB      DNAME    LOC

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

       111          saketh     analyst     mkt        hyd

       333          jagan      manager   mkt        hyd

       222          sudha      clerk         fin          bang

CROSS JOIN

This will gives the cross product.

Ex:

     SQL> select empno,ename,job,dname,loc from emp cross join dept;

 EMPNO  ENAME    JOB        DNAME      LOC

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

       111     saketh   analyst      mkt        hyd

       222     sudha    clerk          mkt        hyd

       333     jagan     manager   mkt        hyd

       444     madhu   engineer   mkt        hyd

       111     saketh   analyst      fin          bang

       222     sudha    clerk          fin          bang

       333     jagan     manager   fin          bang

       444     madhu   engineer   fin          bang

       111     saketh   analyst      hr           bombay

       222     sudha    clerk          hr           bombay

       333     jagan     manager   hr           bombay

       444     madhu   engineer   hr           bombay

OUTER JOIN

Outer join gives the non-matching records along with matching records.

LEFT OUTER JOIN

This will display the all matching records and the records which are in left hand side table those that are not in right hand side table.

Ex:

     SQL> select empno,ename,job,dname,loc from emp e left outer join dept d

             on(e.deptno=d.deptno);

Or

      SQL> select empno,ename,job,dname,loc from emp e,dept d where e.deptno=d.deptno(+);

    

     EMPNO     ENAME   JOB       DNAME      LOC

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

       111          saketh    analyst       mkt        hyd

       333          jagan      manager    mkt        hyd

       222          sudha     clerk           fin          bang

       444          madhu    engineer

RIGHT OUTER JOIN

This will display the all matching records and the records which are in right hand side table those that are not in left hand side table.

Ex:

     SQL> select empno,ename,job,dname,loc from emp e right outer join dept d

              on(e.deptno=d.deptno);

Or

      SQL> select empno,ename,job,dname,loc from emp e,dept d where e.deptno(+) = d.deptno;

     EMPNO    ENAME     JOB      DNAME      LOC

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

       111          saketh     analyst      mkt        hyd

       333          jagan       manager   mkt        hyd

       222          sudha      clerk          fin          bang

                                                       hr           bombay

FULL OUTER JOIN

This will display the all matching records and the non-matching records from both tables.

Ex:

     SQL> select empno,ename,job,dname,loc from emp e full outer join dept d

              on(e.deptno=d.deptno);

 EMPNO   ENAME    JOB        DNAME      LOC

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

       333     jagan     manager    mkt        hyd

       111     saketh   analyst       mkt        hyd

       222     sudha    clerk           fin        bang

       444     madhu   engineer

                                                   hr         bombay

INNER JOIN

This will display all the records that have matched.

Ex:

     SQL> select empno,ename,job,dname,loc from emp inner join dept using(deptno);

     EMPNO     ENAME   JOB        DNAME    LOC

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

       111          saketh     analyst      mkt       hyd

       333          jagan       manager   mkt       hyd

       222          sudha      clerk          fin         bang