SQL Exercises: Employees who earn second lowest salary of all
SQL SUBQUERY: Exercise-52 with Solution
From the following table, write a SQL query to find those employees who earn the second-lowest salary of all the employees. Return all the fields of employees .
Sample table: employees
Sample Solution:
SELECT *
FROM employees m
WHERE 2 = (SELECT COUNT(DISTINCT salary )
FROM employees
WHERE salary <= m.salary);
Sample Output:
employee_id first_name last_name email phone_number hire_date job_id salary commission_pct manager_id department_id 128 Steven Markle SMARKLE 650.124.1434 2008-03-08ST_CLERK 2200.00 0.00 120 50 136 Hazel Philtanker HPHILTAN 650.127.1634 2008-02-06ST_CLERK 2200.00 0.00 122 50
Code Explanation:
The said query in SQL that selects all employees who have the second-lowest distinct salary in the employees table.
A subquery is used to count the number of distinct salaries that are less than or equal to each employee's salary in the outer query, based on the data in the subquery. Exactly two distinct salaries will be less than or equal to the second lowest salary of an employee. Therefore, the outer query selects all employees for whom the subquery returns a count of 2.
Practice Online
Query Visualization:
Duration:

Rows:

Cost:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous SQL Exercise: Employees worked as a Sales Representative in the past.
Next SQL Exercise: Display the details of departments managed by Susan.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
SQL: Tips of the Day
MySQL export schema without data
mysqldump -h yourhostnameorIP -u root -p --no-data dbname > schema.sql
Ref: https://bit.ly/3xzB9dS
- Weekly Trends
- 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
- JavaScript functions Exercises
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises
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