Write a query to find the average salary per department.

Objective:

Write a SQL query to find the average salary per department.

Step-by-step Guide

1. Table Creation (if not already created)

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10,2),
    hire_date DATE
);

2. Insert Sample Data

Let's add sample employee data from multiple departments:

INSERT INTO employees (id, name, department, salary, hire_date) VALUES
(1, 'John Smith', 'HR', 50000.00, '2020-01-15'),
(2, 'Alice Johnson', 'Engineering', 75000.00, '2019-03-22'),
(3, 'Bob Lee', 'Sales', 62000.00, '2021-07-10'),
(4, 'Mary Jane', 'Engineering', 80000.00, '2018-11-05'),
(5, 'Tom Hardy', 'Marketing', 55000.00, '2022-06-01'),
(6, 'Steve Rogers', 'Sales', 60000.00, '2021-09-10'),
(7, 'Tony Stark', 'Engineering', 90000.00, '2017-12-20'),
(8, 'Bruce Banner', 'HR', 52000.00, '2023-02-01');

🔍 3. Query to Find Average Salary per Department

SELECT 
    department,
    AVG(salary) AS average_salary
FROM 
    employees
GROUP BY 
    department;

Explanation:

• SELECT department, AVG(salary) → We select each department and calculate average salary.
• FROM employees → From the employees table.
• GROUP BY department → So the average is calculated per department.

Output Example:

Note:

• AVG() is an aggregate function that calculates the average of a numeric column.
• The result is grouped per unique department name.

Let me know if you want:

• Salaries rounded to 2 decimal places
• Departments sorted by highest average salary
• Or to include departments even if they have no employees (requires LEFT JOIN on another table)