by
SQL Tutorial › SQL Projects › Project: Library Management System

SQL Project: Library Management System

ProjectProject 2 of 530 min read13 queries run on MySQL 8.4

A city library tracks its books, members and loans in MySQL. In this project you answer the questions the librarians ask every day: which copies are on the shelf, which loans are overdue, how much each member owes in fines, which books are popular, and you build a view and a stored procedure that issues books safely. The reference date for “today” is 30 June 2025.

Advertisement
In this lesson
  • Compute availability from loans that are still open
  • Find overdue loans and calculate fines with date functions
  • Rank popular books and active members
  • Package logic in a view and a transaction-safe stored procedure

The database

TableOne row perNotes
bookstitle in the cataloguecopies = how many physical copies the library owns
memberslibrary membermember_type: Student, Regular, Senior
loansone book lent to one memberreturn_date is NULL while the book is still out
books · 12 rows
book_idtitleauthorgenrepublished_yearcopies
1The Pragmatic ProgrammerHunt & ThomasTechnology19993
2Clean CodeRobert MartinTechnology20082
3Atomic HabitsJames ClearSelf-help20184
4SapiensYuval Noah HarariHistory20112
5The AlchemistPaulo CoelhoFiction19883
6Wings of FireA. P. J. Abdul KalamBiography19992
7The GuideR. K. NarayanFiction19581
8Thinking, Fast and SlowDaniel KahnemanPsychology20112
9Deep WorkCal NewportSelf-help20162
10A Brief History of TimeStephen HawkingScience19881
11The God of Small ThingsArundhati RoyFiction19972
12Lessons in TestingCem KanerTechnology20011
members · 10 rows
member_idfull_namemember_typejoined_on
1Rhea KulkarniSenior2024-04-26
2Om PrakashSenior2023-12-27
3Tara SenStudent2023-09-09
4Neil D'SouzaSenior2024-10-09
5Ira MalhotraStudent2024-01-06
6Dev ChauhanSenior2024-08-09
7Noor FatimaSenior2023-06-19
8Kunal BhattRegular2023-09-12
9Asha PillaiStudent2024-02-09
10Yash RawatStudent2024-08-18
loans · first 10 of 55 rows
loan_idbook_idmember_idloan_datedue_datereturn_date
1812025-01-052025-01-192025-01-17
2372025-01-052025-01-192025-01-10
3812025-01-132025-01-272025-01-29
4172025-01-172025-01-312025-02-02
51032025-01-202025-02-032025-02-03
63102025-01-212025-02-042025-02-10
7182025-01-272025-02-102025-02-10
8512025-02-162025-03-022025-02-25
9162025-02-202025-03-062025-03-01
10622025-02-212025-03-072025-03-09

Rules: a loan lasts 14 days (due_date); a late return costs ₹5 per day; a member may hold at most 3 books at a time.

library.sql
-- sqatools.in SQL project 2: library management system (generated by gen_projects.py)CREATE TABLE books (  book_id        INT PRIMARY KEY,  title          VARCHAR(60) NOT NULL,  author         VARCHAR(40) NOT NULL,  genre          VARCHAR(20) NOT NULL,  published_year INT,  copies         INT NOT NULL);CREATE TABLE members (  member_id   INT PRIMARY KEY,  full_name   VARCHAR(40) NOT NULL,  member_type VARCHAR(10) NOT NULL,  joined_on   DATE NOT NULL);CREATE TABLE loans (  loan_id     INT PRIMARY KEY,  book_id     INT NOT NULL,  member_id   INT NOT NULL,  loan_date   DATE NOT NULL,  due_date    DATE NOT NULL,  return_date DATE,  FOREIGN KEY (book_id) REFERENCES books (book_id),  FOREIGN KEY (member_id) REFERENCES members (member_id));INSERT INTO books VALUES(1, 'The Pragmatic Programmer', 'Hunt & Thomas', 'Technology', 1999, 3),(2, 'Clean Code', 'Robert Martin', 'Technology', 2008, 2),(3, 'Atomic Habits', 'James Clear', 'Self-help', 2018, 4),(4, 'Sapiens', 'Yuval Noah Harari', 'History', 2011, 2),(5, 'The Alchemist', 'Paulo Coelho', 'Fiction', 1988, 3),(6, 'Wings of Fire', 'A. P. J. Abdul Kalam', 'Biography', 1999, 2),(7, 'The Guide', 'R. K. Narayan', 'Fiction', 1958, 1),(8, 'Thinking, Fast and Slow', 'Daniel Kahneman', 'Psychology', 2011, 2),(9, 'Deep Work', 'Cal Newport', 'Self-help', 2016, 2),(10, 'A Brief History of Time', 'Stephen Hawking', 'Science', 1988, 1),(11, 'The God of Small Things', 'Arundhati Roy', 'Fiction', 1997, 2),(12, 'Lessons in Testing', 'Cem Kaner', 'Technology', 2001, 1);INSERT INTO members VALUES(1, 'Rhea Kulkarni', 'Senior', '2024-04-26'),(2, 'Om Prakash', 'Senior', '2023-12-27'),(3, 'Tara Sen', 'Student', '2023-09-09'),(4, 'Neil D''Souza', 'Senior', '2024-10-09'),(5, 'Ira Malhotra', 'Student', '2024-01-06'),(6, 'Dev Chauhan', 'Senior', '2024-08-09'),(7, 'Noor Fatima', 'Senior', '2023-06-19'),(8, 'Kunal Bhatt', 'Regular', '2023-09-12'),(9, 'Asha Pillai', 'Student', '2024-02-09'),(10, 'Yash Rawat', 'Student', '2024-08-18');INSERT INTO loans VALUES(1, 8, 1, '2025-01-05', '2025-01-19', '2025-01-17'),(2, 3, 7, '2025-01-05', '2025-01-19', '2025-01-10'),(3, 8, 1, '2025-01-13', '2025-01-27', '2025-01-29'),(4, 1, 7, '2025-01-17', '2025-01-31', '2025-02-02'),(5, 10, 3, '2025-01-20', '2025-02-03', '2025-02-03'),(6, 3, 10, '2025-01-21', '2025-02-04', '2025-02-10'),(7, 1, 8, '2025-01-27', '2025-02-10', '2025-02-10'),(8, 5, 1, '2025-02-16', '2025-03-02', '2025-02-25'),(9, 1, 6, '2025-02-20', '2025-03-06', '2025-03-01'),(10, 6, 2, '2025-02-21', '2025-03-07', '2025-03-09'),(11, 4, 5, '2025-02-21', '2025-03-07', '2025-03-05'),(12, 3, 4, '2025-02-22', '2025-03-08', '2025-03-03'),(13, 5, 6, '2025-02-22', '2025-03-08', '2025-03-06'),(14, 2, 9, '2025-02-24', '2025-03-10', '2025-03-01'),(15, 10, 10, '2025-02-26', '2025-03-12', '2025-03-12'),(16, 6, 3, '2025-02-27', '2025-03-13', '2025-03-13'),(17, 10, 5, '2025-02-27', '2025-03-13', '2025-03-15'),(18, 7, 3, '2025-03-04', '2025-03-18', '2025-03-20'),(19, 5, 3, '2025-03-06', '2025-03-20', '2025-03-20'),(20, 5, 3, '2025-03-07', '2025-03-21', '2025-04-03'),(21, 1, 7, '2025-03-09', '2025-03-23', '2025-03-14'),(22, 11, 3, '2025-03-15', '2025-03-29', '2025-03-28'),(23, 4, 5, '2025-03-17', '2025-03-31', '2025-04-02'),(24, 7, 4, '2025-03-21', '2025-04-04', '2025-04-06'),(25, 7, 10, '2025-03-24', '2025-04-07', '2025-04-02'),(26, 9, 7, '2025-03-26', '2025-04-09', '2025-04-07'),(27, 8, 6, '2025-03-30', '2025-04-13', '2025-04-13'),(28, 9, 10, '2025-03-31', '2025-04-14', '2025-04-16'),(29, 8, 4, '2025-04-01', '2025-04-15', '2025-04-06'),(30, 10, 4, '2025-04-10', '2025-04-24', '2025-04-30'),(31, 1, 5, '2025-04-15', '2025-04-29', '2025-05-01'),(32, 9, 5, '2025-04-21', '2025-05-05', '2025-05-18'),(33, 1, 10, '2025-04-27', '2025-05-11', '2025-05-13'),(34, 11, 9, '2025-05-01', '2025-05-15', '2025-05-13'),(35, 6, 8, '2025-05-01', '2025-05-15', '2025-05-13'),(36, 4, 7, '2025-05-01', '2025-05-15', '2025-05-06'),(37, 5, 8, '2025-05-02', '2025-05-16', '2025-05-29'),(38, 7, 6, '2025-05-03', '2025-05-17', '2025-05-15'),(39, 7, 10, '2025-05-06', '2025-05-20', '2025-05-22'),(40, 11, 2, '2025-05-09', '2025-05-23', '2025-05-29'),(41, 9, 10, '2025-05-12', '2025-05-26', '2025-06-01'),(42, 8, 7, '2025-05-19', '2025-06-02', '2025-05-28'),(43, 11, 8, '2025-05-22', '2025-06-05', '2025-05-31'),(44, 2, 10, '2025-05-23', '2025-06-06', '2025-06-19'),(45, 5, 3, '2025-05-25', '2025-06-08', '2025-06-06'),(46, 3, 1, '2025-06-02', '2025-06-16', '2025-06-16'),(47, 8, 8, '2025-06-09', '2025-06-23', '2025-06-23'),(48, 10, 7, '2025-06-09', '2025-06-23', NULL),(49, 5, 4, '2025-06-13', '2025-06-27', NULL),(50, 10, 3, '2025-06-14', '2025-06-28', '2025-06-19'),(51, 7, 8, '2025-06-19', '2025-07-03', NULL),(52, 1, 2, '2025-06-21', '2025-07-05', NULL),(53, 4, 4, '2025-06-21', '2025-07-05', NULL),(54, 4, 5, '2025-06-22', '2025-07-06', NULL),(55, 2, 6, '2025-06-25', '2025-07-09', NULL);

Task 1: Catalogue overview

SQL
SELECT genre, COUNT(*) AS titles, SUM(copies) AS copiesFROM booksGROUP BY genreORDER BY copies DESC, genre;
ResultMySQL 8.4
genretitlescopies
Fiction36
Self-help26
Technology36
Biography12
History12
Psychology12
Science11
7 rows in set

Task 2: Copies available right now

Available = copies owned − loans that are still open:

SQL
SELECT b.book_id, b.title, b.copies,       COUNT(l.loan_id)            AS on_loan,       b.copies - COUNT(l.loan_id) AS availableFROM books bLEFT JOIN loans l ON l.book_id = b.book_id AND l.return_date IS NULLGROUP BY b.book_id, b.title, b.copiesORDER BY available, b.title;
ResultMySQL 8.4
book_idtitlecopieson_loanavailable
10A Brief History of Time110
4Sapiens220
7The Guide110
2Clean Code211
12Lessons in Testing101
9Deep Work202
5The Alchemist312
11The God of Small Things202
1The Pragmatic Programmer312
8Thinking, Fast and Slow202
6Wings of Fire202
3Atomic Habits404
12 rows in set
How this query works, step by step
  1. The LEFT JOIN attaches only open loans (return_date IS NULL); the condition is in ON so books with no open loan are kept.
  2. COUNT(l.loan_id) counts the open loans of each book (0 if none).
  3. Subtracting from copies gives the copies on the shelf. Books with 0 available come first.

Task 3: Overdue loans today

MySQL
SELECT l.loan_id, m.full_name, b.title, l.due_date,       DATEDIFF('2025-06-30', l.due_date) AS days_overdueFROM loans lJOIN members m ON m.member_id = l.member_idJOIN books b   ON b.book_id = l.book_idWHERE l.return_date IS NULL  AND l.due_date < '2025-06-30'ORDER BY days_overdue DESC;
ResultMySQL 8.4
loan_idfull_nametitledue_datedays_overdue
48Noor FatimaA Brief History of Time2025-06-237
49Neil D'SouzaThe Alchemist2025-06-273
2 rows in set
How this query works, step by step
  1. A loan is overdue when it is still open and its due date has passed.
  2. DATEDIFF(today, due_date) gives the number of days late.
  3. In a live system replace '2025-06-30' with CURDATE().

Task 4: Fines per member

Late days are counted up to the return date for returned books, and up to today for books still out:

MySQL
WITH late AS (  SELECT l.member_id,         GREATEST(DATEDIFF(COALESCE(l.return_date, '2025-06-30'), l.due_date), 0) AS days_late  FROM loans l)SELECT m.member_id, m.full_name,       SUM(late.days_late > 0) AS late_loans,       SUM(late.days_late) * 5 AS fine_rupeesFROM lateJOIN members m ON m.member_id = late.member_idGROUP BY m.member_id, m.full_nameHAVING fine_rupees > 0ORDER BY fine_rupees DESC;
ResultMySQL 8.4
member_idfull_namelate_loansfine_rupees
10Yash Rawat6155
5Ira Malhotra495
3Tara Sen275
8Kunal Bhatt165
4Neil D'Souza355
7Noor Fatima245
2Om Prakash240
1Rhea Kulkarni110
8 rows in set
How this query works, step by step
  1. COALESCE(return_date, '2025-06-30') uses today for loans that are still open.
  2. DATEDIFF(that date, due_date) counts the days after the due date; a negative number means the book came back early.
  3. GREATEST(..., 0) turns early returns into 0 days late.
  4. The fine is the total late days × ₹5; HAVING keeps only members who owe something.
SQL
SELECT b.title, b.genre, COUNT(*) AS loans,       DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) AS popularity_rankFROM loans lJOIN books b ON b.book_id = l.book_idGROUP BY b.book_id, b.title, b.genreORDER BY loans DESC, b.titleLIMIT 5;
ResultMySQL 8.4
titlegenreloanspopularity_rank
The AlchemistFiction71
The Pragmatic ProgrammerTechnology71
A Brief History of TimeScience62
The GuideFiction62
Thinking, Fast and SlowPsychology62
5 rows in set

A window function can rank the grouped rows: DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) runs after GROUP BY.

Task 6: Books nobody has borrowed

SQL
SELECT b.book_id, b.title, b.authorFROM books bWHERE NOT EXISTS (SELECT 1 FROM loans l WHERE l.book_id = b.book_id);
ResultMySQL 8.4
book_idtitleauthor
12Lessons in TestingCem Kaner
1 row in set

Task 7: Member activity

MySQL
SELECT m.member_type,       COUNT(DISTINCT m.member_id) AS members,       COUNT(l.loan_id)            AS loans,       ROUND(COUNT(l.loan_id) / COUNT(DISTINCT m.member_id), 1) AS loans_per_memberFROM members mLEFT JOIN loans l ON l.member_id = m.member_idGROUP BY m.member_typeORDER BY loans_per_member DESC;
ResultMySQL 8.4
member_typemembersloansloans_per_member
Regular166.0
Student4246.0
Senior5255.0
3 rows in set

Task 8: Average loan length by genre

MySQL
SELECT b.genre,       COUNT(*) AS returned_loans,       ROUND(AVG(DATEDIFF(l.return_date, l.loan_date)), 1) AS avg_days_kept,       SUM(l.return_date > l.due_date) AS returned_lateFROM loans lJOIN books b ON b.book_id = l.book_idWHERE l.return_date IS NOT NULLGROUP BY b.genreORDER BY avg_days_kept DESC;
ResultMySQL 8.4
genrereturned_loansavg_days_keptreturned_late
Self-help815.44
Fiction1514.96
Biography314.01
Science513.82
Technology813.54
Psychology611.71
History311.01
7 rows in set

Task 9: Loans per month

MySQL
SELECT DATE_FORMAT(loan_date, '%Y-%m') AS month,       COUNT(*) AS loans,       COUNT(DISTINCT member_id) AS active_membersFROM loansGROUP BY monthORDER BY month;
ResultMySQL 8.4
monthloansactive_members
2025-0175
2025-02108
2025-03116
2025-0453
2025-05127
2025-06108
6 rows in set

Task 10: Members at their borrowing limit

MySQL
SELECT m.member_id, m.full_name, COUNT(*) AS books_out,       GROUP_CONCAT(b.title ORDER BY l.loan_date SEPARATOR '; ') AS titlesFROM loans lJOIN members m ON m.member_id = l.member_idJOIN books b   ON b.book_id = l.book_idWHERE l.return_date IS NULLGROUP BY m.member_id, m.full_nameHAVING COUNT(*) >= 2ORDER BY books_out DESC;
ResultMySQL 8.4
member_idfull_namebooks_outtitles
4Neil D'Souza2The Alchemist; Sapiens
1 row in set

Task 11: A view for the front desk

Save the availability query as a view, so the desk application only has to SELECT from it:

SQL
CREATE VIEW available_books ASSELECT b.book_id, b.title, b.author,       b.copies - COUNT(l.loan_id) AS availableFROM books bLEFT JOIN loans l ON l.book_id = b.book_id AND l.return_date IS NULLGROUP BY b.book_id, b.title, b.author, b.copies; SELECT * FROM available_books WHERE available > 0 AND title LIKE '%Habit%';
ResultMySQL 8.4
mysql> CREATE VIEW available_books AS
Query OK, 0 rows affected
mysql> SELECT * FROM available_books WHERE available > 0 AND title LIKE '%Hab…
book_idtitleauthoravailable
3Atomic HabitsJames Clear4
1 row in set

Task 12: Issue a book safely (stored procedure)

Issuing a book must check two rules and then insert the loan, as one unit of work:

MySQL
DELIMITER //CREATE PROCEDURE issue_book(IN p_book_id INT, IN p_member_id INT, IN p_date DATE)BEGIN  DECLARE v_available INT;  DECLARE v_out INT;  DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END;   START TRANSACTION;  SELECT b.copies - (SELECT COUNT(*) FROM loans WHERE book_id = p_book_id AND return_date IS NULL)    INTO v_available    FROM books b WHERE b.book_id = p_book_id FOR UPDATE;  SELECT COUNT(*) INTO v_out FROM loans WHERE member_id = p_member_id AND return_date IS NULL;   IF v_available IS NULL OR v_available < 1 THEN    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'No copy available';  END IF;  IF v_out >= 3 THEN    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Member already has 3 books';  END IF;   INSERT INTO loans (loan_id, book_id, member_id, loan_date, due_date, return_date)  SELECT MAX(loan_id) + 1, p_book_id, p_member_id, p_date, p_date + INTERVAL 14 DAY, NULL FROM loans;  COMMIT;END //DELIMITER ; CALL issue_book(12, 1, '2025-06-30');SELECT * FROM loans ORDER BY loan_id DESC LIMIT 1;
ResultMySQL 8.4
mysql> CREATE PROCEDURE issue_book(IN p_book_id INT, IN p_member_id INT, IN p…
Query OK, 0 rows affected
mysql> CALL issue_book(12, 1, '2025-06-30')
Query OK, 0 rows affected
mysql> SELECT * FROM loans ORDER BY loan_id DESC LIMIT 1
loan_idbook_idmember_idloan_datedue_datereturn_date
561212025-06-302025-07-14NULL
1 row in set
How this query works, step by step
  1. FOR UPDATE locks the book row, so two librarians cannot hand out the last copy at the same moment.
  2. Two SIGNALs enforce the rules: a copy must be free, and the member must hold fewer than 3 books.
  3. If anything fails, the handler rolls back and passes the error on.
  4. Book 12 had never been borrowed, so member 1 can take it: a new open loan due in 14 days.

Asking for a book with no copy left fails, and nothing is inserted:

MySQL
CALL issue_book(12, 2, '2025-06-30');
ResultMySQL 8.4
ERROR 1644 (45000): No copy available

Your turn

Exercise: Members with nothing borrowed right now

List members who currently have no book out (no open loan).

Expected resultMySQL 8.4
member_idfull_name
1Rhea Kulkarni
3Tara Sen
9Asha Pillai
10Yash Rawat
4 rows in set
Hint 1

NOT EXISTS with a subquery on open loans (return_date IS NULL).

Exercise: Late returns per book

For each book, count how many loans were returned after the due date.

Expected resultMySQL 8.4
titlelate_returns
Deep Work3
The Guide3
The Pragmatic Programmer3
A Brief History of Time2
The Alchemist2
Atomic Habits1
Clean Code1
Sapiens1
The God of Small Things1
Thinking, Fast and Slow1
Wings of Fire1
11 rows in set
Hint 1

Filter returned loans with return_date > due_date, group by title.

What you practised

TaskSQL skills
Availability, front-desk viewLEFT JOIN with a condition in ON, COUNT, views
Overdue loans and finesDATEDIFF, COALESCE, GREATEST, CTE, HAVING
Popular books, membersGROUP BY, DENSE_RANK() OVER, GROUP_CONCAT
Never borrowedNOT EXISTS, anti-join
Issue bookstored procedure, SIGNAL, transaction, FOR UPDATE

Project FAQ

What tables does a library management system database need?

At least books (the catalogue), members, and loans that link a book to a member with loan, due and return dates. Larger systems add copies, reservations, fines and staff.

How do you calculate overdue fines in SQL?

Compute the days between the due date and the return date (or today, for open loans) with DATEDIFF, clamp negatives to zero with GREATEST, and multiply by the daily rate.

How do you check book availability in SQL?

Subtract the number of open loans (return_date IS NULL) from the number of copies, using a LEFT JOIN so books with no loans are included.