SQLite
CREATE TABLE greetings (id INTEGER PRIMARY KEY, message TEXT);

INSERT INTO greetings (message) VALUES
  ('Welcome to Online IDE!'),
  ('Start coding in SQL...');

SELECT message FROM greetings;

Press Run or Ctrl ↵ to execute main.sql.
Type program input here while it runs.

SQLite

Online SQL Compiler

main.sql is fed to the sqlite3 shell from SQLite 3.46, statement by statement, against a database that lives only for that run. Each SELECT prints its rows to the console; the script itself is the input, so there’s nothing to type while it runs.

Try it above

How SQLite runs here

Versions
SQLite 3.46
Run
sqlite3 < main.sql
Limits per run
256 MB memory, 15 s of CPU time on half a core, 2 MB of output
Time
Up to 10 minutes a run (30 on a paid plan); waits 2 minutes for input (5 on a paid plan)
Sandbox
Each run gets its own Linux sandbox with no network access. Text files your program writes next to your code open as new tabs.
Good to know
Each run starts from an empty in-memory database.

A script with two tables

Create the tables, fill them and query them in one file:

SQLite
CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  class TEXT
);

CREATE TABLE scores (
  student_id INTEGER REFERENCES students(id),
  subject TEXT,
  score INTEGER
);

INSERT INTO students (name, class) VALUES ('Ada', 'A'), ('Linus', 'B'), ('Grace', 'A');
INSERT INTO scores VALUES (1, 'math', 95), (1, 'art', 80), (2, 'math', 70), (2, 'art', 88), (3, 'math', 91);

.headers on
SELECT s.name, COUNT(*) AS tests, ROUND(AVG(sc.score), 1) AS average
FROM students s
JOIN scores sc ON sc.student_id = s.id
GROUP BY s.id
ORDER BY average DESC;
Terminal
$ sqlite3 < main.sql
name|tests|average
Grace|1|91.0
Ada|2|87.5
Linus|2|79.0

By default the shell prints rows with | between columns. .headers on adds the column names, and the dot commands below change the layout.

Shell commands for output and tables

Lines that start with a dot are commands for the sqlite3 shell, not SQL. Each sits at the start of its own line with no semicolon:

  • .headers on prints column names above the rows.
  • .mode box, .mode table or .mode markdown draw aligned tables; .mode column lines up columns without borders; .mode csv and .mode json print rows in those formats.
  • .tables lists the tables, and .schema students prints that table’s CREATE statement.
  • .import --csv people.csv people loads an uploaded CSV file into a table. Without the table first, the CSV’s first row becomes the column names.

SQLite is not MySQL

Tutorials often use MySQL or PostgreSQL. These are the differences that trip people up:

  • Auto-increment: write id INTEGER PRIMARY KEY, which numbers rows by itself. MySQL’s INTEGER PRIMARY KEY AUTO_INCREMENT stops with near "AUTO_INCREMENT": syntax error, and the INT AUTO_INCREMENT PRIMARY KEY spelling is accepted but leaves every id NULL.
  • No SHOW TABLES, DESCRIBE or TRUNCATE: each is a syntax error. Use .tables, .schema name or PRAGMA table_info(name), and DELETE FROM name.
  • Dates: there’s no NOW(). Use datetime('now') or CURRENT_TIMESTAMP, and date('now', '-7 days') for date arithmetic.
  • Types are loose: a column declared INTEGER stores the text 'ten' without complaint, and VARCHAR(5) doesn’t limit the length. Add STRICT after the closing parenthesis of CREATE TABLE to reject wrong types.
  • 7 / 2 is 3, because dividing two integers truncates. Write 7 / 2.0 or CAST(x AS REAL).
  • Foreign keys aren’t enforced until the script runs PRAGMA foreign_keys = ON;.

What isn’t available

  • Data that lasts. Every run starts empty, so main.sql must create and fill its tables each time.
  • Typing input while it runs: the whole file is the input.
  • Server features: stored procedures, users and GRANT, and connecting to a MySQL or PostgreSQL server.
  • MySQL and PostgreSQL functions without a SQLite equivalent, such as DATE_FORMAT(); SQLite uses strftime() instead.

Errors you’re likely to see

The shell reports each error with the line it was found near, and goes on with the next statement:

  • “no such table: user”: the table is misspelled, or its CREATE TABLE comes later in the file or failed.
  • “no such column: nmae”: a misspelled column, or a column from a table that isn’t in the FROM or JOIN.
  • “near "SHOW": syntax error”: MySQL syntax. Every “near … syntax error” points at the word where SQLite stopped understanding.
  • “no such function: NOW”: use datetime('now').
  • “UNIQUE constraint failed: users.email”: an INSERT repeats a value in a UNIQUE or PRIMARY KEY column.
  • “NOT NULL constraint failed: products.name”: an INSERT left out a column declared NOT NULL.

After a failed run, Fix with AI explains the error and proposes a change you review before it applies.

Window functions and modern SQL

SQLite 3.46 has window functions. Added to the end of the script above, this ranks students within their class by math score:

SQLite
SELECT s.class, s.name, sc.score,
       RANK() OVER (PARTITION BY s.class ORDER BY sc.score DESC) AS class_rank
FROM students s
JOIN scores sc ON sc.student_id = s.id
WHERE sc.subject = 'math'
ORDER BY s.class, class_rank;
Output
class|name|score|class_rank
A|Ada|95|1
A|Grace|91|2
B|Linus|70|1
  • WITH and WITH RECURSIVE for common table expressions.
  • INSERT … ON CONFLICT (col) DO UPDATE for upserts, and RETURNING to see the rows a statement changed.
  • JSON functions: json_extract(), and the -> and ->> operators.

Questions

Is this MySQL?

No, it’s SQLite 3.46. Most SELECT, JOIN and GROUP BY queries are the same, but table definitions, dates and admin commands differ, as listed above.

Does my data stay between runs?

No. Each run starts with an empty database, so keep the CREATE TABLE and INSERT statements in main.sql.

How do I show column names?

Put .headers on before the query, or .mode box to draw a table with a header row.

How do I list the tables?

Use .tables, or SELECT name FROM sqlite_master WHERE type = 'table';.

How do I make an AUTO_INCREMENT id?

Declare the column as id INTEGER PRIMARY KEY. SQLite fills it with the next number when an INSERT leaves it out.

Can I load a CSV file?

Yes. Upload it, then use .import --csv data.csv tablename in main.sql.