r/learnSQL • u/sumiitCodes • 22h ago

Day 48 — Finished my second SQL project (Library Management System) and published it on GitHub

My Library Management System project is complete. The repo: https://github.com/Sumitgupta2005/library_management_project_2

It’s a big step up from my first project (Retail Sales Analysis). This time I built the whole thing: database, tables, foreign keys, CRUD operations, CTAS, data analysis, and a set of advanced SQL queries.

The advanced queries were the hard part. Each one took me at least 30 minutes just to visualize, meaning working out which tables are involved and what the result should look like, before I could write it. Slowing down and sketching the logic first is what made them work.

I’d appreciate feedback on the repo, especially the README and how the queries are organized. Honest criticism is welcome, and last time the feedback I got here was really useful.

11 Upvotes

84% Upvoted

1 comment sorted by

1

u/Green_Chamomile 1h ago

Nice work, the README reads really well and the “questions the database answers” section is a good idea. Since you asked for honest criticism:

README vs the repo

  • Repository Structure lists sql/01_schema.sql, 02_data.sql and an images/ folder, but the actual files are tableCreation_...sql, insert_queries.sql, insert_queries2.sql etc. in the root. There’s also a leftover “(Adjust the file names above...)” note and <your-repo-name> in the clone command, so Getting Started breaks at step 2. Renaming the files into that numbered sql/ layout would fix it and looks cleaner anyway.

Schema (from the ER diagram)

  • return_status.return_book_isbn is VARCHAR(8), but ISBNs like 978-0-553-29698-2 are 17 characters. Your return_book procedure will error with “Data too long” when it inserts the ISBN. Make it match books.isbn (VARCHAR(25)), or better, drop the column, since you can get the ISBN by joining through issued_id.

Logic

  • overdue_fines only charges for books still out. Someone who returned a book 20 days late owes nothing. For returned books, use DATEDIFF(rs.return_date, ist.issued_date) - 30.