SQL Cheat Sheet: The 16 Essential Topics

Order in which a SQL query is written

The clauses of a query are always written in this order (only SELECT and FROM are required):

SELECT … FROM … JOIN … ON … WHERE … GROUP BY … HAVING … ORDER BY … LIMIT …

SELECT, aliases, DISTINCT

What do SELECT, AS and DISTINCT do in SQL?

SELECT picks the columns to show; AS renames a column, DISTINCT removes duplicates.

Tip: List the columns you need rather than SELECT *: the result is easier to read and faster.

Example query

SELECT DISTINCT department AS dept
FROM staff;

Practice: SQL exercises on SELECT, aliases, DISTINCT (4 questions) · see 4 exercises with solutions

WHERE (filters)

What does WHERE do in SQL?

WHERE keeps the rows that match a condition: =, <>, >, BETWEEN, IN, LIKE, AND, OR, NOT.

Tip: Text and dates go between single quotes: department = 'IT'.

Example query

SELECT name
FROM staff
WHERE salary > 40000 AND department IN ('IT', 'HR');

Practice: SQL exercises on WHERE (filters) (31 questions) · see 15 exercises with solutions

ORDER BY, LIMIT

How do you sort and limit a SQL result with ORDER BY and LIMIT?

ORDER BY sorts (ASC ascending, DESC descending); LIMIT keeps the first n rows.

Tip: Add a second sort column to break ties.

Example query

SELECT name, salary
FROM staff
ORDER BY salary DESC, name
LIMIT 3;

Practice: SQL exercises on ORDER BY, LIMIT (7 questions) · see 6 exercises with solutions

COUNT, SUM, AVG, MIN, MAX

What do COUNT, SUM, AVG, MIN and MAX do in SQL?

Aggregates sum up many rows into one value: COUNT, SUM, AVG, MIN, MAX.

Tip: COUNT(*) counts rows; COUNT(column) skips NULL values.

Example query

SELECT COUNT(*), ROUND(AVG(salary), 1), MAX(salary)
FROM staff;

Practice: SQL exercises on COUNT, SUM, AVG, MIN, MAX (7 questions) · see 3 exercises with solutions

GROUP BY

What does GROUP BY do in SQL?

GROUP BY computes one summary per group: one result row per value of the column.

Tip: Every column shown without an aggregate must appear in the GROUP BY.

Example query

SELECT department, COUNT(*)
FROM staff
GROUP BY department;

Practice: SQL exercises on GROUP BY (27 questions) · see 15 exercises with solutions

JOIN

What is a JOIN in SQL?

JOIN links two tables through a shared column; LEFT JOIN also keeps the rows with no match.

Tip: Give each table a short alias (c, o) and always write the ON condition.

Example query

SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;

Types of join

• INNER JOIN (or just JOIN): keeps only the rows that have a match in both tables.
• LEFT JOIN: keeps every row of the left table; the right-hand columns are NULL when there is no match.
• RIGHT JOIN and FULL JOIN: the reverse of LEFT JOIN, and the union of both. SQLite only supports them since version 3.39; a LEFT JOIN with the tables swapped gives the same result as a RIGHT JOIN.
• CROSS JOIN: every combination of rows from both tables (Cartesian product), with no ON condition.

To find the rows without a match, such as customers who never ordered, combine LEFT JOIN with IS NULL:

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

Practice: SQL exercises on JOIN (60 questions) · see 15 exercises with solutions

HAVING

What is the difference between HAVING and WHERE in SQL?

HAVING filters groups after GROUP BY (WHERE filters rows before).

Tip: A condition on COUNT, SUM or AVG goes in HAVING, never in WHERE.

Example query

SELECT department, AVG(salary)
FROM staff
GROUP BY department
HAVING COUNT(*) >= 3;

WHERE or HAVING: the order of execution

A query does not run in the order it is written: FROM and JOIN first, then WHERE, GROUP BY, HAVING, SELECT, ORDER BY and finally LIMIT. WHERE therefore works before the groups are formed, on each row; HAVING works afterwards, on the groups and their aggregates. That is also why, in standard SQL, an alias defined in SELECT cannot be used in WHERE (SQLite allows it, but most other databases reject it).

The two work together: WHERE first drops rows, then HAVING filters the resulting groups.

SELECT department, COUNT(*)
FROM staff
WHERE salary > 30000
GROUP BY department
HAVING COUNT(*) >= 3;

Practice: SQL exercises on HAVING (20 questions) · see 12 exercises with solutions

Subqueries

What is a SQL subquery?

A subquery is a query inside a query: a value, a list (IN) or a table.

Tip: Write the subquery on its own first to check its result, then plug it in.

Example query

SELECT name
FROM staff
WHERE salary > (SELECT AVG(salary) FROM staff);

Practice: SQL exercises on Subqueries (34 questions) · see 15 exercises with solutions

CASE

What does CASE do in SQL?

CASE picks a value depending on conditions, row by row.

Tip: Conditions are tested in order: the first true one wins.

Example query

SELECT name, CASE WHEN salary >= 45000 THEN 'high' ELSE 'standard' END AS band
FROM staff;

Practice: SQL exercises on CASE (25 questions) · see 15 exercises with solutions

NULL, COALESCE

How do you handle NULL values in SQL?

NULL = missing value: test it with IS NULL / IS NOT NULL; COALESCE replaces it.

Tip: col = NULL never works: write col IS NULL.

Example query

SELECT name, COALESCE(city, 'unknown')
FROM contacts
WHERE email IS NULL;

Practice: SQL exercises on NULL, COALESCE (11 questions) · see 14 exercises with solutions

UNION, INTERSECT, EXCEPT

What do UNION, INTERSECT and EXCEPT do in SQL?

UNION merges two results (no duplicates), INTERSECT keeps what they share, EXCEPT removes the second from the first.

Tip: Both queries must return the same number of columns.

Example query

SELECT member
FROM chess
INTERSECT SELECT member
FROM music;

Practice: SQL exercises on UNION, INTERSECT, EXCEPT (13 questions) · see 11 exercises with solutions

Text and dates

How do you work with text and dates in SQL?

Text and dates: UPPER, LOWER, LENGTH, SUBSTR, ||, LIKE, strftime, julianday, date.

Tip: In LIKE, % stands for any run of characters and _ for a single character.

Example query

SELECT UPPER(name), strftime('%Y', hired)
FROM staff
WHERE name LIKE 'A%';

Practice: SQL exercises on Text and dates (24 questions) · see 15 exercises with solutions

CTEs (WITH)

What is a CTE (WITH) in SQL?

WITH names an intermediate query, which you then reuse like a table.

Tip: Several CTEs can follow each other, separated by commas, before the final SELECT.

Example query

WITH d AS (
  SELECT department, AVG(salary) AS m
  FROM staff
  GROUP BY department)
SELECT *
FROM d
WHERE m > 40000;

Practice: SQL exercises on CTEs (WITH) (31 questions) · see 15 exercises with solutions

EXISTS

What does EXISTS do in SQL?

EXISTS is true when the subquery returns at least one row; NOT EXISTS, when it returns none.

Tip: NOT EXISTS is safer than NOT IN when the subquery may contain NULLs.

Example query

SELECT c.name
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

Practice: SQL exercises on EXISTS (22 questions) · see 15 exercises with solutions

Window functions (OVER)

What is a window function (OVER) in SQL?

Window functions compute over a set of rows without grouping them: ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, SUM() OVER…

Tip: PARTITION BY splits the groups, ORDER BY sets the order inside each group.

Example query

SELECT name, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC)
FROM staff;

ROW_NUMBER, RANK or DENSE_RANK?

All three number the rows following the window’s ORDER BY; they only differ on ties. For salaries of 50,000, 45,000, 45,000 and 40,000:

• ROW_NUMBER: 1, 2, 3, 4 (a unique number, ties are broken arbitrarily);
• RANK: 1, 2, 2, 4 (same rank for ties, then a gap);
• DENSE_RANK: 1, 2, 2, 3 (same rank, no gap).

LAG and LEAD read the value from the previous or next row; SUM() OVER (ORDER BY …) computes a running total. A window function cannot appear in WHERE: to keep the first row of each group, compute the rank in a CTE, then filter on it.

WITH r AS (
  SELECT name, department,
         ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS n
  FROM staff
)
SELECT name, department FROM r WHERE n = 1;

Practice: SQL exercises on Window functions (OVER) (111 questions) · see 15 exercises with solutions

Recursive CTEs

What is a recursive CTE in SQL?

A recursive CTE calls itself to generate a sequence or walk a hierarchy. Always add a stop condition.

Tip: A starting part, UNION ALL, then the part that calls itself with a WHERE that stops.

Example query

WITH RECURSIVE n(x) AS (
  SELECT 1
  UNION ALL SELECT x + 1
  FROM n
  WHERE x < 5)
SELECT x
FROM n;

Practice: SQL exercises on Recursive CTEs (18 questions) · see 11 exercises with solutions

SQLite, PostgreSQL, MySQL: the differences to know

What are the differences between SQLite, PostgreSQL and MySQL?

The queries in this cheat sheet are written almost the same way in all three databases. The gaps are mostly about dates, joining text, division and a few joins. Here are the ones that most often catch people out when moving from SpeedQL (SQLite) to another database.

Tip: SELECT, WHERE, JOIN, GROUP BY, HAVING, subqueries, CTEs and window functions are standard: what you practice in SpeedQL carries over as it is to PostgreSQL and MySQL 8.

Put it into practice

The best way to remember these topics is to use them. Start a timed SQL game, pick a topic in the custom SQL exercises or take on the daily SQL exercise.