Sub Queries in SQL SERVER

Sub Queries

Query within another Query is called sub query,Outer Query is called main Query and inner Query is called Sub Query.

In sub queries first the inner query will execute and gives the result to the outer query for execution.


for an instance, If  we want to get the employee details who is having highest salary it is not possible with normal query

select enum,ename,MAX(sal) from employee10
group by enum,ename

we can achieve that by using sub queries

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

Write a query to get the second highest salary

select Max(sal) from Employee10
where sal<
(Select MAX(sal) from Employee10)

write a query to get all the employees whose sal is grater than avg sal of all the employees

select * from Employee10
where sal>
(select AVG(sal) from Employee10)

Write a query to the all the employees who are working from Hyderabad

select * from Employee10 where dno=(
select deptno from department10 where loc='hyderabad')

Write a query to het all the employees who are working under IT

select * from Employee10 where dno=(
select deptno from department10 where dname='IT')

Write a query to het all the employees who are working under IT,HR

select * from Employee10 where dno in (
select deptno from department10
where dname in ('IT','HR'))

Write a query to get the 4th Highest salary from employee table

select top 1 sal from
(select top 4 sal from Employee10
order by sal desc) S
order by sal IN valid when salaries having duplicate values so we need to use distinct keyword as shown below

select Top 1 sal from
(select distinct top 4 sal from Employee10
order by sal desc) s
order by sal

write a query to get all the employees whose sal is lessthan the maximum sal of employee working under dep 20

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

Let me know if you have any doubts on this article

Thanks

Srinivas