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
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');
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
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.
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.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
SQL: Tips of the Day
What is the most efficient/elegant way to parse a flat table into a tree?
WITH RECURSIVE MyTree AS ( SELECT * FROM MyTable WHERE ParentId IS NULL UNION ALL SELECT m.* FROM MyTABLE AS m JOIN MyTree AS t ON m.ParentId = t.Id ) SELECT * FROM MyTree;
- Weekly Trends
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook