w3resource

SQL Subquery Exercises: Display all the information about those employees who earn second lowest salary of all the employees

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

Practice Online


Query Visualization:

Duration:

Query visualization of Display all the information about those employees who earn second lowest salary of all the employees - Duration

Rows:

Query visualization of Display all the information about those employees who earn second lowest salary of all the employees - Rows

Cost:

Query visualization of Display all the information about those employees who earn second lowest salary of all the employees - Cost

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: From the following table, write a SQL query to find those employees who worked as a 'Sales Representative' in the past. Return all the fields of jobs.
Next: From the following table, write a SQL query to find those departments managed by 'Susan'. Return all the fields of departments.

Test your Programming skills with w3resource's quiz.

What is the difficulty level of this exercise?



Share this Tutorial / Exercise on : Facebook and Twitter

SQL: Tips of the Day

How to count occurrences of a column value in SQL?

Input table:

id | age
--------
0  | 25
1  | 25
2  | 23
SELECT age, count(age) 
  FROM Students 
 GROUP by age

Output:

id | age | count
----------------
0  | 25  | 2
1  | 25  | 2
2  | 23  | 1

Ref: https://bit.ly/3zbLPQm