Os dados de prática
Uma empresa fictícia de reparos registra serviços em duas filiais. Um valor nulo nas horas significa que elas ainda não foram registradas; zero indica um registro de tempo zero. São situações diferentes. Execute a preparação uma vez em um banco de teste PostgreSQL ou abra o curso no navegador, que tem exercícios corrigidos com seus próprios dados.
CREATE TABLE repair_jobs (
job_id integer PRIMARY KEY,
branch text NOT NULL,
status text NOT NULL,
hours numeric(5,2) CHECK (hours >= 0),
details jsonb NOT NULL DEFAULT '{}'::jsonb
);
INSERT INTO repair_jobs VALUES
(101, 'North', 'open', 2.5, '{"priority":"high"}'),
(102, 'North', 'done', 1.0, '{"priority":"low"}'),
(103, 'South', 'open', NULL, '{}'),
(104, 'South', 'open', 0, '{"priority":"high"}');
Os exercícios
- Retorne os identificadores dos serviços abertos, em ordem crescente. O resultado deve ser 101, 103 e 104. Sem
ORDER BY, a consulta não garante essa ordem. - Por filial, conte todos os serviços, os que têm horas registradas e o total de horas. North tem dois serviços, dois valores registrados e 3,5 horas. South tem dois serviços, um valor registrado e zero horas no total.
- Retorne os identificadores com prioridade alta em
details. O resultado deve ser 101 e 104. Uma chave ausente não deve virar prioridade alta. - Dentro de uma transação, altere as horas do serviço 101 para três e desfaça a alteração. Consulte a linha de novo. Ela deve continuar com 2,5 horas.
Antes de comparar respostas, altere os dados: atribua o status open ao serviço 102, registre 1,25 horas no 103 e remova a chave de prioridade do 104. Preveja quais resultados mudarão. Isso testa a consulta em vez de uma lista de identificadores decorada.
As soluções
SELECT job_id FROM repair_jobs
WHERE status = 'open' ORDER BY job_id;
SELECT branch, COUNT(*) AS jobs,
COUNT(hours) AS recorded_hours,
SUM(hours) AS total_hours
FROM repair_jobs GROUP BY branch ORDER BY branch;
SELECT job_id FROM repair_jobs
WHERE details ->> 'priority' = 'high' ORDER BY job_id;
BEGIN;
UPDATE repair_jobs SET hours = 3 WHERE job_id = 101;
ROLLBACK;
SELECT hours FROM repair_jobs WHERE job_id = 101;
COUNT(*) inclui todas as linhas; COUNT(hours) ignora valores nulos. SUM também os ignora e retorna null quando não há nenhuma entrada não nula. Se South tivesse apenas a linha com horas nulas, sua soma seria null. Use COALESCE somente quando a regra do negócio exigir zero.
O operador ->> do JSONB retorna texto. Quando a chave do objeto está ausente, ele retorna um nulo SQL neste caso; por isso a comparação exclui o serviço 103. O exemplo de transação modifica os dados e os restaura com ROLLBACK. Um SELECT sozinho não demonstra esse comportamento.
Planos e próximos exercícios
Execute EXPLAIN (ANALYZE, FORMAT JSON) na consulta com filtro. Com quatro linhas, uma varredura sequencial pode ser adequada mesmo quando existe um índice. Não force o índice apenas para obter o nome de nó que você esperava.
Continue com valores nulos, operadores JSONB e transações. A referência de SQL ajuda com a sintaxe. O curso verifica suas respostas com dados alterados.