SQL subqueries on employee Database: List the name, salary, and department id for each employee who earns a salary greater than the average salary for their department and list the result in ascending order on department id

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)

Practice Online

Structure of employee Database:

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 recently hired employees of every department. Sort the result-set in descending order by hire date. Return complete information about the employees.
Next: From the following table, write a SQL query to find those employees who earn a commission and receive maximum salary. Return complete information about the employees.