SQL — the basics

SQL is a language in which you describe what you want, not how it should be fetched. The database works out how.

SELECT class_name, avg(score) AS mean
FROM   results
WHERE  score IS NOT NULL
GROUP BY class_name
HAVING count(*) >= 5
ORDER BY mean DESC
LIMIT 10;

Read it top to bottom, but think in the order the database runs it:

Order	Keyword	Does
1	FROM / JOIN	which tables
2	WHERE	filter rows
3	GROUP BY	collapse into groups
4	HAVING	filter groups
5	SELECT	pick the columns
6	ORDER BY	sort
7	LIMIT	limit

That order explains the most common beginner's question: why can I not use WHERE count(*) > 5? Because WHERE runs before the groups exist. Groups are filtered with HAVING.

The JOIN types:

Type	Keeps
INNER JOIN	only rows that match in both
LEFT JOIN	everything from the left one, NULL where the right one is missing
RIGHT JOIN	the other way round
FULL OUTER JOIN	everything from both

Use LEFT JOIN when you want to keep everyone in the left table — for instance every pupil, including those who have not taken a test. An INNER JOIN would quietly throw them away, and that is easy not to notice.

NULL behaves differently from what you think. NULL means «unknown», not «empty»:

Expression	Result
NULL = NULL	NULL (not true!)
NULL <> 5	NULL
x IS NULL	true/false — the right way
count(*)	counts every row
count(column)	counts the rows where the column is not NULL
avg(column)	skips NULL

The difference between count(*) and count(column) is a classic source of wrong numbers in reports.

SQL injection. Never build queries with string interpolation:

cur.execute(f"SELECT * FROM pupil WHERE name = '{name}'")    # NEVER
cur.execute("SELECT * FROM pupil WHERE name = %s", (name,))  # right

With parameters the value is sent separately from the query, and the database can never interpret it as code. It is not an optimisation but the only correct method — and it holds even when the value «comes from inside the system», since it rarely stays that way.

When SQL and when Pandas? Filter and aggregate in the database — it is built for that and you avoid moving the data. Fetch home what is left and do the last part in Pandas. Fetching a million rows in order to compute one mean is nearly always the wrong route.

-- The tables
CREATE TABLE pupil (id int primary key, name text, class_name text);
CREATE TABLE test (id int primary key, pupil_id int references pupil(id),
                   subject text, score int, taken_on date);

-- Every pupil with their average — including those without tests (LEFT JOIN)
SELECT p.name,
       p.class_name,
       count(t.id)        AS n_tests,
       round(avg(t.score), 1) AS mean
FROM   pupil p
LEFT JOIN test t ON t.pupil_id = p.id
GROUP BY p.id, p.name, p.class_name
ORDER BY mean DESC NULLS LAST;

-- Classes with at least 5 tests and a mean above 70
SELECT p.class_name, count(*) AS n, round(avg(t.score), 1) AS mean
FROM   test t JOIN pupil p ON p.id = t.pupil_id
WHERE  t.score IS NOT NULL          -- filters ROWS
GROUP BY p.class_name
HAVING count(*) >= 5 AND avg(t.score) > 70   -- filters GROUPS
ORDER BY mean DESC;

-- count(*) against count(column): different answers when there are NULLs
SELECT count(*) AS all_rows, count(score) AS with_score FROM test;

# From Python — always with parameters
import psycopg

with psycopg.connect(dsn) as conn, conn.cursor() as cur:
    cur.execute(
        """SELECT p.class_name, round(avg(t.score), 1)
           FROM test t JOIN pupil p ON p.id = t.pupil_id
           WHERE t.taken_on >= %s
           GROUP BY p.class_name
           HAVING count(*) >= %s""",
        ("2026-01-01", 5),
    )
    for class_name, mean in cur.fetchall():
        print(class_name, mean)