SQL Programming Resources on Telegram: “🚀 SQL Project Series #9 HR Analytics Project 👨…”

🚀 SQL Project Series #9

HR Analytics Project 👨‍💼

Analyze employee data to understand workforce trends, employee performance, attrition, hiring, salaries, and organizational health using SQL.

🎯 Business Objectives

✅ Analyze employee demographics

✅ Measure employee attrition

✅ Track hiring trends

✅ Analyze salaries and compensation

✅ Evaluate department performance

✅ Monitor attendance and leave patterns

✅ Identify high-performing employees

✅ Generate HR dashboards

📂 Step 1: Create Database

CREATE DATABASE hr_analytics_db;

USE hr_analytics_db;

📂 Step 2: Create Departments Table

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100)
);

📂 Step 3: Create Employees Table

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    gender VARCHAR(10),
    age INT,
    department_id INT,
    designation VARCHAR(100),
    salary DECIMAL(10,2),
    hire_date DATE,
    city VARCHAR(50),
    employment_status VARCHAR(20),
    FOREIGN KEY (department_id)
        REFERENCES departments(department_id)
);

📂 Step 4: Create Attendance Table

CREATE TABLE attendance (
    attendance_id INT PRIMARY KEY,
    employee_id INT,
    attendance_date DATE,
    status VARCHAR(20),
    FOREIGN KEY (employee_id)
        REFERENCES employees(employee_id)
);

📂 Step 5: Create Performance Table

CREATE TABLE performance (
    review_id INT PRIMARY KEY,
    employee_id INT,
    review_year INT,
    performance_rating DECIMAL(3,2),
    bonus DECIMAL(10,2),
    FOREIGN KEY (employee_id)
        REFERENCES employees(employee_id)
);

📂 Step 6: Insert Sample Departments

INSERT INTO departments VALUES
(1,'Engineering'),
(2,'Human Resources'),
(3,'Finance'),
(4,'Sales'),
(5,'Marketing');

📂 Step 7: Insert Sample Employees

INSERT INTO employees VALUES
(101,'Rahul Sharma','Male',30,1,'Software Engineer',85000,'2022-01-15','Mumbai','Active'),
(102,'Priya Verma','Female',28,4,'Sales Executive',65000,'2023-03-10','Delhi','Active'),
(103,'Amit Patel','Male',35,3,'Financial Analyst',92000,'2021-06-20','Pune','Active'),
(104,'Sneha Joshi','Female',31,2,'HR Manager',78000,'2020-11-12','Bangalore','Active'),
(105,'Rohan Gupta','Male',29,5,'Marketing Specialist',70000,'2024-02-01','Hyderabad','Resigned');

📂 Step 8: Insert Sample Attendance

INSERT INTO attendance VALUES
(1,101,'2025-01-01','Present'),
(2,102,'2025-01-01','Present'),
(3,103,'2025-01-01','Absent'),
(4,104,'2025-01-01','Present'),
(5,105,'2025-01-01','Leave');

📂 Step 9: Insert Sample Performance Data

INSERT INTO performance VALUES
(1,101,2024,4.8,20000),
(2,102,2024,4.3,12000),
(3,103,2024,4.9,25000),
(4,104,2024,4.5,18000),
(5,105,2024,3.9,10000);