Country or region and language

    Choose your country or region and your language.

    Suggested for you

      All countries and regions

        Choose your character

        It shows in the top bar and on your profile. Change it any time.

        PostgreSQL exercises with worked solutions

        Work through an original dataset before opening the solutions. Each task names the expected result and the edge case your query needs to handle.

        Checked 14 November 2026

        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

        1. Return the IDs of open jobs, ordered by ID. You should get 101, 103 and 104. A query without ORDER BY does not promise that order.
        2. 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.
        3. Return the IDs with high priority in details. You should get 101 and 104. A missing key must not become a high-priority value.
        4. 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.

        Sources

        Your Work Gym account

        Save your progress and pick up on another device. Your browser's progress comes with you.

        or

        You can keep practicing without an account.

        Help and feedback

        Ask a question, tell us what is not working, or share an idea. We reply by email.

        What is it about?

        We use it only to reply to you.