SQL practice › SQL reference
SQL reference, with queries you can run
Each page explains one piece of SQL the way it comes up at work and in interviews: what it does, the mistakes that give wrong rows without any error, and the questions people ask about it. Every query on a page runs on a real database in your browser, so you can change it and see what comes back, then solve a few exercises. Answers are included.
Joins
Grouping
- GROUP BYOne row per group, with COUNT, SUM or AVG for each: by one column, by two, by month, and the mistakes that give wrong numbers.
- WHERE vs HAVINGWHERE filters rows before grouping, HAVING filters groups after. Using both in one query, and why an aggregate in WHERE fails.
- COUNT(DISTINCT)Count each value once. COUNT(*) vs COUNT(col) vs COUNT(DISTINCT col), NULLs, per group, after a join, and more than one column.
Filtering and expressions
- NULLWhy = NULL returns no rows, IS NULL, COALESCE, NULL in COUNT, SUM and AVG, in NOT IN, and NULL versus an empty string.
- LIKEMatch text against a pattern: % and _, starts with, ends with, contains, several patterns with OR, NOT LIKE, and when case matters.
- CASE WHENSQL's if/else: the first WHEN that is true decides the value. Buckets, counting by condition, NULL, custom sort orders.
Window functions
- Window functionsCompute a value for each row from a group of rows without folding them: OVER, PARTITION BY, ROW_NUMBER, LAG.
- RANK vs DENSE_RANK vs ROW_NUMBERThree ways to number rows that differ only on ties: 1, 2, 3, 4 or 1, 2, 2, 4 or 1, 2, 2, 3. Which to pick for top N.
- Running totalSUM() OVER (ORDER BY ...) adds up the rows so far. Per group with PARTITION BY, and what happens when two rows share a date.
Subqueries, CTEs and UNION
- Subquery vs CTEA subquery sits inside the query that uses it; a CTE (WITH) names it first. The same question answered in WHERE, in FROM and with a CTE.
- UNION vs UNION ALLBoth stack the rows of two queries. UNION ALL keeps every row; UNION removes duplicate rows, which changes counts and totals.
Classic interview queries
- Second highest salaryThree queries for the second (or Nth) highest salary, and what each one does when two people earn the same.
- Find duplicatesGROUP BY with HAVING COUNT(*) > 1 for one column or several, then the full duplicate rows and which copy to keep.
- Top N per groupThe top rows of each group: number them with ROW_NUMBER or RANK in a CTE, filter outside. What ties change.