SQL Exercises: Display employees who get the second highest salary
SQL SUBQUERY: Exercise-12 with Solution
From the following table, write a SQL query to find those employees who get second-highest salary. Return all the fields of the employees.
Sample table: employees
Sample Solution:
SELECT *
FROM employees
WHERE employee_id IN
(SELECT employee_id
FROM employees
WHERE salary =
(SELECT MAX(salary)
FROM employees
WHERE salary <
(SELECT MAX(salary)
FROM employees)));
Sample Output:
employee_id first_name last_name email phone_number hire_date job_id salary commission_pct manager_id department_id 101 Neena Kochhar NKOCHHAR515.123.4568 2005-09-21 AD_VP 17000.00 0.00 100 90 102 Lex De Haan LDEHAAN 515.123.4569 2001-01-13 AD_VP 17000.00 0.00 100 90
Code Explanation:
The said query in SQL that retrieves all columns (denoted by *) from the employees table where the employee ID matches the employee ID of the second highest salary in the employees table. The first subquery in the IN clause calculates the maximum salary in the employees table, and the second subquery calculates the second highest salary by finding the maximum salary in the employees table while excluding the highest salary.
The result of the second subquery is then used to find the employee ID(s) of the second highest salary in the main query by matching the salary.
The final result of the query will be all the employees who have the second highest salary in the employees table.
Visual Presentation:

Alternative Statements:
Code - 1:
SELECT *
FROM employees
WHERE salary = (
SELECT MAX(salary)
FROM employees
WHERE salary < (
SELECT MAX(salary)
FROM employees
)
);
Code - 2:
SELECT *
FROM employees
WHERE salary = (
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY
);
Practice Online
Query Visualization:
Duration:

Rows:

Cost:

Contribute your code and comments through Disqus.
Previous SQL Exercise: Display employees not working in certain departments.
Next SQL Exercise: Employee names and hire dates for Claras department.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
SQL: Tips of the Day
How to combine date from one field with time from another field - MS SQL Server
You can simply add the two.
- if the Time part of your Date column is always zero
- and the Date part of your Time column is also always zero (base date: January 1, 1900)
Adding them returns the correct result-
SELECT Combined = MyDate + MyTime FROM MyTable
Ref: https://bit.ly/3wldJYf
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook