koans.app

SQL Koans

SQL is how you ask a database questions. You describe the rows you want, and the database works out how to find them, whether there are ten or ten million. Nearly every application keeps its data in a SQL database, so it’s useful to developers, analysts and anyone who works with data. These koans run on SQLite, a small database engine built into countless apps, and cover queries, grouping, joins, changing data, subqueries, window functions and a few classic puzzles.

72 koans in 9 groups, tested with sqllogictest-style expectations. Each is a small program whose tests fail: replace every ___ until they pass.

Start the path

Awakening

How SQL koans check a query, and the first things to ask a table.

  1. First queries
  2. Tables
  3. WHERE
  4. NULL
  5. ORDER BY and LIMIT
  6. IN and BETWEEN
  7. Aliases
  8. Sorting NULLs

Expressions and functions

Numbers, strings, patterns, CASE, and dates.

  1. Numbers and types
  2. Strings
  3. LIKE and GLOB
  4. CASE
  5. Dates
  6. Type affinity
  7. Arithmetic functions
  8. COALESCE and NULLIF
  9. More string functions
  10. Date arithmetic
  11. Collation
  12. Math functions

Grouping

Aggregate functions, GROUP BY, HAVING, DISTINCT and group_concat.

  1. Aggregates
  2. GROUP BY
  3. HAVING
  4. DISTINCT
  5. group_concat
  6. FILTER
  7. Pivoting
  8. NULLs in aggregates

Joins

Combining tables: inner and outer joins, joining a table to itself, and set operations.

  1. Inner joins
  2. Left joins
  3. Self joins
  4. Set operations
  5. Cross joins
  6. Joins and aggregates
  7. Semi- and anti-joins
  8. USING and NATURAL
  9. Many to many

Changing data

INSERT, UPDATE and DELETE, constraints, keys, upserts, and transactions.

  1. INSERT, UPDATE and DELETE
  2. Constraints
  3. Keys
  4. Upsert
  5. Transactions
  6. INSERT … SELECT and UPDATE … FROM
  7. RETURNING
  8. Defaults and generated columns
  9. Altering tables
  10. Indexes

Subqueries and CTEs

Queries inside queries, correlated subqueries, EXISTS, and WITH, recursion included.

  1. Subqueries
  2. Common table expressions
  3. Recursive CTEs
  4. Correlated subqueries
  5. EXISTS and IN
  6. Walking a hierarchy

Window functions

Calculations across rows related to the current one, without grouping them away.

  1. Window functions
  2. Ranking
  3. Frames
  4. lag and lead
  5. Partitions
  6. Value functions
  7. ROWS, GROUPS and RANGE

SQLite features

JSON, views, triggers, and strict tables.

  1. JSON
  2. JSON tables
  3. Views
  4. Triggers
  5. Strict tables

Puzzles

Classic problems that combine what came before.

  1. Top N per group
  2. Gaps and islands
  3. Removing duplicates
  4. Generating a calendar
  5. Running balance
  6. The second highest
  7. The median