SQL Quick Reference
The 34 most useful clauses and functions for everyday analysis, from SELECT and GROUP BY through joins and window functions.
Download quick reference ↓Learning resource
SQL Field Guide
A searchable reference for core SQL, PostgreSQL, analysis functions, joins, window functions, and more. SQL differs across database systems, so every entry names its dialect and whether it works in this site’s browser playground.
Downloadable resources
Take the SQL guide with you
Use the concise cheat sheet for quick lookups or download the complete reference for studying and offline use.
Complete SQL Field Guide
All 70 entries with plain-language definitions, examples, dialect notes, difficulty, and playground compatibility.
Download complete guide ↓Sortable SQL Reference
Open the full reference in Excel or Google Sheets, then sort and filter it by category, level, dialect, or playground support.
Download CSV ↓Showing 70 of 70 entries
Query basicsPlayground: yes
SELECT
Chooses the columns or expressions returned by a query.
SELECT name, district FROM tributes;Query basicsPlayground: yes
DISTINCT
Removes duplicate result rows.
SELECT DISTINCT district FROM tributes;FilteringPlayground: yes
WHERE
Keeps only rows that meet a condition.
SELECT * FROM tributes WHERE score >= 8;FilteringPlayground: yes
AND / OR / NOT
Combines or reverses filtering conditions.
WHERE district = 12 AND score >= 8FilteringPlayground: yes
IN
Matches any value in a list.
WHERE district IN (4, 7, 12)FilteringPlayground: yes
BETWEEN
Tests whether a value falls inside an inclusive range.
WHERE score BETWEEN 7 AND 10FilteringPlayground: yes
LIKE
Matches a text pattern using % and _ wildcards.
WHERE name LIKE 'K%';NullsPlayground: yes
IS NULL
Finds missing values; use IS NOT NULL for present values.
WHERE mentor IS NULLQuery basicsPlayground: yes
ORDER BY
Sorts results ascending or descending.
ORDER BY score DESC, name ASCQuery basicsPlayground: yes
LIMIT
Returns only a specified number of rows.
SELECT * FROM tributes LIMIT 5;Query basicsPlayground: no
FETCH FIRST
Standard-SQL alternative for limiting rows.
FETCH FIRST 5 ROWS ONLYQuery basicsPlayground: yes
AS
Gives a column or table a temporary alias.
SELECT score AS training_score FROM tributes;AggregatePlayground: yes
COUNT()
Counts rows or non-null values.
SELECT COUNT(*) AS tributes FROM tributes;AggregatePlayground: yes
SUM()
Adds numeric values.
SELECT SUM(points) FROM arena_events;AggregatePlayground: yes
AVG()
Calculates the arithmetic mean.
SELECT AVG(score) FROM tributes;AggregatePlayground: yes
MIN() / MAX()
Returns the smallest or largest value.
SELECT MIN(score), MAX(score) FROM tributes;GroupingPlayground: yes
GROUP BY
Combines rows into groups for aggregate calculations.
SELECT district, AVG(score) FROM tributes GROUP BY district;GroupingPlayground: yes
HAVING
Filters groups after aggregation.
GROUP BY district HAVING AVG(score) >= 8ConditionalPlayground: yes
CASE
Creates conditional values inside a query.
CASE WHEN score >= 8 THEN 'High' ELSE 'Other' ENDNullsPlayground: yes
COALESCE()
Returns the first non-null expression.
COALESCE(mentor, 'Unknown')NullsPlayground: yes
NULLIF()
Returns NULL when two expressions are equal.
points / NULLIF(events, 0)ConversionPlayground: yes
CAST()
Converts a value to another data type.
CAST(score AS DECIMAL(5,2))NumericPlayground: yes
ROUND()
Rounds a number to a chosen precision.
ROUND(AVG(score), 2)NumericPlayground: yes
ABS()
Returns a number's absolute value.
ABS(final_score - initial_score)NumericPlayground: yes
CEILING() / CEIL()
Rounds a number upward to an integer.
CEILING(7.2)NumericPlayground: yes
FLOOR()
Rounds a number downward to an integer.
FLOOR(7.9)NumericPlayground: yes
POWER()
Raises a number to a power.
POWER(score, 2)NumericPlayground: yes
SQRT()
Returns a square root.
SQRT(points)NumericPlayground: yes
MOD()
Returns the remainder after division.
MOD(district, 2)TextPlayground: yes
LOWER() / UPPER()
Changes text to lower- or uppercase.
UPPER(name)TextPlayground: yes
TRIM()
Removes leading and trailing spaces.
TRIM(name)TextPlayground: yes
LTRIM() / RTRIM()
Removes spaces from one side of text.
LTRIM(name)TextPlayground: no
LENGTH() / LEN()
Counts characters; the name varies by dialect.
LENGTH(name)TextPlayground: no
SUBSTRING()
Extracts part of a text value.
SUBSTRING(name FROM 1 FOR 3)TextPlayground: yes
REPLACE()
Replaces matching text.
REPLACE(name, ' ', '_')TextPlayground: yes
CONCAT()
Combines text values.
CONCAT(name, ' — District ', district)TextPlayground: no
STRING_AGG()
Combines values from multiple rows into one string.
STRING_AGG(name, ', ' ORDER BY name)TextPlayground: no
REGEXP_REPLACE()
Replaces text that matches a regular expression.
REGEXP_REPLACE(name, '[^A-Za-z]', '', 'g')Date/timePlayground: no
CURRENT_DATE
Returns today's date.
SELECT CURRENT_DATE;Date/timePlayground: no
CURRENT_TIMESTAMP
Returns the current date and time.
SELECT CURRENT_TIMESTAMP;Date/timePlayground: no
EXTRACT()
Pulls a component such as year or month from a date.
EXTRACT(YEAR FROM event_date)Date/timePlayground: no
DATE_TRUNC()
Rounds a timestamp down to a time unit.
DATE_TRUNC('month', event_date)Date/timePlayground: no
AGE()
Calculates an interval between dates.
AGE(CURRENT_DATE, birth_date)Date/timePlayground: no
INTERVAL
Represents a duration used in date arithmetic.
event_date + INTERVAL '30 days'JoinsPlayground: yes
INNER JOIN
Returns rows that match in both tables.
FROM tributes t JOIN arena_events e ON t.id = e.tribute_idJoinsPlayground: yes
LEFT JOIN
Keeps every left-table row and matching right rows.
FROM tributes t LEFT JOIN arena_events e ON t.id = e.tribute_idJoinsPlayground: yes
RIGHT JOIN
Keeps every right-table row and matching left rows.
FROM tributes t RIGHT JOIN arena_events e ON t.id = e.tribute_idJoinsPlayground: yes
FULL OUTER JOIN
Keeps matched and unmatched rows from both tables.
FROM tributes t FULL OUTER JOIN arena_events e ON t.id = e.tribute_idJoinsPlayground: yes
CROSS JOIN
Returns every possible pairing of two tables.
FROM tributes CROSS JOIN arenasSet operationsPlayground: yes
UNION
Stacks query results and removes duplicates.
SELECT name FROM tributes UNION SELECT name FROM mentors;Set operationsPlayground: yes
UNION ALL
Stacks query results and keeps duplicates.
SELECT name FROM tributes UNION ALL SELECT name FROM mentors;Set operationsPlayground: no
INTERSECT
Returns rows present in both query results.
query_one INTERSECT query_twoSet operationsPlayground: no
EXCEPT
Returns first-query rows absent from the second.
query_one EXCEPT query_twoQuery structurePlayground: yes
WITH (CTE)
Names a temporary result used by the main query.
WITH ranked AS (SELECT * FROM tributes) SELECT * FROM ranked;SubqueriesPlayground: yes
EXISTS
Tests whether a subquery returns at least one row.
WHERE EXISTS (SELECT 1 FROM arena_events e WHERE e.tribute_id = t.id)WindowPlayground: no
ROW_NUMBER()
Assigns a unique sequence number within a result.
ROW_NUMBER() OVER (ORDER BY score DESC)WindowPlayground: no
RANK()
Ranks rows with gaps after ties.
RANK() OVER (ORDER BY score DESC)WindowPlayground: no
DENSE_RANK()
Ranks rows without gaps after ties.
DENSE_RANK() OVER (ORDER BY score DESC)WindowPlayground: no
LAG() / LEAD()
Reads a value from a previous or following row.
LAG(score) OVER (ORDER BY event_date)WindowPlayground: no
FIRST_VALUE() / LAST_VALUE()
Returns the first or last value in a window frame.
FIRST_VALUE(score) OVER (PARTITION BY district ORDER BY event_date)WindowPlayground: no
NTILE()
Splits ordered rows into a chosen number of groups.
NTILE(4) OVER (ORDER BY score DESC)StatisticalPlayground: no
PERCENTILE_CONT()
Calculates an interpolated percentile, including the median.
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY score)StatisticalPlayground: no
STDDEV_SAMP()
Estimates the sample standard deviation.
STDDEV_SAMP(score)StatisticalPlayground: no
VAR_SAMP()
Estimates the sample variance.
VAR_SAMP(score)AggregatePlayground: no
FILTER
Applies a condition to one aggregate calculation.
COUNT(*) FILTER (WHERE score >= 8)PostgreSQLPlayground: no
GENERATE_SERIES()
Generates a sequence of numbers or dates.
SELECT * FROM GENERATE_SERIES(1, 12);PostgreSQLPlayground: no
TO_CHAR()
Formats dates or numbers as text.
TO_CHAR(event_date, 'Mon YYYY')PostgreSQLPlayground: no
TO_DATE()
Parses text into a date using a format.
TO_DATE('08/17/2026', 'MM/DD/YYYY')PostgreSQLPlayground: no
JSONB_EXTRACT_PATH_TEXT()
Reads text from a JSONB value at a path.
JSONB_EXTRACT_PATH_TEXT(details, 'weapon')PostgreSQLPlayground: no
ARRAY_AGG()
Collects grouped values into an array.
ARRAY_AGG(name ORDER BY name)