Employee Management System (MySQL)

A project-based Employee Management System built using MySQL Workbench. This project covers real-world database design, CRUD operations, payroll processing, and interview-ready SQL queries.

---

📌 Project Overview

This system manages:

• Employees
• Departments
• Job Roles
• Attendance
• Salaries
• Payroll (Monthly Salary Slips)

It is suitable for:

• SQL Beginners
• Internship / Fresher Interviews
• College Mini Projects
• Backend / Database Practice

---

🛠️ Tech Stack

• Database: MySQL

• Tool: MySQL Workbench

• SQL Concepts:

  • DDL (CREATE, ALTER)
  • DML (INSERT, UPDATE, DELETE)
  • Joins
  • Constraints
  • Subqueries
  • Aggregation

---

🗂️ Database Schema

Tables Used:

1. departments
2. jobs
3. employees
4. attendance
5. salaries
6. payroll

All tables are connected using Primary Keys and Foreign Keys following real company standards.

---

🧱 Table Structure (Summary)

employees

• emp_id (PK)
• first_name
• last_name
• email
• phone
• hire_date
• dept_id (FK)
• job_id (FK)

payroll

• payroll_id (PK)
• emp_id (FK)
• salary_month
• basic_salary
• bonus
• deductions
• net_salary
• generated_on

---

🔄 CRUD Operations

Create

INSERT INTO employees (first_name, last_name, email, hire_date, dept_id, job_id)
VALUES ('Rahul', 'Mehta', 'rahul@gmail.com', '2025-01-01', 1, 2);

Read

SELECT * FROM employees;

Update

UPDATE employees SET dept_id = 2 WHERE emp_id = 1;

Delete

DELETE FROM employees WHERE emp_id = 5;

---

💰 Payroll Logic

Net Salary Formula:

Net Salary = Basic Salary + Bonus – Deductions

UPDATE payroll
SET net_salary = basic_salary + bonus - deductions;

---

📊 Sample Reports

Monthly Company Expense

SELECT salary_month, SUM(net_salary) AS total_expense
FROM payroll
GROUP BY salary_month;

Highest Paid Employee

SELECT e.first_name, p.net_salary
FROM payroll p
JOIN employees e ON p.emp_id = e.emp_id
ORDER BY p.net_salary DESC
LIMIT 1;

---

🔐 Data Safety Rules

• One salary per employee per month

UNIQUE (emp_id, salary_month)

• Employee must exist before payroll generation
• Salary history preserved using proper foreign keys

---

🎯 Interview Coverage

This project helps answer:

• What is Primary & Foreign Key?
• How do JOINs work?
• How to prevent duplicate records?
• How payroll systems work in real companies?
• Difference between DELETE vs TRUNCATE

---

🚀 How to Run

1. Open MySQL Workbench
2. Create database
3. Run table creation scripts
4. Insert sample data
5. Execute queries from this project

---

👨‍🎓 Author

Prathamesh Vekhande B.Sc. IT Graduate SQL | MySQL | Backend Fundamentals

---

⭐ If you find this project useful

Give it a ⭐ on GitHub and use it for learning & interviews.