Forum

Forums
SQL
Interview Questions...
Write an SQL Query ...
 
Share:
Notifications
Clear all

Write an SQL Query to Find Employees With Salaries Above the Department Average

 
    Last Post
RSS

Posts: 71
 Vinay Kumar
Guest
Topic starter
Nov 09, 2024 6:28 am
(@Vinay Kumar)
Trusted Member
Joined: 4 years ago

How can you write an SQL query to find employees whose salaries are higher than the average salary of their respective department? Assume the employees table has columns employee_id, department, employee_name, and salary.

Answer:

To find employees with salaries above the average salary of their department, we can use a subquery that calculates the average salary for each department and then filters employees whose salaries exceed this average.

Solution: Using a Subquery

Assume the employees table structure is as follows:

employee_id department employee_name salary
1 HR Alice 70000
2 IT Bob 90000
3 HR Carol 80000
4 IT Dave 85000
5 Sales Eve 75000

In this case, we want to retrieve the names of employees whose salaries exceed their department's average salary.

SQL Query:

sql
 
SELECT employee_id, department, employee_name, salary
FROM employees e
WHERE salary > (
SELECT AVG(salary)
FROM employees
WHERE department = e.department
);

Explanation:

  1. Subquery for Department Average Salary: The inner query SELECT AVG(salary) FROM employees WHERE department = e.department calculates the average salary for each department.
  2. Filtering Condition: The WHERE salary > (...) condition in the outer query ensures only employees with salaries above their department’s average are selected.
  3. Correlated Subquery: The subquery is correlated, as it references e.department from the outer query, making it specific to each department.

Expected Output:

Based on the example data, if the average salaries for departments are:

  • HR: 75,000
  • IT: 87,500
  • Sales: 75,000

The output would look like:

employee_id department employee_name salary
3 HR Carol 80000
2 IT Bob 90000

Alternative Solution: Using CTE and JOIN

To make this more readable or handle larger datasets, you can use a Common Table Expression (CTE) to calculate average salaries and join it with the main table.

SQL Query:

sql
 
WITH department_avg AS (
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
)
SELECT e.employee_id, e.department, e.employee_name, e.salary
FROM employees e
JOIN department_avg da ON e.department = da.department
WHERE e.salary > da.avg_salary;

Explanation of Alternative Solution:

  1. CTE for Average Salary Calculation: The CTE department_avg calculates and stores the average salary for each department.
  2. JOIN with Main Table: We join employees with department_avg on the department column.
  3. Filtering Condition: WHERE e.salary > da.avg_salary filters for employees with above-average salaries within their department.

Expected Output:

This alternative solution will produce the same result as the first solution, listing employees with above-average salaries in each department.

Leave a reply

Author Name

Author Email

Title *

 
Preview 0 Revisions Saved
Forum Jump:
  Previous Topic
Next Topic  
Share: