w3resource
SQL exercises

SQL Subquery Exercises: Display the first and last name, salary, and department ID for those employees who earn less than the minimum salary of a department which ID is 70

SQL SUBQUERY: Exercise-44 with Solution

Write a query in SQL to display the first and last name, salary, and department ID for those employees who earn less than the minimum salary of a department which ID is 70.

Sample table: employees


Sample Solution:

SELECT first_name, last_name, salary, department_id 
 FROM employees
  WHERE salary < ALL 
     (SELECT salary 
       FROM employees 
         WHERE department_id = 70);

Sample Output:

first_name	last_name	salary	department_id
Alexander	Hunold		9000.00	60
Bruce		Ernst		6000.00	60
David		Austin		4800.00	60
Valli		Pataballa	4800.00	60
Diana		Lorentz		4200.00	60
Daniel		Faviet		9000.00	100
John		Chen		8200.00	100
Ismael		Sciarra		7700.00	100
Jose 	Manuel	Urman	7800.00	100
Luis		Popp		6900.00	100
Alexander	Khoo		3100.00	30
Shelli		Baida		2900.00	30
Sigal		Tobias		2800.00	30
Guy		Himuro		2600.00	30
Karen		Colmenares	2500.00	30
Matthew		Weiss		8000.00	50
Adam		Fripp		8200.00	50
Payam		Kaufling	7900.00	50
Shanta		Vollman		6500.00	50
Kevin		Mourgos		5800.00	50
Julia		Nayer		3200.00	50
Irene		Mikkilineni	2700.00	50
James		Landry		2400.00	50
Steven		Markle		2200.00	50
Laura		Bissot		3300.00	50
Mozhe		Atkinson	2800.00	50
James		Marlow		2500.00	50
TJ		Olson		2100.00	50
Jason		Mallin		3300.00	50
Michael		Rogers		2900.00	50
Ki		Gee		2400.00	50
Hazel		Philtanker	2200.00	50
Renske		Ladwig		3600.00	50
Stephen		Stiles		3200.00	50
John		Seo		2700.00	50
Joshua		Patel		2500.00	50
Trenna		Rajs		3500.00	50
Curtis		Davies		3100.00	50
Randall		Matos		2600.00	50
Peter		Vargas		2500.00	50
David		Bernstein	9500.00	80
Peter		Hall		9000.00	80
Christopher	Olsen		8000.00	80
Nanette		Cambrault	7500.00	80
Oliver		Tuvault		7000.00	80
Patrick			Sully	9500.00	80
Allan		McEwen		9000.00	80
Lindsey		Smith		8000.00	80
Louise		Doran		7500.00	80
Sarath		Sewall		7000.00	80
Danielle	Greene		9500.00	80
Mattea		Marvins		7200.00	80
David		Lee		6800.00	80
Sundar		Ande		6400.00	80
Amit		Banda		6200.00	80
Tayler		Fox		9600.00	80
William		Smith		7400.00	80
Elizabeth	Bates		7300.00	80
Sundita		Kumar		6100.00	80
Alyssa		Hutton		8800.00	80
Jonathon	Taylor		8600.00	80
Jack		Livingston	8400.00	80
Kimberely	Grant		7000.00	0
Charles		Johnson		6200.00	80
Winston		Taylor		3200.00	50
Jean		Fleaur		3100.00	50
Martha		Sullivan	2500.00	50
Girard		Geoni		2800.00	50
Nandita		Sarchand	4200.00	50
Alexis		Bull		4100.00	50
Julia		Dellinger	3400.00	50
Anthony		Cabrio		3000.00	50
Kelly		Chung		3800.00	50
Jennifer	Dilly		3600.00	50
Timothy		Gates		2900.00	50
Randall		Perkins		2500.00	50
Sarah		Bell		4000.00	50
Britney		Everett		3900.00	50
Samuel		McCain		3200.00	50
Vance		Jones		2800.00	50
Alana		Walsh		3100.00	50
Kevin		Feeney		3000.00	50
Donald		OConnell	2600.00	50
Douglas		Grant		2600.00	50
Jennifer	Whalen		4400.00	10
Pat		Fay		6000.00	20
Susan		Mavris		6500.00	40
William		Gietz		8300.00	110

Practice Online


Query Visualization:

Duration:

Query visualization of Display the first and last name, salary, and department ID for those employees who earn less than the minimum salary of a department which ID is 70 - Duration

Rows:

Query visualization of Display the first and last name, salary, and department ID for those employees who earn less than the minimum salary of a department which ID is 70 - Rows

Cost:

Query visualization of Display the first and last name, salary, and department ID for those employees who earn less than the minimum salary of a department which ID is 70 - Cost

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

Previous: Write a query in SQL to display the full name,email, and designation for all those employees who was hired after the employee whose ID is 165.
Next: Write a query in SQL to display the first and last name, salary, and department ID for those employees who earn less than the average salary, and also work at the department where the employee Laura is working as a first name holder.

What is the difficulty level of this exercise?



New Content: Composer: Dependency manager for PHP, R Programming