Employee Performance Analysis using SQL

Employee Performance Analysis project is a SQL-based project designed to analyze employee salaries, departmental performance and employee records. It uses indexes to improve data retrieval and subqueries to solve complex business questions, while practicing different types of indexes, nested queries and correlated subqueries.

Dataset Overview

Step 1: Create the Employee Performance Tables

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100),
    annual_budget DECIMAL(12, 2)
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    email VARCHAR(150),
    department_id INT,
    salary DECIMAL(10, 2),
    joining_date DATE,
    performance_rating DECIMAL(3, 1),
    FOREIGN KEY (department_id)
        REFERENCES departments(department_id)
);

Output:

department table:

employee table:

Step 2: Insert Employee and Department Records

INSERT INTO departments VALUES
    (1, 'IT', 1200000),
    (2, 'HR', 600000),
    (3, 'Finance', 900000),
    (4, 'Marketing', 750000);

INSERT INTO employees VALUES
    (101, 'James Wilson', 'james@example.com', 1, 65000, '2022-01-15', 4.5),
    (102, 'Emily Carter', 'emily@example.com', 1, 55000, '2023-03-10', 4.2),
    (103, 'Michael Brown', 'michael@example.com', 2, 45000, '2021-07-20', 3.8),
    (104, 'Sophia Miller', 'sophia@example.com', 3, 70000, '2020-05-12', 4.8),
    (105, 'Daniel Taylor', 'daniel@example.com', 1, 48000, '2024-02-18', 3.5),
    (106, 'Olivia Davis', 'olivia@example.com', 4, 52000, '2022-09-25', 4.1),
    (107, 'William Anderson', 'william@example.com', 3, 75000, '2019-11-05', 4.6),
    (108, 'Isabella Thomas', 'isabella@example.com', 2, 42000, '2023-06-14', 3.9);

Output:

department table:

employees table:

Step 3: Find Employees Eligible for a Performance Bonus

The HR department wants to identify employees with a performance rating above 4.0 who also earn less than the company's average salary.

SELECT employee_name, salary, performance_rating
FROM employees
WHERE performance_rating > 4.0
AND salary < (
    SELECT AVG(salary)
    FROM employees
);

Output:

• This query uses a nested subquery to compare individual salaries with the organization's average salary.

Step 4: Identify Employees Earning More Than Their Department's Average

Management wants to identify employees whose salaries are above their respective department averages.

SELECT
    e.employee_name,
    d.department_name,
    e.salary
FROM employees e
JOIN departments d
    ON e.department_id = d.department_id
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e.department_id
);

Output:

• This correlated subquery compares each employee's salary with the average salary of the employee's department.

Step 5: Find the Highest-Paid Employee in Each Department

The HR team wants to identify the highest-paid employee in every department for compensation reviews.

SELECT
    employee_name,
    department_id,
    salary
FROM employees e
WHERE salary = (
    SELECT MAX(salary)
    FROM employees
    WHERE department_id = e.department_id
);

Output:

• This query returns the highest-paid employee in each department, including multiple employees if they share the highest salary.

Step 6: Identify Departments Exceeding Their Salary Budget

Management wants to identify departments where the total annual salaries of employees exceed the department's annual budget.

SELECT
    d.department_name,
    d.annual_budget,
    (
        SELECT SUM(e.salary) * 12
        FROM employees e
        WHERE e.department_id = d.department_id
    ) AS annual_salary_cost
FROM departments d
WHERE (
    SELECT SUM(e.salary) * 12
    FROM employees e
    WHERE e.department_id = d.department_id
) > d.annual_budget;

Output:

• This query uses a correlated subquery to calculate annual salary costs and identify departments that exceed their budgets.

Step 7: Find Employees Who Joined Before the Average Joining Date

The HR team wants to identify employees who joined before the average joining date across the organization.

SELECT employee_name, joining_date
FROM employees
WHERE joining_date < (
    SELECT AVG(joining_date)
    FROM employees
);

Note: Averaging dates is not supported consistently across SQL databases. A more portable approach is to compare against a defined reference date or use database-specific date calculations.

Step 8: Create an Index for Salary-Based HR Reports

HR frequently filters employees by salary when preparing compensation reports. Create an index on the salary column.

CREATE INDEX idx_employee_salary
ON employees(salary);

For example, retrieve employees earning more than 70,000.

SELECT employee_name, salary
FROM employees
WHERE salary > 70000;

Output:

• The index may improve performance when the database can use it efficiently, particularly with larger datasets.