SQL Server Tricky Query Interview Question

Anil Thakur

13 Comments

Join the conversation! Your thoughts help the community grow.

Bharat Singh7y ago
Nice Article. Easy to understand. Thanks a lot Sir.
Vaibhav Agarwal8y ago
In your articles please also generate SQL Insert Sample Data scripts so that we can straight away start querying. Thanks .
- Anil Thakur8y ago
  I will do that from next time
Mayur Gujrathi9y ago
Where #Temp is in query
- Anil Thakur8y ago
  I created temp table #Temp to explain these queries, from which i getting this data.
Humayun Kabir Mamun10y ago
Nice, some more tricky query will be better...
Anu V10y ago
Nice
Subash10y ago
Nice
Manas Mohapatra10y ago
If you could have added more questions then it would be better
- Anil Thakur10y ago
  Isn't it better now?
Munesh Sharma10y ago
Nice
Harinder Kumar10y ago
Very helpful queries !! Thank u so much sir
Vignesh Mani10y ago
Nice

Introduction

Let's write some Sql Server Queries to get the results from a table in a database. I am writing some queries which were asked of me during my interview -- the questions asked in each interview are different but I understand they are related to each other. Every time the person who is doing the interview asks you to do this or that, if things are clear to you, you can easily do what the interviewer asks you to do.

Description

Suppose you have a table like the below image and have the data like this table has,

By mistake I wrote the column name wrong (Department), so we will carry on with this, because we just have to clear the concept, so we can ignore the spelling here. Now we have a table with the data, let's play with it.

Let's create different case requirements from this table, for example,

Get the name of employee who having has the highest salary in each department,

select t.Departmnet,t.name,t.Salary from(select max(Salary) as TotalSalary,Departmnet from #Temp group by Departmnet) as TempNew
Inner Join #Temp t on TempNew.Departmnet=t.Departmnet
and TempNew.TotalSalary=t.Salary

You will get the output like this,

So we have the output we want, now let's do this some other way, the easy way with the CTE.

with cte as
(
select name,departmnet,salary,ROW_NUMBER() over(PARTITION BY departmnet order by salary desc) as RowNum from #Temp
)
select Departmnet,Name,Salary from cte where RowNum=1 order by Departmnet desc

We will get the same output -- the output in the above image with this query too.

Get the Total Salary of each Department.

select sum(Salary) as TotalSalary,Departmnet from #Temp group by Departmnet

With this query you will get the output like this-:

So this is how we can get the total salary of each department.

Get the 2nd highest salary or nth salary in each department.

with cte as
(
select name,departmnet,salary,ROW_NUMBER() over(PARTITION BY departmnet order by salary desc) as RowNum from #Temp
)
select Departmnet,Name,Salary from cte where RowNum=2 order by Departmnet desc

In the above sql query we write nothing new, just change the Rownum=2, because of (Partition by Department) it will give the row number separately to each department starting from 1, so we can get nth salary in each department. Output will be like this,

In the above image you can see the second highest salary of each department.

Now get the third highest salary from the table.

with cte as
(
select name,departmnet,salary,ROW_NUMBER() over(order by salary desc) as RowNum from #Temp
)
select Departmnet,Name,Salary from cte where RowNum=3 order by Departmnet desc

Now if we see the above query you learn that we removed the (Partition by Department), so now we will get row number for whole table not only for Department, and we have to get the third highest salary so we will just have to give RowNum=3, and that's it, we are ready to rock ;).

I know there is another way to to do this, but in the end what matter is output, I shared this with you people because there are so many who are just starting in this, so this might be helpful for those. Happy coding!