Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
How to get Second Highest Salary in SQL?
3.
Example
4.
Frequently Asked Questions
4.1.
Is it possible to add, modify, and drop columns in a single ALTER TABLE command?
4.2.
What is the difference between the HAVING Clause and WHERE Clause in SQL?
4.3.
Is it possible to undo a DELETE operation?
5.
Conclusion
Last Updated: Apr 7, 2024
Medium

Second Highest Salary in SQL

Author Ravi Khorwal
0 upvote
Master Python: Predicting weather forecasts
Speaker
Ashwin Goyal
Product Manager @

Introduction

Managing a database effectively is crucial for any application's success. One of the key tasks in database management is modifying the database structure to meet evolving requirements. The SQL or Structured Query Language is a powerful tool used to manipulate and retrieve data stored in relational databases.

In this blog, we will understand how to find the second-highest salary in SQL. 

How to get Second Highest Salary in SQL?

To get the second highest salary in SQL we first find out the maximum salary of the employee by using the max function in the inner query. After we achieve the results from the inner query we run the outer query to find the maximum salary which is less than the first maximum salary that we achieve by the first inner query. 

Below is the syntax for the following : 

select max(salary) as SecondHighestSalary
from employee 
where salary <(select max(salary)
from employee);
Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

Example

Write a SQL Query to display the Employee name, taking the second-highest Salary. 

Let us decompose the given Query: 

  1. To find the Employee's name who is having the second-highest Salary, we need to find the second highest Salary. 
  2. So, we must firstly find the highest Salary, which can be done with the max() aggregate function. 


The following Query returns the maximum Salary from the table EMPLOYEE. 

SELECT max(Salary) from Employee; -- 60,000

 

The following Query excludes the highest Salary using the <>(NOT EQUAL TO) operator and gives us the Second Highest Salary.

SELECT max(Salary) from Employee 
WHERE Salary <> (SELECT max(Salary) from Employee); -- 50,000 

 

With the help of the above queries we can find the Employee who is taking the second-highest Salary:

SELECT EmpName from Employee 
WHERE Salary IN (SELECT max(Salary) from Employee 
WHERE Salary <> (SELECT max(Salary) from Employee));

 

Output:

Frequently Asked Questions

Is it possible to add, modify, and drop columns in a single ALTER TABLE command?

Yes, you can combine multiple operations in a single ALTER TABLE command. This is efficient as it reduces the number of times the table needs to be locked for changes, but ensure you order operations carefully to avoid conflicts.

What is the difference between the HAVING Clause and WHERE Clause in SQL?

WHERE Clause filters records from a table based on the condition supplied; in contrast, the HAVING Clause filters records from groups based on the condition provided.

Is it possible to undo a DELETE operation?

Once a DELETE operation is executed, it cannot be undone unless you have a backup of the data or if your database supports transactional operations where you can roll back the transaction before it's committed.

Conclusion

In this article, we've learned how to get Second Highest Salary in SQL. Understanding how to accurately use this command is crucial for maintaining the integrity and relevance of your database. 

You can refer to our guided paths on the Coding Ninjas. You can check our course to learn more about DSADBMSCompetitive ProgrammingPythonJavaJavaScript, etc. Also, check out some of the Guided Paths on topics such as Data Structure and AlgorithmsCompetitive ProgrammingOperating SystemsComputer Networks, DBMSSystem Design, etc., as well as some Contests, Test Series, and Interview Experiences curated by top Industry Experts.

Previous article
SQL Month() Function
Next article
MySQL Cursor
Live masterclass