SQLite Filtering, Fetching and NULL

SELECT chooses result columns and FROM supplies source rows. WHERE filters rows before they appear in the result. Write the columns the task needs instead of relying on * when a stable interface matters. Bind a user-supplied range or name pattern separately from SQL text. A retrieval query does not modify source records.

The fixture has four items: Amina at score 10, Ben at 20, Carys at 20 and Dan with a missing score. BETWEEN includes both boundaries. A filter Score BETWEEN 10 AND 20 therefore includes the first three rows. ORDER BY Score DESC, Name ASC makes ties deterministic; without an ORDER BY clause, do not assume a stable returned order.

SQL NULL is a missing or unknown value, not an empty string or numeric zero. A comparison such as Score=NULL does not select missing scores; use Score IS NULL. Likewise, Score<>20 does not automatically include missing scores, because the comparison is unknown for NULL. If missing records should qualify, state OR Score IS NULL explicitly.

LIKE uses % for zero or more characters and _ for one character. The bound pattern A% finds Amina in this fixture. Its case behaviour depends on the database settings and characters, so do not treat it as a universal case-sensitive rule. DISTINCT removes duplicate projected result rows: selecting distinct scores is different from returning each original record that happens to share a score.

fetchone retrieves the next row of the current result or None when no row remains. fetchall returns the remaining rows, not necessarily the complete original result after earlier fetches. A returned row can itself contain None for a NULL field, so distinguish fetchone returning None from fetchone returning a tuple containing None.

The example fetches Amina first from a filtered, ordered query, then fetches the remaining Ben and Carys rows. It issues a new query for missing scores and a separate DISTINCT query. Each expected result is an independently checked list, with explicit ordering where order matters. Test no matches, one match, the exact lower/upper bounds and a value just outside each bound.

import sqlite3

connection = sqlite3.connect(':memory:')
try:
    cursor = connection.cursor()
    cursor.execute('CREATE TABLE Result (Name TEXT NOT NULL, Score INTEGER)')
    cursor.executemany('INSERT INTO Result VALUES (?, ?)',
                       [('Amina', 10), ('Ben', 20), ('Carys', 20), ('Dan', None)])
    cursor.execute('SELECT Name, Score FROM Result WHERE Score BETWEEN ? AND ? ORDER BY Name', (10, 20))
    print(cursor.fetchone())
    print(cursor.fetchall())
    print(cursor.fetchone())
    print(cursor.execute('SELECT Name FROM Result WHERE Score IS NULL ORDER BY Name').fetchall())
    print(cursor.execute('SELECT DISTINCT Score FROM Result WHERE Score IS NOT NULL ORDER BY Score DESC').fetchall())
    print(cursor.execute('SELECT Name FROM Result WHERE Name LIKE ? ORDER BY Name', ('A%',)).fetchall())
finally:
    connection.close()

Attempt the task before opening its worked response. Record each change of state, explain the rule used, and check the result against the original requirements. Code examples labelled Python 3 use that language’s conventions; pseudocode states its own assumptions.