﻿ SQL - Employees who earn second lowest salary of all

# 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.

## 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

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