sql.notes

learn sql, one topic at a time.

35 topics from your first SELECT to window functions and query plans. Every topic is a short explanation, examples, and practice questions with answers. All queries run on the same small sample database, so you can try everything yourself.

start from the beginningtry the problems

how to use this

  1. copy the sample data and run it once in postgres (or paste it into db-fiddle, pick postgres).
  2. read a topic, run the examples, then try the practice questions before looking at the answers.
  3. go in order if you're new. If you're prepping for interviews, jump straight to intermediate.

Beginner

the stuff every single query is built on.

  1. 01SELECT & basic queriesOk so this is where everything starts. Every query you'll ever write has a SELECT in it somewhere, it's basically you telling the database "hey, give me these columns from this table".
  2. 02WHERE & filteringSELECT gets you columns, WHERE decides which rows make it. Without WHERE you get the whole table, with it you get only the rows that pass your condition.
  3. 03logical operators (AND, OR, NOT)One condition is rarely enough. Like "orders from Delhi" is fine but "delivered orders from Delhi above 1000" is what people actually ask for. That's where AND, OR and NOT come in.
  4. 04NULL handlingNULL means "we don't know". Not zero, not empty string, not false. Just unknown. And once you get that, all the weird NULL behaviour starts making sense.
  5. 05sorting with ORDER BYHere's something people don't expect, without ORDER BY the database gives you rows in whatever order it feels like. It might look sorted today and come back different tomorrow. If order matters, say it.
  6. 06LIMIT & OFFSETLIMIT says "only give me this many rows". OFFSET says "skip this many first". That's literally it, but there are a couple of gotchas.
  7. 07aggregation basics (COUNT, SUM, AVG, MIN, MAX)Up till now every query gave back rows from the table. Aggregates squash a bunch of rows into one number. "How many orders?", "total revenue?", "average salary?", all of these are aggregates.
  8. 08DISTINCTDISTINCT removes duplicate rows from your result. Simple idea, but people misuse it a lot so let's go through it properly.
  9. 09data typesEvery column has a type, and the type decides what you can store and what you can do with it. You can't do math on text, you can't do date stuff on a number. Picking the right type early saves so much pain later.
  10. 10CASE WHEN (if else in sql)CASE is how you write if/else logic inside a query. You'll use it for labels, buckets, custom sorting, and later for really useful stuff like conditional counting.

Intermediate

joins, grouping, subqueries, the everyday work.

  1. 01INNER JOINReal data is split across tables. Orders don't store the customer's name, they store a customer_id, and the name lives in customers. JOIN is how you stitch them back together.
  2. 02LEFT, RIGHT & FULL OUTER JOININNER JOIN drops anything without a match. But a lot of the time you want to keep those rows. "Show all customers and their orders, including people who never ordered", that's a LEFT JOIN.
  3. 03self joinsA self join is just joining a table to itself. Nothing new syntax wise, but it trips people up because it feels weird at first.
  4. 04ON vs WHERE (join predicates)"Predicate" is just a fancy word for a condition. This topic is about one question: should a condition go in the ON or in the WHERE? For INNER JOIN it doesn't matter. For LEFT JOIN it changes your answer completely.
  5. 05GROUP BY & HAVINGYou saw GROUP BY in the aggregation basics. This goes deeper, especially HAVING, and the difference between WHERE and HAVING which comes up in like every interview.
  6. 06UNION, INTERSECT, EXCEPTJoins put tables side by side (more columns). Set operations stack results on top of each other (more rows). Different tool, different job.
  7. 07subqueriesA subquery is a query inside another query. You use one when you need an answer from the database before you can ask your actual question. Like "products more expensive than average", first you need the average, then you can compare.
  8. 08CTEs (the WITH clause)A CTE (common table expression) is basically a subquery you give a name to and write at the top instead of stuffing it in the middle. Same power, way more readable.
  9. 09string functionsText data is always messy. Extra spaces, random caps, stuff you need to cut or join. These are the functions you'll keep reaching for. Postgres names, mysql differences noted where they matter.
  10. 10date & time functionsAlmost every real question has a time part to it. Revenue this month, users who signed up last week, days since last order. Dates are also where databases differ the most, so I'll stick to postgres and note mysql where it's very different.
  11. 11join fan-out (why your totals are wrong)This is probably the most common bug in real sql, and it doesn't give an error. The query runs, you get a number, the number is wrong. Usually too big.
  12. 12anti-joins (finding what's missing)A normal join finds matches. An anti-join finds rows that don't have a match. "Customers who never ordered", "products never sold", "users who signed up but never logged in". Super common question, and there are three ways to write it, one of which has a nasty bug.
  13. 13numeric functionsMath stuff. Most of it's obvious, but rounding and division have a few traps that cause real bugs, especially with money.

Advanced

window functions, performance, and the patterns interviews love.

  1. 01window functionsIf there's one advanced topic to really get, it's this one. Window functions show up in basically every data interview, and once they click you'll use them everywhere.
  2. 02indexesYour query is correct but takes 40 seconds. Nine times out of ten, the fix is an index. This topic is about what they actually are, so you know when to add one and when not to.
  3. 03query optimizationMaking slow queries fast. The main skill here isn't memorizing tricks, it's learning to read what the database is actually doing, then fixing the part that's slow.
  4. 04expression indexesA normal index is on a column. An expression index is on the result of something, like LOWER(email). It fixes the "I wrapped my column in a function and now my index is useless" problem.
  5. 05non-equi joinsEvery join so far used = in the ON. A non-equi join uses anything else, <, >, BETWEEN, <>. You need it when rows match by range instead of by exact id.
  6. 06database design & normalizationEvery other topic is about querying tables. This one's about how the tables should look in the first place. Bad design makes every query harder forever, so it's worth getting right.
  7. 07transactions & ACIDA transaction groups several statements into one all-or-nothing unit. Either every step happens, or none of them do. The classic example is moving money, you can't have the debit happen and the credit fail.
  8. 08SQL injection & securitySQL injection is when user input ends up being run as sql code. It's one of the oldest bugs on the web and still one of the most common. The fix is simple once you get why it happens.
  9. 09recursive CTEsA recursive CTE is a CTE that refers to itself. It's how sql walks through trees and chains when you don't know how deep they go, org charts, category trees, folder structures, "who referred who".
  10. 10advanced aggregationYou know COUNT, SUM and GROUP BY. This is the stuff on top, subtotals, pivots, conditional aggregates, stats, and putting things into arrays. Basically everything you need to build a proper report in one query.
  11. 11JSON in sqlSometimes data doesn't fit neat columns. Event logs where every event type has different fields, api responses, user settings. Postgres lets you store JSON in a column and still query inside it. Our events table has a payload JSONB column for exactly this.
  12. 12real world patternsThis is where everything comes together. These are the problems that keep showing up in actual jobs and interviews, and each one is a combo of CTEs, window functions and joins you've already seen. Once you know these shapes, most "hard" sql questions are just one of them in disguise.