Sql Server material 20

SUBQUERY: A subquery is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery. A subquery can be used anywhere an expression is allowed. In this case first the inner query executes and basing upon the result generated by it the outer query executes to generate the final output.

           

WAQ to find the details of employees earning the highest salary.

Sol: SELECT * FROM EMP WHERE SAL=

(SELECT MAX(SAL) FROM EMP)

WAQ to find the details of employees earning the second highest salary.

Sol: SELECT * FROM EMP WHERE SAL=

(SELECT MAX(SAL) FROM EMP WHERE SAL<

(SELECT MAX(SAL) FROM EMP))

WAQ to find the details of employees working in sales department.

Sol: SELECT * FROM EMP WHERE DEPTNO=

(SELECT DEPTNO FROM DEPT WHERE DNAME=’SALES’)

  

WAQ to find the details of employees working in Mumbai.

Sol: SELECT * FROM EMP WHERE DEPTNO=

(SELECT DEPTNO FROM DEPT WHERE LOC=’MUMBAI’)

WAQ to find the details of employees who are earning more than the highest salary of deptno 30

Sol: SELECT * FROM EMP WHERE SAL>

            (SELECT MAX(SAL) FROM EMP WHERE DEPTNO=30)

OR

SELECT * FROM EMP WHERE SAL>

            ALL (SELECT SAL FROM EMP WHERE DEPTNO=30)

-In this case we can use the ALL operator which will compare an expression with set of values, where the expression has to satisfy the condition with all the values.

WAQ to find the details of employees who are earning less than the lowest salary of deptno 20

Sol: SELECT * FROM EMP WHERE SAL<

            (SELECT MIN(SAL) FROM EMP WHERE DEPTNO=20)

OR

SELECT * FROM EMP WHERE SAL<

            ALL(SELECT SAL FROM EMP WHERE DEPTNO=20)

WAQ to find the details of employees who are earning less than the highest salary of deptno 10

Sol: SELECT * FROM EMP WHERE SAL<

            (SELECT MAX(SAL) FROM EMP WHERE DEPTNO=10)

OR

SELECT * FROM EMP WHERE SAL<

            ANY(SELECT SAL FROM EMP WHERE DEPTNO=10)

-In the place of ANY we can use SOME operator also.

- In this case we can use the ANY/SOME operatorS which will compare an expression with set of values, where the expression has to satisfy the condition with at least a single value.

WAQ to find the details of employees who are earning the highest salary in each department.      

            SELECT * FROM EMP WHERE SAL IN

(SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO)

WAQ to find the details of seniors in each department.       

            SELECT * FROM EMP WHERE HIREDATE IN

(SELECT MIN(HIREDATE) FROM EMP GROUP BY DEPTNO)

<<< Previous                                                                                                 Will Come...