Subqueries

Subqueries

It executes the inner query first and then the outer query is executed -- is called subquery....

Display emp details and dept details having max sal for their deptno

select e.empno,e.ename,e.sal,d.dname,d.deptno

from emp e,dept d

where e.deptno = d.deptno

and e.sal

in(

select max(sal)

from emp

group by deptno) -- subquery

order by deptno

----------------------------------------------

1) Display EMP information whose sal is more then

1000 and their deptname is Research...

select  *

from emp

where sal > 1000

and  deptno in (select deptno from dept where dname = 'RESEARCH')

2)display emp information

whose sal > 2000 and their dept name is accounting

and thier designation is manager

select *

from emp

where sal >2000

and deptno in (select deptno from dept where dname = 'ACCOUNTING')

and upper(job) = 'MANAGER'

select e.*,d.*

from emp e ,dept d

where e.deptno = d.deptno

and   e.sal > 2000

and  d.dname = 'ACCOUNTING'

and upper(e.job) = 'MANAGER'

3)List the empoyee detials who are working in WARD employeee department

select *

from emp

where deptno in (select deptno from emp where ename = 'WARD')

4)List the emp detials whose job is same as JONES job

select *

from emp

where upper(job) = (select upper(job) from emp where ename = 'JONES')

5)List the employee details whose sal is  more then BLAKE  Salary

select *

from emp

where sal > (select sal from emp where ename = 'BLAKE')

6)lsit the emp details whose job is same as MARTIN or ADAMS

select *

from emp

where upper(job) = (select upper(job) from emp where ename in ('MARTIN ','ADAMS')

7) List the dept details where there are no

employees assigned to that deptno in emp table.

SELECT *

FROM DEPT

WHERE DEPTNO NOT IN (SELECT DISTINCT DEPTNO FROM EMP);

8)display deptno and min salaries for

those possessing the min salary of that deptno

 greater than the min salary of deptno 20

select min(sal),deptno

from emp

group by deptno

having min(sal) > (select min(sal) from emp where deptno = 20)

9)List the emp details in deptno 20 for those jobs same as dept 10

10)list the emp details for those who are having the

same salary as either KING or SCOTT

select *

from emp

where sal = (select sal from emp where ename = 'KING')

    or sal = (select sal from emp where ename = 'SCOTT')

               

select *

from emp

where sal in (select sal from emp where ename in ('KING','SCOTT'))

11)List the emp details for those having sal

greater then TURNER or Job equivalent to CLARK

12) List the highest paid employee of the sales department

13)List the Manager No's and no of employees working for them

14)list the emp details who are working in sales department

15)Print the details of employees whose manger is BLAKE

16)display emp name, sal, empno,dname who are

 working in NEWYORK Location

17)Modify the above Query by applying condition as

 whose job is not manager

18) Display emp details  who are not working

in sales department and whose job is clerk or manager

19) List emp detials and their joining day for deptno 10

20) List the emp name whose dept name start with Letter S

21) List emp details whose department name  contains letter S more than once

----------------------------------------------------------------------------------------------------------------------------------------------------

22) display first Friday date if the employee  hiredate of emp table is on or before 15

     and display last Friday if the employee hiredate of emp table is after 15.

select next_day(last_day(add_months(hiredate,-1))+1,'FRIDAY')

from emp

select next_day(trunc(hiredate,'Month'),'FRIDAY') from emp

select next_day(last_day(hiredate)-7,'FRIDAY') from emp

select

decode(

sign(to_char(hiredate,'DD')-15)

,-1,next_day(trunc(hiredate,'Month'),'FRIDAY')

,0,next_day(trunc(hiredate,'Month'),'FRIDAY')

,1, next_day(last_day(hiredate)-7,'FRIDAY')) daterr ,hiredate from emp

23) Print the depno 30 job wise salaries

24) job wise salaries to be displayed

25) Deptno,jobwise salaries to be diplayed..

26) list sum of salaries for each deptno having that total sal > 16000

       order by deptno desc

27) list min of sal for each deptno

28) lsit max sal for each dept no having its sal >8000

29) List all dept details and their employee details.

show the dept info even if no employees are assigned to that deptno and show their employee information as null.

30) List all student information and their total marks and grade.

show student marks,result,grade even if the student information does not exists

31) fetch the student information along with his result,marks and grade

32) fetch the student information along with his result,marks and grade

whose result is PASS

33) fetch the student information along with his result,marks and grade

 whose marks are above 398

34) fetch the student information along with his result,marks and grade

whose  grade is A or B or c.

35)fetch the student information along with his result,marks and grade

whose  grade is  D or whose result is PASS.

36) write a query to display emp jobs as if job is accounting

then display as sr accounting,if it is sales then display

as sr clerk else display as the job is..

correlated Subqueries

It executes the outer query first and then the output of the outer query is passed into inner query

It returns more then one row as part of their result

Outer Query alias must be used in the inner query

List the emp names and  sal increase by 10%

whose sal is greater than min sal of that deptno

select e.ename

          ,e.sal+e.sal*0.1

from  emp e

where sal >(select min(sal) from emp where deptno = e.deptno)

-----------------------------------------------------------------------

EXISTS,ANY,ALL  OPERATORS

The above operators are in the select statements. They are used in the sub queries.

EXISTS/NOT EXISTS.

The above operator is a Boolean operator. It returns TURE OR FALSE.

EXISTS

----------

It return the true or false status of the inner query and as per that

status the outer query will display the results.

Ex:1 -- List all dept details for those dept where employees are assigned.

----------------------------------------------------

select dname,deptno,loc

from dept

where deptno in (select deptno               rom emp)

In the above query we are just checking if any employees exists for the deptno in emp table

and if any exists then we are printing those records,.... but Instead of using the IN Operator

we can use exists operator. This will improve the Performanance.

---------------------------------------------------

select d.dname,d.deptno,d.loc

from dept d

where EXISTS (select 1 from emp

                         where deptno = d.deptno);

The above query is an example for correlated subquery.

It executes the outer query first and will pass each result

to inner query and inner query will return TRUE or FALSE.

If the deptno from the outer query exists in the inner query

then it return TRUE else it returns FLASE.

----------------------------------------------------------

EX2: Display  all dept details for which the employees

      ar not assigned

 select * from dept  where deptno not in (select deptno from emp).

Instead of using NOT IN We can use NOT EXISTS in order to Improve the performance.

select d.dname,d.deptno,d.loc

from dept d

where NOT EXISTS (select 1 from emp

                         where deptno = d.deptno);

-------------------------------------------------------------------

Ex3:  List the entire employees infomation

 where there are more then

1 managers in that dept.

select *

from emp e

where  exists ( select count(*),job from emp

                            where  deptno = e.deptno

                and    job = 'manager'

                                        group by job

                            having count(*) >1)

SELECT XSR.SNO         RESULT_SNO

      ,XSR.RESULT      STUDENT_RESULT

      ,XSR.TOTAL_MARKS TOTAL_MARKS

      ,XSG.GRADE       GRADE

      ,XSI.SNO         STUDENT_NUMBER

      ,XSI.SNAME       STUDENT_NAME

      ,XSI.SEX         GENDER

      ,XSI.AGE         STUDENT_AGE

      ,XSI.ADDRESS     ADDRESS

FROM   XX_STD_INFO_ANK     XSI

      ,XX_STD_RESULT_ANK   XSR

      ,XX_MARK_GRADE_ANK   XSG

WHERE  XSR.SNO=XSI.SNO(+)

AND    XSR.TOTAL_MARKS BETWEEN XSG.LMARK AND XSG.HMARK

and    exists (select 1

              from gender_ank g

              where g.code = XSI.SEX

              and   g.name = 'FEMALE');

SELECT XSR.SNO         RESULT_SNO

      ,XSR.RESULT      STUDENT_RESULT

      ,XSR.TOTAL_MARKS TOTAL_MARKS

      ,XSG.GRADE       GRADE

      ,XSI.SNO         STUDENT_NUMBER

      ,XSI.SNAME       STUDENT_NAME

      ,XSI.SEX         GENDER

      ,XSI.AGE         STUDENT_AGE

      ,XSI.ADDRESS     ADDRESS

      ,(select name

        from gender_ank g

        where g.code = XSI.SEX) gender_name

FROM   XX_STD_INFO_ANK     XSI

      ,XX_STD_RESULT_ANK   XSR

      ,XX_MARK_GRADE_ANK   XSG

WHERE  XSR.SNO=XSI.SNO(+)

AND    XSR.TOTAL_MARKS BETWEEN XSG.LMARK AND XSG.HMARK;

ANY /SOME

---------------

It picks the smallest value from the result of inner query....

Ex:

list the entre emp information whose salary is more then

lowest salary of 10 depatment.

select *

from emp

where sal > (select min(sal) from emp where deptno = 10)

instead of using min aggreagte function we can handle such type scenarios using

ANY keyword.

select *

from emp

where sal > ANY(select sal from emp where deptno = 10)

select *

from emp

where sal > SOME(select sal from emp where deptno = 10)                                                                                        

-------------------------------------------------------------------------------

ALL

-----

It picks the highest value from the result of Inner Query

Ex:

List the emp details whose sal is greater than the max

salary of the deptno 10

select *

from emp

where sal > (select max(sal) from emp where deptno = 10)

Instead of using the max group functions

 i can use ALL function to fetch the max  sal of deptno 10

select *

from emp

where sal > ALL(select sal from emp where deptno = 10)

---------------------------------------------------------------