SQL Projects For Beginners: Employee Payroll System

Employee Payroll System:

An Employee Payroll System is a software solution designed to automate and manage the financial records of employees. This system allows for efficient handling of salary payments, deductions, bonuses, taxes, and other payroll-related transactions. It simplifies the payroll process by ensuring accuracy, transparency, and timely payment to employees, reducing the likelihood of human errors associated with manual payroll management.

Creating the Database in MySQL or PostgreSQL:

Create the Database:

-- Create the database for the Payroll Management System
CREATE DATABASE EmpPayrollDB;
USE EmpPayrollDB;

Create the Tables:

Employees Table:

This structure describes the table columns for storing employee information in the Employee Payroll System.

Structure:

Code:

CREATE TABLE Employees (
    employee_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    employee_name VARCHAR(255) NOT NULL,
    department VARCHAR(100),
    position VARCHAR(100),
    hire_date DATE,
    base_salary DECIMAL(10, 2) NOT NULL
);

Attendance Table:

This structure describes the columns for tracking employee attendance in the Employee Payroll System.

Structure:

Code:

-- Create the Attendance table
CREATE TABLE Attendance (
    attendance_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    employee_id INT,
    attendance_date DATE,
    status ENUM('Present', 'Absent', 'Leave'),
    FOREIGN KEY (employee_id) REFERENCES Employees(employee_id)
);

Salaries Table:

This table stores employee salary details, including base salary, bonuses, deductions, and the specific month and year for the salary record.

Structure:

Code:

CREATE TABLE Salaries (
    salary_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    employee_id INT,
    base_salary DECIMAL(10, 2) NOT NULL,
    bonus DECIMAL(10, 2),
    deductions DECIMAL(10, 2),
    month VARCHAR(20),
    year INT,
    FOREIGN KEY (employee_id) REFERENCES Employees(employee_id)
); 

Payroll Table:

This table stores each employee's payroll record, including the total salary paid and the payment date.

Structure:

Code:

CREATE TABLE Payroll (
    payroll_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    employee_id INT,
    total_salary DECIMAL(10, 2),
    payment_date DATE,
    FOREIGN KEY (employee_id) REFERENCES Employees(employee_id)
);

Inserting Data:

Add some sample data to the tables.

Inserting Data into Employees Table:

INSERT INTO Employees (employee_name, department, position, hire_date, base_salary) VALUES 
('Alan Vince', 'Finance', 'Manager', '2020-01-15', 50000.00),
('Alex Kent', 'HR', 'HR Specialist', '2019-03-10', 40000.00);

Inserting Data into Attendance Table:

INSERT INTO Attendance (employee_id, attendance_date, status) VALUES 
(1, '2023-09-01', 'Present'),
(2, '2023-09-01', 'Leave');

Inserting Data into Salaries Table:

INSERT INTO Salaries (employee_id, base_salary, bonus, deductions, month, year) VALUES 
(1, 50000.00, 5000.00, 2000.00, 'September', 2023),
(2, 40000.00, 3000.00, 1000.00, 'September', 2023);

Basic Functionalities of Employee Payroll System:

• Add New Employees:
• Allow users to enter new employee records into the system (name, department, position, salary, etc.).
• Update Employee Information:
• Provide functionality to update existing employee details (e.g., position, salary, department).
• Delete Employee Records:
• Enable users to remove employee records when no longer needed or relevant.
• Track Employee Attendance:
• Record daily attendance for employees, marking them as present, absent, or on leave.
• Calculate Salary:.
• Automatically calculate employee salaries based on base salary, attendance, bonuses, and deductions.
• Manage Deductions and Bonuses:
• Allow users to add or update bonuses and deductions for each employee.
• Update Payroll Records:
• Calculate and store payroll data for each employee, including total salary and payment date.
• Generate Pay Slips:
• Provide functionality to generate detailed pay slips for employees, showing salary breakdowns.
• Update Stock Quantities:
• Allow manual updates to stock levels after receiving new inventory or making sales.
• Generate Payroll Reports:
• Create payroll summaries and reports, such as overall salary expenses, employee attendance, or monthly payroll distributions.

Writing Queries for Functionality:

Query-1: Add New Employee

-- Insert a new record into the Employees table
-- The employee's name is 'Jennith Kery'
-- The department is 'Sales'
-- The position is 'Sales Executive'
-- The hire date is '2023-10-01'
-- The base salary is 30000.00

INSERT INTO Employees (employee_name, department, position, hire_date, base_salary) 
VALUES ('Jennith Kery', 'Sales', 'Sales Executive', '2023-10-01', 30000.00);

Explanation:

This SQL statement inserts a new employee record into the Employees table. The employee_name field is set to "Jennith Kery," indicating the employee's name. The employee is assigned to the "Sales" department with the position of "Sales Executive." The hire_date is set to "2023-10-01," marking the employee's starting date, and the base_salary is defined as 30,000.00. Each value is mapped to the corresponding column in the Employees table, and this operation will create a new row in the table with this information.

Output:

Select * from Employees;
employee_id	employee_name	department	position	hire_date	base_salary
1	Alan Vince	Finance	Manager	2020-01-15	50000.00
2	Alex Kent	HR	HR Specialist	2019-03-10	40000.00
3	Jennith Kery	Sales	Sales Executive	2023-10-01	30000.00

Query-2: Update Employee Information

-- Update the base salary of an employee in the Employees table
-- Set the base salary to 55000.00 for the employee with employee_id = 1
	
UPDATE Employees 
SET base_salary = 55000.00 
WHERE employee_id = 1;

Explanation:

This SQL query updates the base_salary of a specific employee in the Employees table. It changes the base salary to 55,000.00 for the employee whose employee_id is 1. The UPDATE statement modifies existing records, and in this case, only the record that matches employee_id = 1 will have its base_salary updated to the new value. This ensures that only the intended employee's salary is modified while the rest of the information remains unchanged.

Output:

Select * from Employees;
employee_id	employee_name	department	position	hire_date	base_salary
1	Alan Vince	Finance	Manager	2020-01-15	55000.00
2	Alex Kent	HR	HR Specialist	2019-03-10	40000.00

Query-3: Delete Employee Records

-- Delete an employee record from the Employees table
-- The record with employee_id = 3 will be removed from the table
DELETE FROM Employees 
WHERE employee_id = 3; 

Explanation:

This SQL query deletes a specific employee's record from the Employees table. The DELETE statement removes the record where the employee_id is 3. This operation permanently deletes the entire row associated with this employee, including all their details such as name, department, position, salary, and hire date. It is important to use a WHERE clause to ensure only the intended employee record is deleted, preventing unintentional data loss.

Output:

employee_id	employee_name	department	position	hire_date	base_salary
1	Alan Vince	Finance	Manager	2020-01-15	50000.00
2	Alex Kent	HR	HR Specialist	2019-03-10	40000.00

Query-4: Track Attendance

-- Insert a new attendance record into the Attendance table
-- employee_id = 1 indicates the employee being tracked
-- attendance_date = '2023-09-02' is the date for which the attendance is being recorded
-- status = 'Present' indicates the attendance status of the employee on that day
INSERT INTO Attendance (employee_id, attendance_date, status) 
VALUES (1, '2023-09-02', 'Present'); 

Explanation:

This query inserts a new record into the Attendance table, marking the attendance for an employee. The employee with employee_id = 1 is recorded as being "Present" on the date 2023-09-02. The INSERT INTO statement is used to add this information into the table, specifying the relevant columns: employee_id, attendance_date, and status. This is useful for tracking employees' daily attendance.

Output:

Select * from Attendance;
attendance_id	employee_id	attendance_date	status
1	1	2023-09-01	Present
2	2	2023-09-01	Leave
3	1	2023-09-02	Present

Query-5: Salary Calculation

	-- Select the employee_id and calculate total salary for the specified employee
-- The total salary is calculated as: base_salary + bonus - deductions
-- FROM the Salaries table
-- WHERE employee_id = 1 (for employee with ID 1)
-- AND for the month of 'September' and the year 2023

SELECT employee_id, (base_salary + bonus - deductions) AS total_salary
FROM Salaries
WHERE employee_id = 1 AND month = 'September' AND year = 2023;

Explanation:

This query calculates the total salary for the employee with employee_id = 1 for the month of September in the year 2023. The SELECT statement retrieves the employee's employee_id along with the calculated total salary. The total salary is derived from adding the base_salary and bonus and then subtracting any deductions. The query filters records using the WHERE clause to ensure it fetches the salary details only for September 2023. This query is useful for generating salary information for a specific employee within a given time frame.

Output:

employee_id	total_salary
1	53000.00

Query-6: Update Payroll Table

-- Insert a new record into the Payroll table
-- The record will include employee_id, total_salary, and payment_date
-- employee_id is 1 (for the employee with ID 1)
-- total_salary is 53000.00 (calculated total salary for the employee)
-- payment_date is '2023-09-30' (the date the salary was paid)

INSERT INTO Payroll (employee_id, total_salary, payment_date) VALUES 
(1, 53000.00, '2023-09-30');

Explanation:

This SQL INSERT statement is used to add a new record into the Payroll table for an employee with employee_id = 1. The inserted data includes the employee’s total salary of 53,000.00 and the payment date, which is September 30, 2023. This operation stores the payroll information, ensuring the employee's payment details, such as the amount and payment date, are tracked in the Payroll table. It is particularly useful after calculating an employee's salary and finalizing the payment process.

Output:

Select * from Payroll;

payroll_id	employee_id	total_salary	payment_date
1	1	53000.00	2023-09-30

Query-7: Generate Pay Slips

-- Select employee_name from the Employees table
-- Select base_salary, bonus, and deductions from the Salaries table
-- Select total_salary and payment_date from the Payroll table

SELECT e.employee_name, s.base_salary, s.bonus, s.deductions, p.total_salary, p.payment_date
-- Join Employees and Salaries tables based on employee_id
FROM Employees e
JOIN Salaries s ON e.employee_id = s.employee_id
-- Join Salaries and Payroll tables based on employee_id
JOIN Payroll p ON e.employee_id = p.employee_id
-- Filter to return only records for employee with employee_id = 1 and salary details for September 2023
WHERE e.employee_id = 1 AND s.month = 'September' AND s.year = 2023;

Explanation:

This SQL query retrieves a detailed pay slip for an employee with employee_id = 1 for the month of September 2023. It combines data from three tables: Employees, Salaries, and Payroll. The query selects the employee's name, base salary, bonus, deductions, total salary, and payment date. The JOIN clauses ensure that information from the related tables is pulled together using employee_id as the common field. The WHERE clause filters the results to show only the details of this specific employee and for the month of September in the year 2023.

Output:

Output not generated for insufficient data

Query-8: Generate Reports

-- Payroll summary for September 2023
SELECT e.employee_name, p.total_salary, p.payment_date
FROM Payroll p
JOIN Employees e ON p.employee_id = e.employee_id
WHERE p.payment_date BETWEEN '2023-09-01' AND '2023-09-30';

Explanation:

This SQL query retrieves a detailed pay slip for an employee with employee_id = 1 for the month of September 2023. It combines data from three tables: Employees, Salaries, and Payroll. The query selects the employee's name, base salary, bonus, deductions, total salary, and payment date. The JOIN clauses ensure that information from the related tables is pulled together using employee_id as the common field. The WHERE clause filters the results to show only the details of this specific employee and for the month of September in the year 2023.

Output:

Output not generated for insufficient data

Query-9: List Employees by Department

-- This query retrieves a list of employees, grouped by their department.
SELECT department, employee_name
-- Selects the department and employee name from the Employees table
FROM Employees
-- Retrieves data from the Employees table
ORDER BY department, employee_name;
-- Orders the results first by department and then by employee name within each department

Explanation:

This query retrieves a list of employees, grouped by their department, from the Employees table. The SELECT statement pulls the department and employee_name columns. The ORDER BY clause ensures that the results are sorted first by the department and then by the employee's name within each department, organizing the employees in alphabetical order under their respective departments. This allows for easy readability and understanding of how employees are distributed across various departments.

Output:

department	employee_name
Finance	Alan Vince
HR	Alex Kent

Query-10: View Attendance Summary for an Employee

-- Select employee_id and calculate the number of 'Present' days
SELECT employee_id, 
       -- Count the number of days where status is 'Present'
       SUM(CASE WHEN status = 'Present' THEN 1 ELSE 0 END) AS present_days,
       -- Count the number of days where status is 'Absent'
       SUM(CASE WHEN status = 'Absent' THEN 1 ELSE 0 END) AS absent_days,
       -- Count the number of days where status is 'Leave'
       SUM(CASE WHEN status = 'Leave' THEN 1 ELSE 0 END) AS leave_days
-- From the Attendance table
FROM Attendance
-- Filter records within the specified date range (September 2023)
WHERE attendance_date BETWEEN '2023-09-01' AND '2023-09-30'
-- Filter for a specific employee with employee_id = 1
AND employee_id = 1
-- Group the results by employee_id to summarize attendance for the employee
GROUP BY employee_id;

Explanation:

This query generates a summary of the number of days an employee (with employee_id = 1) was present, absent, or on leave during the month of September 2023. The CASE statements count each occurrence of 'Present', 'Absent', or 'Leave' by adding 1 for each match. The SUM function aggregates these counts to provide the total number of days in each status. The WHERE clause restricts the results to the specific date range ('2023-09-01' to '2023-09-30') and the specified employee. The results are grouped by employee_id to ensure one summarized row per employee.

Output:

employee_id	present_days	absent_days	leave_days
1	1	0	0

Query-11: List Employees with Salaries Above a Certain Threshold

-- This query lists all employees whose base salary is above a specified threshold.
SELECT employee_name, base_salary
-- Selects the employee_name and base_salary columns from the Employees table
FROM Employees
-- Filters records to include only those with a base_salary greater than 50000
WHERE base_salary > 50000;

Explanation:

This query retrieves the names and base salaries of all employees from the Employees table whose base salary exceeds 50,000. By specifying the condition in the WHERE clause, it ensures that only employees meeting this salary criterion are included in the results. This can be useful for identifying higher-earning employees, which might assist in salary reviews, budget planning, or performance evaluations.

Output:

Output not generated for insufficient data

Query-12: View Total Deductions for All Employees in a Given Month

-- This query calculates the total deductions for all employees in a specified month and year.
SELECT employee_id, SUM(deductions) AS total_deductions
-- Selects the employee_id and calculates the sum of deductions, renaming it as total_deductions
FROM Salaries
-- Retrieves data from the Salaries table
WHERE month = 'September' AND year = 2023
-- Filters the records to include only those from September 2023
GROUP BY employee_id;
-- Groups the results by employee_id to calculate the total deductions for each employee

Explanation:

This query calculates the total deductions for each employee for the month of September 2023. It does so by summing up the deductions column for each employee_id in the Salaries table, based on the specified month and year. The GROUP BY clause ensures that the sum is calculated individually for each employee. This query helps provide an overview of the total deductions applied to employees' salaries within a specific time period.

Output:

employee_id	total_deductions
1	2000.00
2	1000.00

Query-13: Calculate Average Salary for a Department

-- This query calculates the average salary for employees in a specific department.
SELECT department, AVG(base_salary) AS average_salary
-- Selects the department and calculates the average base salary, renaming it as average_salary
FROM Employees
-- Retrieves data from the Employees table
WHERE department = 'Sales'
-- Filters the records to include only those employees in the 'Sales' department
GROUP BY department;
-- Groups the results by department to calculate the average salary for the 'Sales' department

Explanation:

This query calculates the average base salary for employees within the 'Sales' department. The AVG() function is used to compute the average salary from the base_salary column. The GROUP BY clause ensures that the result is grouped by the department, so the average is calculated specifically for the 'Sales' department. This query helps to determine the overall average salary level for a particular department, in this case, 'Sales'.

Output:

Output not generated for insufficient data

Query-14: List Employees with Attendance Status on a Specific Date

-- This query lists the attendance status of all employees for a given date.
SELECT e.employee_name, a.status
-- Selects the employee name from the Employees table and the attendance status from the Attendance table
FROM Employees e
-- Retrieves data from the Employees table using the alias 'e'
JOIN Attendance a ON e.employee_id = a.employee_id
-- Joins the Employees and Attendance tables based on the matching employee_id in both tables
WHERE a.attendance_date = '2023-09-15';
-- Filters the records to show only the attendance status for the specific date '2023-09-15'

Explanation:

This query retrieves the attendance status for all employees on a specific date, which in this case is '2023-09-15'. The JOIN operation links the Employees and Attendance tables based on the employee_id. The query returns each employee's name and their attendance status (such as 'Present', 'Absent', or 'Leave') on that particular date. The WHERE clause ensures that only records for '2023-09-15' are shown. This query is useful for tracking employee attendance on a given day.

Output:

Output not generated for insufficient data

Query-15: Customers with Unpaid Orders

-- This query lists employees who do not have any attendance records for a specific month.
SELECT e.employee_name
-- Selects the employee name from the Employees table
FROM Employees e
-- Retrieves data from the Employees table using the alias 'e'
LEFT JOIN Attendance a ON e.employee_id = a.employee_id 
    AND MONTH(a.attendance_date) = 9 AND YEAR(a.attendance_date) = 2023
-- Performs a LEFT JOIN between Employees and Attendance tables; matches employee_id and filters records for September 2023
WHERE a.attendance_id IS NULL;
-- Filters the results to include only employees who do not have matching attendance records (attendance_id is NULL) for the specified month

Explanation:

This query retrieves the list of employees who do not have any attendance records for September 2023. It uses a LEFT JOIN to combine the Employees and Attendance tables based on employee_id. The JOIN also filters attendance records for September 2023 (based on the MONTH and YEAR functions). The WHERE clause ensures that only employees with no attendance records (where attendance_id is NULL) are included in the result. This query helps identify employees who were not marked as present, absent, or on leave during the specified month.

Output:

Output not generated for insufficient data

Query-16: View Payroll Summary by Department

-- This query provides the total salary paid to employees in each department for a given month.
SELECT e.department, SUM(p.total_salary) AS total_salary_paid
-- Selects the department and calculates the total salary paid by summing the total_salary from the Payroll table
FROM Payroll p
-- Retrieves data from the Payroll table using the alias 'p'
JOIN Employees e ON p.employee_id = e.employee_id
-- Joins the Employees table with the Payroll table based on matching employee_id to associate each salary with the employee's department
WHERE p.payment_date BETWEEN '2023-09-01' AND '2023-09-30'
-- Filters the records to include only payments made in September 2023
GROUP BY e.department;
-- Groups the results by department to get the total salary paid for each department

Explanation:

This query calculates the total salary paid to employees in each department for the month of September 2023. It sums the total_salary from the Payroll table for each department using the SUM() function. The JOIN operation matches each salary record in the Payroll table with the corresponding employee in the Employees table to access department information. The WHERE clause restricts the records to those with payment dates within September 2023. The query groups the results by department, giving the total salary paid for each department.

Output:

Output not generated for insufficient data

Query-17: List Employees with Bonuses in a Given Month

-- This query lists all employees who received a bonus in a specific month.
SELECT e.employee_name, s.bonus
-- Selects the employee's name and their corresponding bonus from the Employees and Salaries tables
FROM Employees e
-- Retrieves data from the Employees table using the alias 'e'
JOIN Salaries s ON e.employee_id = s.employee_id
-- Joins the Employees and Salaries tables on matching employee_id to associate bonuses with employee names
WHERE s.bonus > 0 
-- Filters the results to include only employees who received a bonus (bonus greater than 0)
AND s.month = 'September'
-- Filters the records to include only salaries for the month of September
AND s.year = 2023;
-- Filters the records to include only salaries for the year 2023

Explanation:

This query retrieves the names of employees who received a bonus during the month of September 2023. It uses a JOIN operation to combine the Employees and Salaries tables based on the employee_id field. The WHERE clause filters the results to include only those records where the bonus is greater than zero, ensuring that only employees with a bonus are listed. Additionally, it restricts the data to bonuses received in September 2023 by filtering the month and year fields in the Salaries table.

Output:

employee_name	bonus
Alan Vince	5000.00
Alex Kent	3000.00

Query-18: List Employees with Deducted Salaries in a Given Month

-- This query lists employees whose salary was deducted in a specific month, along with the deduction amount.
SELECT e.employee_name, s.deductions
-- Selects the employee's name and the corresponding deduction amount from the Employees and Salaries tables
FROM Employees e
-- Retrieves data from the Employees table using the alias 'e'
JOIN Salaries s ON e.employee_id = s.employee_id
-- Joins the Employees and Salaries tables on matching employee_id to associate deductions with employee names
WHERE s.deductions > 0 
-- Filters the results to include only employees who had salary deductions (deductions greater than 0)
AND s.month = 'September'
-- Filters the records to include only salary deductions for the month of September
AND s.year = 2023;
-- Filters the records to include only salary deductions for the year 2023

Explanation:

This query identifies employees whose salary was deducted during the month of September 2023. It uses a JOIN to connect the Employees and Salaries tables based on the employee_id, allowing the query to match employee names with their corresponding deductions. The WHERE clause ensures that only employees with deductions greater than zero are included, while also filtering the results to those related to the month of September in the year 2023. The result will list the employee name along with the deduction amount for that month.

Output:

employee_name	deductions
Alan Vince	2000.00
Alex Kent	1000.00

Query-19: Find the Employee with the Highest Salary

-- This query retrieves the employee with the highest base salary.
SELECT employee_name, base_salary
-- Selects the employee's name and base salary from the Employees table
FROM Employees
-- Retrieves data from the Employees table
ORDER BY base_salary DESC
-- Orders the results by base salary in descending order (highest salary first)
LIMIT 1;
-- Limits the results to only one record, showing the employee with the highest base salary

Explanation:

This query is designed to find the employee with the highest base salary. It selects the employee's name and base salary from the Employees table, then orders the records by the base_salary in descending order using ORDER BY. The DESC keyword ensures that the highest salary appears first. Finally, the LIMIT 1 clause restricts the output to just the top result, which corresponds to the employee with the highest base salary.

Output:

employee_name	base_salary
Alan Vince	50000.00

Query-20: Generate Yearly Salary Report

-- This query provides a yearly salary summary for each employee.
SELECT e.employee_name, SUM(s.base_salary + s.bonus - s.deductions) AS yearly_salary
-- Selects the employee's name and calculates the sum of base salary, bonus, and deductions for the year, representing the yearly salary
FROM Employees e
-- Retrieves data from the Employees table
JOIN Salaries s ON e.employee_id = s.employee_id
-- Joins the Employees table with the Salaries table based on matching employee IDs
WHERE s.year = 2023
-- Filters the data to only include records from the year 2023
GROUP BY e.employee_name;
-- Groups the results by employee name to summarize their salary data for the year

Explanation:

This query calculates the total yearly salary for each employee by summing the base salary, bonus, and deductions from the Salaries table. It selects the employee's name and calculates the yearly salary using SUM(). The JOIN clause links the Employees and Salaries tables based on the employee's ID, ensuring that salary details for each employee are retrieved. The WHERE clause restricts the query to data from the year 2023, and the results are grouped by employee name using GROUP BY, summarizing the salary data for each individual.

Output:

employee_name	yearly_salary
Alan Vince	53000.00
Alex Kent	42000.00

SQL Code Editor: