This is a complete SQL database running inside your browser tab. There is no server, no account and
nothing to install: the engine is SQLite
3.49.1, compiled to WebAssembly by the sql.js project and loaded from this site rather than from
anyone else’s servers, and it executes in a
web worker — a background thread. That detail matters more than it sounds: it is what
lets a runaway query be stopped without freezing the page, so a careless cross join costs you a
click rather than a browser restart.
The sample database
Five related tables, small enough to hold in your head and large enough for every kind of query.
Departments has 5 rows, Employees 12, Customers 8,
Products 7 and Orders 15. They are joined the way a real schema is:
Employees.dept_id
points at Departments, and Employees.manager_id
points back at Employees itself, which is what makes self joins practisable. Every row in Orders
references a customer, an employee and a product, so three-table joins are the normal case rather
than a contrived exercise.
The data was deliberately shaped so that queries can be told apart. One department has nobody in it,
so a LEFT JOIN gives a different answer from an INNER JOIN. Two customers share a country, so
DISTINCT does something. One product costs exactly 400, so more than 400 and
400 or more are not the same query. Five employees have no manager, which is where NULL
stops being an abstraction.
What it can run
SELECT with WHERE, every join type (INNER, LEFT, RIGHT, FULL OUTER and CROSS), GROUP BY with HAVING,
ORDER BY, LIMIT and OFFSET, subqueries both plain and correlated, EXISTS, CTEs written with WITH
(including WITH RECURSIVE), CASE expressions, UNION, INTERSECT and EXCEPT, the aggregate functions,
string and date functions, COALESCE and NULLIF, and window functions —
ROW_NUMBER(),
RANK(),
DENSE_RANK(),
LAG(),
LEAD()
and running totals with SUM() OVER (ORDER BY ...).
It also writes: INSERT (including an upsert with ON CONFLICT, and RETURNING), UPDATE, DELETE,
CREATE TABLE (also CREATE TABLE ... AS SELECT), ALTER TABLE, DROP TABLE, CREATE and DROP VIEW,
CREATE INDEX, triggers, and transactions with BEGIN, COMMIT and ROLLBACK.
Constraints are genuinely enforced on the tables you create — a duplicate primary key, a NULL
in a NOT NULL column, a repeated UNIQUE value, a failed CHECK or a foreign key pointing at nothing
are all refused with an error, which makes them worth practising here. Foreign keys are checked
because the playground switches that on for every session; plain SQLite leaves it off until you run
PRAGMA foreign_keys = ON.
The five sample tables declare only their primary keys, so the relationships between them are ones
you join on, not rules the engine checks.
Where SQLite differs
Nearly everything you learn here carries over to MySQL, PostgreSQL and SQL Server. These are the
differences you are most likely to run into:
- Whole numbers divide to a whole number.
7 / 2 is 3, as in
PostgreSQL and SQL Server; MySQL gives 3.5. Write 7 / 2.0
for the fraction. The sample money columns (salary, budget, unit_price, order_total) are REAL,
so salary / 12 keeps its fraction.
- LIKE ignores the case of English letters; = does not.
first_name LIKE 'ananya'
finds Ananya, but first_name = 'ananya'
finds nothing. PostgreSQL’s LIKE respects case (ILIKE is the form that ignores it), while MySQL
and SQL Server usually ignore case in both LIKE and =, depending on the collation.
- No TOP, TRUNCATE or ILIKE. Write LIMIT at the end of the query,
DELETE FROM table to empty a table, and plain LIKE.
The error message suggests the SQLite form when it sees one of these.
- Dates are text, with their own functions. Dates are stored as
'2024-05-29'-style text, which sorts
and compares correctly. There is no DATEDIFF, DATEADD or NOW(): use
julianday(b) - julianday(a) for the days between two dates,
date(d, '+7 days') to move a date, and
date('now') for today.
- YEAR(), MONTH() and DAY() are added by this playground. SQLite does not have them;
they are included because MySQL and SQL Server do. In plain SQLite write
strftime('%Y', d), which returns text, or
CAST(strftime('%Y', d) AS INTEGER) for a number.
- A double-quoted value can pass for text.
country = "Peru"
works here, because SQLite reads a double-quoted word that is not a column name as text. PostgreSQL
and SQL Server read it as a column name and fail, so the playground shows a warning when it sees one.
Put text in single quotes.
- A column that is neither grouped nor aggregated is accepted.
SELECT dept_id, first_name, COUNT(*) ... GROUP BY dept_id
runs and shows a first_name from one arbitrary row of each group, with a warning. PostgreSQL,
SQL Server and MySQL in its default mode reject the query.
- HAVING may use a SELECT alias.
HAVING n > 2,
where n is named in the SELECT list, works here and in MySQL; PostgreSQL and SQL Server need the
expression itself, HAVING COUNT(*) > 2.
- A subquery that should return one value may return several.
WHERE salary = (SELECT salary FROM Employees WHERE dept_id = 10)
quietly uses the first row the subquery produces. MySQL, PostgreSQL and SQL Server stop with an error.
- Column types are flexible. A column declared INTEGER will still store
'abc',
as text, where other databases refuse the value. And a DECIMAL column stores a whole number such as
10 as an integer, so dividing it by 4 gives 2, not 2.5.
A few things SQLite does not do at all: there are no user accounts, so no GRANT or REVOKE, and no
stored procedures. ATTACH, which opens a second database, is switched off here so that
Reset can always put everything back. Where a tutorial shows SQL this engine cannot run,
the block is labelled not in SQLite rather than given a Run button that would only
produce an error. The
dialect differences guide
sets SQLite, MySQL, PostgreSQL and SQL Server side by side.
Breaking things is the point
Run DELETE FROM Orders
with no WHERE clause and watch fifteen rows disappear. Drop a table. Empty the database. Then press
Reset, or simply reload the page, and the original data is back — the reset also removes
any table, view, index or trigger you created, so nothing you do here can leave a mess behind. This is the one place
where practising the mistakes costs nothing, which is exactly why the
guide to changing data
sends you here rather than to your own database.
Nothing you type is sent anywhere. The queries, the results and the history in the panel above all
stay in this browser, because there is no backend to send them to. The Copy link button
above the editor copies a link that opens your query here; the query travels in the part of the URL
after the #,
which browsers do not send to the server, and the playground reads it and removes it from the address
bar as the page opens, before the scripts that count page views and load times can see it.
Ready to be checked rather than just to experiment?
The 42 practice exercises
run your answer against this same database and compare the rows it returns with the expected ones.