Search Knowledge

© 2026 LIBREUNI PROJECT

Database Systems/Schema design and SQL practice
Page 8 of 20

SQL lab: build and verify an allocation report

Use a deterministic allocation fixture to execute and explain joins, grouped reports, anti-joins, and schema constraints.

Lab briefSchema design and SQL practice · 12 h · 3 outcomes

Required knowledge

  • SQL querying: joins, clause order, grouping, NULL, and NOT EXISTS
  • Schema review: grain, keys, foreign keys, and check constraints

Learning outcomes

  • Load the fixed allocation fixture in the in-browser SQLite runner or a disposable PostgreSQL database, and state the expected result before running a query.
  • Build zero-safe grouped reports and NULL-safe anti-joins in SQL, then verify the claims that are portable across SQLite and PostgreSQL.
  • Use adversarial rows and PostgreSQL EXPLAIN evidence to separate query correctness from a performance observation.

Assessment target

Construct a query portfolio containing a result contract, SQL, predicted and observed edge-case rows, constraint tests, and a brief explanation of one plan observation.

Scope boundary

  • The browser runner executes read-only SQLite against the fixed fixture. It does not provide a PostgreSQL planner, multi-statement mutation, production deployment, transaction-concurrency testing, or vendor portability certification.

Every task uses the same fixed fixture and expected edge cases. The embedded runner executes read-only SQLite in the browser, so a wrong join or a misplaced predicate produces a real result rather than a plausible hand trace. SQLite and PostgreSQL both support aggregate FILTER, but a SQLite result is not evidence of PostgreSQL-specific behavior. Use a disposable local PostgreSQL database for constraint error text and PostgreSQL plans.

Evidence pathWhat it establishesWhat it does not establish
In-browser SQLite runnerexecutable join, grouping, and NOT EXISTS result semantics for this fixturePostgreSQL-specific planner, MVCC, constraint error text, or isolation behavior
PostgreSQL 18 fixturePostgreSQL syntax, constraints, FILTER, and an observed EXPLAIN planbehavior of other engines or every workload

Record the engine and version with every observed result. In the runner, SELECT sqlite_version(); reports the bundled SQLite version. Do not use SQLite output as evidence that a PostgreSQL-specific statement or plan executed.

Lab brief and evidence checklist

ArtifactWhat to includeEvidence of success
Zero-safe allocation reportContract, query, predicted tableService pools with zero active allocations remain
Anti-joinContract, query, predicted tableA client with only an other-region allocation is correctly included
Constraint probesFour deliberately invalid writesEach is rejected by the intended key, foreign key, or check
Plan observation (optional)EXPLAIN output and one cautious claimA measured plan fact, not a promise that all data will use that plan

Create the fixed fixture

The browser runner below loads this fixture into SQLite automatically. To test PostgreSQL-specific behavior, load these statements into a disposable PostgreSQL database; the tables also support hand tracing.

CREATE TABLE client (
  client_id    integer PRIMARY KEY,
  display_name text NOT NULL
);

CREATE TABLE service_pool (
  pool_id      integer PRIMARY KEY,
  service_code text NOT NULL,
  region       text NOT NULL,
  capacity     integer NOT NULL CHECK (capacity >= 0)
);

CREATE TABLE allocation (
  client_id integer NOT NULL REFERENCES client(client_id),
  pool_id   integer NOT NULL REFERENCES service_pool(pool_id),
  status    text NOT NULL CHECK (status IN ('active', 'queued', 'suspended')),
  memory_gb integer CHECK (memory_gb IS NULL OR memory_gb BETWEEN 0 AND 100),
  PRIMARY KEY (client_id, pool_id)
);

INSERT INTO client (client_id, display_name) VALUES
  (1, 'Ada'), (2, 'Bo'), (3, 'Cy'), (4, 'Dee');

INSERT INTO service_pool (pool_id, service_code, region, capacity) VALUES
  (10, 'S101', 'us-east-1', 2),
  (11, 'S201', 'us-east-1', 1),
  (12, 'S301', 'us-east-1', 2),
  (13, 'S101', 'us-west-2', 2);

INSERT INTO allocation (client_id, pool_id, status, memory_gb) VALUES
  (1, 10, 'active', 87),
  (2, 10, 'suspended', 40),
  (3, 11, 'queued', NULL),
  (4, 13, 'active', 93);
RelationFixture facts
clientAda, Bo, Cy, and Dee exist
service_poolS101, S201, S301 are in us-east-1; S101 also has an older us-west-2 pool
allocationAda has active allocation in S101; Bo is suspended; Cy is queued; Dee has active allocation only in us-west-2

Execute the fixture

The runner creates the tables and rows above in SQLite. Predict the warm-up report before pressing Run query, then change the query and explain every difference.

SQL

Service allocation fixture

Count active allocations per us-east-1 service pool. Predict three rows, including the zero counts, before running.

Read-only SQLite fixture; reloaded fresh on each execution (100-row display limit).

Ready. A fresh fixture is created for each query.

Warm-up prediction

Before writing SQL, list the us-east-1 service pools and the count of rows whose status is active. Do not count suspended or queued rows.

HintExpand

Filter the allocation rows by status first, then keep every current-region service pool.

Worked solutionExpand

The desired report has S101 with count 1, S201 with count 0, and S301 with count 0. The fixed fixture has no active row for S201 or S301. This table is the oracle for the first task, not an optional afterthought.

Task 1: keep service pools with zero active allocations

Result contract: one row per service pool in us-east-1, including pools with no active allocations; columns are pool_id, service_code, active_allocations, and is_full; ordered by service code then pool ID.

Start with this skeleton. Decide where the status condition belongs before running it.

Code Exercise

4 tokens to supply

Complete the matching and grouping contract

SELECT p.pool_id,
     p.service_code,
      AS active_allocations,
      AS is_full
FROM service_pool AS p
LEFT JOIN allocation AS a
 a.pool_id = p.pool_id
AND a.status = 'active'
WHERE p.region = 'us-east-1'
 p.pool_id, p.service_code, p.capacity
ORDER BY p.service_code, p.pool_id
SELECT p.pool_id,
       p.service_code,
       COUNT(a.client_id) AS active_allocations,
       COUNT(a.client_id) >= p.capacity AS is_full
FROM service_pool AS p
LEFT JOIN allocation AS a
  ON a.pool_id = p.pool_id
 AND a.status = 'active'
WHERE p.region = 'us-east-1'
GROUP BY p.pool_id, p.service_code, p.capacity
ORDER BY p.service_code, p.pool_id;

Worked example. The left join emits each us-east-1 service pool even when no active right row exists. COUNT(a.client_id) ignores the null-extended value, so it produces zero for S201 and S301; COUNT(*) would instead count the left row and be wrong for this contract.

Expected Task 1 result (SQLite evaluates boolean comparisons such as >= as 0 / 1, whereas PostgreSQL returns false / true):

pool_idservice_codeactive_allocationsis_full
10S10110
11S20100
12S30100

Break it on purpose

Move a.status = 'active' into WHERE, then predict the result. Why is S201 absent even though it has an allocation row?

HintExpand

WHERE filters after NULL extension and after the right-side status is visible.

Worked solutionExpand

Only S101 remains. S201’s only match has status queued, so the WHERE predicate is false; S301 has a null-extended status, so the predicate is UNKNOWN. Both are removed. The query has changed from “all service pools, count active matches” to “service pools with at least one active match.”

Counterexample. A query can be syntactically valid, return a count, and still fail the report contract. A test dataset containing only service pools with active allocations would not expose this ON/WHERE bug; zero and nonqualifying cases must be seeded deliberately.

Task 2: find clients with no active allocation in region us-east-1

Result contract: one row per client who has no active allocation in a us-east-1 service pool; ordered by client ID.

Complete the absence test, then compare your result with the fixture.

Code Exercise

2 tokens to supply

Reconstruct the correlated absence test

SELECT c.client_id, c.display_name
FROM client AS c
WHERE  (
SELECT 1
FROM allocation AS a
JOIN service_pool AS p ON p.pool_id = a.pool_id
WHERE 
  AND a.status = 'active'
  AND p.region = 'us-east-1'
);
SELECT c.client_id, c.display_name
FROM client AS c
WHERE NOT EXISTS (
  SELECT 1
  FROM allocation AS a
  JOIN service_pool AS p ON p.pool_id = a.pool_id
  WHERE a.client_id = c.client_id
    AND a.status = 'active'
    AND p.region = 'us-east-1'
)
ORDER BY c.client_id;

Worked example. Bo is included because suspended is not active. Cy is included because queued is not active. Dee is included because the only active row is in us-west-2. Ada is excluded because the correlated subquery finds the active us-east-1 S101 allocation.

Expected Task 2 result:

client_iddisplay_name
2Bo
3Cy
4Dee

Change the requirement

The platform policy changes “no active allocation” to “no active or queued allocation.” Which predicate changes, and which expected row disappears?

HintExpand

The region boundary is unchanged. Only the accepted allocation states change.

Worked solutionExpand

Change the correlated condition to a.status IN ('active', 'queued'). Cy disappears because Cy is queued in S201 for us-east-1. Bo remains because suspended is still excluded; Dee remains because the only row is in us-west-2.

Task 3: test the schema, not just the queries

For each proposed write, predict the intended rejection. In a local PostgreSQL lab, issue each in its own transaction and roll it back after the expected error.

Proposed factConstraint that should reject it
Add a second (1, 10, 'active', 91) rowComposite primary key on (client_id, pool_id)
Add (1, 999, 'active', 91)Foreign key to service_pool
Add (1, 11, 'active', -4)Memory allocation range check
Add (1, 11, 'active', 101)Memory allocation range check
BEGIN;
INSERT INTO allocation (client_id, pool_id, status, memory_gb)
VALUES (1, 999, 'active', 91);
-- PostgreSQL reports a foreign-key violation here.
ROLLBACK;

Diagnose a missing rule

The infrastructure team now says “a full service pool may not gain another active allocation.” Can you express that correctly with the existing row-local CHECK constraints? State what is missing from the lab design.

HintExpand

Capacity depends on multiple allocation rows, but a CHECK sees its own row's values.

Worked solutionExpand

No. capacity and the count of active allocation rows live in different rows/relations, so an ordinary row-local check cannot safely compare them. The design needs a transaction-aware allocation operation or another coordinated integrity mechanism, plus concurrency tests. This lab intentionally stops before transaction isolation and does not pretend a check constraint solves it.

Task 4: make a cautious plan observation (optional)

Run the zero-safe report with PostgreSQL’s read-only plan command:

EXPLAIN (ANALYZE, BUFFERS)
SELECT p.pool_id,
       p.service_code,
       COUNT(a.client_id) AS active_allocations
FROM service_pool AS p
LEFT JOIN allocation AS a
  ON a.pool_id = p.pool_id
 AND a.status = 'active'
WHERE p.region = 'us-east-1'
GROUP BY p.pool_id, p.service_code
ORDER BY p.service_code, p.pool_id;

Record exactly one observation that the output supports, such as “on this four-pool fixture, PostgreSQL chose a sequential scan.” Do not conclude that a sequential scan is always best or that the plan proves semantic correctness.

Section Exercises

Deep Practice

Solve these problems to test and consolidate your understanding. Complete worked solutions and diagnostic hints are available for each problem.

Exercise 2.4.1: Conditional Aggregation with Status Filtering

Constructionapplication

Write a PostgreSQL query against service_pool(pool_id, service_code, region) and allocation(client_id, pool_id, status) that reports (service_code, active_count, queued_count) for all us-east-1 service pools, preserving pools with zero allocations. Use standard SQL aggregate FILTER clauses.

HintExpand

Use a LEFT JOIN from service_pool to allocation and aggregate using FILTER (WHERE ...).

Worked solutionExpand
SELECT p.service_code,
       COUNT(*) FILTER (WHERE a.status = 'active') AS active_count,
       COUNT(*) FILTER (WHERE a.status = 'queued') AS queued_count
FROM service_pool AS p
LEFT JOIN allocation AS a
  ON a.pool_id = p.pool_id
WHERE p.region = 'us-east-1'
GROUP BY p.pool_id, p.service_code
ORDER BY p.service_code;

COUNT(*) FILTER (WHERE ...) evaluates conditions over right-side matches while preserving empty service pools with count 0.

Exercise 2.4.2: Capacity Check Constraint Scope Limitation

Verificationapplication

A developer attempts to enforce a service pool capacity limit by writing CHECK ((SELECT COUNT(*) FROM allocation a WHERE a.pool_id = pool_id) <= capacity) inside a table definition. Verify why standard SQL rejects or fails to guarantee this constraint under concurrent transactions.

HintExpand

Check constraints are evaluated strictly on the single modified row and cannot safely query dynamic foreign tables.

Worked solutionExpand

Standard SQL table check constraints evaluate predicates over the immediate columns of the mutated row; subqueries in CHECK constraints are either explicitly disallowed by RDBMS engines or susceptible to race conditions under non-serializable isolation levels, where concurrent commits can exceed capacity without seeing each other’s inserts.

Exercise 2.4.3: Query Plan Execution vs. Correctness Semantics

Descriptionrecall

Explain why observing a sequential scan in EXPLAIN ANALYZE on a four-row fixture does not indicate an optimization defect or justify adding an index.

HintExpand

Compare disk page fetch overhead with sequential cache hits on small tables.

Worked solutionExpand

For small relations occupying a single disk page, a sequential scan reads the single page directly into cache without paying the additional overhead of traversing an index B-tree. The optimizer correctly minimizes cost based on catalog statistics. Physical plans reflect cost models, not relational correctness contracts.

Exercise 2.4.4: Multi-Status Allocation Reporting with Null Preservation

Generalisationtransfer

Design test cases for the multi-status allocation query. Identify two adversarial seed rows that expose bugs in: (1) moving status checks to WHERE, and (2) using unfiltered COUNT(*).

HintExpand

Provide a service pool with only suspended allocations, and a service pool with zero allocations.

Worked solutionExpand
  1. Seed a service pool with only a suspended allocation: moving status checks into WHERE removes this pool entirely instead of reporting counts (0, 0).
  2. Seed a service pool with zero allocations: using unfiltered COUNT(*) reports count 1 due to the null-extended joined row, whereas COUNT(*) FILTER (...) or COUNT(a.client_id) correctly yields 0.

References & Further Reading