SQLite Exercise: Display the employee ID, first name, last names, salary of all employees whose salary is above average for their departments

Write a query to display the employee ID, first name, last names, salary of all employees whose salary is above average for their departments.

Sample table : employees

Sample table : departments

SQLite Code:

SELECT employee_id, first_name 
FROM employees AS A 
WHERE salary > 
( SELECT AVG(salary) FROM employees WHERE department_id = A.department_id); 

Output:

employee_id  first_name
-----------  ----------
100          Steven
103          Alexander
104          Bruce
108          Nancy
109          Daniel
114          Den
120          Matthew
121          Adam
122          Payam
123          Shanta
124          Kevin
137          Renske
141          Trenna
145          John
146          Karen
147          Alberto
148          Gerald
149          Eleni
150          Peter
151          David
152          Peter
156          Janette
157          Patrick
158          Allan
162          Clara
163          Danielle
168          Lisa
169          Harrison
170          Tayler
174          Ellen
184          Nandita
185          Alexis
188          Kelly
189          Jennifer
192          Sarah
193          Britney
201          Michael
205          Shelley

Practice SQLite Online

Model Database

Structure of 'hr' database :

Improve this sample solution and post your code through Disqus.

Previous: Write a query to find the names (first_name, last_name) of the employees who are not supervisors.
Next: Write a query to find the 5th maximum salary in the employees table.