Library database

What the finished program does

• The script runs from an empty database to the end without an error, with ON_ERROR_STOP on, and prints nothing except the five answers.
• books (id, title, author, year, genre, copies) is given in the starter as a model: id is the primary key, title and author are required, year is positive, copies is at least 0 and defaults to 1.
• members (id integer, name text, email text, city text, joined date): id is the primary key, name and joined are NOT NULL, and email is UNIQUE; email and city may be NULL.
• loans (id integer, book_id integer, member_id integer, loaned_on date, returned_on date): id is the primary key, book_id references books and member_id references members (both NOT NULL), loaned_on is NOT NULL, and a CHECK makes returned_on no earlier than loaned_on. So INSERT INTO loans VALUES (6, 1, 99, '2026-09-30', NULL) must fail with a foreign-key error.
• Load the sample data from the starter: 6 books, 4 members and 5 loans, with the ids given.
• Record that loan 2 (Kindred, borrowed by Ada) was returned on 2026-09-28, with an UPDATE that changes that one row only.
• Q1: the books on loan now (returned_on is NULL), with the columns title, name and loaned_on, oldest loan first.
• Q2: every member with their number of loans, members with none included as 0, in the columns name and loans, most loans first and then by name.
• Q3: the titles of the books that were never loaned, in the column title, in alphabetical order.
• Q4: the genres loaned more than once, in the columns genre and loans, ordered by genre.
• Q5: the members whose e-mail or city is unknown, in the columns name, email and city, where a missing e-mail shows as no e-mail and a missing city as unknown, ordered by name.

Starter layout

main.sql

The whole script: the schema (books is written for you), the sample data (the rows for members and loans are there, commented out), the change and the five answer queries, each marked TODO.

Milestones

1. Milestone 1

   Keys and constraints

   Write CREATE TABLE members and CREATE TABLE loans after the model of books: PRIMARY KEY, NOT NULL, UNIQUE on email, REFERENCES books and REFERENCES members, and a table CHECK comparing returned_on with loaned_on. Create members before loans, because loans refers to it.

   Checks that pass once this milestone is done:

   • books, members and loans each have a primary key
   • Every loan points at an existing member (a foreign key to members)
   • Every loan points at an existing book (a foreign key to books)
   • No e-mail address appears twice in members (a unique constraint)
   • A loan cannot be returned before it was loaned (a check on loans)
   • A member’s name and joining date are required (NOT NULL)

2. Milestone 2

   Load the data

   Remove the -- in front of the INSERT statements for members and loans. Then try INSERT INTO loans VALUES (6, 1, 99, '2026-09-30', NULL); once, read the foreign-key error, and delete the line again.

   Checks that pass once this milestone is done:

   • The data is loaded: 6 books, 4 members and 5 loans

3. Milestone 3

   Record a return

   Write one UPDATE with a WHERE on the loan id, so that only loan 2 changes. Without the WHERE, every loan would be marked as returned.

   Checks that pass once this milestone is done:

   • Loan 2 was returned on 2026-09-28, and two loans are still open

4. Milestone 4

   Answer the questions

   Write Q1 to Q5 in order, with the column names and the ORDER BY each requirement gives: joins for Q1, a LEFT JOIN with COUNT of a loans column for Q2, a LEFT JOIN with IS NULL for Q3, GROUP BY with HAVING for Q4 and COALESCE for Q5.

   Checks that pass once this milestone is done:

   • The five answers are printed in order, exactly as specified

Build it in the browser

Edit main.sql below; setup.sql stays as it is. “Check my code” runs every acceptance test on a fresh database, so expect failures until the last milestone.

Build it on your computer

Make a folder with these starter files and PostgreSQL 18, then work through the milestones. Run the acceptance tests at any point with:

There is no command for the checks on your computer yet. They are in test.sql: each check query prints t when it passes.

psql runs the files on your own PostgreSQL server. Use an empty database you can throw away: add -d and its name to the command.

-- Library database: schema, data, one change and five answers.
-- Work through the milestones in order and run the file after each one.
-- Every run starts from an empty database.

-- 1. Schema
-- books is written for you, as a model.
CREATE TABLE books (
  id integer PRIMARY KEY,
  title text NOT NULL,
  author text NOT NULL,
  year integer CHECK (year > 0),
  genre text,
  copies integer NOT NULL DEFAULT 1 CHECK (copies >= 0)
);

-- TODO: members (id, name, email, city, joined): id is the primary key,
-- name and joined are required, and no e-mail address may appear twice.

-- TODO: loans (id, book_id, member_id, loaned_on, returned_on): id is the
-- primary key, book_id and member_id are required and must point at an
-- existing book and member, loaned_on is required, and a book cannot be
-- returned before it was loaned.

-- 2. Data
INSERT INTO books (id, title, author, year, genre, copies) VALUES
  (1, 'Dune', 'Frank Herbert', 1965, 'sci-fi', 2),
  (2, 'Emma', 'Jane Austen', 1815, 'classic', 1),
  (3, 'Kindred', 'Octavia E. Butler', 1979, 'sci-fi', 1),
  (4, 'Beloved', 'Toni Morrison', 1987, 'fiction', 1),
  (5, 'The Hobbit', 'J. R. R. Tolkien', 1937, 'fantasy', 3),
  (6, 'Neuromancer', 'William Gibson', 1984, 'sci-fi', 1);

-- Remove the -- in front of these lines once members and loans exist.
-- INSERT INTO members (id, name, email, city, joined) VALUES
--   (1, 'Ada', 'ada@example.com', 'Berlin', '2025-01-15'),
--   (2, 'Grace', 'grace@example.com', 'Munich', '2025-03-02'),
--   (3, 'Linus', NULL, 'Berlin', '2026-02-10'),
--   (4, 'Margaret', 'margaret@example.com', NULL, '2026-05-20');

-- INSERT INTO loans (id, book_id, member_id, loaned_on, returned_on) VALUES
--   (1, 1, 1, '2026-09-01', '2026-09-10'),
--   (2, 3, 1, '2026-09-12', NULL),
--   (3, 2, 2, '2026-09-05', '2026-09-20'),
--   (4, 1, 3, '2026-09-15', NULL),
--   (5, 6, 2, '2026-09-18', NULL);

-- 3. A change
-- TODO: Ada brings Kindred back (loan 2) on 2026-09-28.

-- 4. Answers
-- TODO Q1. Which books are on loan right now, to whom, and since when?
-- TODO Q2. How many loans has each member made, including members with none?
-- TODO Q3. Which books have never been loaned?
-- TODO Q4. Which genres have been loaned more than once?
-- TODO Q5. Whose details are incomplete?

Acceptance tests

The project is done when every check in test.sql passes. Read them before you start: they are the spec, written as code.

-- test: books, members and loans each have a primary key
SELECT count(*) = 3 FROM pg_constraint
WHERE contype = 'p' AND conrelid::regclass::text IN ('books', 'members', 'loans');

-- test: Every loan points at an existing member (a foreign key to members)
SELECT EXISTS (SELECT 1 FROM pg_constraint
  WHERE contype = 'f' AND conrelid::regclass::text = 'loans' AND confrelid::regclass::text = 'members');

-- test: Every loan points at an existing book (a foreign key to books)
SELECT EXISTS (SELECT 1 FROM pg_constraint
  WHERE contype = 'f' AND conrelid::regclass::text = 'loans' AND confrelid::regclass::text = 'books');

-- test: No e-mail address appears twice in members (a unique constraint)
SELECT EXISTS (SELECT 1 FROM pg_constraint c
  JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY (c.conkey)
  WHERE c.contype = 'u' AND c.conrelid::regclass::text = 'members' AND a.attname = 'email');

-- test: A loan cannot be returned before it was loaned (a check on loans)
SELECT EXISTS (SELECT 1 FROM pg_constraint
  WHERE contype = 'c' AND conrelid::regclass::text = 'loans'
    AND pg_get_constraintdef(oid) LIKE '%returned_on%' AND pg_get_constraintdef(oid) LIKE '%loaned_on%');

-- test: A member’s name and joining date are required (NOT NULL)
SELECT count(*) = 2 FROM pg_attribute
WHERE attrelid::regclass::text = 'members' AND attname IN ('name', 'joined') AND attnotnull;

-- test: The data is loaded: 6 books, 4 members and 5 loans
SELECT (SELECT count(*) FROM books) = 6
   AND (SELECT count(*) FROM members) = 4
   AND (SELECT count(*) FROM loans) = 5;

-- test: Loan 2 was returned on 2026-09-28, and two loans are still open
SELECT EXISTS (SELECT 1 FROM loans WHERE id = 2 AND returned_on = '2026-09-28')
   AND (SELECT count(*) FROM loans WHERE returned_on IS NULL) = 2;

-- test: The five answers are printed in order, exactly as specified
-- output:
--     title    | name  | loaned_on
-- -------------+-------+------------
--  Dune        | Linus | 2026-09-15
--  Neuromancer | Grace | 2026-09-18
-- (2 rows)
--
--    name   | loans
-- ----------+-------
--  Ada      |     2
--  Grace    |     2
--  Linus    |     1
--  Margaret |     0
-- (4 rows)
--
--    title
-- ------------
--  Beloved
--  The Hobbit
-- (2 rows)
--
--  genre  | loans
-- --------+-------
--  sci-fi |     4
-- (1 row)
--
--    name   |        email         |  city
-- ----------+----------------------+---------
--  Linus    | no e-mail            | Berlin
--  Margaret | margaret@example.com | unknown
-- (2 rows)

Run the finished program

psql -X -q -v ON_ERROR_STOP=1 -f main.sql

Take it further

• Add a reservations table (member, book, reserved_on) with foreign keys, and a query that lists the books a member is waiting for.
• Add a query that lists each member’s open loans that are older than 14 days on a date you choose, with the number of days.
• Give members.id and loans.id an identity column (GENERATED ALWAYS AS IDENTITY) and insert new rows without writing the ids; use RETURNING id to see the id each one got.
• Add ON DELETE rules to the foreign keys and find out what happens to a member’s loans when the member is deleted, with RESTRICT and with CASCADE.
• Once you know window functions (module I2), rank the books by number of loans within each genre.