Answers on correlated subquery

 Link to Questions


Answers

Basics Questions' answer

  1.  Correlated subquery is the special case of subquery where inner and outer query result depends on each other's output. 
  2. In normal subquery the subquery is independent of outer query and executes once but in correlated subquery the subquery executes for every row of outer query.

Answer of Query on Employee Tables

Query 1

SELECT * FROM Employee AS E 
WHERE E.Salary > (SELECT AVG(Salary) FROM Employee WHERE E.Department = Department )

Query 2

SELECT * FROM Employee AS E 
WHERE E.Salary > (SELECT MIN(Salary) FROM Employee WHERE E.Department = Department )

Query 3

SELECT * FROM Employee AS E 
WHERE Salary in  (SELECT top 2 Salary  FROM Employee WHERE E.Department = Department ORDER BY Salary DESC)

Query 4

SELECT * FROM Employee AS E 
WHERE Salary in  (SELECT top 2 Salary  FROM Employee WHERE E.Department = Department ORDER BY Salary ASC)

Query 5

SELECT *, 
Salary - (SELECT AVG(Salary) FROM Employee WHERE Department = E.Department) As Difference_Of_Salary
FROM Employee E

No comments:

Post a Comment

Write a python program to find factorial of a number ?

Factorial  When we recursively multiply number incrementing from 1 to a given number then its called factorial of that number. We use the no...