Second Highest Salary - Leetcode Solution

Problem Statement:

Write a SQL query to get the second highest salary from the Employee table.

Table schema for Employee table:

+----+--------+--------+ | Id | Salary | Name | +----+--------+--------+ | 1 | 100 | John | | 2 | 200 | Bob | | 3 | 300 | Jerry | | 4 | 400 | Alice | | 5 | 500 | Donald | +----+--------+--------+

Solution:

To find the second highest salary from the Employee table, we can follow the below steps:

1. First, we need to find the maximum (or highest) salary from the Employee table. For this, we can use the MAX() function as:

SELECT MAX(Salary) FROM Employee;

This will give us the highest salary from the Employee table, which in this case is 500.

2. Next, we need to find the salary which is just less than the highest salary. For this, we can use the subquery and the WHERE clause as:

SELECT Salary FROM Employee WHERE Salary < (SELECT MAX(Salary) FROM Employee);

This will give us the salaries which are less than the highest salary, which in this case are 400, 300, 200, and 100.

3. Finally, we need to get the maximum salary from the salaries we obtained in the previous step, which will be the second-highest salary. For this, we can again use the MAX() function as:

SELECT MAX(Salary) FROM Employee WHERE Salary < (SELECT MAX(Salary) FROM Employee);

This will give us the second highest salary from the Employee table, which in this case is 400.

Final SQL query to get the second highest salary from the Employee table:

SELECT MAX(Salary) FROM Employee WHERE Salary < (SELECT MAX(Salary) FROM Employee);

Output: +-------------+ | MAX(Salary) | +-------------+ | 400 | +-------------+

1