Second Highest Salary SQL Challenge - Oracle Solution

1. Introduction

In this post, we'll explore a common database query scenario in Oracle: finding the second-highest salary from a table. This is a typical challenge in SQL querying and can be addressed in various ways.

Problem

The aim is to write an Oracle SQL query to find the second-highest salary from the Employee table. If there's no second-highest salary (like in cases where all employees have the same salary or there's only one employee), the query should return null.

Database Table:

Employee Table:

id	salary
1	100
2	200
3	300

3. Oracle Solution

SELECT MAX(salary) AS SecondHighestSalary
FROM Employee
WHERE salary < (SELECT MAX(salary) FROM Employee);

Output:

Output Table:

SecondHighestSalary
200

Output for case with no second-highest salary:

SecondHighestSalary
null

Explanation:

1. The query uses the MAX function to find the highest salary in the Employee table.

2. It then retrieves the maximum salary that is less than this highest salary.

3. This effectively returns the second-highest salary in the table.

4. If there is no distinct second-highest salary, the query yields null.