The SQL Checklist
Use this interactive page to practice all the SQL you need for data science.
Each concept is a flash card. Say or write out each concept, then click on the item to reveal the answer. Check the box when you know it and feel confident. See Part 1 and Part 2 for more information on each item.
0 / 0 checked
01
Selecting & Filtering
Basic select, limit +
select col from t limit 10;
Ordering — asc/desc, multi-column +
order by a asc, b desc
Filtering with where +
where x = 'y' and z > 100
In / not in +
where x in (1, 2, 3)
Is null +
where x is null
02
Joins
Basic join, table aliasing +
from a left join b on b.id = a.id
Join types & unmatched rows +
inner: match only
left: all left, null right if no match
right: all right, null left if no match
full outer: matched rows + unmatched from both sides (nulls fill gaps)
Anti join +
left join ... where b.id is null
Self join +
from t a join t b on a.parent_id = b.id
Cross join +
from a cross join b
Range join +
on a.date >= b.valid_from and a.date < b.valid_to
03
Grouping & Aggregating
Group by, count/sum/avg/min/max +
group by col
Distinct inside aggregates +
count(distinct col)
Median +
percentile_cont(0.5) within group (order by col)
Handling nulls in aggregates +
coalesce(col, 0)
04
Organizing Complex Queries
CTEs +
with x as (select ...) select * from x
Intermediate tables +
create table x as select ...
05
Window Functions
Over, partition by, order by +
sum(x) over (partition by id order by date)
Top row per group +
row_number() over (partition by id order by date desc)
06
Dates, Strings & New Columns
Date truncation +
date_trunc('month', d)
Date arithmetic +
d + interval '30 days'
String concatenation +
concat(a, ' ', b)
Pattern matching +
where x like '%@gmail.com'
Case when +
case when x >= 1000 then 'large' else 'small' end
07
Sampling, Reshaping & Utilities
Sampling rows +
tablesample bernoulli (1) repeatable (1234)
Hash sampling (reproducible outside the database) +
mod(get_byte(decode(md5(id::text), 'hex'), 0), 100) < 5
Exploding / imploding arrays +
unnest(arr) · array_agg(x)
Finding tables/columns via system tables +
select table_name, column_name from information_schema.columns where column_name like '%id%'
Filling gaps with generate_series +
generate_series('2026-01-01'::date, '2026-01-31'::date, interval '1 day')