SQL Interview Question - 4

Consider two tables: employees and departments.

The employees table contains columns emp_id, emp_name, dept_id, salary.

The departments table contains columns dept_id and dept_name.

Write a SQL query to find the department names along with the average salary of employees in each department. Include all departments, even if there are no employees in them.

Sample Data:

employees

emp_id	emp_name	dept_id	salary
1	Alice	101	50000
2	Bob	102	60000
3	Charlie	101	55000
4	David	103	48000
5	Eve	102	62000

departments

dept_id	dept_name
101	HR
102	Finance
103	Marketing
104	Operations

Solution:

SELECT d.dept_name, COALESCE(AVG(e.salary), 0) AS avg_salary
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_name;

 

Share your alternate solution in comments.