Employee Data Analysis Project

Project Overview

This project performs an in-depth analysis of employee data to derive insights related to department demographics, salary distributions, performance evaluations, leave management, and employee-manager relationships. The analysis leverages various SQL queries to generate reports based on employee performance, salary and more. This project aims to help HR departments and organizational management better understand employee performance, compensation, and overall organizational health.

Schema Description

The database schema consists of several tables that store detailed information about employees, departments, performance scores, salaries, and leaves. Below are the key tables:

1. `departments` Table

This table holds the department information, including the department's ID, name, manager, and location.

Column Name	Data Type	Description
`dep_id`	INT	Primary Key: Unique identifier for each department.
`department_name`	VARCHAR(100)	Name of the department.
`manager_id`	INT	Foreign Key: References the `emp_id` from employees table, identifying the department manager.
`location`	VARCHAR(100)	Location of the department.

2. `employees` Table

This table contains employee details, including personal information, department affiliation, salary, and job title.

Column Name	Data Type	Description
`emp_id`	INT	Primary Key: Unique identifier for each employee.
`first_name`	VARCHAR(100)	Employee's first name.
`last_name`	VARCHAR(100)	Employee's last name.
`date_of_birth`	DATE	Employee's date of birth.
`gender`	VARCHAR(10)	Employee's gender.
`joining_date`	DATE	Date the employee joined the organization.
`department_id`	INT	Foreign Key: References the `dep_id` from the `departments` table.
`salary`	DECIMAL(10,2)	Employee's salary.
`job_title`	VARCHAR(100)	Employee's job title.
`location`	VARCHAR(100)	Employee's location.

3. `salaries` Table

This table stores historical salary data for employees, allowing for tracking of salary changes over time.

Column Name	Data Type	Description
`salary_id`	INT	Primary Key: Unique identifier for each salary record.
`employee_id`	INT	Foreign Key: References the `emp_id` from the `employees` table.
`salary`	DECIMAL(10,2)	Salary value of the employee.
`effective_date`	DATE	Date the salary became effective.

4. `employee_performance` Table

This table records performance reviews for employees, including performance scores and review dates.

Column Name	Data Type	Description
`performance_id`	INT	Primary Key: Unique identifier for each performance record.
`employee_id`	INT	Foreign Key: References the `emp_id` from the `employees` table.
`performance_score`	INT	The employee's performance score (between 1 and 5).
`review_date`	DATE	Date of the performance review.
`manager_id`	INT	Foreign Key: References the `emp_id` from the `employees` table, identifying the reviewer.

5. `employee_leave` Table

This table tracks employee leave records, including leave type, start and end dates.

Column Name	Data Type	Description
`leave_id`	INT	Primary Key: Unique identifier for each leave record.
`employee_id`	INT	Foreign Key: References the `emp_id` from the `employees` table.
`leave_type`	VARCHAR(50)	Type of leave taken (e.g., vacation, sick leave, etc.).
`start_date`	DATE	Start date of the leave.
`end_date`	DATE	End date of the leave.

Relationships

Employees ↔ Departments

One-to-Many: Each employee is assigned to one department, but a department can have multiple employees.
Relationship: The department_id in the employees table is a foreign key referencing dep_id in the departments table.
Manager Relationship: The manager_id in the departments table is a foreign key referencing the emp_id in the employees table, indicating the manager of the department.

Employees ↔ Salaries

One-to-Many: An employee can have multiple salary records over time, but each salary record belongs to one employee.
Relationship: The employee_id in the salaries table is a foreign key referencing emp_id in the employees table.

Employees ↔ Employee Performance

One-to-Many: Each employee can have multiple performance reviews, but each review belongs to one employee.
Relationship: The employee_id in the employee_performance table is a foreign key referencing emp_id in the employees table.
Manager Relationship: The manager_id in the employee_performance table is a foreign key referencing the emp_id in the employees table, indicating the manager who conducted the performance review.

Employees ↔ Employee Leave

One-to-Many: An employee can have multiple leave records, but each leave record belongs to one employee.
Relationship: The employee_id in the employee_leave table is a foreign key referencing emp_id in the employees table.

Queries & Analysis

Employee Demographics & Department Overview

1. Total Number of Employees in Each Department: This query gives the count of employees in each department, which is useful for understanding the size of each department.

SELECT 
    department_name,
    COUNT(emp_id) AS 'Total Employee Cnt per Department'
FROM
    employees
        INNER JOIN
    departments ON employees.department_id = departments.dep_id
GROUP BY department_name;

2. Department with the Most Employees: This query identifies the department with the highest number of employees.

SELECT 
    d.department_name, COUNT(e.emp_id) AS Employees_cnt
FROM
    departments d
        INNER JOIN
    employees e ON d.dep_id = e.department_id
GROUP BY department_name
ORDER BY Employees_cnt DESC
LIMIT 1;

3. Employees Who Have Worked for More Than 5 Years: This query returns employees who have been with the company for more than five years.

SELECT 
    emp_id,
    first_name,
    last_name,
    joining_date,
    ROUND(DATEDIFF(CURDATE(), joining_date) / 365,
            0) AS Years_Of_Service
FROM
    employees
WHERE
    DATEDIFF(CURDATE(), JOINING_DATE) / 365 > 5
ORDER BY Years_Of_Service DESC;

4. Department with the Highest Average Performance Score: This query identifies the department with the best overall performance scores.

SELECT 
    d.department_name,
    ROUND(AVG(ep.performance_score), 1) AS Avg_Performance
FROM
    departments d
        INNER JOIN
    employees e ON d.dep_id = e.department_id
        INNER JOIN
    employee_performance ep ON e.emp_id = ep.employee_id
GROUP BY d.department_name
ORDER BY Avg_Performance DESC
LIMIT 1;

Salary & Compensation

5.Average Salary by Department: This query calculates the average salary in each department.

SELECT 
    department_name, ROUND(AVG(salary), 2) AS 'Avg Salary'
FROM
    departments
        INNER JOIN
    employees ON employees.department_id = departments.dep_id
GROUP BY department_name
ORDER BY department_name;

6. Highest and Lowest Salary in Each Department: This query provides the highest and lowest salaries in each department, helping identify salary disparities.

SELECT 
    department_name,
    MAX(salary) AS 'Highest Salary',
    MIN(salary) AS 'Lowest Salary'
FROM
    employees
        INNER JOIN
    departments ON employees.department_id = departments.dep_id
GROUP BY department_name;

7. Employees with Salary Greater than the Department Average: This query finds employees earning more than the average salary in their department.

SELECT 
    e.emp_id,
    e.first_name,
    e.last_name,
    e.salary,
    d.department_name
FROM
    employees e
        INNER JOIN
    departments d ON e.department_id = d.dep_id
WHERE
    e.salary > (SELECT 
            AVG(salary)
        FROM
            employees
        WHERE
            e.department_id = d.dep_id
            );

8. Total Salary Expenditure by Department: This query calculates the total salary expenditure for each department, useful for budget planning and analysis.

SELECT 
    d.department_name, SUM(e.salary) AS Total_salary
FROM
    departments d
        INNER JOIN
    employees e ON d.dep_id = e.department_id
GROUP BY d.department_name
ORDER BY Total_salary DESC;

Employee Performance & Engagement

9. Employees with a Performance Score of 5: This query lists employees who have received a top performance score.

SELECT 
    e.emp_id, e.first_name, e.last_name, ep.Performance_Score
FROM
    employees e
        JOIN
    employee_Performance ep ON e.emp_id = ep.employee_ID
WHERE
    ep.Performance_Score = 5;

10. Average Performance Score by Department: This query calculates the average performance score for each department.

SELECT 
    d.department_name,
    ROUND(AVG(ep.performance_score), 1) AS avg_performance_score
FROM
    departments d
        INNER JOIN
    employees e ON d.dep_id = e.department_id
        INNER JOIN
    employee_performance ep ON e.emp_id = ep.employee_id
GROUP BY d.department_name
ORDER BY avg_performance_score DESC;

11. Employees with the Most Leaves Taken: This query identifies the employee with the most leave days.

SELECT 
    e.emp_id,
    e.first_name,
    e.last_name,
    COUNT(el.leave_id) AS Total_Leaves
FROM
    employees e
        INNER JOIN
    employee_leave el ON e.emp_id = el.employee_id
GROUP BY e.emp_id , e.first_name , e.last_name
ORDER BY Total_Leaves DESC
LIMIT 1;

12. Employees with No Leaves Taken: This query lists employees who have not taken any leaves.

SELECT 
    e.emp_id, e.first_name, e.last_name
FROM
    employees e
        LEFT JOIN
    employee_leave el ON e.emp_id = el.employee_id
WHERE
    el.employee_id IS NULL;

Employee Management

13. List of Employees with Their Manager's Name: This query provides a list of employees along with their manager's name, which is helpful for organizational structure analysis.

SELECT 
    e.emp_id,
    e.first_name AS employee_first_name,
    e.last_name AS employee_last_name,
    m.first_name AS manager_first_name,
    m.last_name AS manager_last_name
FROM
    departments d
        INNER JOIN
    employees e ON d.dep_id = e.department_id
        LEFT JOIN
    employees m ON d.manager_id = m.emp_id;

Conclusion

This project helps in the evaluation of various aspects of employee data, such as their demographics, compensation, performance, and leave patterns. By analyzing this data, businesses can gain valuable insights into workforce management, salary structures, performance, and overall employee engagement.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

README.md

README.md

Employee Data Analysis Project

Project Overview

Schema Description

1. `departments` Table

2. `employees` Table

3. `salaries` Table

4. `employee_performance` Table

5. `employee_leave` Table

Relationships

Employees ↔ Departments

Employees ↔ Salaries

Employees ↔ Employee Performance

Employees ↔ Employee Leave

Queries & Analysis

Employee Demographics & Department Overview

Salary & Compensation

Employee Performance & Engagement

Employee Management

Conclusion

Files

README.md

Latest commit

History

README.md

File metadata and controls

Employee Data Analysis Project

Project Overview

Schema Description

1. departments Table

2. employees Table

3. salaries Table

4. employee_performance Table

5. employee_leave Table

Relationships

Employees ↔ Departments

Employees ↔ Salaries

Employees ↔ Employee Performance

Employees ↔ Employee Leave

Queries & Analysis

Employee Demographics & Department Overview

Salary & Compensation

Employee Performance & Engagement

Employee Management

Conclusion

1. `departments` Table

2. `employees` Table

3. `salaries` Table

4. `employee_performance` Table

5. `employee_leave` Table