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)

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

                                                                                                                                                                                                                                     

Post a Comment