SQL & Databases · lesson 05/13

🗃️SQL core

⏳ ~15 min

Everything below runs against the practice dataset in this app. Click a table name to peek at its rows, and use the scratchpad at the bottom to try any query.

🗄️ Practice databaseclick a table to preview it

id, name, city, signup_date

id, name, category, price

id, customer_id, order_date, status

order_id, product_id, quantity, unit_price

user_id, event_type, ts

id, name, manager_id, dept, salary

user_id, login_date

customer_id, email, updated_at

Logical order of execution

FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT (window functions run here) → DISTINCT → ORDER BY → LIMIT
This explains most errors. You can't use a SELECT alias in WHERE (it doesn't exist yet), but you can in ORDER BY. You can't filter on a window function in WHERE, so wrap it in a CTE. Aggregates go in HAVING, not WHERE.

JOINs: pick the right one

Join	Keeps	Use for
`INNER JOIN`	Only matching pairs	Enrich facts with dimensions
`LEFT JOIN`	All left rows; NULLs if there's no match	"Including those with zero…", anti-joins
`FULL OUTER JOIN`	Everything from both sides	Reconciling two sources (missing on either side). Not in MySQL: use LEFT ∪ RIGHT
`CROSS JOIN`	Cartesian product	Build a calendar × users grid, then LEFT JOIN facts to fill zeros
Self join	Table joined to itself	Employee → manager, consecutive events

-- Anti-join: customers with no orders
SELECT c.* FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

-- Semi-join: customers with at least one completed order (no duplicates!)
SELECT c.* FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.status = 'completed');

The LEFT JOIN + WHERE trap: LEFT JOIN orders o ON … WHERE o.status='completed' silently turns it back into an INNER JOIN, because unmatched rows have o.status = NULL. Put the condition in the ON clause if you want to keep all left rows.

Fan-out: joining orders → order_items and then COUNT(*) counts items, not orders. Use COUNT(DISTINCT o.id), or aggregate before joining.

GROUP BY, HAVING, aggregates

SELECT c.city,
       COUNT(DISTINCT o.id)                       AS orders,
       SUM(oi.quantity * oi.unit_price)           AS revenue,
       ROUND(AVG(oi.unit_price), 2)               AS avg_price,
       SUM(CASE WHEN o.status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled   -- conditional aggregation
FROM customers c
JOIN orders o       ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
GROUP BY c.city
HAVING COUNT(DISTINCT o.id) >= 2
ORDER BY revenue DESC;

NULL: three-valued logic

NULL = NULL is UNKNOWN, not TRUE. Use IS NULL, or IS NOT DISTINCT FROM (Postgres) / <=> (MySQL) / IS (SQLite).
Aggregates ignore NULLs: AVG over (10, NULL) = 10, not 5. COUNT(col) skips NULLs.
SUM of zero rows is NULL, so wrap it: COALESCE(SUM(x), 0).
NOT IN (subquery with a NULL) returns nothing. Prefer NOT EXISTS.
Division: use NULLIF(denominator, 0) to avoid division-by-zero errors.
GROUP BY puts all NULLs into one group. ORDER BY places NULLs first in MySQL/SQLite (ASC) and last in Postgres.

CASE, COALESCE, string & date functions (dialects!)

Task	MySQL 8	PostgreSQL	SQLite
Month bucket	`DATE_FORMAT(d,'%Y-%m')`	`to_char(d,'YYYY-MM')` / `date_trunc('month',d)`	`strftime('%Y-%m',d)`
Days between	`DATEDIFF(a,b)`	`a::date - b::date`	`julianday(a)-julianday(b)`
Add interval	`d + INTERVAL 7 DAY`	`d + INTERVAL '7 days'`	`date(d,'+7 days')`
Seconds between	`TIMESTAMPDIFF(SECOND,a,b)`	`EXTRACT(EPOCH FROM b-a)`	`(julianday(b)-julianday(a))*86400`
Concat	`CONCAT(a,b)`	`a \|\| b`	`a \|\| b`
Group concat	`GROUP_CONCAT(x ORDER BY x SEPARATOR ',')`	`string_agg(x, ',' ORDER BY x)`	`group_concat(x, ',')`
Integer division	`5/2 = 2.5000`, `5 DIV 2 = 2`	`5/2 = 2` (!)	`5/2 = 2` (!)
Top N	`LIMIT n`	`LIMIT n` / `FETCH FIRST n ROWS`	`LIMIT n`

To get decimal division everywhere, multiply by 1.0 first: 100.0 * a / b. ROUND(x, 2) works in all three.

Subqueries vs CTEs

-- Customers whose total spend is above the average customer's spend
WITH spend AS (
  SELECT o.customer_id, SUM(oi.quantity * oi.unit_price) AS total
  FROM orders o JOIN order_items oi ON oi.order_id = o.id
  WHERE o.status = 'completed'
  GROUP BY o.customer_id
)
SELECT c.name, s.total
FROM spend s JOIN customers c ON c.id = s.customer_id
WHERE s.total > (SELECT AVG(total) FROM spend)       -- scalar subquery
ORDER BY s.total DESC;

CTEs make step-by-step logic readable. Name each step (base → enriched → ranked → final) and debug by SELECT * FROM each step.

Set operations

SELECT customer_id FROM orders WHERE status = 'completed'
INTERSECT                                  -- customers with both completed AND cancelled orders
SELECT customer_id FROM orders WHERE status = 'cancelled';
-- UNION (dedups, slower) vs UNION ALL (keeps dups, fast). EXCEPT = anti-join. MySQL 8.0.31+ supports INTERSECT/EXCEPT.

MySQL 8 traps (Quera's usual SQL engine)

Quera's earlier database contests (the CodeCup data track) used MySQL, so assume MySQL until the statement says otherwise. Practise here in SQLite, but know where MySQL behaves differently:

Trap	What happens in MySQL	Write instead
`'a' \|\| 'b'`	Logical OR, returns `0`	`CONCAT('a', 'b')`
`WHERE name = 'sara'`	Matches 'Sara' and 'SARA' (default collation is case-insensitive)	`WHERE BINARY name = 'sara'` or `COLLATE utf8mb4_bin` if case matters
`"text"` in double quotes	A string, not an identifier (unless ANSI_QUOTES)	Single quotes for strings, backticks for names: `order`
`FULL OUTER JOIN`	Not supported	`LEFT JOIN … UNION … RIGHT JOIN …`
Selecting a column not in GROUP BY	Error under `ONLY_FULL_GROUP_BY` (default)	Group by it, aggregate it (`MAX(name)`), or use `ANY_VALUE()`
`5 / 2`	`2.5000` (decimal division, unlike SQLite/Postgres)	`5 DIV 2` for integer division
`IFNULL` / `IF(c, a, b)`	MySQL-only shortcuts	`COALESCE` / `CASE` work everywhere
`LIMIT 5, 10`	Means OFFSET 5, then 10 rows	`LIMIT 10 OFFSET 5` is clearer
Window function alias in WHERE	Error (same as everywhere)	Wrap in a CTE, filter outside
Reserved words as names	`rank`, `order`, `group`, `key`, `rows` are reserved	Alias as `rnk`, `rn`, or quote with backticks

-- MySQL 8 versions of the date idioms used in the labs
DATE_FORMAT(order_date, '%Y-%m')                     -- month bucket   (SQLite: strftime('%Y-%m', d))
DATEDIFF(b, a)                                       -- days a → b     (SQLite: julianday(b) - julianday(a))
TIMESTAMPDIFF(MINUTE, prev_ts, ts) > 30              -- gap in minutes (SQLite: (julianday(ts) - julianday(prev_ts)) * 1440)
order_date - INTERVAL 1 MONTH                        -- shift          (SQLite: date(d, '-1 month'))
DATE_SUB(login_date, INTERVAL rn DAY)                -- islands key    (SQLite: julianday(d) - rn)
WITH RECURSIVE days AS (SELECT DATE('2026-05-01') AS d UNION ALL
                        SELECT d + INTERVAL 1 DAY FROM days WHERE d < '2026-05-07') SELECT * FROM days;

If the contest allows it, test MySQL queries locally with Docker: docker run --rm -e MYSQL_ROOT_PASSWORD=x -p 3306:3306 mysql:8. Otherwise stick to the portable subset: CASE, COALESCE, NULLIF, CTEs and window functions behave the same in MySQL 8, PostgreSQL and SQLite.

Logical order of execution

JOINs: pick the right one

GROUP BY, HAVING, aggregates

NULL: three-valued logic

CASE, COALESCE, string & date functions (dialects!)

Subqueries vs CTEs

Set operations

MySQL 8 traps (Quera's usual SQL engine)

Scratchpad