Employee Payroll System

Employee Payroll System

(Using Salary Calculation Logic)

STEP 1: Create Database

Create a database to store payroll data.

				
					CREATE DATABASE payroll_db;

-- Verify database creation
SHOW DATABASES;

STEP 2: Use Database

Select the payroll database.

				
					USE payroll_db;

-- Check current database
SELECT DATABASE();

STEP 3: Create Employees Table

This table stores employee details.

employee_id → unique ID
employee_name → name
basic_salary → fixed base salary

				
					CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    employee_name VARCHAR(100),
    basic_salary DECIMAL(10,2)
);

-- Check structure
DESC employees;

STEP 4: Insert Employees

				
					INSERT INTO employees (employee_name, basic_salary) VALUES
('Aman', 20000),
('Priya', 25000),
('Rahul', 18000);

-- View employees
SELECT * FROM employees;

STEP 5: Create Attendance Table

This table stores attendance for salary calculation.

Assumption:

Total working days in month = 30
Salary deduction based on absent days

				
					CREATE TABLE attendance (
    employee_id INT PRIMARY KEY,
    total_days INT,
    present_days INT,
    absent_days INT GENERATED ALWAYS AS (total_days - present_days) STORED,

    FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
);

-- Check structure
DESC attendance;

STEP 6: Insert Attendance Data

				
					INSERT INTO attendance (employee_id, total_days, present_days) VALUES
(1, 30, 28),
(2, 30, 30),
(3, 30, 25);

-- View attendance
SELECT * FROM attendance;
--Students will see absent_days calculated automatically.

STEP 7: Create Salary Table

This table calculates:

Allowance (20% of basic salary)
Deduction (Salary deduction per absent day)
Net salary

Salary deduction formula:
(basic_salary / total_days) × absent_days

				
					CREATE TABLE salary (
    employee_id INT PRIMARY KEY,

    allowance DECIMAL(10,2),

    deduction DECIMAL(10,2),

    net_salary DECIMAL(10,2),

    FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
);

-- Check structure
DESC salary;

STEP 8: Insert Salary Data (With Calculation)

We calculate values while inserting using SELECT.

				
					INSERT INTO salary (employee_id, allowance, deduction, net_salary)
SELECT 
    employees.employee_id,
    employees.basic_salary * 0.20 AS allowance,
    (employees.basic_salary / attendance.total_days) * attendance.absent_days AS deduction,
    (employees.basic_salary 
        + (employees.basic_salary * 0.20)
        - ((employees.basic_salary / attendance.total_days) * attendance.absent_days)
    ) AS net_salary
FROM employees
JOIN attendance
ON employees.employee_id = attendance.employee_id;

-- View salary table
SELECT * FROM salary;

STEP 9: View Complete Payroll Report

Combine all tables to display full salary details.

				
					SELECT employees.employee_name,
       employees.basic_salary,
       attendance.present_days,
       attendance.absent_days,
       salary.allowance,
       salary.deduction,
       salary.net_salary
FROM employees
JOIN attendance ON employees.employee_id = attendance.employee_id
JOIN salary ON employees.employee_id = salary.employee_id;

-- Complete payroll report displayed

🎓 What Students Learn From This Project

Multiple tables
Foreign keys
Generated columns
Salary calculation logic
Mathematical expressions in SQL
INSERT using SELECT
JOIN of three tables