Querydrill

SQL tutorial, one page per clause

SQL explained one clause at a time, in the order you need them: SELECT and WHERE, sorting, aggregation, joins, subqueries and CTEs, window functions, strings and dates. Each page gives the direct answer, the syntax, runnable examples with their results, the usual mistakes, and the Querydrill exercises that practice it on a live database.

40 topics, each with the direct answer, syntax, worked examples, the mistakes people make and the drills that practice it. Updated November 2026.

Basics

  • SELECTHow to write a SQL SELECT: choose columns from a table, select everything with *, compute expressions, and combine it with WHERE, ORDER BY and LIMIT.
  • ORDER BYORDER BY sorts the rows of a query result. Learn ASC and DESC, sorting on multiple columns, sorting by an expression or alias, and where NULLs end up.
  • LIMIT and OFFSETLIMIT returns only the first N rows of a query and OFFSET skips rows before that. Get the top 10, page through results and find the second highest value.
  • DISTINCTSELECT DISTINCT returns each distinct value or combination once. List unique countries, count distinct values, and see why DISTINCT dedupes whole rows.
  • INSERT INTOINSERT INTO adds rows to a table. Syntax with a column list, inserting multiple rows in one statement, INSERT ... SELECT to copy data, RETURNING the new id.
  • UPDATEUPDATE table SET column = value WHERE condition changes matching rows. Several columns, compute from the current value, update from another table, stay safe.
  • DELETEDELETE FROM table WHERE condition removes matching rows. Delete by id, by subquery, duplicates, everything in a table, and DELETE versus TRUNCATE.
  • CREATE TABLEHow to create a table with column types, NOT NULL, DEFAULT, UNIQUE, PRIMARY KEY and FOREIGN KEY in SQLite and PostgreSQL, plus CREATE TABLE AS SELECT.
  • Primary and foreign keysA primary key uniquely identifies each row; a foreign key points to a primary key in another table so the link stays valid. Declaring both, composite keys.
  • Aliases (AS)An alias gives a column or table a temporary name: SELECT price * qty AS total, FROM orders o. Where aliases are allowed (ORDER BY yes, WHERE no) and quoting.

Filtering

  • WHEREUse WHERE to keep only the rows that match a condition: equals, greater than, text matches, NULL checks, and combinations with AND, OR and NOT.
  • LIKELIKE matches text against a pattern: % stands for any run of characters and _ for one character. Starts with, ends with, contains, case sensitivity, NOT LIKE.
  • INWHERE column IN (...) keeps rows whose value appears in a list or in a subquery result. Examples, NOT IN, the NULL trap, and when to use EXISTS instead.
  • BETWEENBETWEEN low AND high keeps values from low to high, both ends included. Examples with numbers and dates, the end-of-day problem with timestamps, NOT BETWEEN.
  • IS NULLNULL means unknown, so = NULL never matches. Use IS NULL and IS NOT NULL to find or exclude missing values, and see how NULL behaves in counts and sorting.
  • AND, OR, NOTHow to combine WHERE conditions with AND, OR and NOT, why AND binds before OR, how parentheses fix it, and how NULL changes the result of a logical test.
  • CASE WHENCASE WHEN returns different values depending on conditions. Label rows, bucket numbers into ranges, pivot with SUM(CASE ...), sort custom orders, handle NULL.

Aggregation

  • GROUP BYGROUP BY collapses rows that share a value into one row per group so you can count, sum or average them. One or several columns, the must-appear rule, HAVING.
  • HAVINGHAVING filters the groups made by GROUP BY using aggregate conditions such as COUNT(*) > 1 or SUM(amount) > 1000. Examples, HAVING vs WHERE, both together.
  • COUNTCOUNT(*) counts rows, COUNT(column) counts non-NULL values, COUNT(DISTINCT column) counts unique values. Examples with WHERE, GROUP BY and conditional counts.
  • SUM, AVG, MIN, MAXThe core aggregates: SUM adds values, AVG averages them, MIN and MAX find the smallest and largest. NULL handling, integer division in AVG, use with GROUP BY.
  • COUNT(DISTINCT)COUNT(DISTINCT column) counts how many different values a column holds, ignoring NULL. Unique customers, distinct values per group, distinct pairs of columns.

Joins

  • INNER JOININNER JOIN returns rows that have a match in both tables. Syntax with ON, joining three tables, aliases, columns from both sides, and the common pitfalls.
  • LEFT JOINLEFT JOIN returns every row of the first table and matching rows from the second, or NULL. Find rows without a match, count with zeros, avoid the WHERE trap.
  • Self joinA self join joins a table to itself using two aliases. Typical uses: employee and manager in the same table, comparing rows in pairs, rows that share a value.
  • FULL OUTER JOINFULL OUTER JOIN returns all rows from both tables, matched where possible and NULL elsewhere. Syntax, rows present on only one side, and the UNION workaround.
  • UNION and UNION ALLUNION combines the rows of two SELECTs and removes duplicates; UNION ALL keeps them and is faster. Column rules, ordering the result, INTERSECT and EXCEPT.

Subqueries and CTEs

  • SubqueryA subquery is a SELECT inside another query. Scalar subqueries (compare to an average), IN subqueries, correlated subqueries and derived tables in FROM.
  • WITH (CTE)A common table expression defines a named temporary result with WITH name AS (...) that the main query uses like a table. Examples, several CTEs, recursion.
  • EXISTSEXISTS is true when the subquery returns at least one row. Use it for has-any checks and NOT EXISTS for rows with no match, the safe alternative to NOT IN.

Window functions

  • Window functionsWindow functions compute rankings, running totals, shares and previous-row values without collapsing rows. The OVER clause, PARTITION BY, ORDER BY and frames.
  • ROW_NUMBERROW_NUMBER() OVER (ORDER BY ...) assigns 1, 2, 3 to rows; with PARTITION BY it restarts per group. Latest row per customer, remove duplicates, paginate.
  • RANK and DENSE_RANKRANK() and DENSE_RANK() rank rows and give ties the same rank; RANK skips numbers after a tie, DENSE_RANK does not. Nth highest salary and top N per group.
  • LAG and LEADLAG() returns a value from a previous row and LEAD() from a following row, without a self join. Month-over-month change, days since the last order, defaults.
  • Running totalA running total adds each row's value to the sum of all previous rows. SUM() OVER (ORDER BY), per-group totals with PARTITION BY, ROWS frames, moving average.
  • PARTITION BYPARTITION BY inside OVER splits rows into groups so a window function restarts per group. Rank per department, share of group total, PARTITION BY vs GROUP BY.

Strings and dates

  • COALESCECOALESCE returns the first non-NULL argument. Show a default instead of NULL, treat missing numbers as zero in sums, fall back across columns, IFNULL, NULLIF.
  • String functionsThe string functions you use in SQLite and PostgreSQL: || for concatenation, UPPER and LOWER, LENGTH, SUBSTR, TRIM, REPLACE, INSTR and POSITION, with notes.
  • Date functionsGet today's date, extract the year or month, group by month, add days and compute date differences in SQLite (strftime, julianday) and PostgreSQL (EXTRACT).
  • CASTCAST(value AS type) converts between types: string to integer, integer to decimal for division, number to text for concatenation, text to date, plus :: form.

Reading is not practicing

Every topic here links to drills that check your answer on live data. All of them are free to try.

Start practicing