SQL Exercise: Employees salary exceed department average, ASC order

SQL subqueries on employee Database: Exercise-53 with Solution

[An editor is available at the bottom of the page to write and execute the scripts.]

53. From the following table, write a SQL query to find those employees who receive a salary higher than the average salary of their department. Sort the result-set in ascending order by department ID. Return employee name, salary, and department ID.

Sample table: employees

Sample Solution:

SELECT e.emp_name,
       e.salary,
       e.dep_id
FROM employees e
WHERE salary >
    (SELECT avg(salary)
     FROM employees
     WHERE e.dep_id = dep_id )
ORDER BY dep_id;

OR

SELECT e.emp_name,
       e.salary,
       e.dep_id
FROM employees e,

  (SELECT avg(salary) A,
          dep_id D
   FROM employees
   GROUP BY dep_id) D1
WHERE D1.D = e.dep_id
  AND e.salary > D1.A;

Sample Output:

 emp_name | salary  | dep_id
----------+---------+--------
 KAYLING  | 6000.00 |   1001
 JONAS    | 2957.00 |   2001
 SCARLET  | 3100.00 |   2001
 FRANK    | 3100.00 |   2001
 BLAZE    | 2750.00 |   3001
 ADELYN   | 1700.00 |   3001
(6 rows)

Explanation:

The said query in SQL that selects the emp_name, salary, and dep_id columns from the 'employees' table for all employees whose salary is greater than the average salary for their department

The subquery to the outer query, that returns the average salary for the department that each employee belongs to. The ORDER BY dep_id clause sorts the results by dep_id.

Practice Online

Structure of employee Database:

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

Previous SQL Exercise: Recent hires in every department in order of hire date.
Next SQL Exercise: Employees who earn a commission and maximum salary.