Skip to main content
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

  1. Create a new SQLite database using sqlite3 or DB Browser for SQLite.
  2. Run the starter SQL below; SQLite foreign keys must be enabled per connection.
  3. Insert sample members and books before adding a loan.

Starter code

library.sql

This 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

  1. Create members, books and loans tables with appropriate keys.
  2. Insert at least three books, two members and three loan records.
  3. Write JOIN queries for active loans and members with borrowed books.
  4. Reject an invalid member ID by keeping foreign key checks enabled.
  5. 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.