SQL essentials for managers: reading queries, checking joins and auditing AI-written SQL

Why a manager reads SQL

Most business systems store their data in tables, and SQL is the language that asks those tables precise questions.

Sales, stock, deliveries and HR records usually live in relational tables. A manager does not need to write SQL fluently, but reading it pays off in four ways. You can check a number before you present it. You can ask precisely: which rows, which filter, counted how. You do not have to wait in an analyst's queue for a simple count. And you can audit what an AI assistant writes, since assistants now produce plausible SQL in seconds.

The last reason shapes the whole session. A query that runs is not necessarily a query that is right: the dangerous errors produce a result that looks reasonable. Learning to read a query, starting from the right clause, is what lets you notice them.

Back to contents.

Keys and grain: how the tables fit

Before counting anything, know what one row of each table stands for and which column links it to the others.

A primary key identifies each row of a table exactly once, such as order_id in the orders table. A foreign key is a column that points to a row of another table, such as the customer identifier written on each order. A join does nothing more than follow that pointer.

The grain of a table is what one row represents. In the Olist case, orders has one row per order and sits at the centre of the map. Three tables can hold several rows for the same order: items, payments and reviews. Those are the tables where joins go wrong. The geolocation table is joined on a zip code prefix that appears on many rows, so it must be reduced to one row per prefix before any join.

The deck flags one trap in the customers table: in this dataset the customer identifier stored on an order identifies the customer record of that order, while the person behind several orders has a separate identifier. Whenever the question is about people, check which column actually names a person.

Back to contents.

Anatomy of a query, and the order it really runs in

A query is a pivot table written down as text: pick the table, filter rows, group them, measure, sort.

Every choice made with clicks in a pivot table has a name in SQL. The source data is FROM, the filter is WHERE, the rows of the pivot are GROUP BY, the values are aggregates such as COUNT, SUM or AVG inside SELECT, and the sort is ORDER BY. The difference is that SQL keeps each choice as text, which a colleague can rerun next month and read line by line, whereas a pivot built with clicks leaves no trace to audit.

A query has up to six clauses, always written in the same order. Only SELECT and FROM are compulsory. Text values go in single quotes; in double quotes, DuckDB looks for a column with that name. Here is an example in the same spirit as the demos, written for this summary.

SELECT s.seller_state,
       COUNT(*) AS n_items,
       ROUND(AVG(i.freight_value), 2) AS avg_freight
FROM order_items AS i
JOIN sellers AS s ON s.seller_id = i.seller_id
WHERE i.price >= 100
GROUP BY s.seller_state
ORDER BY avg_freight DESC
LIMIT 5;

How to read it: in the order the database works, not the order it is written

1. FROM and the joins. Take order items and attach to each the seller it came from. This decides which rows exist.
2. WHERE. Keep only the items priced at 100 or more.
3. GROUP BY. Form one group per seller state.
4. SELECT. In each group, count the rows and average the freight.
5. ORDER BY, then LIMIT. Sort by that average, largest first, and keep five rows.

This logical order is the deck's main reading principle: to understand someone else's query, and above all one written by an assistant, start at FROM and the joins, never at SELECT. The engine may organise the actual work differently, but the result behaves as if it followed this order.

The live demos then answer three questions of the case: orders by status, late deliveries among orders placed in 2017, and orders by customer state. The second one rewrites the late-delivery rule of the first session with CASE WHEN, which writes a label on each row, and EXTRACT, which takes the year out of a date. It filters on both the status and the presence of a delivery date, because the two are separate columns and nothing guarantees that they agree. The third is a first join: the state lives in customers, not in orders.

Back to contents.

INNER JOIN and LEFT JOIN: the question decides

An inner join keeps only rows that find a partner; a left join holds on to all rows of the first table, partner or not.

A plain JOIN is an INNER JOIN: a row survives only if it finds a match on both sides. A LEFT JOIN retains all rows of the first table, the one named in FROM, and fills the other table's columns with NULL when no match exists. Neither is better in general. An average score over reviewed orders is fine with an inner join; the proportion of orders with at least one review needs a left join, or the unreviewed orders never reach the denominator.

A second, subtler way to lose rows concerns filters. After a left join, a condition on the right-hand table placed in WHERE is tested on every row, an empty value fails every comparison, and the rows without a partner disappear: the left join has quietly become an inner join. Placed in ON, the same condition only decides which partner rows are attached, and every row of the first table stays in the result.

Back to contents.

When a join multiplies rows

Joining two tables that each have several rows per order pairs every row with every other, and sums quietly inflate.

Take one order with two items and two payments, and join items to payments on the order identifier. The engine pairs each item with each payment, so the order now occupies four rows and every payment appears twice. Any sum of payments over those rows is too high, and nothing warns you.

The error is hard to see because most orders have one item and one payment, so only part of the total is inflated. The result is wrong without being absurd, which is exactly why such numbers end up in reports.

Count before, count after

The cheapest check of the session takes ten seconds. In the orders table, the number of rows equals the number of distinct orders. Run the same two counts after the join and compare.

SELECT COUNT(*) AS n_rows,
       COUNT(DISTINCT o.order_id) AS n_orders
FROM orders AS o
JOIN payments AS p ON p.order_id = o.order_id;

Both alarms can ring in the same join. The fix, when two many-sided tables are needed together, is to summarise, then join: first collapse each table so that every order appears once, and only then join these summaries, which pair up exactly. WITH gives a name to each small query so it can be used like a table. A manager need not write this fluently, but should recognise it, and ask for it when items and payments are joined raw.

WITH per_order_items AS (
  SELECT order_id, COUNT(*) AS items
  FROM order_items
  GROUP BY order_id
), per_order_score AS (
  SELECT order_id, AVG(review_score) AS score
  FROM reviews
  GROUP BY order_id
)
SELECT i.items, COUNT(*) AS n_orders, ROUND(AVG(s.score), 2) AS avg_score
FROM per_order_items AS i
JOIN per_order_score AS s ON s.order_id = i.order_id
GROUP BY i.items
ORDER BY i.items;

Back to contents.

Auditing SQL written by an AI assistant

Errors that matter rarely stop a query from running, so a result from an assistant must be checked, not just executed.

The last practical part shows three short queries of the kind an assistant writes from a loose request, each hiding one classic error. In pairs, students say which line is wrong and whether the error pushes the result up or down; deciding the direction forces them to understand the error rather than merely spot it. The deck then generalises the lesson into four checks, to run in this order.

Four checks before trusting a query

1. Grain. What is one row before and after each join? Compare the row count with the count of distinct keys.
2. Missing values and filters. Look at empty dates, the status of the records and the period covered; give missing values a label of their own so you can see how many there are.
3. A total you already know. Recompute one total with as few tables as possible; if the complex query disagrees, stop and find out why.
4. Make it explain. Ask the assistant to explain each line and state its assumptions, then verify the claims in the data, since an explanation is not a proof.

The session closes by asking each group to add one finding to its brief that a query answers better than a pivot table, stated in one sentence with its number and scope, and to keep the query and its row counts, because someone will ask how the number was obtained.

Back to contents.

Key takeaways

1. Read a query from FROM and its joins: they fix the set of rows before any counting happens.
2. Ask what one row is before counting: an item is not an order, and a record is not necessarily a person.
3. Count rows and distinct keys before and after every join; more rows means inflated sums, fewer keys means lost records.
4. Summarise many-sided tables to one row per key before joining them together.
5. With a LEFT JOIN, put conditions on the right-hand table in ON, not in WHERE.
6. A query that runs is not yet a query that is right: check AI-written SQL against a total you already know.

Glossary

Primary key

Column whose value identifies each row of a table exactly once.

Foreign key

Column that points to a row of another table, the path a join follows.

Grain

What one row of a table, or of a result, represents: an order, an item, a payment.

Query

A question to a database written as text in SQL, which anyone can rerun and read.

Aggregate

A function such as COUNT, SUM or AVG that turns the rows of a group into one value.

INNER JOIN

A join that keeps only the rows with a match on both sides.

LEFT JOIN

A join that retains all rows of the first table and fills missing matches with NULL.

NULL

A missing value; any comparison with it is unknown rather than true or false.

Fan-out

The multiplication of rows when a join meets several matching rows on each side.

CTE (WITH)

A named sub-query declared with WITH and then used like a table.

Pivot table

Spreadsheet summary built with clicks; SQL writes the same choices down as text.

The other notes of the course

One public summary per deck, in the order of the course.

Previous: Data visualisation and storytellingNext: Predictive analytics for decision-makers

1. Session 1From data to decisions
2. Session 2Data visualisation and storytelling
3. Session 3SQL essentials for managers
4. Session 4Predictive analytics for decision-makers
5. Session 5Generative AI, GDPR and the AI Act
6. Session 6Pitching data insights

All the notes, as a reading list

About these notes

Written by the lecturer from the slides of the deck, as a public summary: the key ideas, the formulas and one worked example. The exercises, their solutions, the tests and the grading stay in the deck, for the students of the course.

Revision: 16 October 2026.