SQL for AI people

Watch the full flow once before you try it. Python ships with the sqlite3 module built in, so this needs no install, no server, and no internet — one script builds a real database file on your laptop.

Step 1 — build the database. Three kirana stores and eight orders, with two traps planted on purpose: order O05 has a NULL status, and order O08 points at store S9, which is not in the stores table:

import sqlite3

con = sqlite3.connect("kirana.db")
cur = con.cursor()
cur.executescript("""
DROP TABLE IF EXISTS stores;
DROP TABLE IF EXISTS orders;
CREATE TABLE stores (store_id TEXT PRIMARY KEY, name TEXT, city TEXT);
CREATE TABLE orders (order_id TEXT, store_id TEXT, amount REAL, status TEXT);
INSERT INTO stores VALUES
  ('S1','Annapoorna Stores','Coimbatore'),
  ('S2','Sri Balaji Kirana','Erode'),
  ('S3','Lakshmi Provisions','Salem');
INSERT INTO orders VALUES
  ('O01','S1',250,'PAID'), ('O02','S1',120,'PAID'),
  ('O03','S2',480,'FAILED'), ('O04','S2',90,'PAID'),
  ('O05','S3',310,NULL),    ('O06','S1',60,'FAILED'),
  ('O07','S2',700,'PAID'),  ('O08','S9',150,'PAID');
""")
con.commit()
print(cur.execute("SELECT COUNT(*) FROM orders").fetchone())

The script prints (8,) — eight rows landed. executescript ran the whole block of SQL statements, commit saved them to the kirana.db file, and the last line is your first query: count the rows. Every result comes back as a Python tuple, one per row. That is the whole bridge between Python and SQL — send text in, get tuples back.

Step 2 — the four-slot query. The three biggest successful orders:

rows = cur.execute("""
    SELECT order_id, amount
    FROM orders
    WHERE status = 'PAID'
    ORDER BY amount DESC
    LIMIT 3
""").fetchall()
for r in rows:
    print(r)

Output: ('O07', 700.0), ('O01', 250.0), ('O08', 150.0). Read the query top to bottom: take these two columns, from this table, keep only PAID rows, sort by amount largest-first, return three. The database scanned all eight rows but shipped back only three — with forty lakh rows the shape of the answer stays exactly this small. Note what SELECT named is what came back; no SELECT * grabbing columns nobody asked for.

Step 3 — GROUP BY, and the pandas mirror. One report line per store, both ways:

sql_report = cur.execute("""
    SELECT store_id, COUNT(*) AS orders, SUM(amount) AS total
    FROM orders
    GROUP BY store_id
    HAVING SUM(amount) > 300
""").fetchall()
print(sql_report)   # [('S1', 3, 430.0), ('S2', 3, 1270.0), ('S3', 1, 310.0)]

# the same report in pandas, from module 0.5:
# df.groupby("store_id").agg(orders=("order_id", "count"),
#                            total=("amount", "sum"))
# then: g[g["total"] > 300]

Line up the two halves and the mapping is one-to-one: GROUP BY store_id is groupby("store_id"), COUNT(*) and SUM(amount) are the agg pairs, and HAVING is the filter you apply to the finished groups. Store S9 (total 150) was grouped but then dropped by HAVING. If the condition had been on raw rows — say only PAID orders — it would go in WHERE instead, before the split. WHERE trims rows going in; HAVING trims groups coming out.

Step 4 — JOIN, inner versus left. Give every order its store name and city:

inner = cur.execute("""
    SELECT o.order_id, o.amount, s.name, s.city
    FROM orders o
    JOIN stores s ON o.store_id = s.store_id
""").fetchall()
left = cur.execute("""
    SELECT o.order_id, o.amount, s.name, s.city
    FROM orders o
    LEFT JOIN stores s ON o.store_id = s.store_id
""").fetchall()
print(len(inner), len(left))   # 7 8
print([r for r in left if r[2] is None])
# [('O08', 150.0, None, None)]

Eight orders went in; the inner join returned seven. Order O08 points at store S9, which the stores table does not have, so the inner join dropped it without a word. The left join kept all eight and filled the missing name and city with NULL — Python shows them as None. That last line is the auditor's move: after any left join, hunt for the NULL rows, because each one is a key that failed to match. Print row counts before and after every join, every time.

Step 5 — NULL, the quiet liar. Three counts that look like they should agree:

print(cur.execute(
    "SELECT COUNT(*), COUNT(status) FROM orders").fetchone())        # (8, 7)
print(cur.execute(
    "SELECT COUNT(*) FROM orders WHERE status != 'FAILED'").fetchone())   # (5,)
print(cur.execute("""
    SELECT COUNT(*) FROM orders
    WHERE status != 'FAILED' OR status IS NULL""").fetchone())       # (6,)
con.close()

COUNT(*) counts rows; COUNT(status) counts only rows where status is not NULL — that gap of one is O05. Then the filter: eight orders, two FAILED, yet != 'FAILED' returns five, not six. NULL compared to anything is neither true nor false, so WHERE quietly drops O05 as well. The only honest tests for missing values are IS NULL and IS NOT NULL, and the third query uses one to recover the lost row. This exact silence is WR-017 — meet it here first, cheaply.