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 this post 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
Sampling rows +
tablesample bernoulli (1)
Exploding / imploding arrays +
unnest(arr) · array_agg(x)