Projeto: Biblioteca

Aos sábados, a Ana é voluntária na biblioteca comunitária do bairro, em Teresina. Os empréstimos são anotados num caderno: quem levou, que livro, em que dia. Para saber quem está atrasado, alguém folheia o caderno inteiro e conta os dias no calendário. Neste projeto você troca o caderno por um banco de dados, construído em versões: começa com uma tabela só, descobre por que ela não basta, separa os autores, registra sócios e empréstimos e, no fim, o banco calcula sozinho os atrasos, as multas e os livros que estão na estante.

Você vai aprender

A começar simples (uma tabela) e a perceber quando é hora de dividir.
Por que o nome do autor vai para uma tabela própria, ligada por chave estrangeira.
A registrar empréstimos com data de retirada e de devolução, e a achar os que estão em aberto com IS NULL.
A calcular dias, prazos e multas com julianday e date.
A contar exemplares disponíveis com LEFT JOIN, e a armadilha do filtro no lugar errado.
A proteger o banco com CHECK e UNIQUE, e a emprestar e devolver com INSERT e UPDATE.

Como as caixas deste projeto funcionam

A biblioteca é um banco novo, que não tem nada a ver com a Loja Aurora. Só que o editor do guia, a cada Rodar, cria um banco limpo e carrega a Aurora; ele não lembra o que você criou no Rodar anterior. Por isso o SQL de cada caixa faz tudo, do começo:

Monta a biblioteca: CREATE TABLE e INSERT das tabelas daquela versão.
Consulta: o SELECT que responde à pergunta da versão.

A montagem se repete de caixa em caixa, sempre igual à da versão anterior, mais a novidade. As tabelas da Aurora continuam lá, mas os nomes não se misturam: a biblioteca usa livros, autores, socios e emprestimos, que a Aurora não tem. Fora do editor, num arquivo de verdade, você montaria uma vez só (veja o projeto a Loja Aurora Completa, no fim).

Versão 1: uma tabela só, igual ao caderno

O jeito mais rápido de começar é copiar o caderno: uma tabela de livros, com o título, o autor e o ano.

CREATE TABLE livros (
    id     INTEGER PRIMARY KEY,
    titulo TEXT NOT NULL,
    autor  TEXT,
    ano    INTEGER
);

INSERT INTO livros (titulo, autor, ano) VALUES
('Dom Casmurro',                    'Machado de Assis', 1899),
('Memórias Póstumas de Brás Cubas', 'Machado de assis', 1881),
('Iracema',                         'José de Alencar',  1865),
('O Guarani',                       'José de Alencar',  1857);

SELECT * FROM livros;

id INTEGER PRIMARY KEY: O número de cada livro. O INSERT não informou o id, e o SQLite numerou sozinho: 1, 2, 3, 4 (aula PRIMARY KEY).
titulo TEXT NOT NULL: Todo livro tem título (aula NOT NULL).
autor TEXT: O nome do autor, digitado em cada livro, como no caderno.
ano INTEGER: O ano da primeira edição: um número inteiro, que dá para comparar e ordenar.

Funciona, e para quatro livros parece ótimo. Mas olhe o segundo livro: quem digitou escreveu Machado de assis, com "a" minúsculo. Guarde isso; a próxima versão mostra o estrago.

Missão: chegaram duas doações. Cadastre O Cortiço, de Aluísio Azevedo, de 1890, e Triste Fim de Policarpo Quaresma, de Lima Barreto, de 1915. Termine listando os livros do mais antigo para o mais novo (aula ORDER BY).

CREATE TABLE livros (
    id     INTEGER PRIMARY KEY,
    titulo TEXT NOT NULL,
    autor  TEXT,
    ano    INTEGER
);

INSERT INTO livros (titulo, autor, ano) VALUES
('Dom Casmurro',                    'Machado de Assis', 1899),
('Memórias Póstumas de Brás Cubas', 'Machado de assis', 1881),
('Iracema',                         'José de Alencar',  1865),
('O Guarani',                       'José de Alencar',  1857);

-- as doações:


SELECT * FROM livros;

CREATE TABLE livros (
    id     INTEGER PRIMARY KEY,
    titulo TEXT NOT NULL,
    autor  TEXT,
    ano    INTEGER
);

INSERT INTO livros (titulo, autor, ano) VALUES
('Dom Casmurro',                    'Machado de Assis', 1899),
('Memórias Póstumas de Brás Cubas', 'Machado de assis', 1881),
('Iracema',                         'José de Alencar',  1865),
('O Guarani',                       'José de Alencar',  1857);

-- as doações:
INSERT INTO livros (titulo, autor, ano) VALUES
('O Cortiço',                        'Aluísio Azevedo', 1890),
('Triste Fim de Policarpo Quaresma', 'Lima Barreto',    1915);

SELECT * FROM livros ORDER BY ano;

Versão 2: os autores ganham uma tabela

A coordenadora da biblioteca pergunta: "quantos livros temos de cada autor?". Um GROUP BY responde (aula GROUP BY), e entrega o problema:

CREATE TABLE livros (id INTEGER PRIMARY KEY, titulo TEXT NOT NULL, autor TEXT, ano INTEGER);
INSERT INTO livros (titulo, autor, ano) VALUES
('Dom Casmurro',                    'Machado de Assis', 1899),
('Memórias Póstumas de Brás Cubas', 'Machado de assis', 1881),
('Iracema',                         'José de Alencar',  1865),
('O Guarani',                       'José de Alencar',  1857);

SELECT autor, COUNT(*) AS livros
FROM livros
GROUP BY autor;

Para o banco, Machado de Assis e Machado de assis são textos diferentes, e o Machado virou dois autores com um livro cada. Com o nome digitado em cada livro, todo erro de digitação cria um autor "novo", e corrigir exige achar cada cópia. A saída é a mesma que a Loja Aurora usa para as categorias: o autor vira uma tabela própria, com o nome escrito uma vez só, e o livro guarda apenas o número dele (aulas Tabelas que se Relacionam e Normalização).

CREATE TABLE autores (
    id   INTEGER PRIMARY KEY,
    nome TEXT NOT NULL
);
INSERT INTO autores VALUES
(1, 'Machado de Assis'), (2, 'José de Alencar'),
(3, 'Aluísio Azevedo'),  (4, 'Lima Barreto');

CREATE TABLE livros (
    id         INTEGER PRIMARY KEY,
    titulo     TEXT NOT NULL,
    autor_id   INTEGER NOT NULL REFERENCES autores(id),
    ano        INTEGER,
    exemplares INTEGER NOT NULL DEFAULT 1
);
INSERT INTO livros VALUES
(1, 'Dom Casmurro',                     1, 1899, 2),
(2, 'Memórias Póstumas de Brás Cubas',  1, 1881, 1),
(3, 'Iracema',                          2, 1865, 1),
(4, 'O Guarani',                        2, 1857, 1),
(5, 'O Cortiço',                        3, 1890, 1),
(6, 'Triste Fim de Policarpo Quaresma', 4, 1915, 1);

SELECT a.nome AS autor, COUNT(*) AS livros
FROM livros l
JOIN autores a ON a.id = l.autor_id
GROUP BY a.id;

autores: Uma linha por autor. O nome do Machado existe num lugar só: se estiver errado, corrige-se uma vez.
autor_id … REFERENCES autores(id): A chave estrangeira: o livro aponta para o autor pelo número, e o banco não deixa apontar para um autor que não existe (aula FOREIGN KEY).
exemplares INTEGER NOT NULL DEFAULT 1: Quantos volumes daquele título a biblioteca tem. Dom Casmurro tem 2; os outros, 1. Vai ser útil na versão 5.
JOIN autores a ON a.id = l.autor_id: Na consulta, o número volta a ser nome (aula INNER JOIN).

Agora o Machado de Assis aparece uma vez, com 2 livros. Repare que o INSERT de livros não lista as colunas: os valores vêm na ordem em que as colunas foram criadas.

Missão: monte o catálogo da estante: os 6 livros com as colunas exatamente titulo, autor (o nome, não o número) e ano, em ordem alfabética de título.

CREATE TABLE autores (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO autores VALUES
(1, 'Machado de Assis'), (2, 'José de Alencar'),
(3, 'Aluísio Azevedo'),  (4, 'Lima Barreto');
CREATE TABLE livros (id INTEGER PRIMARY KEY, titulo TEXT NOT NULL,
    autor_id INTEGER NOT NULL REFERENCES autores(id), ano INTEGER,
    exemplares INTEGER NOT NULL DEFAULT 1);
INSERT INTO livros VALUES
(1, 'Dom Casmurro', 1, 1899, 2), (2, 'Memórias Póstumas de Brás Cubas', 1, 1881, 1),
(3, 'Iracema', 2, 1865, 1),      (4, 'O Guarani', 2, 1857, 1),
(5, 'O Cortiço', 3, 1890, 1),    (6, 'Triste Fim de Policarpo Quaresma', 4, 1915, 1);

-- o catálogo:
SELECT titulo, autor_id, ano FROM livros;

CREATE TABLE autores (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO autores VALUES
(1, 'Machado de Assis'), (2, 'José de Alencar'),
(3, 'Aluísio Azevedo'),  (4, 'Lima Barreto');
CREATE TABLE livros (id INTEGER PRIMARY KEY, titulo TEXT NOT NULL,
    autor_id INTEGER NOT NULL REFERENCES autores(id), ano INTEGER,
    exemplares INTEGER NOT NULL DEFAULT 1);
INSERT INTO livros VALUES
(1, 'Dom Casmurro', 1, 1899, 2), (2, 'Memórias Póstumas de Brás Cubas', 1, 1881, 1),
(3, 'Iracema', 2, 1865, 1),      (4, 'O Guarani', 2, 1857, 1),
(5, 'O Cortiço', 3, 1890, 1),    (6, 'Triste Fim de Policarpo Quaresma', 4, 1915, 1);

-- o catálogo:
SELECT l.titulo, a.nome AS autor, l.ano
FROM livros l
JOIN autores a ON a.id = l.autor_id
ORDER BY l.titulo;

Versão 3: sócios e empréstimos

O caderno da biblioteca, na verdade, é uma lista de empréstimos: "a Helena levou Dom Casmurro no dia 1º, devolveu no dia 10". Cada empréstimo liga um livro a um sócio, com duas datas. A data de devolução fica vazia (NULL) enquanto o livro não volta (aula O Valor NULL).

-- montagem da versão 2
CREATE TABLE autores (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO autores VALUES
(1, 'Machado de Assis'), (2, 'José de Alencar'),
(3, 'Aluísio Azevedo'),  (4, 'Lima Barreto');
CREATE TABLE livros (id INTEGER PRIMARY KEY, titulo TEXT NOT NULL,
    autor_id INTEGER NOT NULL REFERENCES autores(id), ano INTEGER,
    exemplares INTEGER NOT NULL DEFAULT 1);
INSERT INTO livros VALUES
(1, 'Dom Casmurro', 1, 1899, 2), (2, 'Memórias Póstumas de Brás Cubas', 1, 1881, 1),
(3, 'Iracema', 2, 1865, 1),      (4, 'O Guarani', 2, 1857, 1),
(5, 'O Cortiço', 3, 1890, 1),    (6, 'Triste Fim de Policarpo Quaresma', 4, 1915, 1);

-- NOVO: sócios
CREATE TABLE socios (
    id   INTEGER PRIMARY KEY,
    nome TEXT NOT NULL
);
INSERT INTO socios VALUES
(1, 'Helena Duarte'), (2, 'Jonas Ribeiro'), (3, 'Clara Nogueira'),
(4, 'Davi Lemos'),    (5, 'Marina Coelho');

-- NOVO: empréstimos
CREATE TABLE emprestimos (
    id        INTEGER PRIMARY KEY,
    livro_id  INTEGER NOT NULL REFERENCES livros(id),
    socio_id  INTEGER NOT NULL REFERENCES socios(id),
    retirada  TEXT NOT NULL,
    devolucao TEXT
);
INSERT INTO emprestimos VALUES
(1, 1, 1, '2026-09-01', '2026-09-10'),
(2, 3, 2, '2026-09-05', '2026-09-25'),
(3, 1, 3, '2026-09-15', '2026-09-28'),
(4, 5, 1, '2026-09-20', NULL),
(5, 2, 2, '2026-10-01', NULL),
(6, 1, 4, '2026-10-10', NULL),
(7, 6, 3, '2026-10-12', NULL);

SELECT e.id, s.nome AS socio, l.titulo, e.retirada, e.devolucao
FROM emprestimos e
JOIN socios s ON s.id = e.socio_id
JOIN livros l ON l.id = e.livro_id;

emprestimos: A tabela "do meio": cada linha liga um livro a um sócio. Um livro tem muitos empréstimos ao longo do tempo e um sócio faz muitos: é uma relação muitos-para-muitos com informação própria, as datas (aula Relação Muitos-para-Muitos).
retirada TEXT NOT NULL: Todo empréstimo tem data de saída, no formato AAAA-MM-DD, o que as funções de data entendem (aula Datas e Horas).
devolucao TEXT: Sem NOT NULL: vazia quer dizer "o livro ainda está com o sócio".
dois JOINs: O empréstimo sabe os números; os JOINs trazem o nome do sócio e o título.

Repare que Dom Casmurro saiu três vezes (empréstimos 1, 3 e 6) e que a Marina Coelho, sócia 5, nunca pegou nada.

Missão: a pergunta de todo sábado: quem está com livro agora? Mostre o nome do sócio, o título e a data de retirada só dos empréstimos ainda não devolvidos (aula IS NULL e IS NOT NULL), do mais antigo para o mais recente.

CREATE TABLE autores (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO autores VALUES (1, 'Machado de Assis'), (2, 'José de Alencar'),
                           (3, 'Aluísio Azevedo'), (4, 'Lima Barreto');
CREATE TABLE livros (id INTEGER PRIMARY KEY, titulo TEXT NOT NULL,
    autor_id INTEGER NOT NULL REFERENCES autores(id), ano INTEGER,
    exemplares INTEGER NOT NULL DEFAULT 1);
INSERT INTO livros VALUES
(1, 'Dom Casmurro', 1, 1899, 2), (2, 'Memórias Póstumas de Brás Cubas', 1, 1881, 1),
(3, 'Iracema', 2, 1865, 1),      (4, 'O Guarani', 2, 1857, 1),
(5, 'O Cortiço', 3, 1890, 1),    (6, 'Triste Fim de Policarpo Quaresma', 4, 1915, 1);
CREATE TABLE socios (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO socios VALUES (1, 'Helena Duarte'), (2, 'Jonas Ribeiro'),
    (3, 'Clara Nogueira'), (4, 'Davi Lemos'), (5, 'Marina Coelho');
CREATE TABLE emprestimos (id INTEGER PRIMARY KEY,
    livro_id INTEGER NOT NULL REFERENCES livros(id),
    socio_id INTEGER NOT NULL REFERENCES socios(id),
    retirada TEXT NOT NULL, devolucao TEXT);
INSERT INTO emprestimos VALUES
(1, 1, 1, '2026-09-01', '2026-09-10'), (2, 3, 2, '2026-09-05', '2026-09-25'),
(3, 1, 3, '2026-09-15', '2026-09-28'), (4, 5, 1, '2026-09-20', NULL),
(5, 2, 2, '2026-10-01', NULL),         (6, 1, 4, '2026-10-10', NULL),
(7, 6, 3, '2026-10-12', NULL);

-- quem está com livro agora?
SELECT socio_id, livro_id, retirada FROM emprestimos;

CREATE TABLE autores (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO autores VALUES (1, 'Machado de Assis'), (2, 'José de Alencar'),
                           (3, 'Aluísio Azevedo'), (4, 'Lima Barreto');
CREATE TABLE livros (id INTEGER PRIMARY KEY, titulo TEXT NOT NULL,
    autor_id INTEGER NOT NULL REFERENCES autores(id), ano INTEGER,
    exemplares INTEGER NOT NULL DEFAULT 1);
INSERT INTO livros VALUES
(1, 'Dom Casmurro', 1, 1899, 2), (2, 'Memórias Póstumas de Brás Cubas', 1, 1881, 1),
(3, 'Iracema', 2, 1865, 1),      (4, 'O Guarani', 2, 1857, 1),
(5, 'O Cortiço', 3, 1890, 1),    (6, 'Triste Fim de Policarpo Quaresma', 4, 1915, 1);
CREATE TABLE socios (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO socios VALUES (1, 'Helena Duarte'), (2, 'Jonas Ribeiro'),
    (3, 'Clara Nogueira'), (4, 'Davi Lemos'), (5, 'Marina Coelho');
CREATE TABLE emprestimos (id INTEGER PRIMARY KEY,
    livro_id INTEGER NOT NULL REFERENCES livros(id),
    socio_id INTEGER NOT NULL REFERENCES socios(id),
    retirada TEXT NOT NULL, devolucao TEXT);
INSERT INTO emprestimos VALUES
(1, 1, 1, '2026-09-01', '2026-09-10'), (2, 3, 2, '2026-09-05', '2026-09-25'),
(3, 1, 3, '2026-09-15', '2026-09-28'), (4, 5, 1, '2026-09-20', NULL),
(5, 2, 2, '2026-10-01', NULL),         (6, 1, 4, '2026-10-10', NULL),
(7, 6, 3, '2026-10-12', NULL);

-- quem está com livro agora?
SELECT s.nome AS socio, l.titulo, e.retirada
FROM emprestimos e
JOIN socios s ON s.id = e.socio_id
JOIN livros l ON l.id = e.livro_id
WHERE e.devolucao IS NULL
ORDER BY e.retirada;

Daqui em diante, todo exemplo e toda caixa começam com a montagem desta missão: as quatro tabelas, em forma compacta, sempre iguais. Pule direto para o fim de cada uma: é lá que está a consulta nova.

Versão 4: prazos, atrasos e multas

A regra da biblioteca: o prazo é de 14 dias e cada dia de atraso custa R$ 0,50. Para contar dias entre duas datas, o SQLite tem a função julianday: ela transforma uma data num número de dias, e a diferença entre dois desses números é a distância em dias (aula Funções de Data).

Um detalhe: para o resultado desta página não mudar a cada dia, o "hoje" do projeto é fixo, 18 de outubro de 2026, escrito como '2026-10-18'. Num sistema de verdade, no lugar dele vai date('now'), a data do dia.

CREATE TABLE autores (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO autores VALUES (1, 'Machado de Assis'), (2, 'José de Alencar'),
                           (3, 'Aluísio Azevedo'), (4, 'Lima Barreto');
CREATE TABLE livros (id INTEGER PRIMARY KEY, titulo TEXT NOT NULL,
    autor_id INTEGER NOT NULL REFERENCES autores(id), ano INTEGER,
    exemplares INTEGER NOT NULL DEFAULT 1);
INSERT INTO livros VALUES
(1, 'Dom Casmurro', 1, 1899, 2), (2, 'Memórias Póstumas de Brás Cubas', 1, 1881, 1),
(3, 'Iracema', 2, 1865, 1),      (4, 'O Guarani', 2, 1857, 1),
(5, 'O Cortiço', 3, 1890, 1),    (6, 'Triste Fim de Policarpo Quaresma', 4, 1915, 1);
CREATE TABLE socios (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO socios VALUES (1, 'Helena Duarte'), (2, 'Jonas Ribeiro'),
    (3, 'Clara Nogueira'), (4, 'Davi Lemos'), (5, 'Marina Coelho');
CREATE TABLE emprestimos (id INTEGER PRIMARY KEY,
    livro_id INTEGER NOT NULL REFERENCES livros(id),
    socio_id INTEGER NOT NULL REFERENCES socios(id),
    retirada TEXT NOT NULL, devolucao TEXT);
INSERT INTO emprestimos VALUES
(1, 1, 1, '2026-09-01', '2026-09-10'), (2, 3, 2, '2026-09-05', '2026-09-25'),
(3, 1, 3, '2026-09-15', '2026-09-28'), (4, 5, 1, '2026-09-20', NULL),
(5, 2, 2, '2026-10-01', NULL),         (6, 1, 4, '2026-10-10', NULL),
(7, 6, 3, '2026-10-12', NULL);

-- os empréstimos em aberto, com o prazo e os dias corridos
SELECT s.nome AS socio, l.titulo, e.retirada,
       date(e.retirada, '+14 days') AS prazo,
       CAST(julianday('2026-10-18') - julianday(e.retirada) AS INTEGER) AS dias
FROM emprestimos e
JOIN socios s ON s.id = e.socio_id
JOIN livros l ON l.id = e.livro_id
WHERE e.devolucao IS NULL;

date(e.retirada, '+14 days'): A data de retirada mais 14 dias: o último dia do prazo. A Helena levou O Cortiço em 20/09 e devia ter devolvido até 04/10.
julianday('2026-10-18') - julianday(e.retirada): Quantos dias se passaram desde a retirada. O resultado vem com casa decimal (28.0), porque o julianday também conta horas.
CAST(… AS INTEGER): Converte para inteiro: 28 (aula Os Tipos no SQLite).

A Helena está com O Cortiço há 28 dias, e o Jonas com Memórias Póstumas há 17. Os dois passaram dos 14. O atraso é "dias menos 14", mas só quando isso dá positivo; para quem está dentro do prazo, o atraso é zero. O MAX com dois valores devolve o maior deles, e resolve: MAX(dias - 14, 0).

Missão: monte a lista de cobrança: só os empréstimos em aberto atrasados, com as colunas socio, titulo, atraso (em dias) e multa (R$ 0,50 por dia). A consulta de dentro já calcula os dias; use-a num WITH (aula CTE (WITH)) e faça as contas por fora.

CREATE TABLE autores (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO autores VALUES (1, 'Machado de Assis'), (2, 'José de Alencar'),
                           (3, 'Aluísio Azevedo'), (4, 'Lima Barreto');
CREATE TABLE livros (id INTEGER PRIMARY KEY, titulo TEXT NOT NULL,
    autor_id INTEGER NOT NULL REFERENCES autores(id), ano INTEGER,
    exemplares INTEGER NOT NULL DEFAULT 1);
INSERT INTO livros VALUES
(1, 'Dom Casmurro', 1, 1899, 2), (2, 'Memórias Póstumas de Brás Cubas', 1, 1881, 1),
(3, 'Iracema', 2, 1865, 1),      (4, 'O Guarani', 2, 1857, 1),
(5, 'O Cortiço', 3, 1890, 1),    (6, 'Triste Fim de Policarpo Quaresma', 4, 1915, 1);
CREATE TABLE socios (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO socios VALUES (1, 'Helena Duarte'), (2, 'Jonas Ribeiro'),
    (3, 'Clara Nogueira'), (4, 'Davi Lemos'), (5, 'Marina Coelho');
CREATE TABLE emprestimos (id INTEGER PRIMARY KEY,
    livro_id INTEGER NOT NULL REFERENCES livros(id),
    socio_id INTEGER NOT NULL REFERENCES socios(id),
    retirada TEXT NOT NULL, devolucao TEXT);
INSERT INTO emprestimos VALUES
(1, 1, 1, '2026-09-01', '2026-09-10'), (2, 3, 2, '2026-09-05', '2026-09-25'),
(3, 1, 3, '2026-09-15', '2026-09-28'), (4, 5, 1, '2026-09-20', NULL),
(5, 2, 2, '2026-10-01', NULL),         (6, 1, 4, '2026-10-10', NULL),
(7, 6, 3, '2026-10-12', NULL);

WITH abertos AS (
    SELECT s.nome AS socio, l.titulo,
           CAST(julianday('2026-10-18') - julianday(e.retirada) AS INTEGER) AS dias
    FROM emprestimos e
    JOIN socios s ON s.id = e.socio_id
    JOIN livros l ON l.id = e.livro_id
    WHERE e.devolucao IS NULL
)
SELECT socio, titulo, dias
FROM abertos;

CREATE TABLE autores (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO autores VALUES (1, 'Machado de Assis'), (2, 'José de Alencar'),
                           (3, 'Aluísio Azevedo'), (4, 'Lima Barreto');
CREATE TABLE livros (id INTEGER PRIMARY KEY, titulo TEXT NOT NULL,
    autor_id INTEGER NOT NULL REFERENCES autores(id), ano INTEGER,
    exemplares INTEGER NOT NULL DEFAULT 1);
INSERT INTO livros VALUES
(1, 'Dom Casmurro', 1, 1899, 2), (2, 'Memórias Póstumas de Brás Cubas', 1, 1881, 1),
(3, 'Iracema', 2, 1865, 1),      (4, 'O Guarani', 2, 1857, 1),
(5, 'O Cortiço', 3, 1890, 1),    (6, 'Triste Fim de Policarpo Quaresma', 4, 1915, 1);
CREATE TABLE socios (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO socios VALUES (1, 'Helena Duarte'), (2, 'Jonas Ribeiro'),
    (3, 'Clara Nogueira'), (4, 'Davi Lemos'), (5, 'Marina Coelho');
CREATE TABLE emprestimos (id INTEGER PRIMARY KEY,
    livro_id INTEGER NOT NULL REFERENCES livros(id),
    socio_id INTEGER NOT NULL REFERENCES socios(id),
    retirada TEXT NOT NULL, devolucao TEXT);
INSERT INTO emprestimos VALUES
(1, 1, 1, '2026-09-01', '2026-09-10'), (2, 3, 2, '2026-09-05', '2026-09-25'),
(3, 1, 3, '2026-09-15', '2026-09-28'), (4, 5, 1, '2026-09-20', NULL),
(5, 2, 2, '2026-10-01', NULL),         (6, 1, 4, '2026-10-10', NULL),
(7, 6, 3, '2026-10-12', NULL);

WITH abertos AS (
    SELECT s.nome AS socio, l.titulo,
           CAST(julianday('2026-10-18') - julianday(e.retirada) AS INTEGER) AS dias
    FROM emprestimos e
    JOIN socios s ON s.id = e.socio_id
    JOIN livros l ON l.id = e.livro_id
    WHERE e.devolucao IS NULL
)
SELECT socio, titulo,
       dias - 14         AS atraso,
       (dias - 14) * 0.5 AS multa
FROM abertos
WHERE dias > 14;

A Helena deve 14 dias de atraso, R$ 7,00; o Jonas, 3 dias, R$ 1,50. Aqui o WHERE dias > 14 já tira quem está no prazo, então nem foi preciso o MAX. Ele faz falta quando você quer todos os empréstimos na lista, com atraso zero para quem está em dia:

Para	Escreva	Resultado
Só os atrasados	`SELECT dias - 14 … WHERE dias > 14`	2 linhas
Todos, com zero para os em dia	`MAX(dias - 14, 0) AS atraso`	4 linhas
Todos, com um texto	`CASE WHEN dias > 14 THEN 'atrasado' ELSE 'no prazo' END`	4 linhas (aula CASE)
Comparar datas direto	`WHERE date(e.retirada, '+14 days') < '2026-10-18'`	2 linhas, sem contar dias

Versão 5: o que está na estante

Um sócio chega e pede Dom Casmurro. Tem na estante? A biblioteca tem 2 exemplares, e um está emprestado: sobra 1. A conta é "exemplares menos empréstimos em aberto", para cada livro, inclusive os que não têm empréstimo nenhum. "Inclusive os que não têm par" é trabalho do LEFT JOIN (aula LEFT JOIN):

CREATE TABLE autores (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO autores VALUES (1, 'Machado de Assis'), (2, 'José de Alencar'),
                           (3, 'Aluísio Azevedo'), (4, 'Lima Barreto');
CREATE TABLE livros (id INTEGER PRIMARY KEY, titulo TEXT NOT NULL,
    autor_id INTEGER NOT NULL REFERENCES autores(id), ano INTEGER,
    exemplares INTEGER NOT NULL DEFAULT 1);
INSERT INTO livros VALUES
(1, 'Dom Casmurro', 1, 1899, 2), (2, 'Memórias Póstumas de Brás Cubas', 1, 1881, 1),
(3, 'Iracema', 2, 1865, 1),      (4, 'O Guarani', 2, 1857, 1),
(5, 'O Cortiço', 3, 1890, 1),    (6, 'Triste Fim de Policarpo Quaresma', 4, 1915, 1);
CREATE TABLE socios (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO socios VALUES (1, 'Helena Duarte'), (2, 'Jonas Ribeiro'),
    (3, 'Clara Nogueira'), (4, 'Davi Lemos'), (5, 'Marina Coelho');
CREATE TABLE emprestimos (id INTEGER PRIMARY KEY,
    livro_id INTEGER NOT NULL REFERENCES livros(id),
    socio_id INTEGER NOT NULL REFERENCES socios(id),
    retirada TEXT NOT NULL, devolucao TEXT);
INSERT INTO emprestimos VALUES
(1, 1, 1, '2026-09-01', '2026-09-10'), (2, 3, 2, '2026-09-05', '2026-09-25'),
(3, 1, 3, '2026-09-15', '2026-09-28'), (4, 5, 1, '2026-09-20', NULL),
(5, 2, 2, '2026-10-01', NULL),         (6, 1, 4, '2026-10-10', NULL),
(7, 6, 3, '2026-10-12', NULL);

SELECT l.titulo, l.exemplares,
       COUNT(e.id)                AS emprestados,
       l.exemplares - COUNT(e.id) AS disponiveis
FROM livros l
LEFT JOIN emprestimos e
       ON e.livro_id = l.id
      AND e.devolucao IS NULL
GROUP BY l.id;

LEFT JOIN emprestimos e ON … AND e.devolucao IS NULL: Junta cada livro só com os seus empréstimos em aberto. A condição da devolução está no ON, não no WHERE: assim, o livro sem empréstimo aberto continua na lista, com as colunas de e vazias. (Ponha a condição no WHERE e veja o que acontece em Quando dá errado.)
COUNT(e.id): Conta só os valores não vazios. Para Iracema, o e.id é NULL, e a contagem dá 0. Um COUNT(*) daria 1, porque conta a linha (aula COUNT()).
l.exemplares - COUNT(e.id): O que sobra na estante.

Missão: o sócio não quer ver a lista toda, só o que dá para levar agora. Mostre o titulo e os disponiveis só dos livros com pelo menos um exemplar na estante. Lembre-se: o filtro sobre um COUNT vai no HAVING (aula HAVING).

CREATE TABLE autores (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO autores VALUES (1, 'Machado de Assis'), (2, 'José de Alencar'),
                           (3, 'Aluísio Azevedo'), (4, 'Lima Barreto');
CREATE TABLE livros (id INTEGER PRIMARY KEY, titulo TEXT NOT NULL,
    autor_id INTEGER NOT NULL REFERENCES autores(id), ano INTEGER,
    exemplares INTEGER NOT NULL DEFAULT 1);
INSERT INTO livros VALUES
(1, 'Dom Casmurro', 1, 1899, 2), (2, 'Memórias Póstumas de Brás Cubas', 1, 1881, 1),
(3, 'Iracema', 2, 1865, 1),      (4, 'O Guarani', 2, 1857, 1),
(5, 'O Cortiço', 3, 1890, 1),    (6, 'Triste Fim de Policarpo Quaresma', 4, 1915, 1);
CREATE TABLE socios (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO socios VALUES (1, 'Helena Duarte'), (2, 'Jonas Ribeiro'),
    (3, 'Clara Nogueira'), (4, 'Davi Lemos'), (5, 'Marina Coelho');
CREATE TABLE emprestimos (id INTEGER PRIMARY KEY,
    livro_id INTEGER NOT NULL REFERENCES livros(id),
    socio_id INTEGER NOT NULL REFERENCES socios(id),
    retirada TEXT NOT NULL, devolucao TEXT);
INSERT INTO emprestimos VALUES
(1, 1, 1, '2026-09-01', '2026-09-10'), (2, 3, 2, '2026-09-05', '2026-09-25'),
(3, 1, 3, '2026-09-15', '2026-09-28'), (4, 5, 1, '2026-09-20', NULL),
(5, 2, 2, '2026-10-01', NULL),         (6, 1, 4, '2026-10-10', NULL),
(7, 6, 3, '2026-10-12', NULL);

SELECT l.titulo, l.exemplares - COUNT(e.id) AS disponiveis
FROM livros l
LEFT JOIN emprestimos e
       ON e.livro_id = l.id
      AND e.devolucao IS NULL
GROUP BY l.id;

CREATE TABLE autores (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO autores VALUES (1, 'Machado de Assis'), (2, 'José de Alencar'),
                           (3, 'Aluísio Azevedo'), (4, 'Lima Barreto');
CREATE TABLE livros (id INTEGER PRIMARY KEY, titulo TEXT NOT NULL,
    autor_id INTEGER NOT NULL REFERENCES autores(id), ano INTEGER,
    exemplares INTEGER NOT NULL DEFAULT 1);
INSERT INTO livros VALUES
(1, 'Dom Casmurro', 1, 1899, 2), (2, 'Memórias Póstumas de Brás Cubas', 1, 1881, 1),
(3, 'Iracema', 2, 1865, 1),      (4, 'O Guarani', 2, 1857, 1),
(5, 'O Cortiço', 3, 1890, 1),    (6, 'Triste Fim de Policarpo Quaresma', 4, 1915, 1);
CREATE TABLE socios (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO socios VALUES (1, 'Helena Duarte'), (2, 'Jonas Ribeiro'),
    (3, 'Clara Nogueira'), (4, 'Davi Lemos'), (5, 'Marina Coelho');
CREATE TABLE emprestimos (id INTEGER PRIMARY KEY,
    livro_id INTEGER NOT NULL REFERENCES livros(id),
    socio_id INTEGER NOT NULL REFERENCES socios(id),
    retirada TEXT NOT NULL, devolucao TEXT);
INSERT INTO emprestimos VALUES
(1, 1, 1, '2026-09-01', '2026-09-10'), (2, 3, 2, '2026-09-05', '2026-09-25'),
(3, 1, 3, '2026-09-15', '2026-09-28'), (4, 5, 1, '2026-09-20', NULL),
(5, 2, 2, '2026-10-01', NULL),         (6, 1, 4, '2026-10-10', NULL),
(7, 6, 3, '2026-10-12', NULL);

SELECT l.titulo, l.exemplares - COUNT(e.id) AS disponiveis
FROM livros l
LEFT JOIN emprestimos e
       ON e.livro_id = l.id
      AND e.devolucao IS NULL
GROUP BY l.id
HAVING disponiveis > 0;

Três livros na estante: Dom Casmurro (1 dos 2), Iracema e O Guarani. Os outros três estão todos com sócios.

Versão 6: regras que o banco garante

Até aqui, o banco aceita qualquer coisa que a voluntária digitar. Duas regras evitariam os erros mais comuns do caderno:

Autor repetido com outra caixa de letra, como o "Machado de assis" da versão 1. Um UNIQUE proíbe nome repetido (aula UNIQUE), e o COLLATE NOCASE faz o SQLite comparar sem ligar para maiúsculas e minúsculas.
Devolução antes da retirada, por erro de digitação na data. Um CHECK da tabela compara as duas colunas (aula CHECK).

CREATE TABLE emprestimos (
    id        INTEGER PRIMARY KEY,
    livro_id  INTEGER NOT NULL,
    socio_id  INTEGER NOT NULL,
    retirada  TEXT NOT NULL,
    devolucao TEXT,
    CHECK (devolucao IS NULL OR devolucao >= retirada)
);

INSERT INTO emprestimos (livro_id, socio_id, retirada, devolucao)
VALUES (1, 1, '2026-10-18', '2026-10-08');

CHECK (devolucao IS NULL OR devolucao >= retirada): Uma regra de tabela (escrita depois das colunas), porque usa duas colunas. Lê-se: "ou ainda não devolveu, ou devolveu no mesmo dia ou depois". Comparar datas como texto funciona porque estão em AAAA-MM-DD.
a mensagem: CHECK constraint failed: devolucao IS NULL OR devolucao >= retirada: o SQLite recusa e repete a regra quebrada. Alguém digitou 08 no lugar de 28.

Missão: do jeito que está, o banco aceita o "machado de assis" em minúsculas como um autor novo. Mude a coluna nome para TEXT NOT NULL UNIQUE COLLATE NOCASE e rode de novo: o segundo INSERT tem de ser recusado.

CREATE TABLE autores (
    id   INTEGER PRIMARY KEY,
    nome TEXT NOT NULL
);
INSERT INTO autores (nome) VALUES ('Machado de Assis');
INSERT INTO autores (nome) VALUES ('machado de assis');

SELECT * FROM autores;

CREATE TABLE autores (
    id   INTEGER PRIMARY KEY,
    nome TEXT NOT NULL UNIQUE COLLATE NOCASE
);
INSERT INTO autores (nome) VALUES ('Machado de Assis');
INSERT INTO autores (nome) VALUES ('machado de assis');

SELECT * FROM autores;

O erro é UNIQUE constraint failed: autores.nome. Sem o COLLATE NOCASE, o UNIQUE sozinho deixaria passar, porque "Machado" e "machado" são textos diferentes. Atenção: o NOCASE do SQLite só iguala as letras sem acento (A–Z); "JOSÉ" e "josé" continuam diferentes no "É".

Versão 7: o dia a dia, emprestar e devolver

O banco está pronto. No sábado, a voluntária não roda CREATE TABLE; ela faz só duas coisas: emprestar é um INSERT em emprestimos, e devolver é um UPDATE que preenche a devolucao (aulas INSERT INTO e UPDATE).

Missão final: hoje, 18/10/2026, aconteceram duas coisas. A Helena (sócia 1) devolveu O Cortiço (é o empréstimo 4), e a Marina (sócia 5) levou O Guarani (livro 4). Registre as duas antes da consulta de estante, que já está no fim. Na devolução, use WHERE id = 4: um UPDATE sem WHERE devolveria todos os livros de uma vez (aula O Perigo do UPDATE e DELETE sem WHERE).

CREATE TABLE autores (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO autores VALUES (1, 'Machado de Assis'), (2, 'José de Alencar'),
                           (3, 'Aluísio Azevedo'), (4, 'Lima Barreto');
CREATE TABLE livros (id INTEGER PRIMARY KEY, titulo TEXT NOT NULL,
    autor_id INTEGER NOT NULL REFERENCES autores(id), ano INTEGER,
    exemplares INTEGER NOT NULL DEFAULT 1);
INSERT INTO livros VALUES
(1, 'Dom Casmurro', 1, 1899, 2), (2, 'Memórias Póstumas de Brás Cubas', 1, 1881, 1),
(3, 'Iracema', 2, 1865, 1),      (4, 'O Guarani', 2, 1857, 1),
(5, 'O Cortiço', 3, 1890, 1),    (6, 'Triste Fim de Policarpo Quaresma', 4, 1915, 1);
CREATE TABLE socios (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO socios VALUES (1, 'Helena Duarte'), (2, 'Jonas Ribeiro'),
    (3, 'Clara Nogueira'), (4, 'Davi Lemos'), (5, 'Marina Coelho');
CREATE TABLE emprestimos (id INTEGER PRIMARY KEY,
    livro_id INTEGER NOT NULL REFERENCES livros(id),
    socio_id INTEGER NOT NULL REFERENCES socios(id),
    retirada TEXT NOT NULL, devolucao TEXT);
INSERT INTO emprestimos VALUES
(1, 1, 1, '2026-09-01', '2026-09-10'), (2, 3, 2, '2026-09-05', '2026-09-25'),
(3, 1, 3, '2026-09-15', '2026-09-28'), (4, 5, 1, '2026-09-20', NULL),
(5, 2, 2, '2026-10-01', NULL),         (6, 1, 4, '2026-10-10', NULL),
(7, 6, 3, '2026-10-12', NULL);

-- o sábado, 18/10/2026:


-- a estante depois do sábado
SELECT l.titulo, l.exemplares,
       COUNT(e.id)                AS emprestados,
       l.exemplares - COUNT(e.id) AS disponiveis
FROM livros l
LEFT JOIN emprestimos e ON e.livro_id = l.id AND e.devolucao IS NULL
GROUP BY l.id;

CREATE TABLE autores (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO autores VALUES (1, 'Machado de Assis'), (2, 'José de Alencar'),
                           (3, 'Aluísio Azevedo'), (4, 'Lima Barreto');
CREATE TABLE livros (id INTEGER PRIMARY KEY, titulo TEXT NOT NULL,
    autor_id INTEGER NOT NULL REFERENCES autores(id), ano INTEGER,
    exemplares INTEGER NOT NULL DEFAULT 1);
INSERT INTO livros VALUES
(1, 'Dom Casmurro', 1, 1899, 2), (2, 'Memórias Póstumas de Brás Cubas', 1, 1881, 1),
(3, 'Iracema', 2, 1865, 1),      (4, 'O Guarani', 2, 1857, 1),
(5, 'O Cortiço', 3, 1890, 1),    (6, 'Triste Fim de Policarpo Quaresma', 4, 1915, 1);
CREATE TABLE socios (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO socios VALUES (1, 'Helena Duarte'), (2, 'Jonas Ribeiro'),
    (3, 'Clara Nogueira'), (4, 'Davi Lemos'), (5, 'Marina Coelho');
CREATE TABLE emprestimos (id INTEGER PRIMARY KEY,
    livro_id INTEGER NOT NULL REFERENCES livros(id),
    socio_id INTEGER NOT NULL REFERENCES socios(id),
    retirada TEXT NOT NULL, devolucao TEXT);
INSERT INTO emprestimos VALUES
(1, 1, 1, '2026-09-01', '2026-09-10'), (2, 3, 2, '2026-09-05', '2026-09-25'),
(3, 1, 3, '2026-09-15', '2026-09-28'), (4, 5, 1, '2026-09-20', NULL),
(5, 2, 2, '2026-10-01', NULL),         (6, 1, 4, '2026-10-10', NULL),
(7, 6, 3, '2026-10-12', NULL);

-- o sábado, 18/10/2026:
UPDATE emprestimos SET devolucao = '2026-10-18' WHERE id = 4;
INSERT INTO emprestimos (livro_id, socio_id, retirada) VALUES (4, 5, '2026-10-18');

-- a estante depois do sábado
SELECT l.titulo, l.exemplares,
       COUNT(e.id)                AS emprestados,
       l.exemplares - COUNT(e.id) AS disponiveis
FROM livros l
LEFT JOIN emprestimos e ON e.livro_id = l.id AND e.devolucao IS NULL
GROUP BY l.id;

O Cortiço voltou para a estante e O Guarani saiu. O empréstimo novo ganhou o número 8 sozinho, e a devolucao dele ficou NULL, porque o INSERT não a citou. E a multa da Helena? Com a devolução registrada, a lista de cobrança da versão 4 já não a mostraria: ela olha só os empréstimos em aberto. Cobrar quem devolveu atrasado é outra consulta, sobre devolucao - retirada, e fica de exercício para você.

Quando dá errado

A condição no WHERE em vez do ON

A Ana escreveu a consulta da estante com o filtro da devolução no WHERE:

CREATE TABLE livros (id INTEGER PRIMARY KEY, titulo TEXT NOT NULL, exemplares INTEGER NOT NULL DEFAULT 1);
INSERT INTO livros (id, titulo, exemplares) VALUES
(1, 'Dom Casmurro', 2), (2, 'Memórias Póstumas de Brás Cubas', 1), (3, 'Iracema', 1),
(4, 'O Guarani', 1), (5, 'O Cortiço', 1), (6, 'Triste Fim de Policarpo Quaresma', 1);
CREATE TABLE emprestimos (id INTEGER PRIMARY KEY, livro_id INTEGER NOT NULL REFERENCES livros(id),
                          retirada TEXT NOT NULL, devolucao TEXT);
INSERT INTO emprestimos (id, livro_id, retirada, devolucao) VALUES
(1, 1, '2026-09-01', '2026-09-10'), (2, 3, '2026-09-05', '2026-09-25'),
(3, 1, '2026-09-15', '2026-09-28'), (4, 5, '2026-09-20', NULL),
(5, 2, '2026-10-01', NULL),         (6, 1, '2026-10-10', NULL),
(7, 6, '2026-10-12', NULL);

SELECT l.titulo, l.exemplares - COUNT(e.id) AS disponiveis
FROM livros l
LEFT JOIN emprestimos e ON e.livro_id = l.id
WHERE e.devolucao IS NULL
GROUP BY l.id;

O que aconteceu: 5 livros em vez de 6. Iracema sumiu, sem erro nenhum. O LEFT JOIN trouxe Iracema com o seu único empréstimo, que já foi devolvido; o WHERE veio depois e jogou essa linha fora, porque a devolucao dela não é NULL. O Guarani escapou por sorte: sem empréstimo nenhum, as colunas de e vieram vazias e passaram no IS NULL.

Como consertar: condição sobre a tabela da direita de um LEFT JOIN vai no ON: ON e.livro_id = l.id AND e.devolucao IS NULL. O ON escolhe com quem juntar; o WHERE escolhe que linhas ficam.

Emprestou um livro que não existe

CREATE TABLE livros (id INTEGER PRIMARY KEY, titulo TEXT NOT NULL);
INSERT INTO livros VALUES (1, 'Dom Casmurro');
CREATE TABLE emprestimos (id INTEGER PRIMARY KEY,
    livro_id INTEGER NOT NULL REFERENCES livros(id),
    retirada TEXT NOT NULL, devolucao TEXT);

INSERT INTO emprestimos (livro_id, retirada) VALUES (99, '2026-10-18');

O que aconteceu: FOREIGN KEY constraint failed. Não existe livro 99. No editor as chaves estrangeiras estão ligadas; no programa sqlite3, lembre-se de rodar PRAGMA foreign_keys = ON; ao abrir o banco, senão esse empréstimo entraria (aula PRAGMAs Úteis).

Como consertar: confira o número do livro, ou cadastre o livro antes.

Data num formato que o julianday não entende

SELECT julianday('2026-10-18') - julianday('2026-09-20') AS iso,
       julianday('18/10/2026') - julianday('20/09/2026') AS brasileiro;

O que aconteceu: com AAAA-MM-DD, 28 dias. Com DD/MM/AAAA, NULL, sem mensagem de erro: o SQLite não reconhece o formato e devolve "sem valor". Um empréstimo digitado assim nunca apareceria como atrasado.

Como consertar: guarde sempre as datas em AAAA-MM-DD. Para o banco recusar qualquer outro formato, use CHECK (date(retirada) IS retirada) na tabela: date('18/10/2026') dá NULL, que não é o texto digitado, e o INSERT falha com CHECK constraint failed: date(retirada) IS retirada. Cuidado com a versão "óbvia", CHECK (retirada = date(retirada)): ela não funciona, porque a comparação com NULL dá NULL, e um CHECK que dá NULL deixa passar (aula CHECK).

Devolveu todos os livros de uma vez

CREATE TABLE emprestimos (id INTEGER PRIMARY KEY, livro_id INTEGER NOT NULL,
                          retirada TEXT NOT NULL, devolucao TEXT);
INSERT INTO emprestimos (id, livro_id, retirada, devolucao) VALUES
(4, 5, '2026-09-20', NULL), (5, 2, '2026-10-01', NULL),
(6, 1, '2026-10-10', NULL), (7, 6, '2026-10-12', NULL);

UPDATE emprestimos SET devolucao = '2026-10-18';

SELECT * FROM emprestimos;

O que aconteceu: "4 linhas afetadas". A voluntária queria registrar a devolução da Helena e esqueceu o WHERE: agora o banco acha que todos os livros voltaram, e ninguém mais aparece como atrasado.

Como consertar: sempre WHERE id = … num UPDATE. Um bom hábito: rode antes um SELECT com o mesmo WHERE e confira que ele mostra só a linha que você quer mudar.

Nos outros bancos

O julianday é do SQLite. Nos bancos com tipo DATE de verdade, contar dias é mais direto. Trechos ilustrativos, que não rodam no editor:

-- PostgreSQL: subtrair duas datas dá o número de dias
SELECT DATE '2026-10-18' - DATE '2026-09-20';           -- 28
-- MySQL / MariaDB
SELECT DATEDIFF('2026-10-18', '2026-09-20');             -- 28
-- SQL Server
SELECT DATEDIFF(day, '2026-09-20', '2026-10-18');        -- 28

Repare que a ordem dos argumentos do DATEDIFF é invertida entre o MySQL e o SQL Server. O COLLATE NOCASE também é do SQLite; nos outros bancos, comparar sem diferenciar maiúsculas depende da collation da coluna. Mais em As Diferenças entre os Bancos.

Referência rápida

Tarefa da biblioteca	SQL
Livro aponta para autor	`autor_id INTEGER NOT NULL REFERENCES autores(id)`
Empréstimos em aberto	`WHERE e.devolucao IS NULL`
Dias desde a retirada	`CAST(julianday('2026-10-18') - julianday(e.retirada) AS INTEGER)`
Último dia do prazo	`date(e.retirada, '+14 days')`
Atraso nunca negativo	`MAX(dias - 14, 0)`
Exemplares na estante	`LEFT JOIN emprestimos e ON e.livro_id = l.id AND e.devolucao IS NULL` + `l.exemplares - COUNT(e.id)`
Nome sem repetir, sem ligar para maiúsculas	`nome TEXT NOT NULL UNIQUE COLLATE NOCASE`
Devolução nunca antes da retirada	`CHECK (devolucao IS NULL OR devolucao >= retirada)`
Emprestar	`INSERT INTO emprestimos (livro_id, socio_id, retirada) VALUES (…)`
Devolver	`UPDATE emprestimos SET devolucao = '…' WHERE id = …`
"Hoje" de verdade	`date('now')`

Exercícios

1. Ainda não voltou

Complete o filtro dos empréstimos em aberto:

WHERE e.devolucao

2. O fim do prazo

Complete para calcular o último dia de um prazo de 14 dias:

date(e.retirada, '')

3. Por que o nome do autor saiu da tabela livros e foi para uma tabela autores?

Porque o SQLite não aceita duas colunas de texto na mesma tabela Para o nome ser escrito uma vez só: um erro de digitação não cria um "autor novo" e corrige-se num lugar Para a consulta ficar mais curta Porque livros com o mesmo autor não podem ficar na mesma tabela

4. Na consulta da estante com LEFT JOIN, o filtro e.devolucao IS NULL foi parar no WHERE. O que acontece?

Erro de sintaxe Nada muda Aparecem livros repetidos Somem os livros cujos empréstimos foram todos devolvidos, como Iracema

5. Para Iracema, sem empréstimo aberto, quanto dá COUNT(e.id) e quanto daria COUNT(*)?

0 e 1 1 e 1 0 e 0 NULL e 1

Desafio: quem devolveu atrasado também paga. Mostre socio, titulo, atraso e multa dos empréstimos já devolvidos que passaram dos 14 dias, contando da retirada até a devolução (não até hoje).

CREATE TABLE livros (id INTEGER PRIMARY KEY, titulo TEXT NOT NULL);
INSERT INTO livros VALUES (1, 'Dom Casmurro'), (2, 'Memórias Póstumas de Brás Cubas'),
    (3, 'Iracema'), (4, 'O Guarani'), (5, 'O Cortiço'), (6, 'Triste Fim de Policarpo Quaresma');
CREATE TABLE socios (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO socios VALUES (1, 'Helena Duarte'), (2, 'Jonas Ribeiro'),
    (3, 'Clara Nogueira'), (4, 'Davi Lemos'), (5, 'Marina Coelho');
CREATE TABLE emprestimos (id INTEGER PRIMARY KEY,
    livro_id INTEGER NOT NULL REFERENCES livros(id),
    socio_id INTEGER NOT NULL REFERENCES socios(id),
    retirada TEXT NOT NULL, devolucao TEXT);
INSERT INTO emprestimos VALUES
(1, 1, 1, '2026-09-01', '2026-09-10'), (2, 3, 2, '2026-09-05', '2026-09-25'),
(3, 1, 3, '2026-09-15', '2026-09-28'), (4, 5, 1, '2026-09-20', NULL),
(5, 2, 2, '2026-10-01', NULL),         (6, 1, 4, '2026-10-10', NULL),
(7, 6, 3, '2026-10-12', NULL);

SELECT s.nome AS socio, l.titulo, e.retirada, e.devolucao
FROM emprestimos e
JOIN socios s ON s.id = e.socio_id
JOIN livros l ON l.id = e.livro_id
WHERE e.devolucao IS NOT NULL;

CREATE TABLE livros (id INTEGER PRIMARY KEY, titulo TEXT NOT NULL);
INSERT INTO livros VALUES (1, 'Dom Casmurro'), (2, 'Memórias Póstumas de Brás Cubas'),
    (3, 'Iracema'), (4, 'O Guarani'), (5, 'O Cortiço'), (6, 'Triste Fim de Policarpo Quaresma');
CREATE TABLE socios (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
INSERT INTO socios VALUES (1, 'Helena Duarte'), (2, 'Jonas Ribeiro'),
    (3, 'Clara Nogueira'), (4, 'Davi Lemos'), (5, 'Marina Coelho');
CREATE TABLE emprestimos (id INTEGER PRIMARY KEY,
    livro_id INTEGER NOT NULL REFERENCES livros(id),
    socio_id INTEGER NOT NULL REFERENCES socios(id),
    retirada TEXT NOT NULL, devolucao TEXT);
INSERT INTO emprestimos VALUES
(1, 1, 1, '2026-09-01', '2026-09-10'), (2, 3, 2, '2026-09-05', '2026-09-25'),
(3, 1, 3, '2026-09-15', '2026-09-28'), (4, 5, 1, '2026-09-20', NULL),
(5, 2, 2, '2026-10-01', NULL),         (6, 1, 4, '2026-10-10', NULL),
(7, 6, 3, '2026-10-12', NULL);

WITH devolvidos AS (
    SELECT s.nome AS socio, l.titulo,
           CAST(julianday(e.devolucao) - julianday(e.retirada) AS INTEGER) AS dias
    FROM emprestimos e
    JOIN socios s ON s.id = e.socio_id
    JOIN livros l ON l.id = e.livro_id
    WHERE e.devolucao IS NOT NULL
)
SELECT socio, titulo, dias - 14 AS atraso, (dias - 14) * 0.5 AS multa
FROM devolvidos
WHERE dias > 14;

Só o Jonas: ficou 20 dias com Iracema, 6 de atraso, R$ 3,00. A Helena e a Clara devolveram Dom Casmurro em 9 e 13 dias.

Resumo

Comece simples, com uma tabela, e divida quando aparecer repetição: o autor digitado em cada livro virou uma tabela autores, ligada por autor_id.
Um empréstimo é a tabela do meio entre livros e sócios, com as datas. devolucao vazia (NULL) quer dizer "ainda está com o sócio".
julianday(b) - julianday(a) conta dias; date(a, '+14 days') soma dias; MAX(x, 0) impede atraso negativo. Datas sempre em AAAA-MM-DD, senão as funções devolvem NULL.
Para contar "inclusive quem não tem par", LEFT JOIN + COUNT(coluna). Condição sobre a tabela da direita vai no ON; no WHERE, ela some com linhas sem avisar.
UNIQUE COLLATE NOCASE e CHECK fazem o banco recusar os erros do caderno.
No dia a dia, emprestar é INSERT e devolver é UPDATE … WHERE id = ….