SQL Project: Design and Query a Database

Thirteen lessons of single skills. Time to use all of them on one thing. We're going to build a small library database from nothing. Design the tables, fill them with books and members, then answer the questions a real librarian would actually ask. Which books are out right now? What's the most borrowed title? Who's overdue? Every query runs live, right here, against tables you can see being created.

The library is a clean fit because the relationships are obvious: authors write books, members borrow them, and a loan ties a member to a book on a date. Four tables, a couple of keys, and you've got something that answers genuine questions.

Step 1: design the tables

Before typing any SQL, name the things and the links between them. A library has authors, books, members, and loans. The first three are nouns you can point at. The fourth, loans, is the interesting one. It's the record that says "member 2 borrowed book 1 on this date, due back on that date." It connects the others.

Each book points at one author through author_id. Each loan points at one book and one member. Those pointers are foreign keys, a column in one table that holds the id of a row in another. That's the whole trick to relational design: don't copy an author's name into every book, just store the author's id and look it up when you need it.

Here's the schema. Run it. It creates four empty tables, and the last statement lists them back so you can see they exist:

A few choices worth pointing out. id INTEGER PRIMARY KEY gives every row a unique, never-repeating identifier, the thing foreign keys point at. author_id INTEGER REFERENCES authors(id) says "this column holds an author's id." And returned_date is allowed to be empty (NULL) on purpose: a book that's still out hasn't been returned yet, so it has no return date. That NULL is going to do real work later.

Step 2: add the data

Empty tables answer no questions. Let's stock the library: four authors, six books, four members, and a handful of loans. The order matters: insert authors before books (a book references an author), and books and members before loans (a loan references both).

That last query is already doing the thing foreign keys exist for: each book stores only an author_id, but the join follows that number into authors and pulls back the real name. "The Glass Forest" stored author_id = 1, the join matched it to Naomi Frost. Store the number once, look up the name on demand.

Now the members and the loans. A loan records who borrowed what, when it went out, when it's due, and (if it's back) when it was returned:

Eight loan records. Some have a returned_date, some are NULL. Those NULL ones are books still out in the world. The data's loaded. Now the fun part: asking it things.

Question 1: which books are on loan right now?

A book is "on loan" when it has a loan record with no returned_date. But a loan row only stores book_id and member_id, bare numbers. To get a useful answer, we need the book title and the borrower's name, which means joining loans to both books and members. Two joins in one query, plus a WHERE to keep only the ones not yet back.

Five books are out. The query starts at loans, hops to books to get each title and to members to get each borrower, then WHERE loans.returned_date IS NULL throws away every loan that's already been returned. Note IS NULL, not = NULL. NULL means "unknown," and you can't check equality against unknown, so SQL gives you IS NULL for exactly this. This one query touches three tables and answers a question no single table could.

Question 2: what's the most borrowed book?

Popularity is a counting question, which means GROUP BY plus COUNT. We group the loan records by which book they're for, count the rows in each group, and sort so the most-borrowed sits on top.

"The Glass Forest" wins with 3 loans. We grouped by books.id — not by title — because the id is guaranteed unique, while two different books could in theory share a title. Then COUNT(*) counts the loan rows in each group, and ORDER BY times_borrowed DESC brings the winner up. Notice we counted all loans, returned or not, because total popularity is the question. If you only wanted currently-out copies, you'd add a WHERE loans.returned_date IS NULL before the grouping.

Question 3: who has overdue loans?

Overdue means three things at once: the book isn't back yet (returned_date IS NULL), and its due_date is in the past. Today is 2026-06-16, so "past" means a due date earlier than that. This is a WHERE filter on dates, combined with the still-out check.

Four overdue loans. Tomas is the repeat offender. He's sitting on both "Northbound" (due back in April) and "Quiet Machines." This works because the dates are stored as YYYY-MM-DD text, and that format sorts and compares correctly as plain strings: '2026-04-24' < '2026-06-16' is true exactly when the real dates are in that order. That's not luck, it's why ISO date format is the one to use. Store dates any other way (24/04/2026, say) and < would compare them as nonsense.

Question 4: rank authors by popularity

Last one, and it pulls in the newest tool: window functions. We want every author with their total number of loans and a rank (1st, 2nd, 3rd) across the whole library. Counting per author is a GROUP BY. Adding the rank number on top is what RANK() does.

Naomi Frost takes first with 4 loans across her two books. The chain of joins is what makes this possible: authors → books (her titles) → loans (every time one went out). Then GROUP BY authors.id folds it to one row per author with a count, and RANK() OVER (ORDER BY COUNT(loans.id) DESC) stamps a rank onto each. Watch the bottom two: Diego Marsh and Lena Vogt both have 1 loan, so they tie at rank 3. That's RANK() doing the right thing with ties, exactly the behavior a plain ROW_NUMBER() wouldn't give you. A leaderboard, from raw loan records, in one query.

You built a database

Step back and look at what just happened. You started with nothing and ended with a working library: four tables tied together by foreign keys, real data in them, and five queries answering questions that genuinely matter to whoever runs the place. None of it needed a new skill. Every piece came from an earlier lesson, snapped together.

That's the shape of real SQL work. The exercises were always one technique at a time, but actual questions don't arrive labelled "this is a join question." They arrive as "who's overdue?" and you decide it needs the still-out filter, two joins, and a date comparison. The skill that matters now isn't memorizing syntax. It's looking at a question and seeing which tools it's made of.

Here's the whole series, in the order the tools showed up:

• Why learn SQL and SELECT: getting data out
• WHERE, ORDER BY, LIMIT: filtering and sorting
• Aggregates, GROUP BY and HAVING: summarizing
• Joins and subqueries: combining tables
• INSERT / UPDATE / DELETE, CREATE TABLE, and foreign keys: building and changing data
• Indexes and window functions: performance and ranking

Where to go from here: point these same skills at a real database. SQLite ships on basically everything, Postgres runs every serious web app's data layer, and the SQL you wrote here transfers to both with tiny dialect tweaks. The official SQLite language reference is the place to look up the exact syntax for anything you reach for next. Design a database for something you actually care about (a workout log, a movie watchlist, your bookshelf) and start asking it questions. That's how this stops being lessons and starts being a tool you reach for.