Library Database SQL Tutorial: Create Tables, Insert Data, Update & Delete Records

Library Database SQL Queries and Schema

This post explains the Library database schema and provides SQL queries including table creation, inserting sample data, updating, and deleting records.

---

1. Library Database Schema

Librarian Table

Column	Data Type	Key
librarian_id	INT	Primary Key
librarian_name	VARCHAR(50)	-
contact_number	VARCHAR(15)	-
email	VARCHAR(50)	-

Authors Table

Column	Data Type	Key
author_id	INT	Primary Key
author_name	VARCHAR(50)	-

Books Table

Column	Data Type	Key
book_id	INT	Primary Key
title	VARCHAR(100)	-
author_id	INT	Foreign Key → Authors(author_id)
publisher	VARCHAR(50)	-
copies_available	INT	-

Members Table

Column	Data Type	Key
member_id	INT	Primary Key
member_name	VARCHAR(50)	-
membership_date	DATE	-
contact_number	VARCHAR(15)	-

---

2. Insert Sample Data

Librarians

INSERT INTO Librarian VALUES (1, 'Ali Khan', '01712345678', 'ali@example.com');
INSERT INTO Librarian VALUES (2, 'Sara Rahman', '01898765432', 'sara@example.com');

Authors

INSERT INTO Authors VALUES (1, 'J.K. Rowling');
INSERT INTO Authors VALUES (2, 'George Orwell');

Books

INSERT INTO Books VALUES (1, 'Harry Potter and the Sorcerer''s Stone', 1, 'Bloomsbury', 5);
INSERT INTO Books VALUES (2, '1984', 2, 'Secker & Warburg', 3);
INSERT INTO Books VALUES (3, 'Harry Potter and the Chamber of Secrets', 1, 'Bloomsbury', 2);

Members

INSERT INTO Members VALUES (1, 'Rahim', '2020-10-15', '01612345678');
INSERT INTO Members VALUES (2, 'Fatema', '2021-05-20', '01723456789');
INSERT INTO Members VALUES (3, 'Karim', '2022-01-10', '01834567890');

---

3. Update Book Copies

Increase the number of copies available for book_id = 3:

UPDATE Books
SET copies_available = copies_available + 5
WHERE book_id = 3;

---

4. Delete Members Registered Before 28-11-2020

DELETE FROM Members
WHERE membership_date < '2020-11-28';