SQL · Guides · October 30, 2026 · 13 min read

SQL cheat sheet for data analysts: every clause and function with a runnable example

This is the SQL an analyst uses day to day, on one page: the clauses of a query, the filters, aggregates, joins, CASE, NULL handling, subqueries and CTEs, window functions, and the date and string functions you reach for most. Each section has a short reference table and one example with its real output, so you can see what the syntax actually returns rather than guess.

Every example runs on the airline database from the Nomto SQL course: 104 airports, 9 aircraft models, 33,121 flights from 12 July to 10 September 2026, 262,788 bookings, with money in US dollars and times in Moscow time. The syntax is PostgreSQL. You can paste any query into the Nomto SQL room and run it as is.

The tables used below

Query skeleton: the clauses and their order

You write them in the order of the first column of that table, but the database runs them in the order of the last. That is why an alias defined in SELECT works in ORDER BY but not in WHERE.

SELECT model, range
FROM aircrafts
WHERE range >= 5000
ORDER BY range DESC
LIMIT 3;

Filters (WHERE)

SELECT COUNT(*) FILTER (WHERE status IN ('Departed', 'Arrived')) AS flown,
       COUNT(*) FILTER (WHERE scheduled_departure BETWEEN '2026-08-01' AND '2026-08-07 23:59:59') AS first_week_aug,
       COUNT(*) FILTER (WHERE actual_departure IS NULL) AS not_departed
FROM flights;

FILTER (WHERE ...) lets you put three different filters in one query; each COUNT only sees its own rows. For date ranges, >= '2026-08-01' AND < '2026-08-08' is safer than BETWEEN, because you do not have to remember the 23:59:59.

Aggregates

SELECT COUNT(*)                                  AS bookings,
       COUNT(DISTINCT DATE_TRUNC('day', book_date)) AS days,
       SUM(total_amount)                         AS revenue,
       ROUND(AVG(total_amount), 2)               AS avg_booking,
       MIN(total_amount)                         AS min_b,
       MAX(total_amount)                         AS max_b
FROM bookings;

GROUP BY and HAVING

Every column in SELECT must be in GROUP BY or inside an aggregate. WHERE filters rows before grouping; HAVING filters groups after.

SELECT city, COUNT(*) AS airports
FROM airports
GROUP BY city
HAVING COUNT(*) > 1
ORDER BY airports DESC, city;

More on the difference in GROUP BY vs HAVING.

Joins

Look up a value from another table:

SELECT f.flight_no, a.model
FROM flights f
JOIN aircrafts a ON a.aircraft_code = f.aircraft_code
WHERE f.flight_id = 1;

Find rows with no partner, here the aircraft model that never flies:

SELECT a.model
FROM aircrafts a
LEFT JOIN flights f ON f.aircraft_code = a.aircraft_code
WHERE f.flight_id IS NULL;

The trap to remember: when one row matches several rows, it is repeated once per match. Summing bookings.total_amount after joining tickets gives 343,206,192 instead of the correct 207,669,809, because bookings with several tickets are counted several times. The SQL joins guide covers this fan-out in detail.

CASE: if-then logic

CASE WHEN condition1 THEN value1
     WHEN condition2 THEN value2
     ELSE default
END

Conditions are checked top to bottom and the first true one wins, so put the narrowest first.

SELECT CASE WHEN total_amount >= 1000 THEN 'large'
            WHEN total_amount >= 300  THEN 'medium'
            ELSE 'small'
       END AS size,
       COUNT(*) AS bookings
FROM bookings
GROUP BY size
ORDER BY bookings DESC;

SUM(CASE WHEN ... THEN 1 ELSE 0 END) is the portable way to count by condition; COUNT(*) FILTER (WHERE ...) does the same in PostgreSQL.

NULL handling

SELECT flight_no, scheduled_departure, status,
       COALESCE(CAST(actual_departure AS VARCHAR), 'not departed') AS actual
FROM flights
WHERE flight_no = 'PG0405'
  AND scheduled_departure BETWEEN '2026-08-10' AND '2026-08-13'
ORDER BY scheduled_departure;

A safe percentage, which returns NULL instead of an error if the table is empty:

SELECT ROUND(100.0 * COUNT(*) FILTER (WHERE status = 'Cancelled') / NULLIF(COUNT(*), 0), 2) AS cancelled_pct
FROM flights;

Write 100.0, not 100: with two integers, some databases do integer division and return 0.

Subqueries and CTEs

A subquery is a query inside another query. Bookings above the average:

SELECT COUNT(*) AS above_avg
FROM bookings
WHERE total_amount > (SELECT AVG(total_amount) FROM bookings);

A CTE (WITH) names a subquery so you can read the query top to bottom. Flights per day, then the minimum, average and maximum of those daily counts:

WITH daily AS (
  SELECT CAST(scheduled_departure AS DATE) AS day, COUNT(*) AS flights
  FROM flights
  GROUP BY day
)
SELECT MIN(flights) AS min_day, ROUND(AVG(flights), 1) AS avg_day, MAX(flights) AS max_day
FROM daily;

Use a CTE whenever you need to aggregate twice (an average of daily counts, a share of a total) or when the query gets longer than a screen.

Window functions

A window function adds a value to each row without collapsing rows. The shape is function() OVER (PARTITION BY ... ORDER BY ... frame).

Day-over-day comparison and a running total in one query:

SELECT CAST(book_date AS DATE) AS day,
       COUNT(*) AS bookings,
       LAG(COUNT(*)) OVER (ORDER BY CAST(book_date AS DATE)) AS prev_day,
       SUM(COUNT(*)) OVER (ORDER BY CAST(book_date AS DATE)) AS running_total
FROM bookings
GROUP BY day
ORDER BY day
LIMIT 4;

Top 1 per group with ROW_NUMBER: the busiest departure airport for each aircraft model.

SELECT *
FROM (
  SELECT a.model, f.departure_airport, COUNT(*) AS flights,
         ROW_NUMBER() OVER (PARTITION BY a.model ORDER BY COUNT(*) DESC) AS rn
  FROM flights f
  JOIN aircrafts a USING (aircraft_code)
  GROUP BY a.model, f.departure_airport
) t
WHERE rn = 1
ORDER BY flights DESC
LIMIT 4;

You cannot filter on a window function in WHERE of the same query, hence the subquery. Full walkthrough: SQL window functions and running totals.

Dates and times

Flights per month:

SELECT DATE_TRUNC('month', scheduled_departure) AS month, COUNT(*) AS flights
FROM flights
GROUP BY month
ORDER BY month;

July and September are partial months here (the schedule runs from 12 July to 10 September), which is worth saying out loud whenever you compare months.

Adding and subtracting dates:

SELECT book_ref, book_date,
       book_date + INTERVAL '30 days' AS plus_30,
       '2026-08-11'::DATE - CAST(book_date AS DATE) AS days_ago
FROM bookings
ORDER BY book_ref
LIMIT 2;

The longest departure delays, in minutes:

SELECT flight_no, scheduled_departure, actual_departure,
       ROUND(EXTRACT(EPOCH FROM actual_departure - scheduled_departure) / 60) AS delay_min
FROM flights
WHERE status = 'Arrived'
ORDER BY delay_min DESC
LIMIT 3;

For days with no rows at all (a day with zero bookings simply does not appear in GROUP BY), see the date spine guide.

Strings

Passenger names are stored in capitals as “FIRST LAST”, and contacts are JSON:

SELECT passenger_name,
       LOWER(SPLIT_PART(passenger_name, ' ', 2)) AS last_lower,
       contact_data ->> 'phone' AS phone
FROM tickets
ORDER BY ticket_no
LIMIT 3;

Building a label and cutting a string at a character:

SELECT airport_name || ' (' || airport_code || ')' AS label,
       SUBSTRING(timezone, 1, POSITION('/' IN timezone) - 1) AS region
FROM airports
WHERE city = 'Moscow'
ORDER BY airport_code;

Mistakes this cheat sheet saves you from

• = NULL. Always IS NULL; the equality returns no rows and no error.
• LIMIT without ORDER BY. You get some rows, not the top rows.
• Summing after a one-to-many join. Aggregate the “many” side first, then join.
• Integer division. 100 * a / b can round to 0; write 100.0.
• Filtering a window function in WHERE. Wrap it in a subquery or CTE.
• BETWEEN on timestamps. '2026-08-07' means midnight; use >= start and < next day.

Dialect notes

Everything here is PostgreSQL. Most of it is the same in other databases; the usual differences are:

How to use this cheat sheet

Keep it open while you work, but do not try to memorise it. You remember syntax by using it on questions you care about. In the Nomto SQL room every query above runs on the same airline database, so change a filter or a grouping column and see what moves. If you are just starting, basic SQL queries explains the first half of this page step by step, and the SQL practice problems give you tasks to apply the second half.

FAQ

What SQL should a data analyst know?

Solidly: SELECT, WHERE, GROUP BY, HAVING, joins, CASE, NULL handling, subqueries and CTEs, and date functions. Then window functions (ROW_NUMBER, LAG, running totals), which come up in most analyst interviews. Indexes, stored procedures and database administration matter much less for the role.

Is this cheat sheet valid for MySQL and SQL Server?

The clauses, joins, aggregates, CASE, COALESCE, subqueries, CTEs and window functions work the same way. Date functions, string concatenation and LIMIT differ; the dialect table above lists the common cases.

What is the difference between a subquery and a CTE?

They do the same job. A CTE gives the subquery a name and moves it to the top, so a long query reads in steps. Use whichever is easier to read; for anything with two or more steps, that is usually the CTE.

Can I run these examples without installing a database?

Yes. Open the Nomto SQL room, paste a query and run it in the browser on the same airline data.

Also in Español, Português (Brasil), Tiếng Việt, 简体中文