SQL quick reference
SQL in the SQL Playground, which runs SQLite 3: creating tables, SELECT with WHERE and ORDER BY, GROUP BY, JOIN, INSERT / UPDATE / DELETE, transactions, functions, CTEs and window functions, CSV data — and how every run starts from a fresh database.
Written for SQLite 3. Examples that show an output were run in the Zerethon sandbox (SQLite 3) on 2026-10-20.
Basics
-
Getting started
Your first query, and how the SQL Playground runs a file: a fresh, empty SQLite database every time.
-
CREATE TABLE and INSERT
Create tables with types and constraints, insert rows, and how SQLite stores types.
-
SELECT, WHERE and ORDER BY
Choose columns, filter rows with WHERE, sort with ORDER BY and limit with LIMIT.
-
COUNT, SUM, AVG and GROUP BY
Summarise rows with aggregate functions, one row per group with GROUP BY, and filter groups with HAVING.
-
JOIN
Combine tables with INNER JOIN and LEFT JOIN on a matching column.
-
UPDATE and DELETE
Change and remove rows — always with a WHERE clause.
-
Transactions
Group statements with BEGIN and COMMIT, or undo them with ROLLBACK.
Functions and queries
-
Text functions and patterns
Join text with
||,upper,length,substr,replace,trim, and match patterns with LIKE and GLOB. -
Numbers, dates and NULL
Arithmetic and
round, SQLite's date functions, and handling NULL withcoalesce. -
Subqueries, CTEs and window functions
Queries inside queries, named steps with WITH, and ranking with window functions.
In Zerethon IDE
-
Loading CSV data
Put rows in
data/<table>.csvand query them: each file becomes a table before your query runs. -
Running SQL in Zerethon IDE
What Run does in the SQL Playground, setup files, result tables, limits, query plans and tests.
-
Common errors
“no such table”, “no such column”, syntax errors, and constraint failures — what they mean in SQLite.
-
Official documentation
Where to read more: the SQLite SQL reference, its functions, and how it differs from other databases.