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

Delete from emp e  where rowid>(select min(rowid) from emp where e.empno=empno)
 

OR

Delete from emp 
where rowid not in(select max(rowid) from emp group  by empno)
 

Ex#2
------------
Display first position of employee

select * from emp e where 0=(select count(distinct(sal)) from emp
where sal>e.sal) order by sal desc
 

Ex#3
------------
Display details of employee whose sal greater than avg sal of it own department

Select * from emp e
Where sal>(select avg(sal) from emp where deptno=e.deptno)
 

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.

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);
 

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.

SELECT * FROM emp  WHERE sal < ANY ( SELECT sal FROM  emp WHERE job= 'CLERK')  AND job <> 'CLERK';
 

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

SELECT * FROM emp WHERE sal > ALL(SELECT avg(sal)  FROM emp GROUP BY deptno);
 

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.

SELECT  * FROM  ( SELECT rownum rn, empno, ename FROM emp)  temp
 WHERE  MOD(temp.rn,2) = 1
 

Ex#2
----------
Display manager and its salary

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
 

Ex#3
----------
Top three employee who earn hightest salary.

select * from (select  * from emp order by sal where rownum<4
 

Ex#4
----------
Query for listing Deptno, ename, sal, SUM(sal in that dept) :

 
SELECT deptno, ename, sal, (SELECT SUM(sal) FROM emp WHERE a.deptno =deptno) FROM emp a  ORDER BY deptno
 

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

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)
 

[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.