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

 
Interview Questions & Answers
Last Post by Anonymous 1 year ago
1 Posts
1 Users
0 Reactions
2,233 Views
RSS
Posts: 69
 Vinay Kumar
Topic starter
Nov 09, 2024 6:28 am
(@Vinay Kumar)
Joined: 6 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_iddepartmentemployee_namesalary
1HRAlice70000
2ITBob90000
3HRCarol80000
4ITDave85000
5SalesEve75000

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

SQL Query:

 
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_iddepartmentemployee_namesalary
3HRCarol80000
2ITBob90000

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:

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.


This topic was modified 7 months ago by SQL Admin

Leave a reply

Author Name

Author Email

Title *

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