Using Subqueries - 2

[debasisdas]

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
[code=oracle]
Delete from emp e where rowid>(select min(rowid) from emp where e.empno=empno)
[/code]
OR
[code=oracle]
Delete from emp
where rowid not in(select max(rowid) from emp group by empno)
[/code]

Ex#2
------------
Display first position of employee
[code=oracle]
select * from emp e where 0=(select count(distinct( sal)) from emp
where sal>e.sal) order by sal desc
[/code]

Ex#3
------------
Display details of employee whose sal greater than avg sal of it own department
[code=oracle]
Select * from emp e
Where sal>(select avg(sal) from emp where deptno=e.deptno )
[/code]

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.
[code=oracle]
SELECT * FROM emp
WHERE sal IN ( SELECT MIN(sal) FROM emp GROUP BY deptno);

is same as

SELECT * FROM emp WHERE sal IN (800, 950, 1300);
[/code]

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.
[code=oracle]
SELECT * FROM emp WHERE sal < ANY ( SELECT sal FROM emp WHERE job= 'CLERK') AND job <> 'CLERK';
[/code]

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
[code=oracle]
SELECT * FROM emp WHERE sal > ALL(SELECT avg(sal) FROM emp GROUP BY deptno);
[/code]

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.
[code=oracle]
SELECT * FROM ( SELECT rownum rn, empno, ename FROM emp) temp
WHERE MOD(temp.rn,2) = 1
[/code]

Ex#2
----------
Display manager and its salary
[code=oracle]
SELECT A.MGR, B.SAL FROM (SELECT DISTINCT MGR FROM EMP
WHERE MGR IS NOT NULL) A LEFT OUTER JOIN
(SELECT EMPNO, SAL FROM EMP) B ON A.MGR=B.EMPNO
[/code]

Ex#3
----------
Top three employee who earn hightest salary.
[code=oracle]
select * from (select * from emp order by sal where rownum<4
[/code]

Ex#4
----------
Query for listing Deptno, ename, sal, SUM(sal in that dept) :
[code=oracle]
SELECT deptno, ename, sal, (SELECT SUM(sal) FROM emp WHERE a.deptno =deptno) FROM emp a ORDER BY deptno
[/code]

Ex#5
----------
Display manager no and name who has more employee

[code=oracle]
select empno,ename from emp where empno=(select mgr from
(select mgr,count(empno ) from emp where mgr is not null group by mgr
order by count(empno) desc) where rownum<2)
[/code]

[Soracle]

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.