SQL Interview Questions Part -4

Assume there is view with name as EMP_V so what are all the objects can be created with same Name.


Below are the objects which we can't create with existed object Name.

1.   create or replace synonym emp_v for emp  --Not allowed
2.  create table emp_v (id integer); --Not Allowed
3.  create materialized view emp_v  as select * from emp; --Not Allowed
4.   create sequence emp_v --Not Allowed
5. CREATE OR REPLACE PACKAGE EMP_V IS PROCEDURE P1; END;   --Not Allowed
6. CREATE OR REPLACE PROCEDURE EMP_V IS BEGIN   NULL; END;  -- Not Allowed

Below are the objects which we can create with existed object Name.

1. create or replace public synonym emp_v for emp  --allowed
2. create index emp_v on emp(mgr); --Allowed

ORA-02438: Column check constraint cannot reference other columns

ORA-02438: Column check constraint cannot reference other columns
Cause: attempted to define a column check constraint that references another column.
Action: define it as a table check constraint.

Lets check scenario:

Case 1: Missing Constraint Name

CREATE TABLE EMP_tab (
            EMPNO NUMBER (4,0) PRIMARY KEY,
            ENAME VARCHAR2 (20) NOT NULL,
            MGR NUMBER (4,0) REFERENCES EMP (EMPNO)
                  ON DELETE SET NULL,
            DOB DATE,
            HIREDATE DATE,
            EMAIL VARCHAR2 (50) UNIQUE,
            CONSTRAINT MY_Ch1 CHECK (HIREDATE >= DOB+365*16),
            CONSTRAINT  CHECK  ((INSTR  (EMAIL, '@') > 0 AND (INSTR(EMAIL,'.') > 0))
      ))

ORA-02438: Column check constraint cannot reference other columns


Here add explicit constraint name for last check constraint and issue will be fixed.

SQL> CREATE TABLE EMP_tab (
              EMPNO NUMBER (4,0) PRIMARY KEY,
              ENAME VARCHAR2 (20) NOT NULL,
              MGR NUMBER (4,0) REFERENCES EMP (EMPNO)
                    ON DELETE SET NULL,
              DOB DATE,
              HIREDATE DATE,
              EMAIL VARCHAR2 (50) UNIQUE,
              CONSTRAINT MY_Ch1 CHECK (HIREDATE >= DOB+365*16),
              CONSTRAINT  My_Ch2 CHECK  ((INSTR  (EMAIL, '@') > 0 AND (INSTR(EMAIL,'.') > 0))
        ));

Table created


Case 2: Specify Comma before adding constraints.

SQL> create table check_tab(txn_id integer,txn_amount NUMBER(20,2),Txn_date date check(txn_amount > 100));
create table check_tab(txn_id integer,txn_amount NUMBER(20,2),Txn_date date check(txn_amount > 100))

ORA-02438: Column check constraint cannot reference other columns

rewrite with comma as below

SQL> create table check_tab(txn_id integer,txn_amount NUMBER(20,2),Txn_date date, check(txn_amount > 100));

Table created

Multi Table Insert Statement

Multi Table Insert:

Its very important concept to know when we have scenario to insert data to multiple tables based on single query output.

say eg: Usually we use external table to read data from local files and then to load to target tables.

so here instead of writing complex program to load data from external tables to Target tables ,we can simply use Multi table insert concept.

Lets go through examples.

Assume our scenario is load data from EMP table (In SCOTT Schema) data to another 2 different target tables.

(Note: Here you can take your external table also instead of EMP table).

Creating 2 target tables as below:


create table dest_table1 as select * from emp where 1=2;



create table dest_table2 as select * from emp where 1=2;

Case 1: Unconditional Insert All

When we are using Unconditional Insert All, the results which are returned by query(Driving Query) will insert as new record in target tables which are specified in INTO clauses.




select * from emp where rownum<=4

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7839 KING        PRESIDENT           11/17/1981   5000.00               10
 7698 BLAKE      MANAGER    7839  5/1/1981      2850.00               30
 7782 CLARK      MANAGER    7839  6/9/1981      2450.00               10

 7566 JONES      MANAGER    7839  4/2/1981      2975.00               20


SQL> INSERT ALL
         INTO dest_table1 VALUES(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
             INTO dest_table2 VALUES(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
    SELECT * FROM emp
    where rownum<=4;


8 rows inserted

In  Above example ,the Driving query returns 4 records so each target table(which are in INTO clause) will get 4 new records) so total 8 records for 2 tables. 

So basically here we are not applying any condition using "INSERT ALL", it means whatever records are coming from driving query will get inserted to target tables.

So how can we achieve if we have scenario to insert records based on condition ,Now we have to go for case 2.

Case 2: Conditional Insert ALL

In this case, we can add conditions to the INTO clause.Its looks like a CASE statement but here each condition is always tested for current row from the Driving query.

SQL> INSERT ALL
     WHEN DEPTNO=10 THEN //This case will be evaluated 4 times as we 4 records in driving query even though for deptno 10 records are 2.
           INTO dest_table1 VALUES(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
      WHEN DEPTNO IN (20,30) THEN
 //This case will be evaluated 4 times as we 4 records in driving query even though for deptno 20,30 records are 2.
              INTO dest_table2 VALUES(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
    SELECT * FROM emp
    where rownum<=4;


4 rows inserted

Hope you got clarity on how Conditional Insert All works.

In below case ,we added 1=1 at the end ,It means its always true so all records will be inserted into target table here.

SQL> INSERT ALL
   WHEN DEPTNO=10 THEN //here 2 records will be inserted
        INTO dest_table1  
VALUES(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
      WHEN DEPTNO IN (20,30) THEN  //here 2 records will be inserted
              INTO dest_table2
VALUES(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
   WHEN 1=1 THEN //here 4 records will be inserted
              INTO dest_table2 VALUES(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
    SELECT * FROM emp
    where rownum<=4;


8 rows inserted

Case 3: Insert First

In this case conditions are tested until First Match is found and no further conditions are tested.

Lets take EMP table itself to understand clearly.

SQL> select * from emp
  where rownum<=4;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7839 KING        PRESIDENT          11/17/1981    5000.00               10
 7698 BLAKE      MANAGER    7839 5/1/1981      2850.00                 30
 7782 CLARK      MANAGER    7839 6/9/1981      2450.00                 10

 7566 JONES      MANAGER    7839 4/2/1981      2975.00                 20

SQL> INSERT ALL
     WHEN SAL>4000 THEN
         INTO dest_table1 VALUES(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
      WHEN SAL>2500 THEN
              INTO dest_table2 VALUES(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
   SELECT * FROM emp
    where rownum<=4;


4 rows inserted

I have taken INSERT ALL case here first and then will show INSERT FIRST.

As Per INSERT ALL,each condition is always tested for current row. so "dest_table1 " table will have only one record i.e with Salary of 5000/- but another table "dest_table2" will have 3 records i.e. with salary of 5000,2850,2975. Here if you observe 5000 salary record is getting inserted in 2nd table also even though it got inserted in first table so to avoid this we have INSERT FIRST case.

SQL> INSERT FIRST
    WHEN SAL>4000 THEN //here only one record
         INTO dest_table1 VALUES(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
      WHEN SAL>2500 THEN //here only 2 records
            INTO dest_table2 VALUES(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
    SELECT * FROM emp
    where rownum<=4;
  

3 rows inserted


The restrictions on multi table inserts are as follows.

• Multi table inserts can only be performed on tables, not on views or materialized views.
• You cannot perform a multi table insert via a DB link.
• You cannot perform multi table inserts into nested tables.
• The sum of all the INTO columns cannot exceed 999.
• Sequences cannot be used in the multi table insert statement. It is considered a single statement, so only one sequence value will be generated and used for all rows.
• Multi table inserts can't be used with plan stability.
• If the PARALLEL hint is used for any target tables, the whole statement will be parallelized. If not, the statement will only be parallelized if the tables have PARALLEL defined.
• Multi table statements will not be parallelized if any of the tables are index-organized, or have bitmap indexes defined on them.

Analytical Functions in Oracle

We use analytical functions instead writing sub queries and joins where requirement comes along with aggregation.

Analytical functions extends the power of SQL, which means we can write queries in less lines of code with analytical function when compared with Native SQL.

Lets start with Aggregate functions (MIN, MAX, AVG,COUNT,SUM) to explore analytical  functions.

Note: We are going to use EMP table which is present in SCOTT schema for examples.


SQL> select sum(sal),deptno from emp
  2  group by deptno;
  SUM(SAL) DEPTNO
---------- ------
      9400     30
      8750     10
     10875     20


Now we will write above query using Analytical function.


SQL> select deptno, sum(sal)over(partition by deptno ) as grp from emp;
DEPTNO        GRP
------ ----------
    10       8750
    10       8750
    10       8750
    20      10875
    20      10875
    20      10875
    20      10875
    20      10875
    30       9400
    30       9400
    30       9400
    30       9400
    30       9400
    30       9400
14 rows selected


as we observed both queries output, Analytic functions also operate on subsets of rows, similar to aggregate functions in GROUP BY queries, but they didn't reduce the number of rows returned by the query.


Syntax:

 Lets understand syntax here

analytic_function([ arguments ]) OVER (analytic_clause)

 Here  "analytic_function" can be any aggregate function or any other analytic function(eg: lead,lag,dense_rank etc)

 "Arguments" --> Based on requirement we pass arguments to analytic function. eg: in above query we passed "sal".


The analytic_clause breaks down into the following optional elements.

[ query_partition_clause ] [ order_by_clause [ windowing_clause ] ]


query_partition_clause

The query_partition_clause divides the result set into partitions, or groups, of data. The operation of the analytic function is restricted to the boundary imposed by these partitions, similar to the way a GROUP BY clause affects the action of an aggregate function. If the query_partition_clause is omitted, the whole result set is treated as a single partition. The following query uses an empty OVER clause, so the sum presented is based on all the rows of the result set.

 SQL> select deptno, sum(sal)over() as grp from emp;


DEPTNO        GRP
------ ----------
    10      29025
    30      29025
    10      29025
    20      29025
    20      29025
    20      29025
    20      29025
    30      29025
    30      29025
    30      29025
    30      29025
    20      29025
    30      29025
    10      29025
14 rows selected

order_by_clause:
Its optional, we can use whenever want to sort records.

Windowing_clause

 We have seen previously the query_partition_clause controls the window, or group of rows, the analytic operates on. The windowing_clause gives some analytic functions a further degree of control over this window within the current partition, or whole result set if no partitioning clause is used. The windowing_clause is an extension of the order_by_clause and as such, it can only be used if an order_by_clause is present. The windowing_clause has two basic forms.


RANGE BETWEEN start_point AND end_point
ROWS BETWEEN start_point AND end_point
When using ROWS BETWEEN, you are indicating a specific number of rows relative to the current row, either directly, or via an expression. Assuming you don't cross a partition boundary, that number of rows is fixed. In contrast, when you use RANGE BETWEEN you are referring to a range of values in a specific column relative to the value in the current row. As a result, Oracle doesn't know how many rows are included in the range until the ordered set is created.

Possible values for "start_point" and "end_point" are:

UNBOUNDED PRECEDING : The window starts at the first row of the partition, or the whole result set if no partitioning clause is used. Only available for start points.
UNBOUNDED FOLLOWING : The window ends at the last row of the partition, or the whole result set if no partitioning clause is used. Only available for end points.
CURRENT ROW : The window starts or ends at the current row. Can be used as start or end point.
value_expr PRECEDING : A physical or logical offset before the current row using a constant or expression that evaluates to a positive numerical value. When used with RANGE, it can also be an interval literal if the order_by_clause uses a DATE column.
value_expr FOLLOWING : As above, but an offset after the current row.

How to connect to sqlplus in linux

To connect in linux server where your DB is installed follow as below

1) First you should know your oracle home as we required to SET this in linux server.

We can find easily Oracle_home using below query.

 select SYS_CONTEXT ('USERENV','ORACLE_HOME') from dual;

2) Connect to Linux server (Where DB is installed) using Putty or any other Tool which you prefer


sh-4.2$ export ORACLE_HOME=/scratch/oraofss/app_18c/product/18.3.0.0/dbhome_1
sh-4.2$ export PATH=/scratch/oraofss/app_18c/product/18.3.0.0/dbhome_1/bin

sh-4.2$ sqlplus user_name/password@Oracle_Database

Refer screenshot, Its connected to SQL.

How to KILL EXPDP JOB

When we are doing export, After some time , we dont want to continue Export Dump now so we terminated .

Usually we need to KILL job manually as it runs in background if we just assume that we terminated with CTL+C in command prompt.

Just run below query to cross verify whether your job is running currently or not.

SELECT owner_name, job_name, operation, job_mode, state FROM dba_datapump_jobs;

In My case there 1 job is in  "running" status, so now i need to kill those jobs as i don't required.

So first Note down job name from above query and then do as follows:

Step 1:

type expdp attch=<job_name>  // here job name which we identified from above query
and then it will prompt for schema credentials where this job is running as below.

Once you had provided credentials , it will come to Export prompt as below 

Step 2:

type "KILL_JOB" and provide Yes for "Are you sure you wish to stop this job". It will take some time to KILL JOB.

Parallelly, we can verify in below query to check whether its Killed or not.

SELECT owner_name, job_name, operation, job_mode, state FROM dba_datapump_jobs

How to get distinct values without using Distinct Keyword - Interview Questions Part 3

How to get distinct values without using Distinct Keyword:-

Generally we use DISTINCT keyword to get distinct values/to filter duplicate values.

Alternative for DISTINCT:

Lets take following table with data for this scenario:

SQL>  select * from dept1;
DEPTNO DNAME          LOC
------ -------------- -------------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
    30 SALES          CHICAGO
    40 OPERATIONS     BOSTON
    40 medicine       LONDON

We generally use as below.

SQL>  select distinct deptno from dept1;
DEPTNO
------
    30
    20
    40
    10

1st Alternative: Using Group By 

SQL>  select deptno from dept1 group by deptno;
DEPTNO
------
    30
    20
    40
    10

2ndAlternative: Using Row_number analytical function

 SQL> select * from (select deptno, row_number()over(partition by deptno order by 1) rn from dept1) where rn=1 ;

DEPTNO         RN
------ ----------
    10          1
    20          1
    30          1
    40          1

 3rd Method: Using SET operator (i.e UNION)

SQL>  select deptno from dept1
       union
      select deptno from dept1
 
DEPTNO
------
    10
    20
    30
    40


Interview Questions Part1: Check out here ---> Interview Questions Part1

Interview Questions Part2: Check out here ---> Interview Questions Part-2

Interview Questions Part 2

1) How to count the no of records of a table without using COUNT function?

Ans: using rownum we can get this.

SQL> select max(rownum) from emp;
MAX(ROWNUM)
-----------
         14

2)  How to print ,output as below?
*
**
***
****
*****
******
 
Ans:
SQL>  select lpad('*',level,'*') from dual
   connect by level <=6

LPAD('*',LEVEL,'*')
-----------------------
*
**
***
****
*****
******
6 rows selected

3) select 'Venky'  from dual where null=null .. What is the output ?

Ans: we need to remember that in oracle, we can't compare null with another null using equal operator and we always need to use "IS NULL" to compare so for above query, we get output as NULL.

SQL> select 'Venky'  from dual where null=null;
'VENKY'
--------------------------------
 
we ill get output as 'Venky' when we use is null as below.


SQL> select 'Venky'  from dual where null is null;
'VENKY'
--------------------------------
Venky


SQL> select * from dual where null is null; --> Defaut value will be 'X' for dual table
DUMMY
-----
X


4) I have one table EMP with columns :Empno,Depno,ename,sal.
Now i want to have max(sal) in each department?

Sol1: using Group by
SQL>  select max(sal),deptno from emp    group by deptno
 
  MAX(SAL) DEPTNO
---------- ------
      2850     30
      3000     20
      5000     10

Sol2: Using Analytical function

SQL>  select distinct deptno, max(sal)over(partition by deptno order by 1) from emp;
DEPTNO MAX(SAL)OVER(PARTITIONBYDEPTNO
------ ------------------------------
    10                           5000
    20                           3000
    30                           2850

5)  Addition to above question, i have table "DEPT" with columns deptno and deptno so now i need 
max(sal) in each department along with deptname ?

SQL>  select max(sal),e1.deptno,d1.dname from emp e1 join dept d1 on d1.deptno = e1.deptno
   group by e1.deptno,d1.dname;

  MAX(SAL) DEPTNO DNAME
---------- ------ --------------
      5000     10 ACCOUNTING
      3000     20 RESEARCH
      2850     30 SALES

6) Addition to above question, i need ename whose salary is maximum in each deptno along with deptname?



SQL>  with my_dummy as (
    select max(sal) sal1,e1.deptno,d1.dname from emp e1 join dept d1 on d1.deptno = e1.deptno
     group by e1.deptno,d1.dname)
   select e2.empno,e2.ename,m1.sal1,m1.dname,m1.deptno from my_dummy m1 join emp e2 on e2.sal = m1.sal1;

EMPNO ENAME            SAL1 DNAME          DEPTNO
----- ---------- ---------- -------------- ------
 7839 KING             5000 ACCOUNTING         10
 7698 BLAKE            2850 SALES              30
 7788 SCOTT            3000 RESEARCH           20
 7902 FORD             3000 RESEARCH           20



Interview Questions Part1: Check out here ---> Interview Questions Part1

Interview Questions Part3: Check out here ---> Interview Questions Part-3

Different ways to Delete Duplicate Records - Interview Questions Part 1

1) How to find duplicated records and write all possible queries to find duplicate records?

Method 1: Using Rowid

Ans: When someone ask you to get duplicate then you need to give preference write query using ROWID as its faster to retrieve records from data file.

SQL> select * from dept_1;
DEPTNO DNAME          LOC
------ -------------- -------------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
    30 SALES          CHICAGO
    40 OPERATIONS     BOSTON
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
6 rows selected


we have taken above table for this scenario and  here we could see dept no 10 and 20 are repeated so lets find out using query.



First lets understand about rowid here,

ROWID: rowid is pseudocolumn and it can be used as Data type same as integer, varchar2.

Oracle server will assign unique address for each record in each table to identify row in table.

we can see in above screenshot that 10,20 having different rowid it means we can understand that there are more than 1 record for deptno 10 and 20.

Below query is showing our required output and if you NOT IN in below query then you will get only duplicate records.

SQL> select * from dept_1 where rowid in (select min(rowid) from dept_1 group by deptno);
 
DEPTNO DNAME          LOC
------ -------------- -------------
    30 SALES                  CHICAGO
    20 RESEARCH         DALLAS
    40 OPERATIONS     BOSTON
    10 ACCOUNTING   NEW YORK


Method 2: Using Group by and having clause.

SQL> select deptno from dept_1    group by deptno having count(deptno) >1;
DEPTNO
------
    20
    10


Method 3: Using Count Analytical function and Inline view.

Here we are using count function and then finally with inline view we are getting output.

SQL> select * from (select  deptno, count(*)over(partition by deptno order by deptno) rn from dept_1)   where rn>1
 
DEPTNO         RN
------ ----------
    10          2
    10          2
    20          2
    20          2

Method 4: Using Row_number Analytical function and Inline view.

SQL> select * from
   (select deptno, row_number()over(partition by deptno order by deptno) r_number, rowid from dept_1)    where r_number <>1

  DEPTNO   R_NUMBER ROWID
------ ---------- ------------------------------------
    10          2 ABE8AUAF7AAAx4fAAA
    20          2 ABE8AUAF7AAAx4fAAB


Method 5: Using Dense_rank Analytical function and Inline view.

Note: in below query we are using "Order by rowid" to get sequential values.

 SQL> select * from
    (select deptno, dense_rank()over(partition by deptno order by rowid) r_number, rowid from dept_1)     where r_number <>1
 
DEPTNO   R_NUMBER ROWID
------ ---------- --------------------
    10          2 ABE8AUAF7AAAx4fAAA
    20          2 ABE8AUAF7AAAx4fAAB



Interview Questions Part2: Check out here ---> Interview Questions Part-2

Interview Questions Part3: Check out here ---> Interview Questions Part-3

Interview Questions on Self Join

We used to get below questions frequently on SELF JOIN concept.

1) Find out emp id who are managers.
2) Find out managers with count of sub ordinates.
3) Find out managers who are having more than one sub ordinate



we are going to take EMP table to show examples.

SQL> select * from emp;




EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7839 KING       PRESIDENT          11/17/1981    5000.00                  10
 7698 BLAKE      MANAGER    7839 5/1/1981      2850.00                   30
 7782 CLARK      MANAGER    7839 6/9/1981      2450.00                   10
 7566 JONES      MANAGER    7839 4/2/1981      2975.00                   20
 7788 SCOTT      ANALYST     7566 4/19/1987     3000.00                  20
 7902 FORD       ANALYST      7566 12/3/1981     3000.00                  20
 7369 SMITH      CLERK         7902 12/17/1980     800.00                   20
 7499 ALLEN      SALESMAN   7698 2/20/1981     1600.00    300.00     30
 7521 WARD       SALESMAN  7698 2/22/1981     1250.00    500.00     30
 7654 MARTIN     SALESMAN  7698 9/28/1981     1250.00   1400.00    30
 7844 TURNER     SALESMAN  7698 9/8/1981      1500.00      0.00       30
 7876 ADAMS      CLERK        7788 5/23/1987     1100.00                   20
 7900 JAMES      CLERK         7698 12/3/1981      950.00                    30
 7934 MILLER     CLERK         7782 1/23/1982     1300.00                   10



Case 1: Find out emp id who are managers.



SQL> select e1.empno, e1.mgr from emp e1 join emp e2 on e2.empno= e1.mgr;

EMPNO   MGR
----- -----
 7698  7839
 7782  7839
 7566  7839
 7788  7566
 7902  7566
 7369  7902
 7499  7698
 7521  7698
 7654  7698
 7844  7698
 7876  7788
 7900  7698
 7934  7782

If we observe empno in  above ouput , we didnt get "7839" as its President and He does not need to report to anyone.

If we want president ID also then we can use left join to get it as below.

SQL> select e1.empno, e1.mgr from emp e1 left join emp e2 on e2.empno= e1.mgr;

EMPNO   MGR
----- -----
 7839
 7698  7839
 7782  7839
 7566  7839
 7788  7566
 7902  7566
 7369  7902
 7499  7698
 7521  7698
 7654  7698
 7844  7698
 7876  7788
 7900  7698
 7934  7782
 
 Case 2: Find out managers with count of sub ordinates.

As we required count the sub ordinates so we will use aggregate  function ie. COUNT and we also know that whenever we use aggregate function its mandatory to use Group by clause for remaining  columns of select query.
 
SQL> select count(e1.empno), e1.mgr from emp e1 join emp e2 on e2.empno= e1.mgr group by e1.mgr;
COUNT(E1.EMPNO)   MGR
--------------- -----
              2  7566
              5  7698
              1  7782
              1  7788
              3  7839
              1  7902 

Case 3: Find out managers who are having more than one sub ordinate


 Since we required to find more than one sub ordinate so we need to use having clause as below.
SQL> select count(e1.empno), e1.mgr from emp e1 join emp e2 on e2.empno= e1.mgr group by e1.mgr having count(e1.empno) >1;
 
COUNT(E1.EMPNO)   MGR
--------------- -----
              2  7566
              5  7698
              3  7839


 

Hierarchical queries

Hierarchical query is a type of SQL query which is commonly used to get meaningful results from  hierarchical data.

Hierarchical data means table columns which are related to each other by hierarchical relationships.

Hierarchical Relationship exist when one column data is parent to another column data.


In Our real life, Below would be hierarchical relations.

1) Employee and Manager Hierarchy
2)  Organization Hierarchy

Below clauses we need to know to write Hierarchical queries.



START WITH: It specifies the root rows of the hierarchy.

Connect by: It specifies the relationship between parent rows and child rows of the hierarchy.

Prior: We use this keyword to specify parent row.

In Hierarchical queries,One expression in condition must be specified with PRIOR to refer Parent row.
eg:

… PRIOR expr = expr
or
… expr = PRIOR expr

Level: It refers to a pseudo column in Oracle which is used in a hierarchical query to identify the hierarchy level in numeric format.


A database evaluates hierarchical queries in the following order:

•     A join, if present, is evaluated first, whether the join is specified in the FROM clause or with WHERE clause predicates

•     The CONNECT BY condition is evaluated

•     Any remaining WHERE clause predicates are evaluated

The database engine then uses the information from these evaluations to form the hierarchy using the following steps (referred from Oracle 10g documentation):

1) It selects the root row(s) of the hierarchy, meaning those rows that satisfy the START WITH condition.

2) It selects the child rows of each root row. Each child row must satisfy the condition of the CONNECT BY condition with respect to one of the root rows.

3) It selects successive generations of child rows. The database engine first selects the children of the rows returned in step 2, and then the children of those children, and so on. The database always selects children by evaluating the CONNECT BY condition with respect to a current parent row.

4) If the query contains a WHERE clause without a join, then the database eliminates all rows from the hierarchy that do not satisfy the condition of the WHERE clause. The database evaluates this condition for each row individually, rather than removing all the children of a row that does not satisfy the condition.

5) The database returns the rows in the order shown in Figure 1 below. In the diagram, children appear below their parents.


lets take EMP table to write examples.

case1: If we want to know relation between empid and manager.

SQL> select e1.empno,e1.mgr manager from emp e1
  2  connect by mgr = prior empno
  3  start with mgr is null
  4  /
EMPNO MANAGER
----- -------
 7839
 7566    7839
 7788    7566
 7876    7788
 7902    7566
 7369    7902
 7698    7839
 7499    7698
 7521    7698
 7654    7698
 7844    7698
 7900    7698
 7782    7839
 7934    7782

Here we used start with mgr is null as we would like to see CEO empid as well but he does not report to anyone.

and same kind of output we can get with below query as well but here we will not have output in hierarchical relation. so  we can compare both above and below output to understand difference.

SQL> select e1.empno, e1.mgr from emp e1 left  join emp e2 on e2.empno= e1.mgr;
 
EMPNO   MGR
----- -----
 7839
 7698  7839
 7782  7839
 7566  7839
 7788  7566
 7902  7566
 7369  7902
 7499  7698
 7521  7698
 7654  7698
 7844  7698
 7876  7788
 7900  7698
 7934  7782

Case 2: We need to get level for each hierarchy.

SQL> select e1.empno,e1.mgr manager ,level, decode(level,1,'CEO',2,'Director',3,'Manager',4, 'Employee') from emp e1
  2  connect by mgr = prior empno
  3  start with mgr is null;


EMPNO MANAGER      LEVEL DECODE(LEVEL,1,'CEO',2,'DIRECT
----- ------- ---------- --------------------------------
 7839                  1 CEO
 7566    7839          2 Director
 7788    7566          3 Manager
 7876    7788          4 Employee
 7902    7566          3 Manager
 7369    7902          4 Employee
 7698    7839          2 Director
 7499    7698          3 Manager
 7521    7698          3 Manager
 7654    7698          3 Manager
 7844    7698          3 Manager
 7900    7698          3 Manager
 7782    7839          2 Director
 7934    7782          3 Manager

case 3: generate Sequential values.


SQL> select level from dual
  2  connect by level <=5
  3  ;
     LEVEL
----------
         1
         2
         3
         4
         5

You can change value 5 and generate up to whichever value you want.