Nested Sub Queries in SQL SERVER

Nested Sub Queries

Querie within another sub Querie is called nested sub querie. It will contain minimum of three select statements.We can call them as main query,sub query,nested sub querie.

write a Query to get the employee details who is having second highest salary.

select * from Employee10 where sal=
(select Top 1 sal from
(select distinct top 2 sal from Employee10
order by sal desc) S order by sal)

write a Query to get the employee details who is having second highest salary without using top clause

select * from Employee10 where sal=
(select MAX(sal) from Employee10
where sal<(select MAX(sal) from Employee10))

write a query to get the employee details who is having third highest sal

select * from Employee10 where sal=(
select max(sal) from employee10 where sal<
(select Max(sal) from Employee10
where sal<
(Select MAX(sal) from Employee10)))

Write a Query to get the senior employee in the organisation

select * from Employee10 where hiredate=(
select MIN(hiredate) from Employee10)

Write a Query to get the senior employee in each dept in the organisation

select * from Employee10 where Hiredate in(
select MIN(hiredate) from Employee10
group by dno)

Let me know if you have any doubts on this article

Thanks
Srinivas