﻿ SQL: Employees salary exceed department average, ASC order

# 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
(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.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

﻿