The practice dataset
A fictional repair business tracks jobs at two branches. A missing hours value means the work has not been recorded; zero means someone recorded no time. Those states answer different questions. Run the setup once in a PostgreSQL scratch database, or open the browser course for checked exercises with its own fixtures.
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"}');
The tasks
- Return the IDs of open jobs, ordered by ID. You should get 101, 103 and 104. A query without
ORDER BYdoes not promise that order. - For each branch, count every job, count jobs with recorded hours and total the hours. North has two jobs, two recorded values and 3.5 hours. South has two jobs, one recorded value and zero total hours.
- Return the IDs with high priority in
details. You should get 101 and 104. A missing key must not become a high-priority value. - Inside a transaction, change job 101 to three hours, then roll it back. Read the row again. It should still contain 2.5 hours.
Try a second dataset before comparing answers: change job 102 to open, give job 103 1.25 hours and remove the priority key from job 104. Predict which results should change. This stops a memorized list of IDs from substituting for a query.
Reference solutions
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(*) includes every row; COUNT(hours) ignores nulls. SUM also ignores null inputs and returns null when there are no non-null inputs. If South had only the null row, its sum would be null, so use COALESCE only when the business rule calls for zero.
The JSONB text operator ->> returns text. A missing object key returns SQL null here, so the equality predicate excludes job 103. The transaction example changes data and restores it with ROLLBACK; a plain SELECT cannot demonstrate that behavior.
Plans and next practice
Run EXPLAIN (ANALYZE, FORMAT JSON) on the filtering query. With four rows, a sequential scan can be reasonable even after an index exists. Do not force an index just to obtain the node name you expected.
Continue with NULL semantics, JSONB operators and transactions and savepoints. The cheat sheet is a syntax reference. The course checks your answers on changed data.