w3resource

SQL Subquery Exercises: Display the name, salary, department id for those employees who earn such amount of salary which is the smallest salary of any of the departments

SQL SUBQUERY: Exercise-3 with Solution

From the following table, write a SQL query to find those employees whose salary matches the lowest salary of any of the departments. Return first name, last name and department ID.

Sample table: employees


Sample Solution:

SELECT first_name, last_name, salary, department_id  
FROM employees  
WHERE salary IN  
( SELECT MIN(salary)  
FROM employees  
GROUP BY department_id 
);

Sample Output:

first_name	last_name	salary	department_id
Neena		Kochhar		17000.00	90
Lex		De Haan		17000.00	90
Bruce		Ernst		6000.00		60
Diana		Lorentz		4200.00		60
Luis		Popp		6900.00		100
Karen		Colmenares	2500.00		30
Shanta		Vollman		6500.00		50
James		Marlow		2500.00		50
TJ		Olson		2100.00		50
Joshua		Patel		2500.00		50
Peter		Vargas		2500.00		50
Peter		Tucker		10000.00	80
Oliver		Tuvault		7000.00		80
Janette		King		10000.00	80
Sarath		Sewall		7000.00		80
Harrison	Bloom		10000.00	80
Sundita		Kumar		6100.00		80
Kimberely	Grant		7000.00		0
Martha		Sullivan	2500.00		50
Nandita		Sarchand	4200.00		50
Randall		Perkins		2500.00		50
Jennifer	Whalen		4400.00		10
Pat		Fay		6000.00		20
Susan		Mavris		6500.00		40
Hermann		Baer		10000.00	70
William		Gietz		8300.00		110

Pictorial Presentation:

SQL Subqueries: Display the name, salary, department id for those employees who earn such amount of salary which is the smallest salary of any of the departments

Practice Online


Query Visualization:

Duration:

Query visualization of Display the name, salary, department id for those employees who earn such amount of salary which is the smallest salary of any of the departments - Duration

Rows:

Query visualization of Display the name, salary, department id for those employees who earn such amount of salary which is the smallest salary of any of the departments - Rows

Cost:

Query visualization of Display the name, salary, department id for those employees who earn such amount of salary which is the smallest salary of any of the departments - Cost

Contribute your code and comments through Disqus.

Previous: From the following table, write a SQL query to find those employees whose designation is the same as the employee whose ID is 169. Return first name, last name, department ID and job ID.
Next: From the following table, write a SQL query to find those employees who earn more than the average salary. Return employee ID, first name, last name.

Test your Programming skills with w3resource's quiz.

What is the difficulty level of this exercise?



SQL: Tips of the Day

"where 1=1" statement?

It's usually when folks build up SQL statements.

When you add and value = "Toyota" you don't have to worry about whether there is a condition before or just WHERE.

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