Use a CASE expression to label a bounded score

By Batu · English technical notes

Also published in our primary archive.

Use a CASE expression to label a bounded score

Use a SQLite CASE expression when a bounded score needs an explanatory label. A boolean range test such as value BETWEEN 0 AND 10 identifies acceptable values, but its 0 results do not say whether a score is below the lower boundary or above the upper boundary.

This in-memory fixture holds four scores: -1, 0, 7, and 11. The naïve query prints a numeric truth value for each score: 0, 1, 1, and 0. That is enough to filter accepted scores, but it loses the direction of each failure. The CASE query tests the lower boundary first, then the upper boundary, and assigns low, high, or ok. It consequently prints low, ok, ok, and high in score order.

SQLite’s expression syntax includes CASE expressions, and SELECT result columns may be expressions. The branches are ordered: the first true WHEN is selected, so retain mutually clear conditions. This example uses stable sqlite3 APIs and no newer Python version is required.

The assertion verifies the classification rule chosen here, including that the endpoints 0 and 10 are acceptable. Change the comparisons if your policy treats either endpoint differently. It does not validate score input, enforce a database constraint, or replace a domain-specific explanation for why a value is out of range.

Sources: SQLite SELECT and SQLite expressions.

AI assistance disclosure: This article was drafted with AI assistance and verified against the cited documentation and a synthetic in-memory example.

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE score (value INTEGER NOT NULL)")
seed_rows = [(-1,), (0,), (7,), (11,)]
con.executemany("INSERT INTO score (value) VALUES (?)", seed_rows)

boolean_labels = con.execute(
    "SELECT value, value BETWEEN 0 AND 10 FROM score ORDER BY value"
).fetchall()
case_labels = con.execute(
    "SELECT value, CASE "
    "WHEN value < 0 THEN 'low' "
    "WHEN value > 10 THEN 'high' "
    "ELSE 'ok' END "
    "FROM score ORDER BY value"
).fetchall()

print("schema: CREATE TABLE score (value INTEGER NOT NULL)")
print("seed rows:", seed_rows)
print("BETWEEN labels:", boolean_labels)
print("CASE labels:", case_labels)

assert case_labels == [(-1, "low"), (0, "ok"), (7, "ok"), (11, "high")]
con.close()

schema: CREATE TABLE score (value INTEGER NOT NULL)
seed rows: [(-1,), (0,), (7,), (11,)]
BETWEEN labels: [(-1, 0), (0, 1), (7, 1), (11, 0)]
CASE labels: [(-1, 'low'), (0, 'ok'), (7, 'ok'), (11, 'high')]