SQL & Databases

1 · Tables & SELECT — the basics basic advanced

Start with a moment you've probably had. Your agent works beautifully on a Python list of a few hundred dictionaries. Then the real data arrives — millions of rows that won't fit in memory, that other services also read and write, that must survive a restart. Your neat little list can't do any of that. The moment you need data that persists, is shared, and is bigger than RAM, you've arrived at a database — and SQL is the language you talk to it with.

Here's the mental model that makes SQL click. A table is a spreadsheet with rules: named columns across the top, one fact per row underneath, and every value in a column forced to be the same type (a number column can't secretly hold text). That "spreadsheet with rules" is the whole of the relational model — everything else is just ways to ask questions of those grids.

The part that trips up programmers coming from Python is that SQL is declarative, not imperative. In Python you write how: loop over the rows, test each one, append the matches. In SQL you write what you want — "give me the high-severity incidents, longest first" — and the engine's query planner decides how to get it (scan the table? use an index? in what order?). You describe the destination, not the driving directions. This feels strange at first and is exactly why it's powerful: the same SELECT that reads three rows on your laptop reads three billion in production, because the engine, not you, chooses the strategy.

A SELECT is built from clauses that each do one job: SELECT picks the columns, FROM names the table, WHERE keeps only the rows that match a condition, and ORDER BY sorts what survives. Read a query left to right and it almost narrates itself. The common beginner mistake is to reach for a Python loop to filter rows after fetching them all — pulling a million rows into your program to keep three. Let the database do the filtering; that is the entire point of the WHERE clause, and it is the difference between a query that touches only what it needs and one that drags the whole table across the wire.

Everything below runs on Python's built-in sqlite3 — a real SQL engine in a single file, no server to install — so you can paste each snippet and watch it work.

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.

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)

2 · Grouping & aggregation intermediate

You've almost certainly written this loop. You fetch every incident, then in Python you build a dictionary keyed by service, appending each row to its bucket, and finally walk the buckets to count them and average the minutes. It works — and it hauls the entire table into your process to do arithmetic the database could have done without moving a byte. The instant your question starts with the words "per" or "for each" — incidents per service, revenue per month, errors per user — you want GROUP BY, not a Python loop.

Think of GROUP BY as a sorting machine that drops rows into labelled bins. Every row that shares the same value in the grouping column lands in the same bin — all the api rows in one, all the web rows in another. Once the rows are binned, an aggregate function squeezes each bin down to a single summary number: COUNT(*) tallies how many rows fell in, SUM adds a column up, AVG averages it, MAX/MIN find the extremes. The output is one row per bin — you traded many detailed rows for a few summary rows. That is exactly the Counter/defaultdict grouping pattern from the DSA track, except the engine runs it over data far bigger than memory.

Now the distinction interviewers love and beginners get wrong: WHERE versus HAVING. Both filter, but at different moments. WHERE runs before the rows are binned — it decides which individual rows are even allowed into the grouping. HAVING runs after the bins are formed — it decides which finished groups survive, and it's the only place you can filter on an aggregate. "Only incidents from this month" is a WHERE (a per-row test). "Only services with more than one incident" is a HAVING (a per-group test on COUNT(*)). Try to put WHERE COUNT(*) > 1 and the engine rejects it, because at WHERE time the counts don't exist yet — nothing has been grouped. Get this backwards and you either filter the wrong things or get a confusing error; the fix is always to ask "am I filtering a row or a bucket?"

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.

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.

3 · Joins — combining tables intermediate → advanced

Here's the puzzle that forces you to learn joins. Your incidents table records that service api had an outage — but who owns api? The owning team lives in a different table, owners, because storing the team name on every single incident row would be wasteful and error-prone (rename the team once and you'd have to fix a thousand rows). So the data you need for one report is deliberately scattered across two tables. A join is how you put it back together for the length of a single query.

Why is the data split in the first place? Because of normalization — the discipline of storing each fact exactly once, in one place. The service-to-team mapping belongs in owners; the incident details belong in incidents; they meet on the value they share, the service name, which acts as the key that links a row here to a row there. A join says "for each incident, go find the owners row whose service matches, and glue the columns together." The ON clause is where you write that matching rule — ON o.service = i.service — and getting it wrong is the classic join bug: forget the ON condition and the engine pairs every left row with every right row (a cartesian product), quietly producing a huge, meaningless result.

The one idea that unlocks joins is understanding what happens to rows that don't match — and that is the entire difference between the join types. An INNER JOIN is strict: it returns only the rows that have a partner on both sides. If an incident references a service with no entry in owners, that incident silently vanishes from the result. A LEFT JOIN is generous: it keeps every row from the left table no matter what, and where the right side has no match it fills those columns with NULL (SQL's "there is no value here"). The practical rule: reach for INNER when both sides must exist for the row to make sense, and LEFT when you want a complete list from one table with optional extras — "list all services and their incident count, showing zero where there were none." The costly, invisible mistake is using INNER when you meant LEFT: your report looks fine but has quietly dropped every unmatched row, so the totals are wrong and nothing warns you.

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.

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)

4 · Indexes — why queries are fast or slow advanced

This is the "it worked in testing, it died in production" story of databases. Your query is instant against the 500 rows in your dev database. Ship it; six months later the table has 50 million rows and that same query takes eight seconds, the dashboard times out, and nobody changed a line of SQL. What changed was n — and without an index, every filtered query is O(n): to find the rows where service = 'api', the engine has no choice but to read every single row and check each one. This is a full table scan, and it's the number-one cause of slow queries in the wild.

An index is the fix, and you already know the structure behind it. Picture the index at the back of a textbook: instead of reading all 900 pages to find "mitochondria," you flip to the alphabetical index, land on the word in a couple of hops, and jump straight to the pages. A database index is the same idea made precise — a B-tree (the balanced tree from D4) that keeps one column's values sorted with pointers back to the rows. Because the tree is sorted and balanced, each step down it discards roughly half the remaining candidates, so a lookup that was O(n) becomes O(log n). Concretely: finding a row among a million with a scan is ~1,000,000 checks; with a B-tree index it's about 20. That is the exact array-versus-tree trade-off from the DSA track, now paid out against disk instead of RAM.

So why not slap an index on every column and make everything fast? Because indexes are not free — and this is the part beginners miss. An index is a second sorted copy of the data that the engine must keep in sync: every INSERT, UPDATE, and DELETE now has to update the table and rebalance the tree, so writes get slower and the index eats extra disk. It's the classic space-and-write-time cost buying you read-time speed. The rule that falls out of this: index the columns you actually filter, join, or sort on — the ones in your WHERE, JOIN ... ON, and ORDER BY clauses — and leave the rest alone. And crucially, don't guess whether an index is helping: ask the engine. EXPLAIN QUERY PLAN tells you whether a query does a SCAN (reads everything) or a SEARCH ... USING INDEX (jumps straight in), turning "I think this is fast" into "I can see it's fast."

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.

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")

5 · Transactions & ACID advanced

Imagine the worst possible timing. Your code moves money between two accounts: subtract $150 from Alice, add $150 to Bob. The first UPDATE succeeds — and then the process crashes, the network drops, or an exception fires before the second one runs. Now $150 has simply evaporated: gone from Alice, never arrived at Bob. There is no amount of careful coding that prevents this, because the danger isn't a bug in your logic — it's that two separate writes can be interrupted between them. A transaction is the database's answer: a way to bundle several statements so they all take effect or none do, with no visible in-between.

The shape in code is always the same three beats. BEGIN opens the bundle; from that point your changes are provisional, held to one side rather than written for keeps. COMMIT seals them — the moment it returns, every change in the bundle is permanent, together. If anything goes wrong before the commit, ROLLBACK throws the whole bundle away and the database snaps back to exactly how it looked at BEGIN, as if you'd never started. In Python you wrap the work in try/except: commit on the happy path, roll back in the except. The mistake to avoid is doing multi-step changes with no transaction at all and hoping nothing interrupts them — hope is not an atomicity strategy.

The guarantees a transaction gives you have a name — ACID — and each letter is a promise worth knowing by feel. Atomic: all-or-nothing, the money story above. Consistent: the database moves from one valid state to another, so declared constraints (a balance can't go negative, a foreign key must point somewhere real) still hold after the commit. Isolated: transactions running at the same time don't see each other's half-finished work, so two concurrent transfers can't read a torn, mid-update balance. Durable: once COMMIT returns, the data survives a power cut — it's on disk, not just in memory. For an agent this matters the moment it does more than answer questions: any multi-step change to stored state (deduct a credit and record the usage, create a record and enqueue a job) belongs inside one transaction, ideally paired with the idempotency keys from A6 so a retried request can't double-apply.

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).

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

6 · Connection pooling advanced

Here's a failure that only shows up under load. Your service opens a fresh database connection at the start of each request, runs its query, and closes it. Locally, with you as the only user, it's fine. Then real traffic arrives — a few hundred requests a second — and everything crawls, or the database starts refusing connections entirely. The culprit is that opening a connection is expensive: a TCP handshake, TLS negotiation, authentication, session setup — often tens of milliseconds of pure overhead before a single row is read. Do that per request and you've bolted a costly ceremony onto every query; do it hundreds of times a second and you either drown in that overhead or blow past the database's hard cap on simultaneous connections.

A connection pool fixes both problems with one idea: open a fixed set of connections once, keep them alive, and lend them out. A request borrows a ready-made connection from the pool, does its work, and returns it for the next request to reuse — nobody pays the handshake cost again after startup. The taxi rank is the right picture: riders grab a cab that's already idling and hand it back when done, instead of each buying a new car for one trip. Because the pool has a fixed size, it also does a second, quieter job — it caps how many connections ever hit the database at once, so a traffic spike queues for a free connection instead of stampeding the server into collapse.

The size of the pool is the real design decision, and both extremes hurt. Too small and requests pile up waiting for a free connection even though the database has capacity to spare. Too large and you recreate the original problem — you can overwhelm the database, which has its own connection limit shared across every service talking to it. The honest answer is that a pool trades a little idle memory (connections sitting open, unused, between spikes) for a lot of saved latency and a safety cap — the same "reuse an expensive resource rather than recreate it" pattern as the rate limiter in P6. In practice you almost never hand-roll this: SQLAlchemy, psycopg_pool, or your web framework give you a pool, and your job is to size it and make sure every borrowed connection is reliably returned (a leaked connection that's never given back is a slow, mysterious way to starve the pool).

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.

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

This is the oldest trick in web security, and LLMs just handed it a second life. The bug looks innocent: you build a query by pasting a variable into a string — f"SELECT * FROM users WHERE name = '{name}'". It works for every polite input you test. Then someone types name = "x'; DROP TABLE users;--", and because you glued their text straight into the command, that closing quote ends your string early and the rest becomes runnable SQL. Your query to look up one user now drops the entire table. That's SQL injection: untrusted text smuggled in as code instead of treated as data, and it can read, alter, or destroy anything the connection can reach.

The fix is a single, non-negotiable habit: never build queries by string-formatting; use parameters. You write the query with a placeholder — cur.execute("... WHERE name = ?", (name,)) — and hand the value separately. The database driver then binds that value as pure data, so even if it contains quotes, semicolons, or a full DROP TABLE, it is compared as a literal string and can never break out to become part of the command. The mental model: parameterizing keeps a hard wall between the query's structure (which you wrote) and the values (which the user supplied). One caveat that catches people — placeholders bind values, not identifiers. You can't parameterize a table or column name, so when the user gets to choose one of those, you must validate it against a fixed allowlist of names you control, never interpolate it raw.

Now the LLM twist, and why this section lives in an AI course. When you let a model turn natural language into SQL — "show me last month's failed payments" — the model is effectively an untrusted author of executable code. A prompt-injected or simply confused model can emit DELETE FROM users just as easily as a SELECT, and it does so inside your trusted backend. So text-to-SQL needs defense in depth: validate that the generated query is read-only (it must start with SELECT and contain no write/DDL keywords like INSERT, UPDATE, DELETE, DROP, ALTER), and run it on a connection opened in read-only mode (mode=ro) so that even a query slipping past the validator physically cannot write. Two independent layers, because the whole point is that if one fails the other still holds — the belt-and-suspenders principle you'll see throughout the security material. The mistake is trusting the model's output because it "usually" produces safe SQL; usually is not a security boundary.

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.

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)

🎯 Interview practice interview

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

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;

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

Checkpoint expert

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)

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)

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)

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=?)')]

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

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)

Knowledge check check yourself