SQL Interview Question: Part 1

LO1: Getting the Nth Highest Salary of Employees

LO2: Getting the Nth Lowest Salary of Employees

This is the most common question in interviews. The interviewer asks to get the nth highest, nth lowest, or to the get the nth highest or nth lowest Salary of Employees as per specific department.

Getting the Nth Highest Salary of Employees

Method 1: By using this method, Nth highest salary will be found.
Let's say we have a table called tblemployee which contains the salary of different employees as below.


Here we are going to use the subquery to get the nth highest salary.
Let's say we need to find the 2nd highest salary to do that we need to run the following query.

select e.name, e.salary from tblemployee e where 2 = 
(select count(distinct(b.salary)) from tblemployee b where e.salary <=b.salary)



By running the above query we will get the second highest salary. In the same way, if we need to find the 3rd highest salary then we need to replace 2 with 3 so the query will be as given below.

select e.name, e.salary from tblemployee e where 3 = 
(select count(distinct(b.salary)) from tblemployee b where e.salary <=b.salary)

Method 2: By using this method we can find Nth highest salary.

Sometimes the interviewer will ask to give more options to find the 2nd highest salary, to do that we can use the top keyword. The query will be as follows.

select top 1 salary from 
(select top 2 salary from tblemployee order by salary desc) t order by salary asc



Now to find the 3rd highest salary we need to change the top 2 to top 3 and we will get the desired result.

select top 1 salary from 
(select top 3 salary from tblemployee order by salary desc) t order by salary asc


And if you asked to find the maximum salary then it is quite easy, to find the maximum salary we can use the MAX aggregate function that will give the required result.

select MAX(salary) from tblEmployee


Getting the Nth Lowest Salary of Employees

Method 1: By using this method, Nth lowest salary will be found.
As we did to find the nth highest salary, in the same way, we can find the nth lowest salary.

select e.name, e.salary from tblemployee e where 2 = 
(select count(distinct(b.salary)) from tblemployee b where e.salary >=b.salary)


In the above query, we have changed the less than < sign to greater than > sign and get the expected result.

In the same way, we can find the nth lowest salary to do that we need to change the value 2 to 3 if we need to find the 3rd lowest salary.

select e.name, e.salary from tblemployee e where 3 = 
(select count(distinct(b.salary)) from tblemployee b where e.salary >=b.salary)


Method 2: By using this method, Nth lowest salary will be found.
As we did to find the nth highest salary in method 2, we can also use find the lowest nth salary.

select top 1 salary from 
(select top 2 salary from tblemployee order by salary asc) t order by salary desc



Now to find the 3rd highest salary we need to change the top 2 to top 3 and we will get the desired result.

select top 1 salary from 
(select top 3 salary from tblemployee order by salary asc) t order by salary desc

That's all for today. Thank You. 

Comments

Post a Comment

Popular posts from this blog

SQL Day 11: Group By in SQL Server

Image
LO1: Grouping rows using Group By LO2: Filtering Groups LO3: Difference between Where and Having Clause Group By clause is used to group a selected set of rows into a set of summary rows by the values of one or more columns or expression. It is always used in conjunction with one or more aggregate functions. Let’s say we have a table tblEmployee which contains all the salary of employees including the cities. So if we need to find the maximum salary of employee we can use the MAX(Column_Name) aggregate function to find the highest salary of employees. select MAX(salary) from tblemployee In the same way, we can find the minimum salary and total salary of all the employees using different aggregate functions. select MIN(salary) as [Minimum Salary] from tblemployee select SUM(salary) as [Total Salary] from tblemployee Let’s say we have to find out the employee salary as per the different cities, to achieve this we need to use the Group B...
Keep reading

JAVA Script Executor Code to Click on an Element

Click on an Element in Selenium WebDriver Sometimes click() method does not work because two views are loaded at the same time on a page. In this case, we can use the JavascriptExecutor to get the work done. Below is code to click on an element using Javascript Executor. 1 2 3 4 5 6 7 @FindBy(id = Checkbox. financialAssistanceAccountEmailId ) public WebElement financialAssistanceAccountEmail; //Click to element JavascriptExecutor executor = (JavascriptExecutor) driver; executor. executeScript ( "arguments[0].click()" , financialAssistanceAccountEmail);
Keep reading

SQL Day 2: Creating, Altering and Dropping a Database

Image
LO1: Creating, Altering and Dropping a Database Prerequisite:   Connecting to SQL Server using SSMS After connecting to the database through SSMS first we need to create the database on which different queries will run. Let’s start with creating a Database. A SQL Server Database can be Created, Altered or Dropped in two ways: Graphically using SQL Server Management Studio or Using SQL Query Graphically using SQL Server Management Studio: Within the Object Explorer, right-click on Database folder and select the New Database. When clicked on New Database a new dialog box will open. Here you need to provide the database name and when clicking on OK, SQL Server run the query internally and will create the new database for us. When you expand the database folder then you will see the Sample database here Creating Database Using Query: We can also create the database by executing the query in the query editor. For creating a...
Keep reading