🚀 SQL Project Series #28

Library Management Analytics 📚

Analyze books, members, authors, borrowings, returns, and fines using SQL to improve library operations, monitor book circulation, and enhance member engagement.

🎯 Business Objectives
✅ Track book borrowings
✅ Monitor overdue books
✅ Analyze member activity
✅ Measure book popularity
✅ Track fine collections
✅ Analyze author performance
✅ Improve inventory utilization
✅ Build executive library dashboards

📂 Step 1: Create Database
CREATE DATABASE library_db;
USE library_db;

📂 Step 2: Create Members Table
CREATE TABLE members (
member_id INT PRIMARY KEY,
member_name VARCHAR(100),
membership_type VARCHAR(30),
join_date DATE,
city VARCHAR(50)
);

📂 Step 3: Create Authors Table
CREATE TABLE authors (
author_id INT PRIMARY KEY,
author_name VARCHAR(100),
country VARCHAR(50)
);

📂 Step 4: Create Books Table
CREATE TABLE books (
book_id INT PRIMARY KEY,
title VARCHAR(200),
author_id INT,
genre VARCHAR(50),
publication_year INT,
available_copies INT,
FOREIGN KEY (author_id)
REFERENCES authors(author_id)
);

📂 Step 5: Create Borrowings Table
CREATE TABLE borrowings (
borrowing_id INT PRIMARY KEY,
member_id INT,
book_id INT,
borrow_date DATE,
due_date DATE,
return_date DATE,
fine_amount DECIMAL(10,2),
FOREIGN KEY (member_id)
REFERENCES members(member_id),
FOREIGN KEY (book_id)
REFERENCES books(book_id)
);

📂 Step 6: Insert Sample Members
INSERT INTO members VALUES
(1,'Rahul Sharma','Premium','2024-01-10','Mumbai'),
(2,'Priya Verma','Standard','2024-02-15','Delhi'),
(3,'Amit Patel','Premium','2024-03-08','Pune'),
(4,'Sneha Joshi','Student','2024-04-20','Bangalore'),
(5,'Rohan Gupta','Standard','2024-05-12','Hyderabad');

📂 Step 7: Insert Sample Authors
INSERT INTO authors VALUES
(101,'James Clear','USA'),
(102,'Morgan Housel','USA'),
(103,'Yuval Noah Harari','Israel'),
(104,'Robert C. Martin','USA');

📂 Step 8: Insert Sample Books
INSERT INTO books VALUES
(201,'Atomic Habits',101,'Self Help',2018,12),
(202,'The Psychology of Money',102,'Finance',2020,8),
(203,'Sapiens',103,'History',2011,10),
(204,'Clean Code',104,'Programming',2008,6);

📂 Step 9: Insert Sample Borrowings
INSERT INTO borrowings VALUES
(1001,1,201,'2025-01-05','2025-01-19','2025-01-17',0),
(1002,2,202,'2025-01-08','2025-01-22','2025-01-25',150),
(1003,3,204,'2025-01-10','2025-01-24',NULL,0),
(1004,4,203,'2025-01-12','2025-01-26','2025-01-24',0),
(1005,5,201,'2025-01-15','2025-01-29',NULL,0);

▎🧠 SQL Concepts You'll Practice

✔ DDL & DML
✔ INNER JOIN
✔ LEFT JOIN
✔ Aggregate Functions
✔ GROUP BY
✔ HAVING
✔ CASE WHEN
✔ Date Functions
✔ CTEs
✔ Window Functions
✔ Ranking Functions

▎📊 Business KPIs You Can Build

📈 Total Books
📈 Total Members
📈 Active Members
📈 Total Borrowings
📈 Books Currently Issued
📈 Overdue Books
📈 Overdue Percentage
📈 Total Fine Collected
📈 Average Fine per Member
📈 Most Borrowed Books
📈 Least Borrowed Books
📈 Most Popular Authors
📈 Genre-wise Borrowings
📈 Monthly Borrowing Trend
📈 Book Availability Rate
📈 Average Borrowing Duration
📈 Repeat Borrowers
📈 Member Activity by City
📈 Library Utilization Rate
📈 Executive Library Dashboard

🎯 This project reflects real-world SQL analysis performed by Library Administrators, Educational Institutions, Public Libraries, Digital Library Platforms, and Business Intelligence professionals to optimize inventory, improve member engagement, and monitor library performance.

Double Tap ❤️ For More