Salary Related Queries SQL Server Interview Questions

1. Write a Query to get 1st,2nd,3rd,4th,5th ,6th highest salary  from employee table.

The following solution is for getting 6th highest salary from Employee table . You can change the value for getting 1st or 2nd etc..

Ans: Select top 1 salary from

         ( select distinct top 6 salary from employee order by salary desc)  t order by salary asc

2. Write a Query to get 1st,2nd,3rd,4th,5th ,6th least salary  from employee table.

The following solution is for getting 6th highest salary from Employee table . You can change the value for getting 1st or 2nd etc..

Ans: Select top 1 salary from

         ( select distinct top 6 salary from employee order by salary asc) t order by salary desc

3.Write a query to get 1^st,2^nd ,3^rd,4^th,5^th,6^th etc higest salary from the employee table without using subquery or TOP Keyword.?

For 5th highest below is the query

with CTE as

(

    select t.*, ROW_NUMBER() OVER(ORDER BY t.Salary desc) AS RowNum

    from employeeTable as t

)

select * from CTE

where RowNum = 5

4. Write a Query to display  firstname and Gender as M/F  from employee (if male then 'M', if Female then 'F').


Ans: Select firstname, case when gender='Male' then 'M' when gender='Female' then 'F' end as Gender  from employee