02 Retrieving data: challenges

Create sql_retrieval.py in VS Code and paste this complete Python runner. With your Python interpreter and Python extension selected, click Run Python File. Standard Python installations normally include sqlite3.

Change only the SQL inside query = """...""" for each exercise. Keep setup_sql unchanged. Click Run again to rebuild the same in-memory dataset and print the new query result in the terminal. Python prints None for SQL NULL and quotes empty strings as ''. No data persists after the program ends.

import sqlite3

connection = sqlite3.connect(":memory:")
setup_sql = """
CREATE TABLE Clubs (
    club_id INTEGER PRIMARY KEY,
    club_name TEXT NOT NULL,
    category TEXT NOT NULL,
    capacity INTEGER NOT NULL,
    room TEXT,
    fee_cents INTEGER NOT NULL,
    session_minutes INTEGER NOT NULL
);

INSERT INTO Clubs
    (club_id, club_name, category, capacity, room, fee_cents, session_minutes)
VALUES
    (1, 'Robotics', 'Technology', 12, 'AIR Lab', 1500, 90),
    (2, 'Game Design', 'Technology', 16, 'Room 12', 0, 60),
    (3, 'Art', 'Creative', 12, 'Studio', 800, 60),
    (4, 'Film', 'Creative', 20, NULL, 1000, 90),
    (5, 'Chess', 'Strategy', 16, 'Room 12', 0, 45),
    (6, 'Coding', 'Technology', 20, 'AIR Lab', 0, 60),
    (7, 'Drama', 'Creative', 24, NULL, 1200, 90),
    (8, 'Robotics Junior', 'Technology', 10, '', 500, 45);
"""

query = """
SELECT club_id, club_name, category, capacity, room, fee_cents, session_minutes
FROM Clubs
ORDER BY club_id;
"""

try:
    connection.executescript(setup_sql)
    cursor = connection.execute(query)
    print(" | ".join(column[0] for column in cursor.description))
    for row in cursor.fetchall():
        print(row)
except sqlite3.Error as error:
    print("Database error:", error)
finally:
    connection.close()

Choose only two result columns; a sorting field need not be displayed.

SELECT club_name, capacity FROM Clubs ORDER BY club_id;

The table retains all its fields. The query chooses its output columns.

AND requires both comparisons.

SELECT club_name, capacity FROM Clubs WHERE category = 'Technology' AND capacity >= 15 ORDER BY club_id;

Game Design and Coding qualify.

Group the alternatives before requiring a zero fee.

SELECT club_name, category FROM Clubs WHERE (category = 'Technology' OR category = 'Strategy') AND fee_cents = 0 ORDER BY club_id;

Without suitable grouping, a free-only requirement can accidentally apply to only one branch.

A deterministic ORDER BY comes before LIMIT.

SELECT club_name, capacity FROM Clubs ORDER BY capacity DESC, club_id ASC LIMIT 3;

Drama, Film and Coding are returned under this rule.

Skip the first three rows.

SELECT club_id, club_name FROM Clubs ORDER BY club_id LIMIT 3 OFFSET 3;

The result contains IDs 4, 5 and 6. OFFSET is a row count to skip, not a page number.

Percent can match zero characters.

SELECT club_name FROM Clubs WHERE club_name LIKE 'Robotics%' ORDER BY club_id;

Robotics and Robotics Junior match. Percent does not require an extra character.

Combine IN with BETWEEN.

SELECT club_name, category, capacity FROM Clubs WHERE category IN ('Creative', 'Strategy') AND capacity BETWEEN 12 AND 20 ORDER BY club_id;

Art, Film and Chess qualify. Both range boundaries are included.

NULL and empty text require different tests.

SELECT club_name FROM Clubs WHERE room IS NULL OR trim(room) = '' ORDER BY club_id;

Film, Drama and Robotics Junior qualify. IS NOT NULL alone would not remove a blank string.

DISTINCT acts on the complete selected pair.

SELECT DISTINCT category, capacity FROM Clubs ORDER BY category, capacity;

Do not include club_id if you want repeated category–capacity pairs removed.

Use real division and SQLite text concatenation.

SELECT club_name, fee_cents / 100.0 AS amount, club_name || ' (' || category || ')' AS label FROM Clubs WHERE fee_cents > 0 ORDER BY amount DESC, club_id;

The stored cents and names are unchanged. Free clubs are excluded by the explicit condition.

Fix clause order and use the missing-value test.

SELECT club_name FROM Clubs WHERE room IS NULL ORDER BY capacity DESC, club_id ASC LIMIT 2;

The NULL rooms belong to Drama and Film. A blank string is not included.

Separate eligibility, output calculations and ordering; filter the source fee field.

SELECT club_name || ' (' || category || ')' AS club_label, capacity, fee_cents / 100.0 AS amount, session_minutes * 4 AS four_session_minutes FROM Clubs WHERE category IN ('Technology', 'Creative') AND capacity >= 12 AND room IS NOT NULL AND trim(room) <> '' ORDER BY fee_cents ASC, capacity DESC, club_id ASC LIMIT 4;

Coding, Game Design, Art and Robotics qualify in that order. Four is a maximum; this dataset happens to provide four matches.

A limit does not define order. Equality with NULL is not true. Unique IDs distinguish each selected pair. Integer division truncates when both operands are integers, while a real operand gives real division.