SQL Subquery Exercises: 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

SQL SUBQUERY: Exercise-45 with Solution

From the following table, write a SQL query to find those employees who earn less than the average salary and work at the department where Laura (first name) is employed. Return first name, last name, salary, and department ID.

Sample table: employees

Sample Solution:

SELECT first_name, last_name, salary, department_id 
 FROM employees
   WHERE salary < 
      (SELECT AVG(salary) 
        FROM employees )
         AND department_id = 
               (SELECT department_id 
                 FROM employees 
                  WHERE first_name = 'Laura');

Sample Output:

first_name	last_name	salary	department_id
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
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

Pictorial Presentation:

Practice Online

Query Visualization:

Duration:

Rows:

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 earn less than the minimum salary of a department of ID 70. Return first name, last name, salary, and department ID.
Next: From the following tables, write a SQL query to find those employees whose department is located in the city 'London'. Return first name, last name, salary, and department ID.