Databases & SQLBeginnerFree to access
SQL Library Database: Tables, Keys & JOINs
Design a three-table library lending database and write queries for active loans and overdue items.
Download free Markdown guideNo email, account or payment required · .md text format
What you will build
Understand primary keys, foreign keys, constraints and readable relational queries.
Before you begin
- SQL editor or local SQLite database
- Basic SELECT and INSERT familiarity
Local setup instructions
- Create a new SQLite database using sqlite3 or DB Browser for SQLite.
- Run the starter SQL below; SQLite foreign keys must be enabled per connection.
- Insert sample members and books before adding a loan.
Starter code
library.sqlThis is a working starting example, not the finished project. Follow the setup above and complete each milestone to build your own solution.
PRAGMA foreign_keys = ON;
CREATE TABLE members (
id INTEGER PRIMARY KEY,
full_name TEXT NOT NULL
);
CREATE TABLE books (
id INTEGER PRIMARY KEY,
title TEXT NOT NULL
);
CREATE TABLE loans (
id INTEGER PRIMARY KEY,
member_id INTEGER NOT NULL REFERENCES members(id),
book_id INTEGER NOT NULL REFERENCES books(id),
returned_at TEXT
);
INSERT INTO members (id, full_name) VALUES (1, 'Aisha');
INSERT INTO books (id, title) VALUES (1, 'Python Basics');
INSERT INTO loans (id, member_id, book_id) VALUES (1, 1, 1);
SELECT m.full_name, b.title
FROM loans AS l
JOIN members AS m ON m.id = l.member_id
JOIN books AS b ON b.id = l.book_id
WHERE l.returned_at IS NULL;Project build milestones
- Create members, books and loans tables with appropriate keys.
- Insert at least three books, two members and three loan records.
- Write JOIN queries for active loans and members with borrowed books.
- Reject an invalid member ID by keeping foreign key checks enabled.
- Explain how a loan record differs from a book record.
Completion checklist
Use these checks to self-review the finished project.
- Each member and book has a unique primary key.
- The loans table references valid members and books.
- Active-loan query lists book title and borrower name.
- You can explain why deleting a member with active loans should be handled carefully.
Optional challenges
- Add loan dates and overdue queries with a documented due-date rule.
- Create a simple Python sqlite3 command-line client using parameterized queries.