SQL for testers in 10 exercises

A tester should turn a business rule into a query and explain what a returned row would mean. Here the rule is simple: paid orders contribute to revenue; cancelled orders do not. All customers are fake. Amounts are integer paise, so 10000 means ₹100.

Verified on Linux with Python 3.14.4 and SQLite 3.46.1. Python's SQLite library version can differ from a separately installed SQLite command-line tool. No database server or third-party Python package is needed.

Set up and run

lab=$(mktemp -d /tmp/qa-sql-XXXXXX)
cd "$lab"
python3 --version
python3 -c 'import sqlite3; print(sqlite3.sqlite_version)'

Save as exercises.py. Each numbered check is one exercise; predict its rows before running it. The expected rows are executable answers, including their order.

# exercises.py
import sqlite3

con = sqlite3.connect(":memory:")
try:
    con.executescript("""
        CREATE TABLE customers (id INTEGER PRIMARY KEY, email TEXT);
        CREATE TABLE orders (
            id INTEGER PRIMARY KEY, customer_id INTEGER,
            amount_paise INTEGER NOT NULL, status TEXT NOT NULL
        );
        INSERT INTO customers VALUES
            (1, '[email protected]'), (2, '[email protected]'),
            (3, '[email protected]'), (4, NULL);
        INSERT INTO orders VALUES
            (101, 1, 10000, 'paid'), (102, 1, 20000, 'paid'),
            (103, 2, 5000, 'cancelled'), (104, 2, 10000, 'paid');
    """)

    def check(number, sql, expected, params=()):
        actual = con.execute(sql, params).fetchall()
        assert actual == expected, (number, actual, expected)
        print(f"Exercise {number}: {actual}")

    check(1, "SELECT id FROM orders WHERE status = ? ORDER BY id",
          [(101,), (102,), (104,)], ('paid',))
    check(2, """SELECT id FROM orders
          WHERE amount_paise BETWEEN 10000 AND 20000 ORDER BY id""",
          [(101,), (102,), (104,)])
    check(3, "SELECT id FROM customers WHERE email IS NULL ORDER BY id",
          [(4,)])
    check(4, """SELECT o.id, c.email FROM orders o
          JOIN customers c ON c.id = o.customer_id
          WHERE o.status = 'paid' ORDER BY o.id""",
          [(101, '[email protected]'), (102, '[email protected]'),
           (104, '[email protected]')])
    check(5, """SELECT c.id, COUNT(o.id) FROM customers c
          LEFT JOIN orders o ON o.customer_id = c.id
          GROUP BY c.id ORDER BY c.id""",
          [(1, 2), (2, 2), (3, 0), (4, 0)])
    check(6, """SELECT customer_id, SUM(amount_paise) FROM orders
          WHERE status = 'paid' GROUP BY customer_id ORDER BY customer_id""",
          [(1, 30000), (2, 10000)])
    check(7, """SELECT email, COUNT(*) FROM customers
          WHERE email IS NOT NULL GROUP BY email HAVING COUNT(*) > 1
          ORDER BY email""", [('[email protected]', 2)])
    check(8, """SELECT c.id FROM customers c WHERE NOT EXISTS
          (SELECT 1 FROM orders o WHERE o.customer_id = c.id) ORDER BY c.id""",
          [(3,), (4,)])
    check(9, """SELECT DISTINCT amount_paise FROM orders
          WHERE status = 'paid' ORDER BY amount_paise DESC LIMIT 1 OFFSET 1""",
          [(10000,)])

    con.execute('BEGIN')
    con.execute('UPDATE orders SET amount_paise = ? WHERE id = ?', (1, 101))
    assert con.execute('SELECT amount_paise FROM orders WHERE id = 101').fetchone() == (1,)
    con.rollback()
    check(10, 'SELECT amount_paise FROM orders WHERE id = 101', [(10000,)])
finally:
    con.close()

python3 exercises.py

Expect ten Exercise lines and a successful exit. Every run recreates the data in memory. An assertion reports the exercise number, actual rows and expected rows when they differ.

1. Filter paid orders

Exercise 1 binds paid through a placeholder. Keep data separate from SQL text, especially for values coming from user input. Expected order IDs: 101, 102 and 104.

2. Include both price boundaries

Exercise 2 uses BETWEEN for ₹100 through ₹200 inclusive. The two ₹100 orders and the ₹200 order qualify. A boundary test that checks only a middle value would miss an exclusive-boundary bug.

3. Find a missing email

Exercise 3 returns customer 4. NULL represents missing information; testing it with ordinary equality is a common error. An empty string would be a different stored value.

4. Join an order to its customer

Exercise 4 associates each paid order with its customer's email. Join on the customer ID, not the email: the deliberately duplicated address would make email a poor identity key.

5. Keep customers with zero orders

Exercise 5 returns all four customers. Count the matched order ID so customers 3 and 4 have zero. Counting every joined row would count the unmatched placeholder row too.

6. Reconcile paid revenue

Exercise 6 returns 30000 and 10000 paise. Customer 2's cancelled ₹50 order contributes nothing. In a real reconciliation, first agree which statuses, refunds and taxes the report includes.

7. Find duplicate addresses

Exercise 7 returns [email protected] with count 2. HAVING filters grouped results. This is evidence to investigate, not automatic permission to delete either customer.

8. Find customers without any orders

Exercise 8 returns IDs 3 and 4. The correlated NOT EXISTS checks for an order for each customer. A customer with only cancelled orders still has an order, so customer 2 does not qualify.

9. Find the second distinct paid amount

Exercise 9 returns 10000 paise. DISTINCT matters because two orders share that amount. Clarify whether an interviewer wants the second row, second distinct amount or every tied order before choosing the query.

10. Verify an update and roll it back

Exercise 10 changes one amount inside a transaction, checks the temporary value, rolls back and checks the original. The update uses an ID predicate; omitting it would affect every order. This is a disposable database, not a recipe to experiment against production.

What to say in an interview

“I create a small dataset containing a duplicate, a missing value, a cancelled order and customers without orders. I state the expected rows, run the query and compare both values and counts. For updates, I check the affected record and transaction outcome.”

Follow-up: why ORDER BY? Without it, row order is not a stable assertion. The fixture deliberately has no foreign-key constraint or email uniqueness rule; it teaches queries, not a production schema. The temporary script folder can be deleted when you finish.