Nested Queries in SQL Server – SQL Server Tutorial

Nested Queries in SQL Server :-

A sub query embedded in another subquery is called “nested query”.

Select  → main query

            (select   → sub query

                   (select))  → Nested query                      

Get the name of employee whos earning 2^nd maximum salary.

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

Display names of the department employees earning 2^nd maximum salary.

Select dname from dept where deptno in (select deptno from emp) where sal=(select max(sal) from emp where sal<(select max(sal) from emp)))

Sub-queries with update command  :–

Update the employee salary to max salary whose eno=1001.

Update emp set  al =(select max(sal), from emp)  where empno=1001.

Update the employee salary to max,salary of the sales department whose eno=1001.

Update emp set Sal=(select max(sal) from emp where depno=(select deptno from dept where dnsme=’sales’)) where empno=1001.

Sub-queries with DELETE Command   :–

• Delete all employee records whose job= job of smith.
• Delete from emp where job=(select job from emp where ename=’smith’).