Find Average Salary

Problem Statement:

Write a query to find the average salary of the employees for each department.

• Save the new average salary as 'Average_salary'.

• Return the columns 'department_id', 'department_name', and 'Average_salary'.

• Return the result ordered by department_id in ascending order.

Dataset Description:

Sample Input:

Table: employees

Table: departments

Sample Output:

Approach 1: SQL Join Syntax

SELECT
    e.department_id,
    d.department_name,
    AVG(e.salary) AS Average_salary
FROM
    employees e
JOIN
    departments d ON e.department_id = d.department_id
GROUP BY
    e.department_id,
    d.department_name
ORDER BY
    e.department_id;

Explanation:

This query uses an explicit JOIN clause to join the employees and departments tables. This approach is more modern and generally preferred for clarity and maintainability.

Approach 2: Use Common Table Expression (CTE):

sqlCopy codeWITH DepartmentSalaries AS (
    SELECT
        e.department_id,
        d.department_name,
        e.salary
    FROM
        employees e
    JOIN
        departments d ON e.department_id = d.department_id
)
SELECT
    department_id,
    department_name,
    AVG(salary) AS Average_salary
FROM
    DepartmentSalaries
GROUP BY
    department_id,
    department_name
ORDER BY
    department_id;

Explanation:

This approach uses a CTE to first select the relevant data from the employees and departments tables, and then the outer query performs the aggregation and sorting. This can sometimes make complex queries easier to understand and maintain.

The article presents two approaches to find the average salary by department in a SQL database: using explicit JOIN syntax and utilizing a Common Table Expression (CTE). The dataset includes 'employees' and 'departments' tables, and the desired output is columns for 'department_id', 'department_name', and 'Average_salary', sorted by 'department_id'.