Employee Management System (EMS) - SQL Project

📌 Project Overview

The Employee Management System (EMS) is a MySQL-based database project created to manage and analyze employee-related information.

The project contains employee, department, job, attendance, leave, performance, salary, and HR-related data.

The main purpose of this project is to practice and demonstrate SQL skills using a real-world business scenario.

---

🎯 Project Objectives

• Manage employee information
• Store department and job details
• Track employee attendance and leaves
• Store employee performance information
• Analyze employee salaries
• Perform business-related data analysis
• Practice advanced SQL concepts
• Understand database objects and transaction management

---

🛠️ Technologies Used

• MySQL
• MySQL Workbench
• SQL

---

📂 Project Structure

File	Description
01_database.sql	Creates the EMS database
02_tables.sql	Creates tables and defines constraints
03_insert_data.sql	Inserts sample data
04_queries.sql	Contains SQL analysis and practice queries
05_views.sql	Creates reusable SQL views
06_stored_procedures.sql	Creates stored procedures
07_triggers.sql	Creates database triggers
08_transactions.sql	Demonstrates transactions and savepoints
09_indexes.sql	Creates and analyzes indexes

---

🗄️ Database Tables

The project contains the following main tables:

1. Departments

Stores department information such as:

• Department ID
• Department Name
• Location

2. Jobs

Stores job-related information such as:

• Job ID
• Job Title
• Minimum Salary
• Maximum Salary

3. Employees

Stores employee information such as:

• Employee ID
• First Name
• Last Name
• Gender
• Email
• Phone
• Hire Date
• Salary
• Department
• Job
• Manager

4. Attendance

Stores employee attendance records.

5. Leaves

Stores employee leave information.

6. Performance

Stores employee performance reviews and ratings.

---

🔑 SQL Concepts Covered

Basic SQL

• SELECT
• WHERE
• ORDER BY
• DISTINCT
• LIKE
• IN
• BETWEEN

Aggregate Functions

• COUNT()
• SUM()
• AVG()
• MIN()
• MAX()

Grouping

• GROUP BY
• HAVING

Joins

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• CROSS JOIN
• SELF JOIN

Advanced Queries

• Subqueries
• Common Table Expressions (CTEs)
• CASE statements
• Window Functions
• RANK()
• DENSE_RANK()
• ROW_NUMBER()
• LAG()
• LEAD()

Database Objects

• Views
• Stored Procedures
• Triggers
• Indexes

Transaction Management

• START TRANSACTION
• COMMIT
• ROLLBACK
• SAVEPOINT
• ROLLBACK TO SAVEPOINT

Query Optimization

• CREATE INDEX
• DROP INDEX
• UNIQUE INDEX
• Composite Index
• EXPLAIN

---

👁️ Example SQL Analysis

Find the highest-paid employee

SELECT *
FROM employees
WHERE salary = (
    SELECT MAX(salary)
    FROM employees
);