typestar

library_schema.sql en SQL

Un esquema pequeño desde cero: tres tablas, las reglas entre ellas y dos reportes.

-- Una biblioteca de préstamos: todo el esquema, algo de datos y las dos
-- preguntas que todo el mundo le hace.

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);

-- ¿Qué ejemplares están en un estante ahora mismo?
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;

-- ¿Quién va atrasado y por cuánto tiempo?
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;

Cómo funciona

  1. Cada tabla declara lo que no aceptará, así ningún llamador tiene que recordarlo.
  2. Los índices siguen a las consultas del final, no al revés.
  3. Re-ejecutable: cada objeto se elimina antes de crearse.

Palabras clave y builtins usados aquí

El intento, en números

Líneas
81
Caracteres a escribir
2185
Tokens
478
Ritmo de tres estrellas
85 tpm

Al ritmo de tres estrellas de 85 tokens por minuto, este intento toma unos 337 segundos.

Escribe este fragmento

Paso 2 de 2 en Bis; paso 23 de 23 en Fundamentos del lenguaje.

← Anterior