library_schema.sql in SQL

A small schema from nothing: three tables, the rules between them, and two reports.

-- A lending library: the whole schema, some data, and the two questions
-- anyone ever asks of it.

DROP TABLE IF EXISTS loans;
DROP TABLE IF EXISTS books;
DROP TABLE IF EXISTS authors;

CREATE TABLE authors (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    born INTEGER CHECK (born BETWEEN 1000 AND 2100)
);

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    author_id INTEGER NOT NULL REFERENCES authors(id) ON DELETE RESTRICT,
    title TEXT NOT NULL,
    isbn TEXT NOT NULL UNIQUE,
    copies INTEGER NOT NULL DEFAULT 1 CHECK (copies > 0),
    UNIQUE (author_id, title)
);

CREATE TABLE loans (
    id INTEGER PRIMARY KEY,
    book_id INTEGER NOT NULL REFERENCES books(id) ON DELETE CASCADE,
    borrower TEXT NOT NULL,
    taken_on TEXT NOT NULL DEFAULT CURRENT_DATE,
    due_on TEXT NOT NULL,
    returned_on TEXT,
    CHECK (due_on > taken_on),
    CHECK (returned_on IS NULL OR returned_on >= taken_on)
);

CREATE INDEX loans_open
    ON loans (book_id)
    WHERE returned_on IS NULL;

CREATE INDEX loans_by_due
    ON loans (due_on);

INSERT INTO authors (id, name, born)
VALUES (1, 'Ursula K. Le Guin', 1929),
       (2, 'Italo Calvino', 1923);

INSERT INTO books (id, author_id, title, isbn, copies)
VALUES (1, 1, 'A Wizard of Earthsea', '978-0553262506', 3),
       (2, 1, 'The Dispossessed', '978-0060512750', 2),
       (3, 2, 'Invisible Cities', '978-0156453804', 1);

INSERT INTO loans (book_id, borrower, taken_on, due_on, returned_on)
VALUES (1, 'ada', '2026-06-01', '2026-06-22', '2026-06-19'),
       (1, 'grace', '2026-07-05', '2026-07-26', NULL),
       (3, 'ada', '2026-07-10', '2026-07-31', NULL);

-- Which copies are on a shelf right now?
SELECT
    b.title,
    a.name AS author,
    b.copies,
    COUNT(l.id) FILTER (WHERE l.returned_on IS NULL) AS on_loan,
    b.copies - COUNT(l.id) FILTER (WHERE l.returned_on IS NULL) AS available
FROM books AS b
JOIN authors AS a
    ON a.id = b.author_id
LEFT JOIN loans AS l
    ON l.book_id = b.id
GROUP BY b.id
ORDER BY available, b.title;

-- Who is late, and by how long?
SELECT
    l.borrower,
    b.title,
    l.due_on,
    CAST(JULIANDAY('now') - JULIANDAY(l.due_on) AS INTEGER) AS days_late
FROM loans AS l
JOIN books AS b
    ON b.id = l.book_id
WHERE l.returned_on IS NULL
    AND l.due_on < DATE('now')
ORDER BY days_late DESC;

How it works

Every table declares what it will not accept, so no caller has to remember.
The indexes follow the queries at the bottom, not the other way round.
Re-runnable: each object is dropped before it is created.

Keywords and builtins used here

AND
AS
BETWEEN
BY
CASCADE
CAST
CHECK
COUNT
CREATE
CURRENT_DATE
DATE
DEFAULT
DELETE
DESC
DROP
EXISTS
FROM
GROUP
IF
INDEX
INSERT
INTEGER
INTO
IS
JOIN
KEY
LEFT
NOT
NULL
ON
OR
ORDER
PRIMARY
REFERENCES
RESTRICT
SELECT
TABLE
TEXT
UNIQUE
VALUES
WHERE

The run, in numbers

Lines: 81
Characters to type: 2152
Tokens: 478
Three-star pace: 85 tpm

At the three-star pace of 85 tokens a minute, this run takes about 337 seconds.

Type this snippet

Step 2 of 2 in Encore, step 23 of 23 in Language basics.

← Previous