Los datos de práctica
Una empresa ficticia de reparaciones registra trabajos en dos sucursales. Un valor nulo en las horas significa que todavía no se han registrado; un cero indica que alguien ha registrado tiempo cero. Son situaciones distintas. Ejecuta la preparación una vez en una base de datos de prueba de PostgreSQL o abre el curso en el navegador, que incluye ejercicios corregidos con sus propios datos.
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"}');
Los ejercicios
- Devuelve los identificadores de los trabajos abiertos, ordenados de menor a mayor. El resultado debe ser 101, 103 y 104. Sin
ORDER BY, la consulta no garantiza ese orden. - Por sucursal, cuenta todos los trabajos, los que tienen horas registradas y el total de horas. North tiene dos trabajos, dos valores registrados y 3,5 horas. South tiene dos trabajos, un valor registrado y cero horas en total.
- Devuelve los identificadores cuya prioridad en
detailssea alta. El resultado debe ser 101 y 104. La ausencia de la clave no debe convertirse en una prioridad alta. - Cambia las horas del trabajo 101 a tres dentro de una transacción y después deshazla. Vuelve a consultar la fila. Debe conservar 2,5 horas.
Antes de comparar las respuestas, cambia los datos: asigna el estado open al trabajo 102, registra 1,25 horas en el 103 y elimina la clave de prioridad del 104. Predice qué resultados cambiarán. Así compruebas la consulta en lugar de memorizar una lista de identificadores.
Las soluciones
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(*) incluye todas las filas; COUNT(hours) omite los valores nulos. SUM también los omite y devuelve null si no hay ningún valor no nulo. Si South solo tuviera la fila con horas nulas, la suma sería null. Usa COALESCE únicamente cuando la regla del negocio exija un cero.
El operador ->> de JSONB devuelve texto. Si falta la clave del objeto, devuelve un nulo de SQL en este caso; por eso la comparación excluye el trabajo 103. El ejemplo de transacción modifica los datos y los restaura con ROLLBACK. Una consulta SELECT por sí sola no demuestra ese comportamiento.
Planes y práctica posterior
Ejecuta EXPLAIN (ANALYZE, FORMAT JSON) sobre la consulta con filtro. Con cuatro filas, un recorrido secuencial puede ser razonable aunque exista un índice. No fuerces su uso solo para obtener el nombre de nodo que esperabas.
Continúa con los valores nulos, los operadores de JSONB y las transacciones. La chuleta sirve como referencia de sintaxis. El curso comprueba tus respuestas con datos modificados.