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