This Article is collection of mostly asked sql,.net interview questions

1. How to calculate nth max salary of employee. 

We have employee table as 

 

Now with table please focus on EmpID and Sal column rest of column carry with some other purpose. So I want to get employee who get maximum salary.

SELECT E1.Name, E1.Sal  FROM employee E1 WHERE 0  = 

(SELECT COUNT(*) FROM Employee E2 WHERE E2.Sal > E1.Sal)

This is correlated sub query In this for every rowset of outer resultset, inner query execute and used the value we have in outer rowset and execute inner query and validate condition.

So SELECT E1.Name, E1.Sal FROM employee E1  for this part we have Six rowset  now we need to full condition 

 0  = 

(SELECT COUNT(*) FROM Employee E2 WHERE E2.Sal > E1.Sal)

So Step 1. We have E1.Sal = 10000 now have a look of condition and sub query result 

0 = Select Count(*) FROM Employee E2 Where E2.Sal > 10000

Ohhh... we are luck that its full fill condition in step 1. so finally we get result as 

Name	Sal
Arun Gupta	10000

So Now we can write generic query for nth max as 

SELECT E1.Name, E1.Sal  FROM employee E1 WHERE (n-1)  = 

(SELECT COUNT(*) FROM Employee E2 WHERE E2.Sal > E1.Sal)