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.

Click to learn more

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