SQL Exercises: Employees earn less than the average salary like Laura

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

Code Explanation:

The said query in SQL that retrieves data from the 'employees' table. The columns selected are "first_name", "last_name", "salary", and "department_id". The query only returns rows where the salary is less than the average salary of all employees, and the department_id matches the department_id of an employee named "Laura".
In the WHERE clause, the salary column is selected and the AVG() aggregate function is used to calculate the average salary for all employees. It is then compared to each employee's salary in the table 'employees'.
The another subquery selects the department_id of the employee from the 'employees' table where the first_name is 'Laura'. The result of this subquery is then used to compare with the department_id of each row in the 'employees' table.

Visual Presentation:

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

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

Previous SQL Exercise: Minimum salary of a department which ID is 70.
Next SQL Exercise: Employees whose department is in London.