Menu
Temporary named result sets for cleaner and readable queries
Useful for recursive queries and breaking complex queries into parts
Example highlight: “Used recursive CTEs to handle hierarchical data retrieval.”
INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN
Show how you combined data across tables efficiently
Virtual tables to simplify complex queries
Security benefits by restricting direct table access
Storing intermediate results temporarily during complex processing
Using INSERT, UPDATE, DELETE with joins or subqueries for batch operations
This project is a database-driven Library Management System designed to efficiently manage book lending, member registration, employee records, and library operations.
It automates common library tasks such as issuing books, returning books, tracking overdue items, calculating income, and generating performance reports for library branches.
The project leverages MySQL stored procedures, triggers, joins, views, and subqueries to provide dynamic data management and reporting features.
Automate Book Lending Operations:
Simplify issuing and returning books with proper status updates.
Track Library Activity:
Maintain accurate records of book issues, returns, overdue books, and member activity.
Generate Insights and Reports:
Provide summarized data like total revenue, top-performing employees, and branch performance.
Ensure Data Integrity:
Use relational constraints, stored procedures, and controlled updates to ensure reliable data.
Enhance Efficiency:
Replace manual processes with automated SQL procedures and queries.
| Table Name | Description |
|---|---|
| Books | Stores details of books (ISBN, title, category, rental price, status). |
| Issued_Status | Records details of each book issue transaction (issued_id, member, employee, date). |
| Return_Status / Returned | Stores information on book returns (return_id, return_date, book details). |
| Members | Contains member information (member_id, name, registration date, etc.). |
| Employees | Contains employee details (emp_id, name, position, branch_id, etc.). |
| Branch | Represents library branches (branch_id, name, manager, etc.). |
Objective:
To display all books that belong to a specific genre or category from the library database.
Description:
This query filters records from the Books table based on the category column. By specifying a category (e.g., ‘Classic’), it retrieves all relevant books, including details such as the book title, author, ISBN, rental price, and availability status.
SQL Query:
Objective:
To calculate the total rental income generated by each book category and identify how many books were issued in each category.
Description:
This query joins the Issued_Status and Books tables to connect issued transactions with their respective book details. It then groups the results by category and uses aggregate functions —
SUM(b.rental_price) to compute the total rental income, and
COUNT(*) to count the number of books issued per category.
Purpose & Outcome:
Provides a summary of income generated by each category.
Helps management understand which genres contribute most to the library’s revenue.
Supports data-driven decisions for inventory and pricing strategies.
Objective:
To identify and list all members who have joined the library within the past 180 days.
Description:
This query filters records from the Members table based on the reg_date (registration date). By comparing each member’s registration date with the current system date, it retrieves only those members whose registration falls within the last 180 days.
Purpose & Outcome:
Helps the library analyze recent membership growth.
Useful for identifying new or active members for promotional activities or feedback.
Supports decision-making regarding membership trends and engagement.
SQL Query:
Objective:
To display a detailed list of employees along with their branch information and the name of their respective branch managers.
Description:
This query joins the Employees and Branch tables to link each employee with their assigned branch. It also performs a self-join on the Employees table to retrieve the manager’s name (since both employees and managers are stored in the same table). The result provides a comprehensive view of employee roles, salaries, branch details, and managerial hierarchy.
Purpose & Outcome:
Provides a clear organizational structure for each branch.
Useful for HR and administrative reporting.
Helps track which employees report to which managers and in which branch they work.
Strengthens internal management visibility and coordination.
SQL Query:
Objective:
To generate a new table that contains only books with a rental price higher than a specified amount (in this case, greater than 7.00).
Description:
This query creates a new table called bookOverSeven by selecting all records from the Books table where the rental price exceeds $7.00. This allows for easy access and further analysis of higher-value books in the collection.
Purpose & Outcome:
Helps identify premium or high-value books in the inventory.
Enables targeted marketing or special handling for these books.
Improves data organization by creating a focused subset for reporting or analysis.
SQL Query:
Objective:
To identify and list all books that have been issued but not yet returned by members.
Description:
This query performs a left join between the Issued_Status and Return_Status tables, matching records on issued_id. It filters for cases where there is no corresponding return record (return_id IS NULL), effectively listing all books currently outstanding.
Purpose & Outcome:
Helps library staff track overdue or unreturned books.
Enables timely follow-up with members to ensure book returns.
Supports maintaining accurate inventory and availability status.
SQL Query:
Objective:
To identify members who have books overdue by more than 365 days and create a view for easy reference.
Description:
This query creates a database view named OverDue3 that lists members with books not yet returned after a one-year borrowing period. It joins the Issued_Status, Members, and Books tables to display member details, book titles, issue dates, and calculates how many days the book is overdue. It uses a left join with Return_Status to exclude books that have already been returned.
SQL Query:
Objective:
To automate the process of updating the book’s availability status to “no” when it is issued, ensuring real-time tracking of book availability.
Description:
This stored procedure, Issue_status, performs the following steps:
Checks if the requested book’s status is available ('yes').
If available, inserts a new record into the Issued_Status table logging the issue transaction.
Updates the corresponding book’s status in the Books table to 'no', marking it as unavailable.
Returns a confirmation message to acknowledge the successful borrowing of the book.
If the book is not available, it returns a message indicating unavailability.
Purpose & Outcome:
Ensures that book availability is immediately reflected upon issuing.
Prevents double-issuing the same book.
Enhances the reliability and integrity of the library management system.
Provides clear user feedback on the borrowing status.
SQL Stored Procedure:
Objective:
To automate the process of updating a book’s availability status to “yes” when it is returned, ensuring accurate tracking of book inventory.
Description:
This stored procedure, return_status, handles book returns by:
Inserting a new record into the returned table to log the return details including return ID, book name, return date, ISBN, and the related issue ID.
Updating the corresponding book’s status in the Books table to 'yes', marking it as available again for borrowing.
Returning a confirmation message to acknowledge the successful return of the book.
SQL Stored Procedure:
Objective:
To generate a comprehensive performance report for each branch, summarizing book circulation and revenue.
Description:
This query aggregates key performance metrics for every library branch by:
Counting the total number of books issued (number_of_books_issued).
Counting the total number of books returned (number_of_books_returned).
Calculating the total rental revenue generated from issued books (total_revenue_generated), by summing the rental prices of all issued books.
The query joins the Issued_Status, Books, Employees, and Branch tables to associate each issued book with the processing employee and their respective branch. A left join with Return_Status helps tally returns accurately.
Purpose & Outcome:
Provides branch managers with valuable insights into their branch’s book circulation and revenue.
Enables data-driven decision-making to improve branch performance.
Supports library-wide analysis for operational efficiency and resource allocation.
SQL Query:
Objective:
To identify and maintain a list of active library members who have borrowed books recently.
Description:
This task creates a new table, Active_members, that includes details of members who have issued at least one book within the last two months. It uses a subquery to filter Members based on their recent activity in the Issued_Status table.
Purpose & Outcome:
Helps the library easily track and analyze currently engaged members.
Supports targeted communication and promotional activities.
Enhances member management by focusing on recent active users.
SQL Query:
Objective:
To identify and recognize the top-performing employees based on the number of books they have issued.
Description:
This query uses Common Table Expressions (CTEs) to calculate and display the top 3 employees who have processed the highest number of book issues. It:
Aggregates the count of books issued by each employee from the Issued_Status table.
Joins with the Employees table to get employee names and branch details.
Sorts the results in descending order of books issued and limits the output to the top 3 employees.
SQL Query:
This Library Management System project effectively automates and streamlines all critical functions involved in managing a modern library. From book issuance and returns to member registration and employee tracking, the system ensures accurate and efficient handling of daily library operations.
By implementing a robust database design coupled with optimized SQL queries and stored procedures, the system maintains real-time updates on book availability, prevents conflicts like double issuing, and tracks overdue items systematically. Additionally, the inclusion of member activity monitoring and employee performance analysis empowers library administrators to make informed decisions and improve service delivery.
The project showcases how technology can significantly enhance library management by reducing manual errors, saving time, and improving overall user experience. It provides a scalable and adaptable solution that can support the growing needs of any library, ensuring smooth operation and better resource utilization.
Emmanuel Olorunda
Copyright © 2025 | All Rights Reserved
Every project starts with a chat, Emmanuel is keen to conversation and open to discussing your project. he will pull in the right expertise when needed