Subquery

§  Query with in another Query is known as subquery.

§  If you want to fetch data from one table but based on condition of another table then we     should go for Subquery.

§  Types of Subquery: -

1.     Single row Subquery (getting single value) (using = operator)

2.     Multi Sub Query (if query generating more than output value is called [In operator       uses]

v A subquery may occur in:

  §  A SELECT clause

     §  A FROM clause

     §  A WHERE clause

§   The subquery can be nested inside a SELECT, INSERT, UPDATE, or DELETE statement or inside another subquery.

§   A subquery is usually added within the WHERE Clause of another SQL     SELECT statement.

 §   You can use the comparison operators, such as >, <, or =. The comparison operator           can also be a multiple-row operator, such as IN, ANY, or ALL.

Execution Process-

§  Inner most Query To outermost Query except in the case of co-related query.

§  The inner query executes first before its parent query so that the results of an        inner  query can be passed to the outer query.

Que-

 In which case joins required and In Which case Subquery is required?

Ans-

§  If you want to fetch data from multiple table get placed one column from table and another column from second table i.e. if you want to fetch two columns from two table definitely, we should use joins.

§  Subquery said if you are expecting the data from one table but based on condition of another table it is better to use subquery.

select DEPARTMENTNAME from tbldepartment where id=(select DEPARTMENTID from tblemployee where id=1 )

 

SELECT * FROM TBLEMPLOYEE

WHERE DEPARTMENTID IN (SELECT ID FROM TBLDEPARTMENT WHERE DEPARTMENTNAME='Payroll' OR DEPARTMENTNAME='IT')

Write a query to display senior most employee details from emp table.

select *
from emp

where hiredate=( select min(hiredate) from  emp)

Write an query to display junior most employee details from emp table.

select *
from emp

where hiredate=( select max(hiredate) from  emp)

Write a query to display highest sal details emp table
 
select *
from emp

where sal= (select max(sal) from emp)

Write a query to display Second highest sal details emp table.

select *
from emp

where sal= (select max(sal) from emp where sal< select max(sal) from emp))

Write a query to display highest paid deptno emp table.

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

Write an query to display highest paid deptname dept emp table.

select dname from dept

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

Write a query to display the employees who are working under 'BLAKE' from emp table

select * from emp

where mgr= (select empno from emp where ename='BLAKE')

• email
• facebook
• linkedin
• twitter
• reddit
• google+

Author Name

Author Description!

Get Free Email Updates to your Inbox!

• Rss
• Twitter
• FaceBook
• Google +