By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,336 Members | 1,081 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

Using Subqueries - 2

debasisdas
Expert 5K+
P: 8,127
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
Share this Article
Share on Google+
1 Comment

P: 1
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