SQL queries: filter, join and group

Engineering Toolkit, lesson 8

Ask a database questions with SELECT, WHERE, JOIN and GROUP BY.

Some Python, and "Pipes, variables and scripts" for filtering and counting rows. SQLite comes with Python, so nothing needs installing.

Part 1. Tables, rows and SELECT

A table is like a DataFrame with fixed columns: every row has the same columns, each with a type. A database holds many tables and answers questions about them in SQL. SQLite keeps a whole database in one file and comes with Python, so you can try everything here with no server.

Here's a tiny shop: five customers, and seven orders. Run this once, and it saves shop.db next to it; the other blocks in this lesson open that file.

Making a small shop database, in Python

import os
import sqlite3

if os.path.exists("shop.db"):
    os.remove("shop.db")
db = sqlite3.connect("shop.db")
db.executescript("""
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL, country TEXT);
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers (id),
    placed_on TEXT NOT NULL,
    total REAL NOT NULL
);
INSERT INTO customers VALUES (1, 'Amara', 'Kenya'), (2, 'Bruno', 'Brazil'),
    (3, 'Chen', 'Singapore'), (4, 'Dana', 'Canada'), (5, 'Emil', 'Germany');
INSERT INTO orders VALUES (101, 1, '2026-10-01', 40.0), (102, 2, '2026-10-02', 120.0),
    (103, 1, '2026-10-05', 25.5), (104, 3, '2026-10-06', 80.0), (105, 2, '2026-10-09', 60.0),
    (106, 4, '2026-10-12', 15.0), (107, 1, '2026-10-20', 99.0);
""")
db.commit()
for table in ("customers", "orders"):
    print(table, db.execute(f"SELECT COUNT(*) FROM {table}").fetchone()[0])
# customers 5
# orders 7

Primary key

A column whose value is different in every row, so it names exactly one row, like the id of a customer. The database refuses a second row with the same key.

A query names the columns you want, FROM which table, and WHERE filters the rows. ORDER BY sorts, DESC means largest first, and LIMIT keeps the first few.

The three biggest orders over 50, in SQL

import sqlite3

db = sqlite3.connect("shop.db")
query = """
SELECT id, placed_on, total
FROM orders
WHERE total > 50
ORDER BY total DESC
LIMIT 3
"""
for row in db.execute(query):
    print(row)
# (102, '2026-10-02', 120.0)
# (107, '2026-10-20', 99.0)
# (104, '2026-10-06', 80.0)

WHERE can combine tests with AND, OR and NOT, and that's where the first classic mistake lives. You want orders from customer 1 or 2 that are over 100, and write the line below without brackets.

Your turn

WHERE customer_id = 1 OR customer_id = 2 AND total > 100 on these seven orders. How many rows come back?

AND is worked out before OR, so this reads "customer 1, or (customer 2 and over 100)". All three of Amara's orders come back, whatever their size, plus Bruno's one over 100: four rows. Brackets around the OR give the one row you meant.

Show the answer

D. AND is worked out before OR, so this reads "customer 1, or (customer 2 and over 100)". All three of Amara's orders come back, whatever their size, plus Bruno's one over 100: four rows. Brackets around the OR give the one row you meant.

The same filter with and without brackets

import sqlite3

db = sqlite3.connect("shop.db")
loose = "SELECT id FROM orders WHERE customer_id = 1 OR customer_id = 2 AND total > 100"
tight = "SELECT id FROM orders WHERE (customer_id = 1 OR customer_id = 2) AND total > 100"
print([row[0] for row in db.execute(loose)])
print([row[0] for row in db.execute(tight)])
# [101, 102, 103, 107]
# [102]

Part 2. JOIN: rows from two tables at once

Keep going with Pro

You've done the first part. Pro opens the rest of this lesson, and every other one.

Still to come

JOIN: rows from two tables at once
GROUP BY: one row per group

See Pro plans I have Pro: sign in

Next lessonWindow functions, indexes and safe queriesRunning totals and ranks, why a query is slow, and SQL from Python. Start lesson 9

Engineering Toolkit overviewSpotted a mistake? Tell us.

08 · SQL queries: filter, join and group