Specialized Topics · Part T5

SQL & Databases

Q: The lesson says an index converts an O(n) scan into an O(log n) lookup. What structure makes that possible, and what is the trade-off that means you shouldn't index every column?

An index is a sorted B-tree (the balanced tree from the DSA track) on a column, so the engine descends the tree instead of checking every row — each step eliminates roughly half the remaining rows. The trade-off: indexes use extra space and slow writes (the tree must be kept updated), so you index only the columns you filter, join, or sort on.

Q: For LLM-generated SQL the lesson uses validate_sql() plus a read-only connection. Why are two independent defenses used instead of one?

It's defense-in-depth: validate_sql requires the query to start with SELECT and rejects write/DDL keywords (INSERT/UPDATE/DELETE/DROP/…), while opening the DB with mode=ro means even a query that slips past the validator physically can't write. If a hijacked model produces DELETE FROM users, one layer rejects it and the other would refuse it anyway. (Parameterizing with ? separately blocks classic injection by treating input as data, not SQL.)

Every real agent eventually touches a database — the data-analyst project queries one, and any production system stores state in one. This part is a from-scratch SQL & databases track: queries and joins, indexing (why queries are fast or slow), transactions/ACID, connection pooling, and the security that matters for LLMs — SQL injection and safe text-to-SQL. Basic → advanced, with runnable SQLite you can paste into Python.

⏱️ ~2.5 hours🎯 Basic → Advanced🗄️ query → scale → securerunnable

Learning objectives

Write SELECT queries with filtering, sorting, grouping, and joins.
Explain how an index turns an O(n) scan into an O(log n) lookup.
Use transactions and understand ACID.
Manage connections with a pool (and why).
Prevent SQL injection and build safe text-to-SQL for an LLM.

1 · Tables & SELECT — the basics basic advanced

A relational database stores tables (rows × typed columns). SQL is the declarative language to query them: you describe what you want, the engine figures out how. Everything runnable here uses Python's built-in sqlite3 — no install.

Example code for learning — review, test, and adapt it before running against real or production systems. Commands can create, change, or delete resources. See the Terms & Disclaimer.

Create, insert, query (runnable)

pythonimport sqlite3
con = sqlite3.connect(":memory:")      # in-memory DB for the demo
cur = con.cursor()
cur.execute("""CREATE TABLE incidents (
    id INTEGER PRIMARY KEY, service TEXT, severity TEXT, minutes INTEGER)""")
cur.executemany("INSERT INTO incidents (service, severity, minutes) VALUES (?, ?, ?)",
    [("api", "high", 42), ("api", "low", 5), ("web", "high", 30)])
con.commit()

for row in cur.execute(
    "SELECT service, severity, minutes FROM incidents "
    "WHERE severity = 'high' ORDER BY minutes DESC"):
    print(row)          # ('api','high',42)  ('web','high',30)

▶ How this works

This is a complete first taste of a database, all in Python. It creates a table, puts three rows in it, then asks a question of the data and prints the answer. A table is just a grid: named columns (like a spreadsheet header) and rows of data underneath.

sqlite3.connect(":memory:") starts a tiny throwaway database that lives only in memory (nothing is saved to disk). cur = con.cursor() gives you a cursor — the object you send SQL through and read results back from.
CREATE TABLE incidents (...) defines the shape of the table: four columns and their types. id INTEGER PRIMARY KEY means id is a whole number that uniquely identifies each row; service/severity hold text and minutes holds a number.
executemany("INSERT INTO ... VALUES (?, ?, ?)", [...]) adds three rows at once. The ? marks are placeholders — the real values come from the list, so ("api","high",42) fills the first row. (Using ? instead of gluing text together is also the safe habit you'll see again in §7.)
con.commit() saves the inserts. Then the SELECT reads them back: SELECT service, severity, minutes picks which columns to return, FROM incidents names the table, WHERE severity = 'high' keeps only the high-severity rows, and ORDER BY minutes DESC sorts them highest-minutes-first. The Python for row in cur.execute(...) loop then prints each matching row.

What the output means: Two rows print — the two high-severity incidents, longest outage first: ('api','high',42) then ('web','high',30). The low row is filtered out by WHERE.

Try this: Change WHERE severity = 'high' to WHERE minutes > 10 and predict which rows come back before running. Then swap DESC for ASC and watch the sort order flip to smallest-first.

The clause order to memorizeSELECT cols FROM table WHERE row-filter GROUP BY buckets HAVING group-filter ORDER BY sort LIMIT n. The engine logically applies them roughly in that order — WHERE filters rows before grouping, HAVING filters after.

2 · Grouping & aggregation intermediate

GROUP BY collapses rows into buckets and runs aggregates (COUNT, SUM, AVG, MAX) per bucket — the SQL equivalent of D3's Counter/defaultdict grouping, but done in the engine.

Aggregate per group

Setup to run this snippet

class _cur_t:
    execute = 'demo'
    def execute(self, *a, **k): return 'demo'
    def __getattr__(self, k): return 'demo'
cur = _cur_t()

pythonfor row in cur.execute("""
    SELECT service, COUNT(*) AS n, AVG(minutes) AS avg_min
    FROM incidents
    GROUP BY service
    HAVING COUNT(*) > 1
    ORDER BY avg_min DESC"""):
    print(row)          # ('api', 2, 23.5)
# WHERE filters rows before grouping; HAVING filters the grouped results.

▶ How this works

Grouping answers "per-category" questions like "how many incidents and the average outage per service?". GROUP BY collapses many rows into one row per category, and aggregate functions (COUNT, AVG, SUM, MAX) summarise each group.

GROUP BY service gathers all rows that share the same service into one bucket — so all the api rows become a single group, all the web rows another.
COUNT(*) AS n counts the rows in each group; AVG(minutes) AS avg_min averages the minutes column within the group. AS n just gives the result column a friendly name.
HAVING COUNT(*) > 1 filters the groups — it keeps only services that had more than one incident. This is different from WHERE: WHERE filters individual rows before grouping; HAVING filters the finished groups after.
ORDER BY avg_min DESC sorts the surviving groups by their average, worst first.

What the output means: One row per qualifying service. Here only api has more than one incident, so you get ('api', 2, 23.5) — 2 incidents, averaging 23.5 minutes.

Try this: Remove the HAVING line and re-run: now web (with just 1 incident) also appears. That single line is the difference between "all groups" and "only busy ones".

3 · Joins — combining tables intermediate → advanced

Data is split across tables (normalization); joins recombine them on a shared key. The join types differ in how they treat rows with no match.

INNER keeps only matches; LEFT keeps all left rows. Use INNER when you need both sides present; LEFT when you want every left row and NULLs where the right has no match (e.g. "all services, and their incident count if any").

🗺️ How to read this diagram

This is a Venn-diagram view of the two most common joins. Each circle is one table's rows; the shaded area is what the join returns.

The left picture (INNER JOIN) shades only the overlap of the two circles — rows that have a match in both tables. Rows with no partner are dropped.
The right picture (LEFT JOIN) shades the entire left circle plus the overlap — you keep every left-table row, matched or not.
Where a left row has no match on the right, the right-side columns come back as NULL (SQL's word for "no value"). That's the caption's "NULLs".
Rule of thumb from the caption: use INNER when you need both sides to exist; use LEFT when you want all left rows regardless (e.g. "list all services, with their incident count if any").

In short: INNER = "only where they match." LEFT = "everything on the left, plus matches where they exist." That one distinction covers most day-to-day joins.

A join across two tables

Setup to run this snippet

class _con_t:
    commit = 'demo'
    def commit(self, *a, **k): return 'demo'
    def __getattr__(self, k): return 'demo'
con = _con_t()
class _cur_t:
    execute = 'demo'
    executemany = 'demo'
    def execute(self, *a, **k): return 'demo'
    def executemany(self, *a, **k): return 'demo'
    def __getattr__(self, k): return 'demo'
cur = _cur_t()

pythoncur.execute("CREATE TABLE owners (service TEXT, team TEXT)")
cur.executemany("INSERT INTO owners VALUES (?, ?)",
    [("api", "payments"), ("web", "frontend")])
con.commit()

for row in cur.execute("""
    SELECT i.service, o.team, COUNT(*) AS incidents
    FROM incidents i
    INNER JOIN owners o ON o.service = i.service
    GROUP BY i.service, o.team"""):
    print(row)          # ('api','payments',2)  ('web','frontend',1)

▶ How this works

A join stitches two tables together on a shared column. Incident data lives in incidents, but the owning team lives in a separate owners table. A join lets one query pull from both so you can report incidents with their team.

First we build the second table: CREATE TABLE owners (service TEXT, team TEXT) and insert two rows mapping each service to a team (api → payments, web → frontend).
FROM incidents i and INNER JOIN owners o give each table a short alias (i and o) so you can write i.service or o.team without repeating the full name.
ON o.service = i.service is the matching rule: pair up rows where the service names are equal. INNER JOIN keeps only rows that have a match on both sides.
SELECT i.service, o.team, COUNT(*) combined with GROUP BY i.service, o.team then counts incidents per service+team pair — a join and an aggregate working together.

What the output means: One row per service, now enriched with its team and incident count: ('api','payments',2) and ('web','frontend',1).

Try this: Add a service to owners that has no incidents (say ('db','data')). With INNER JOIN it won't appear — no matching incident. Switch to LEFT JOIN (see the diagram above) and it would show up with a count of 0-ish / NULLs.

4 · Indexes — why queries are fast or slow advanced

Without an index, WHERE service = 'api' scans every row — O(n). An index is a sorted structure (a B-tree — the balanced tree from D4) on that column, turning the lookup into O(log n). This is the same array-vs-tree trade-off from the DSA track, applied to disk.

An index is a B-tree on a column. It converts a full O(n) scan into an O(log n) descent — the D4 tree win, on disk. Cost: indexes use space and slow writes (they must be updated), so index the columns you filter/join/sort on, not everything.

🗺️ How to read this diagram

This contrasts the two ways the database can find a row — the slow way (no index) on the left, the fast way (index) on the right — and why an index is worth having.

On the left, the stacked rectangles are table rows. Without an index the engine must check every row top to bottom ("check all →"). That's O(n): double the rows, double the work.
On the right, the connected circles are a B-tree — a sorted, branching structure. The engine starts at the top node and follows one branch down to the answer ("found").
Because each step down the tree eliminates roughly half the remaining rows, the index lookup is O(log n) — vastly fewer steps for large tables.
The trade-off in the caption: indexes take extra space and make writes a little slower (the tree must be kept updated), so you index the columns you filter/join/sort on — not everything.

In short: Think of a phone book: scanning every name is the left side; the alphabetical ordering that lets you flip straight to "S" is the index on the right.

Create an index & read the query plan

Setup to run this snippet

class _cur_t:
    execute = 'demo'
    def execute(self, *a, **k): return 'demo'
    def __getattr__(self, k): return 'demo'
cur = _cur_t()

pythoncur.execute("CREATE INDEX idx_service ON incidents(service)")

# EXPLAIN QUERY PLAN shows whether the index is used:
for row in cur.execute(
    "EXPLAIN QUERY PLAN SELECT * FROM incidents WHERE service='api'"):
    print(row)      # ... "SEARCH incidents USING INDEX idx_service" (not "SCAN")

▶ How this works

An index makes lookups fast. Without one, finding service='api' forces the engine to read every row (slow). An index is a pre-sorted lookup structure on a column, so the engine can jump straight to the matches. This lab creates one and then proves it's being used.

CREATE INDEX idx_service ON incidents(service) builds an index on the service column. Think of it like the index at the back of a book: instead of reading every page, you look up the word and jump to the right pages.
EXPLAIN QUERY PLAN in front of a query does not run it for real — it asks the engine "how would you execute this?" and prints the strategy.
The loop prints that plan. You're looking for the word SEARCH ... USING INDEX idx_service (fast, jumps to matches) rather than SCAN (slow, reads every row). Seeing SEARCH ... USING INDEX confirms your index is doing its job.

What the output means: A plan row containing SEARCH incidents USING INDEX idx_service — the engine will use the index instead of scanning the whole table.

Try this: Index a column you never filter on and it just wastes space. Rule of thumb: index the columns you actually use in WHERE, JOIN, or ORDER BY — not every column.

5 · Transactions & ACID advanced

A transaction groups statements so they all succeed or all fail — no half-updates. The guarantees are ACID: Atomic (all-or-nothing), Consistent (constraints hold), Isolated (concurrent txns don't corrupt each other), Durable (committed = survives a crash).

All-or-nothing with rollback

pythontry:
    cur.execute("BEGIN")
    cur.execute("UPDATE incidents SET minutes = minutes - 10 WHERE id = 1")
    cur.execute("UPDATE incidents SET minutes = minutes + 10 WHERE id = 2")
    con.commit()          # both succeed together
except Exception:
    con.rollback()        # any failure → undo BOTH (atomicity)
    raise

▶ How this works

A transaction bundles several changes so they all happen or none happen. The classic case is moving a value from one row to another: you must subtract from one and add to the other — never just half of it. This code does exactly that and undoes everything if anything goes wrong.

cur.execute("BEGIN") opens the transaction — from here, changes are held together as one unit rather than saved one-by-one.
The two UPDATE statements are the paired change: subtract 10 minutes from incident id = 1, add 10 to id = 2. SET minutes = minutes - 10 means "take the current value and lower it by 10".
con.commit() makes both updates permanent together. If both lines ran without error, this seals the deal.
except Exception: catches any failure. con.rollback() then throws away every change since BEGIN, so you can't be left with only the subtraction applied. raise re-throws the error so the caller still knows it failed. This all-or-nothing behaviour is the A (Atomic) in ACID.

Try this: Imagine a crash happening between the two UPDATEs. Without the transaction, 10 minutes would vanish from id 1 and never arrive at id 2. With rollback(), the database snaps back to how it started — the whole point of transactions.

The classic exampleA bank transfer is two updates (debit + credit). Without a transaction, a crash between them loses money. Wrapped in a transaction, it's atomic — both or neither. For an agent, wrap any multi-step state change (and pair with idempotency keys from A6).

6 · Connection pooling advanced

Opening a DB connection is expensive (handshake, auth). A pool keeps a set of open connections and hands them out, so a busy service reuses instead of reconnecting per request — the same "reuse expensive resources" idea as the rate limiter/resource management in P6.

A pool reuses connections. Requests borrow a live connection and return it, avoiding per-request connect cost and capping total connections to protect the DB. Libraries like SQLAlchemy, psycopg-pool, or your framework provide this — you rarely hand-roll it.

🗺️ How to read this diagram

This shows why busy services keep a pool of database connections instead of opening a new one for every request. Opening a connection is slow (handshake + login), so reuse pays off.

On the left, req 1 and req 2 are incoming requests that each need to talk to the database.
The middle box (pool) holds a fixed number of already-open connections ("N open conns"). Requests borrow one, use it, then hand it back for the next request — the arrows show requests flowing into the pool.
The single arrow from the pool to the database on the right shows the connections are shared and reused, not recreated per request.
Two wins from the caption: no per-request connect cost, and a cap on total connections so a traffic spike can't overwhelm the database. Libraries (SQLAlchemy, psycopg-pool) provide this — you rarely build it yourself.

In short: It's like a pool of taxis waiting at a rank: riders grab a waiting cab and return it, instead of everyone buying a new car for each trip.

7 · SQL injection & safe text-to-SQL the LLM angle

The classic web vuln — and it's back with a vengeance when an LLM writes the SQL. Two rules: never string-format user/model input into a query (use parameters), and when an LLM generates SQL, validate + run read-only.

✗ injection → ✓ parameterized → ✓ safe text-to-SQL

Illustrative fragment — defines demo values / files are needed before this runs standalone.

python# ✗ NEVER — string interpolation = SQL injection
# cur.execute(f"SELECT * FROM users WHERE name = '{name}'")
#   name = "x'; DROP TABLE users;--"  →  catastrophe

# ✓ Parameterized — the driver escapes safely, always
cur.execute("SELECT * FROM incidents WHERE service = ?", (user_value,))

# ✓ LLM-generated SQL: validate it's read-only, then run on a RO connection
import re
def validate_sql(sql):
    if not re.match(r"^\s*SELECT\b", sql, re.I):
        raise ValueError("only SELECT allowed")
    if re.search(r"\b(INSERT|UPDATE|DELETE|DROP|ALTER|ATTACH|PRAGMA)\b", sql, re.I):
        raise ValueError("write/DDL keyword rejected")
    return sql

# belt + suspenders: open the DB read-only so even a bypass can't write
ro = sqlite3.connect("file:app.db?mode=ro", uri=True)

▶ How this works

This lab shows the single most important database security habit, plus the extra guardrails you need when an LLM writes the SQL. "SQL injection" is when untrusted text sneaks in and changes what your query does — potentially deleting your data.

The first (commented-out) block is the dangerous way: gluing a variable straight into a query string with an f-string. If name is "x'; DROP TABLE users;--", that hostile text becomes part of the command and can drop your whole table. Never do this.
The safe version uses a parameter: cur.execute("... WHERE service = ?", (user_value,)). The ? is a placeholder and the driver inserts user_value as pure data, never as runnable SQL — so injection can't happen.
validate_sql(sql) guards LLM-written queries. re.match(r"^\s*SELECT\b", ...) requires the query to start with SELECT (a read-only query), and re.search(r"\b(INSERT|UPDATE|DELETE|DROP|...)\b", ...) rejects any query containing a word that changes data. Failing either check raises an error.
sqlite3.connect("file:app.db?mode=ro", uri=True) is the "belt and suspenders" layer: it opens the database in read-only mode, so even if a bad query slips past the validator, the connection itself physically can't write.

Try this: Picture an LLM that gets tricked into producing DELETE FROM users. validate_sql rejects it (fails the SELECT-only check), and the read-only connection would refuse it anyway. Two independent defenses — that's why both exist.

🔗 In this courseThis is exactly the Data Analyst project's defense: validate_sql() (SELECT-only, reject write keywords) + a read-only connection, so a hijacked model (T1) still can't mutate data. Parameterization is the DB case of "never feed model/user input to a dangerous sink" from T1 §6.

🎯 Interview practice interview

The interview questions this topic gets asked — worked, with code. For the full pattern catalog see D8 · Big Tech DSA patterns.

Second-highest salary (a SQL-round classic)

Two idioms: a subquery with MAX, or DENSE_RANK() in a window function.

sql-- subquery form
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- window-function form (handles ties, top-N generally)
SELECT salary FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS r
  FROM employees) t
WHERE r = 2;

▶ How this works

A staple SQL interview question: "find the second-highest salary." It's tricky because SQL has no built-in "give me the 2nd value" — you have to express it. Two standard idioms are shown.

Subquery form: the inner (SELECT MAX(salary) FROM employees) finds the top salary. The outer query then takes MAX(salary) among everyone earning less than that (WHERE salary < ...) — which is, by definition, the second-highest.
A subquery is just a query nested inside another; the inner one runs first and its result feeds the outer WHERE.
Window-function form: DENSE_RANK() OVER (ORDER BY salary DESC) stamps every row with a rank — 1 for the highest salary, 2 for the next distinct one, and so on. It's computed over all rows without collapsing them (that's what OVER (...) means).
The outer WHERE r = 2 then simply keeps the rows ranked second. This form generalises: change 2 to 3 for third-highest, and it handles tied salaries correctly.

What the output means: Both queries return one number: the second-distinct-highest salary in the employees table.

Try this: If two people share the top salary, the subquery form still works (it looks strictly below the max). DENSE_RANK also handles ties by giving equal salaries the same rank — that's why interviewers like the window-function version.

Prevent SQL injection — parameterize

Never string-format input; use bound parameters. For LLM-generated SQL, validate read-only too.

sql# ✗ cur.execute(f"SELECT * FROM users WHERE name = '{name}'")
# ✓ parameterized
cur.execute("SELECT * FROM users WHERE name = ?", (name,))

Checkpoint expert

Write SELECT/WHERE/GROUP BY/JOIN queries and know the clause order.
Explain how a B-tree index makes lookups O(log n), and read a query plan.
Use transactions and state the ACID guarantees.
Explain why connection pooling matters.
Prevent SQL injection with parameters, and build safe read-only text-to-SQL for an LLM.

🪜 Practice ladder beginner → industry

Six graded exercises, easy to real-world. Try each before opening its solution.

Exercise 1 · Create, insert, and SELECT with a filterBeginner

Context: Everything in text-to-SQL rests on the basics: create a table, insert rows with placeholders, and filter with a WHERE clause. Placeholders are also the habit that prevents SQL injection.

Your task: On an in-memory SQLite database, create an incidents table, insert rows with placeholders, then SELECT the high-severity rows ordered by minutes descending.

Requirements:

Use the stdlib sqlite3 module and an in-memory connection
Create the table with a primary key and typed columns
Insert rows with executemany and ? placeholders, then commit
SELECT with a WHERE severity = 'high' filter ordered by minutes descending
Print the matching rows and note that placeholders (not string-gluing) fill values safely

💡 Hint: Passing values as a parameter tuple to execute/executemany is the same safe habit that shows up again in the final rung.

Show solution

Runnable, stdlib only (sqlite3):

import sqlite3

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("""CREATE TABLE incidents (
    id INTEGER PRIMARY KEY,
    service TEXT, severity TEXT, minutes INTEGER)""")
cur.executemany(
    "INSERT INTO incidents (service, severity, minutes) VALUES (?, ?, ?)",
    [("api", "high", 42), ("web", "low", 5), ("api", "high", 18)])
con.commit()

for row in cur.execute(
        "SELECT service, severity, minutes FROM incidents "
        "WHERE severity = 'high' ORDER BY minutes DESC"):
    print(row)
# ('api', 'high', 42)
# ('api', 'high', 18)

? placeholders (not string-gluing) fill values safely — the same habit that prevents SQL injection.

Exercise 2 · Group and aggregate per serviceIntermediate

Context: Per-category questions are answered with grouping: collapse rows into one bucket per key, then summarize each bucket with aggregate functions.

Your task: For each service, report how many incidents there were and the average outage minutes using GROUP BY with COUNT(*) and AVG(minutes).

Requirements:

Seed an incidents table on an in-memory SQLite database
GROUP BY service to make one bucket per service
Select COUNT(*) and AVG(minutes) as aliased columns
Order the buckets (e.g. by count descending)
Print one summarized row per service

💡 Hint: The GROUP BY key determines the buckets; every non-aggregated column in the SELECT should be that key.

Show solution

Runnable, stdlib only:

import sqlite3
con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("CREATE TABLE incidents (service TEXT, severity TEXT, minutes INTEGER)")
cur.executemany("INSERT INTO incidents VALUES (?, ?, ?)",
    [("api","high",42),("web","low",5),("api","high",18),("web","high",30)])
con.commit()

for row in cur.execute("""
    SELECT service, COUNT(*) AS n, AVG(minutes) AS avg_min
    FROM incidents
    GROUP BY service
    ORDER BY n DESC"""):
    print(row)
# ('api', 2, 30.0)
# ('web', 2, 17.5)

GROUP BY service collapses rows into one bucket per service; the aggregates summarize each bucket.

Exercise 3 · Join incidents to their owning teamAdvanced

Context: Real questions span more than one table. An INNER JOIN combines rows that match on a key, keeping only rows present in both tables.

Your task: Create an owners(service, team) table and INNER JOIN it to incidents to count incidents per team.

Requirements:

Create and seed both an incidents and an owners table
INNER JOIN on the shared service key with an ON clause
GROUP BY team and COUNT(*) the incidents per team
Order the result by incident count descending
Note that INNER JOIN keeps only rows with a match in both tables

💡 Hint: The ON clause names the matching column; put the join before the GROUP BY so you aggregate the joined rows.

Show solution

Runnable, stdlib only:

import sqlite3
con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("CREATE TABLE incidents (service TEXT, minutes INTEGER)")
cur.executemany("INSERT INTO incidents VALUES (?, ?)",
    [("api",42),("web",5),("api",18)])
cur.execute("CREATE TABLE owners (service TEXT, team TEXT)")
cur.executemany("INSERT INTO owners VALUES (?, ?)",
    [("api","platform"),("web","frontend")])
con.commit()

for row in cur.execute("""
    SELECT o.team, COUNT(*) AS incidents
    FROM incidents i
    INNER JOIN owners o ON o.service = i.service
    GROUP BY o.team
    ORDER BY incidents DESC"""):
    print(row)
# ('platform', 2)
# ('frontend', 1)

INNER JOIN keeps only rows with a match in both tables; the ON clause is the matching key (service).

Exercise 4 · Add an index and read the query planExpert

Context: Indexes are why the same query gets dramatically faster on large tables. The query planner shows the difference — a full scan versus a targeted index search.

Your task: On a table with many rows, run a filtered query and print EXPLAIN QUERY PLAN before and after creating an index on the filtered column.

Requirements:

Populate a table with thousands of rows so the plan difference is meaningful
Run EXPLAIN QUERY PLAN on a filtered SELECT and observe a SCAN
Create an index on the filtered column
Re-run the plan and observe it change to a SEARCH using the index
Explain that a SCAN reads every row while the index turns it into a targeted SEARCH

💡 Hint: Prefix the same query string with EXPLAIN QUERY PLAN both times and compare the SCAN vs SEARCH wording in the output.

Show solution

Runnable, stdlib only. Watch the plan change from a full scan to an index search:

import sqlite3
con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("CREATE TABLE incidents (service TEXT, minutes INTEGER)")
cur.executemany("INSERT INTO incidents VALUES (?, ?)",
    [("svc%d" % (i % 50), i) for i in range(5000)])
con.commit()

q = "SELECT * FROM incidents WHERE service = 'svc7'"
print("BEFORE:", cur.execute("EXPLAIN QUERY PLAN " + q).fetchall())
# BEFORE: [(..., 'SCAN incidents')]

cur.execute("CREATE INDEX idx_service ON incidents(service)")
print("AFTER: ", cur.execute("EXPLAIN QUERY PLAN " + q).fetchall())
# AFTER:  [(..., 'SEARCH incidents USING INDEX idx_service (service=?)')]

Without an index the engine scans every row (SCAN); the index turns it into a targeted SEARCH, which is why the same query gets dramatically faster on large tables.

Exercise 5 · All-or-nothing writes with a transaction rollbackProfessional

Context: Money and state changes must be atomic — the A in ACID. A transaction lets a multi-step change either fully commit or fully vanish, never leaving a partial result visible.

Your task: Show a transaction that debits one account and credits another, and if the second step's precondition fails, ROLLBACK so neither change persists.

Requirements:

Seed an accounts table with balances on an in-memory database
Open a transaction with BEGIN and debit the first account
Raise and abort (e.g. insufficient funds) before crediting the second account
Call rollback() in the failure path so neither row changes
Print the balances afterward to prove no partial transfer is visible

💡 Hint: Do the check between the debit and the credit; on failure the rollback discards the debit so the balances match their starting values.

Show solution

Runnable, stdlib only:

import sqlite3
con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("CREATE TABLE accounts (name TEXT PRIMARY KEY, balance INTEGER)")
cur.executemany("INSERT INTO accounts VALUES (?, ?)", [("a", 100), ("b", 0)])
con.commit()

try:
    cur.execute("BEGIN")
    cur.execute("UPDATE accounts SET balance = balance - 150 WHERE name='a'")
    if cur.execute("SELECT balance FROM accounts WHERE name='a'").fetchone()[0] < 0:
        raise ValueError("insufficient funds")     # abort the whole transfer
    cur.execute("UPDATE accounts SET balance = balance + 150 WHERE name='b'")
    con.commit()
except ValueError as e:
    con.rollback()
    print("rolled back:", e)

print(cur.execute("SELECT name, balance FROM accounts ORDER BY name").fetchall())
# rolled back: insufficient funds
# [('a', 100), ('b', 0)]   -- neither row changed

Atomicity (the A in ACID): the debit and credit either both commit or both vanish — no partial transfer is ever visible.

Exercise 6 · Safe text-to-SQL: parameterize + validate against injectionIndustry scenario

Context: You're building a text-to-SQL feature. Gluing user input into a query lets an attacker supplying x' OR '1'='1 read every row; the fix is placeholders for values and an allowlist for anything the user gets to name.

Your task: Show the unsafe string-glued query leaking all rows, then the parameterized version that treats the input as a value, plus an allowlist for any column the user gets to name.

Requirements:

Seed an incidents table with a sensitive column
Build the unsafe query by string-formatting the attack input and show it returns every row
Rewrite with a ? placeholder so the input is a value and matches nothing
Add a safe_select(col, value) that validates the column name against an allowlist since identifiers can't be parameterized
Show a valid column lookup working and a malicious column name being blocked
State the rule: values go through placeholders; identifiers get an allowlist; never glue model/user text into SQL

💡 Hint: Placeholders bind values only — a column or table name must be checked against a fixed allowlist before it's ever interpolated into the query string.

Show solution

Runnable, stdlib only — the attack actually fires against the unsafe query, then is neutralized:

import sqlite3
con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("CREATE TABLE incidents (service TEXT, secret TEXT)")
cur.executemany("INSERT INTO incidents VALUES (?, ?)",
    [("api","a1"),("web","w1"),("db","d1")])
con.commit()

evil = "x' OR '1'='1"

# UNSAFE: gluing input into SQL -> the OR '1'='1' returns EVERY row
unsafe = "SELECT * FROM incidents WHERE service = '%s'" % evil
print("unsafe:", cur.execute(unsafe).fetchall())   # leaks all 3 rows

# SAFE: ? placeholder -> input is a VALUE, not SQL. Matches nothing.
print("safe:  ", cur.execute(
    "SELECT * FROM incidents WHERE service = ?", (evil,)).fetchall())  # []

# Columns/tables can't be parameterized -> allowlist them
ALLOWED_COLS = {"service", "secret"}
def safe_select(col, value):
    if col not in ALLOWED_COLS:
        raise ValueError(f"disallowed column: {col!r}")
    return cur.execute(
        f"SELECT * FROM incidents WHERE {col} = ?", (value,)).fetchall()

print("byname:", safe_select("service", "web"))    # [('web', 'w1')]
try:
    safe_select("secret; DROP TABLE incidents", "x")
except ValueError as e:
    print("blocked:", e)

Rule: user-supplied values always go through ? placeholders; anything that must be an identifier (column/table) can't be parameterized, so validate it against an allowlist. Never glue model/user text into SQL.

Knowledge check check yourself

✓ Knowledge check

The lesson says an index converts an O(n) scan into an O(log n) lookup. What structure makes that possible, and what is the trade-off that means you shouldn't index every column?

Show answer

An index is a sorted B-tree (the balanced tree from the DSA track) on a column, so the engine descends the tree instead of checking every row — each step eliminates roughly half the remaining rows. The trade-off: indexes use extra space and slow writes (the tree must be kept updated), so you index only the columns you filter, join, or sort on.

✓ Knowledge check

For LLM-generated SQL the lesson uses validate_sql() plus a read-only connection. Why are two independent defenses used instead of one?

Show answer

It's defense-in-depth: validate_sql requires the query to start with SELECT and rejects write/DDL keywords (INSERT/UPDATE/DELETE/DROP/…), while opening the DB with mode=ro means even a query that slips past the validator physically can't write. If a hijacked model produces DELETE FROM users, one layer rejects it and the other would refuse it anyway. (Parameterizing with ? separately blocks classic injection by treating input as data, not SQL.)

Educational content, provided as-is and without warranty. Code samples are examples — review, test, and adapt them before using in production. See the Terms of Use & Disclaimer. Use at your own risk.

Learning objectives

1 · Tables & SELECT — the basics basic advanced

2 · Grouping & aggregation intermediate

3 · Joins — combining tables intermediate → advanced

4 · Indexes — why queries are fast or slow advanced

5 · Transactions & ACID advanced

6 · Connection pooling advanced

7 · SQL injection & safe text-to-SQL the LLM angle

🎯 Interview practice interview

Checkpoint expert

🪜 Practice ladder beginner → industry

Knowledge check check yourself

More in Specialized topics