472,794 Members | 1,884 Online

# Using Subqueries - 2

8,127 Expert 4TB
Using Co-related sub query
========================
While a subquery is evaluated only once for each table, a correlated subquery is evaluated once for each row.

Sub query can take value from outer query.

Ex#1
---------
Delete duplicate record from table
Expand|Select|Wrap|Line Numbers
1. Delete from emp e  where rowid>(select min(rowid) from emp where e.empno=empno)
2.
OR
Expand|Select|Wrap|Line Numbers
1. Delete from emp
2. where rowid not in(select max(rowid) from emp group  by empno)
3.
Ex#2
------------
Display first position of employee
Expand|Select|Wrap|Line Numbers
1. select * from emp e where 0=(select count(distinct(sal)) from emp
2. where sal>e.sal) order by sal desc
3.
Ex#3
------------
Display details of employee whose sal greater than avg sal of it own department
Expand|Select|Wrap|Line Numbers
1. Select * from emp e
2. Where sal>(select avg(sal) from emp where deptno=e.deptno)
3.
Multiple - Row Subqueries
=======================
Inner query returns more than one row
Use multiple - row comparison operators
IN ----Equal to any member in the list
ANY--Compare value to each value returned by the sub query.
ALL---Compare value to every value returned by the sub query.
<ANY means less than the maximum
>ANY means more than the minimum
=ANY is equivalent to IN
>ALL means more than the maximum
<ALL means less than minimum.

The NOT operator can be used with IN, ANY, and ALL operators

Using IN
==========
Example #1
-------------------
Find the employees who earn the same salary as the minimum salary for departments.
Expand|Select|Wrap|Line Numbers
1. SELECT * FROM emp
2. WHERE sal IN ( SELECT MIN(sal) FROM emp GROUP BY deptno);
3.
4. is  same as
5.
6. SELECT * FROM emp WHERE sal IN (800, 950, 1300);
7.
Using ANY
=============
The ANY operator (and its synonym SOME operator) compares a value to each value returned by a sub query.

Example #2
--------------------------
Display employees whose salary is less than any clerk and who are not clerks.
Expand|Select|Wrap|Line Numbers
1. SELECT * FROM emp  WHERE sal < ANY ( SELECT sal FROM  emp WHERE job= 'CLERK')  AND job <> 'CLERK';
2.
Using ALL
============
The ALL operator compares a value to every value returned by a subquery.

Example #3
--------------------------
Display details of employee whose sal greater than avg sal of all department
Expand|Select|Wrap|Line Numbers
1. SELECT * FROM emp WHERE sal > ALL(SELECT avg(sal)  FROM emp GROUP BY deptno);
2.
The example above displays employees whose salary is greater than the average salaries of all the department. The highest average salary of a department is 2916.66 , so the query returns those employees whose salary is greater than 2916.66

Note :--Please Try on Scott Schema

Using In line sub query(inline views)
==============================
Ex#1
----------
Display Odd number records.
Expand|Select|Wrap|Line Numbers
1. SELECT  * FROM  ( SELECT rownum rn, empno, ename FROM emp)  temp
2.  WHERE  MOD(temp.rn,2) = 1
3.
Ex#2
----------
Display manager and its salary
Expand|Select|Wrap|Line Numbers
1. SELECT A.MGR, B.SAL FROM (SELECT DISTINCT  MGR FROM EMP
2.  WHERE MGR IS NOT NULL) A   LEFT OUTER JOIN
3.  (SELECT EMPNO, SAL FROM EMP) B  ON  A.MGR=B.EMPNO
4.
Ex#3
----------
Top three employee who earn hightest salary.
Expand|Select|Wrap|Line Numbers
1. select * from (select  * from emp order by sal where rownum<4
2.
Ex#4
----------
Query for listing Deptno, ename, sal, SUM(sal in that dept) :
Expand|Select|Wrap|Line Numbers
1.
2. SELECT deptno, ename, sal, (SELECT SUM(sal) FROM emp WHERE a.deptno =deptno) FROM emp a  ORDER BY deptno
3.
Ex#5
----------
Display manager no and name who has more employee

Expand|Select|Wrap|Line Numbers
1. select empno,ename from emp where empno=(select mgr from
2. (select mgr,count(empno) from emp  where mgr is not null group by mgr
3. order by count(empno) desc) where rownum<2)
4.
Sep 17 '07 #1
1 11306
Hi Debasis,

Can you please tell me how to find the employees with greater than and less than the average salaries for their concerned departments? Thanks for your time.
Feb 23 '14 #2