Introduction
The Employee Management System is an SQL-based project that streamlines managing employee and department data, tracks performance reviews, and generates insights through queries. It uses key SQL concepts like database creation, table relationships, joins, and window functions to analyze employee performance, salary, and department metrics. This system ensures efficient data management and provides insights for strategic HR decision-making.
Project Overview
Database Creation: employee_management
Tables
departments: Stores information about departments (name, location).
employees: Stores employee data (personal details, hire date, salary, job title, department).
performance_reviews: Tracks performance reviews with ratings and comments.
Database Creation
We begin by creating the employee_management database, which stores employee, department, and performance review data.
-- CREATING DATABASE
CREATE DATABASE employee_management;
USE employee_management;
Table Creation
Next, we create three tables: departments, employees, and performance_reviews, each linked via foreign keys to model relationships between employees, their departments, and performance reviews.
-- CREATING TABLES
CREATE TABLE departments(
department_id INT PRIMARY KEY AUTO_INCREMENT,
department_name VARCHAR(50) NOT NULL,
location VARCHAR(100)
);
CREATE TABLE employees(
employee_id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
hire_date DATE NOT NULL,
salary DECIMAL(10,2),
department_id INT,
job_title VARCHAR(50),
FOREIGN KEY (department_id) REFERENCES departments(department_id)
);
CREATE TABLE performance_reviews(
review_id INT PRIMARY KEY AUTO_INCREMENT,
employee_id INT,
review_date DATE NOT NULL,
rating INT CHECK (rating BETWEEN 1 AND 5),
comments TEXT,
FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
);
Data Insertion
Once the tables are created, we can insert sample data into the departments, employees, and performance_reviews tables.
-- INSERTING DATA
-- Departments Table
INSERT INTO departments(department_name, location) VALUES
('Sales', 'New York'),
('HR', 'Los Angeles'),
('IT', 'San Francisco');
-- Employees Table
INSERT INTO employees(first_name, last_name, email, hire_date, salary, department_id, job_title)
VALUES
('Maurine', 'Nyongesa', 'maurine.nyongesa@gmail.com', '2021-03-15', 55000, 1, 'Sales Executive'),
('Asa', 'Moh', 'asa.moh@gmail.com', '2020-06-30', 60000, 2, 'HR Manager'),
('Steve', 'Jobs', 'steve.jobs@gmail.com', '2019-08-10', 75000, 3, 'IT Specialist');
-- Performance Reviews Table
INSERT INTO performance_reviews(employee_id, review_date, rating, comments) VALUES
(1, '2023-06-15', 4, 'Great performance but needs improvement in client interaction.'),
(2, '2023-05-12', 5, 'Excellent management skills, highly recommended for promotion.'),
(3, '2023-07-20', 3, 'Satisfactory performance but needs more technical training.');
Querying Data
Below are SQL queries used to retrieve, update, and analyze the data in the Employee Management System.
Basic Queries
Retrieve all employee details:
SELECT * FROM employees;
Update an employee's salary:
UPDATE employees
SET salary = 65000
WHERE employee_id = 1;
Add a new employee:
INSERT INTO employees (first_name, last_name, email, hire_date, salary, job_title, department_id)
VALUES ('Jane', 'Doe', 'jane.doe@email.com', '2023-05-15', 55000, 'Marketing Specialist', 2);
Advanced Queries
Joining Tables: Retrieve all employees with their department names.
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;
Salary Analysis: Find employees with a salary above 60,000.
SELECT first_name, last_name
FROM employees
WHERE salary > 60000;
Performance Review: Retrieve performance reviews for a specific employee.
SELECT e.first_name, e.last_name, p.review_date, p.rating, p.comments
FROM employees e
JOIN performance_reviews p ON e.employee_id = p.employee_id
WHERE e.employee_id = 1;
Salary Analysis by Department: Calculate the average salary for each department
This Query the average salary for each department and shows it alongside each employee's information.
SELECT AVG(e.salary) AS average_salary, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name;
Employee Ranking by Salary:
SELECT first_name, last_name, salary,
RANK() OVER (ORDER BY salary) AS salary_rank
FROM employees;
Cumulative Salary:
SELECT first_name, last_name, salary,
SUM(salary) OVER (ORDER BY salary) AS cumulative_salary
FROM employees;
Row Number Based on Hire Date:
This assigns a row number to each employee, sorted by the date they were hired.
It’s useful for scenarios where you want to track the order of employee hiring.
SELECT first_name, last_name, hire_date,
ROW_NUMBER() OVER (ORDER BY hire_date) AS row_num
FROM employees;
Performance Review Trend using Lead and Lag Functions:
This query shows an employee's current review rating along with the previous and next ratings (if they exist).
It uses the LAG() function to pull the previous review and LEAD() to pull the next review, within the same employee’s records
SELECT employee_id, review_date, rating,
LAG(rating) OVER (PARTITION BY employee_id ORDER BY review_date) AS prev_rating,
LEAD(rating) OVER (PARTITION BY employee_id ORDER BY review_date) AS next_rating
FROM performance_reviews;
Percentile Ranking of Salaries:
This query calculates the percentile rank of each employee’s salary compared to others.
SELECT first_name, last_name, salary,
PERCENT_RANK() OVER (ORDER BY salary) AS percent_rank_salary
FROM employees;
Conclusion
The Employee Management System demonstrates how SQL can be leveraged to efficiently manage and analyze employee and department data. By utilizing core SQL concepts such as database creation, table relationships, and advanced queries like window functions and joins, this system offers valuable insights into employee performance, salary distributions, and department metrics. This project showcases the powerful capabilities of SQL in organizing and extracting data, aiding
in data-driven decision-making for HR and management.
You can explore more of my work on LinkedInand find the complete repository for this Employee Management System project on Github
Top comments (0)