Sub Queries and Correlated SubQueries in SQL and Oracle

Sub Query: It is an select statement in another select statement.

Syntax:

select <column_list> from <table_name> where column operator in (select <column_list> from <table_name>)

Based on the result of sub query or inner query outer query is executed. i.e., outer query depends on inner query.

Q. Write a query to display all employees in king department .

A.select * 
       from emp 
       where deptno=(select deptno 
                     from emp 
                     where ename='KING')

Q. Write a query to display all employees if their salaries are more than min sal of 30th dept

A.select * 
       from emp 
       where sal > (select min(sal) 
                    from emp 
                    where deptno=30)

Classification of sub queries:

1. Singlerow SubQueries:

    The sub query returns a single value is called single row sub query.

Q. Write a query to divide the table into groups based on department no 

and display the group if the min sal of group is more than min sal of 30th dept 

A.select deptno, min(sal)  
from emp 
group by deptno having min(sal) >
 (select min(sal) 
 from  emp 
 where deptno=30)

Q. Write a query to divide the table into groups based on department no and display the group if the avg sal is more than average sal of 10th department

A.select deptno, avg(sal)  
       from emp 
       group by deptno 
       having avg(sal) > 
  (select avg(sal) 
  from  emp 
  where deptno=10)

Q. Write a query to divide the table in to groups based on job and display the group if the maximum salary of group is more than maximum salary of manager group?

A.select job, max(sal)  
       from emp 
       group by job having max(sal) > 
  (select max(sal) 
  from  emp 
  where job='Manager')

If the sub query is returning only one value then it is fine. If the sub query is returning more than one value you have to go through special characters.

Special operators with sub queries:

The special operators are used with sub query when sub queries return more than one value.

a. IN (List of values): It fix values from the list.

Q. Write a query to display all employee details who are working as manager for other employees.

A. select * 
 from emp 
 where empno in ( select 
    distinct mgr 
    from emp)

b.ANY:

     1. > any (list of values):It fix the mininum value from the list and display all the values from the table which are more than minimum value in the list.

Q. Write a query to display all employees if the salary is more than min sal of 10th department.

A. select * 
 from emp 
 where sal > any ( select sal 
      from emp  
      where deptno=10);
   or
 select * 
 from emp 
 where sal > ( select min(sal) 
        from emp 
        where detptno=10)

    2. < Any (list of values); It fix the maximum value from the list and display all the values from table which are less than maximum value in the list.

Q. Write a query to display all employee details having salaries less than maximum salary of 20th department.

A. select * 
 from emp 
 where sal < any ( select sal 
     from emp 
     where deptno=20)
   or
         select * 
  from emp 
  where sal < ( select max(sal) 
         from emp 
                       where deptno=20

c. ALL:

      1. > ALL (list of values): It fix the maximum values from the list and it will display all the values from the gable which are more than maximum value in the list.

Q. Write a query to display all employees having salaries more than maximum sal of 20th dept.

A. select * 
 from emp 
 where sal > all (select sal 
    from emp 
    where deptno=20)

       2. < ALL (list of values): It fix the mininum value from the list and display all the values from then table which are less than minimum value int he list.

Q. Write a query to display all employees if the salaries are less than minimum sal of 10th dept.

A. sleect * 
 from emp 
 where sal < all (select sal 
    from emp 
    where deptno=10)

d. EXISTS: It is a boolean operator which returns either success or failure of a query.

Q.Write a query to display all manager details if no of employees of manager in a table are more than 2.

A. select * 
 from emp 
 where job='manager' 
  and exists (select count(*) 
      from emp 
       where job='manager' 
      group by job 
                    having count(*)>2)

Correlated Sub Queries:

• If a sub query is evaluated repeatedly once for each row selected by the outer query such sub query is called correlated sub query.
• Here first outer query will be executed based on the result of outer query inner query will be executed. i.e., inner query depends on outer query.
• If outer query is retrieved 'n' records inner query has to execute 'n' times.

Q. Write a query to display all employees if their salaries are more than avg salaries of their own department.

A. select e.empno, e.ename,e.sal,e.deptno 
        from emp e 
        where e.sal>(select avg(sal))
                     from emp 
                     where deptno=e.deptno)

Q. Write a query to display all employees who are seniors than their own managers.

A. select e.empno, e.ename,e.mgr,e.hiredate
    from emp e
    where e.hiredate>(select hiredate
                                 from emp
                                 where empno=e.mgr);

                                                                              Back to SQL Tutorial Index