Which SQL Does This Use?

SQL Tester runs SQLite, a widely used SQL engine, compiled to WebAssembly so it can run inside the page. It is a recent version (3.49 at the time of writing). Most standard SQL works: SELECT, WHERE, ORDER BY, GROUP BY, HAVING, subqueries, every kind of join, common table expressions (WITH) and window functions. SQLite describes itself as a self-contained, serverless database engine that reads and writes ordinary files, which is why it fits so naturally inside a web page. [SQLite, About SQLite]

Handy Patterns to Copy

• Count rows: SELECT COUNT(*) FROM my_table;
• Group and count: SELECT city, COUNT(*) AS n FROM people GROUP BY city ORDER BY n DESC;
• Join two files: SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id;
• Find rows with no match: SELECT c.name FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.order_id IS NULL;
• Search text: SELECT * FROM my_table WHERE name LIKE '%smith%';
• Remove duplicates: SELECT DISTINCT city FROM people;
• List the columns of a table: PRAGMA table_info(my_table);

Joins

A join lines up rows from two tables using a shared value. A plain JOIN keeps only rows that match on both sides. LEFT JOIN keeps every row from the first table and fills in NULL where the second table has no match, which is the standard way to find what is missing. RIGHT JOIN and FULL OUTER JOIN also work in this tool, so you can keep every row from the second table or from both. Give each table a short alias (such as o and c above) so column names stay readable.

Working With Text

• Join pieces of text with two vertical bars: SELECT first_name || ' ' || last_name FROM people;
• Plain comparison with = is case-sensitive, so 'ann' does not equal 'Ann'. LIKE ignores case for English letters, while GLOB is case-sensitive and uses * and ? as wildcards.
• There is no ILIKE command like some other databases have. Use LIKE instead.
• Use single quotes for text values and double quotes for names that contain spaces, such as "Order Date".

Working With Numbers

When both numbers are whole numbers, SQLite divides without decimals, so 7 / 2 gives 3. Write 7 / 2.0 to get 3.5. Sorting a column that was detected as text puts "10" before "9", because text sorts character by character. If numbers sort strangely, change the column type to INTEGER or REAL in the Tables panel. You can also convert on the fly with CAST('42' AS INTEGER).

Missing Values (NULL)

NULL means "no value", and it is not equal to anything, not even another NULL. Test for it with IS NULL or IS NOT NULL, never with = NULL. To show a stand-in value, use COALESCE: SELECT COALESCE(city, 'Unknown') FROM people;

Dates and Times

SQLite has no separate date type. Dates are normally stored as text in the form YYYY-MM-DD, as day numbers, or as Unix timestamps, and the built-in date functions work with all three. Text dates in year-month-day order also sort correctly as plain text. Useful examples: date('2026-10-16', '+1 month') returns 2026-11-16, and strftime('%Y', hire_date) pulls out the year. [SQLite, Datatypes in SQLite] [SQLite, Date and Time Functions]

Flexible Typing

Unlike many databases, SQLite lets the type belong to the value instead of the column. A column's declared type is a preference called affinity, not a strict rule. This is why a column of mixed numbers and words can still be loaded and queried without errors. [SQLite, Datatypes in SQLite]

Window Functions and Running Totals

Window functions calculate across related rows without collapsing them into one. For example, RANK() OVER (ORDER BY salary DESC) numbers people by pay while still listing every person. Other built-ins include row_number, dense_rank, lag, lead, first_value, last_value and ntile. These are available in SQLite from version 3.25.0 onward. [SQLite, Window Functions]

Building a Query Step by Step

If a long query gives an unexpected answer, build it in stages. Start with SELECT * and a LIMIT to see the raw rows, add the WHERE filter, then add the join, and add GROUP BY last. A WITH clause lets you name an intermediate step and use it like a table, which keeps complicated questions readable.

Reading Error Messages

• near "SELEC": syntax error means the word shown is misspelled or in the wrong place. Check the text just before it too.
• no such table means the table name is misspelled or the file was not loaded. Look in the Tables panel for the exact name.
• no such column means the column name does not match. Click the name in the Tables panel to insert it exactly.

The Idea Behind SQL

SQL grew out of a 1970 paper by Edgar F. Codd of IBM, published in Communications of the ACM and titled "A Relational Model of Data for Large Shared Data Banks." Instead of storing data in rigid hierarchies tied to a particular computer, Codd proposed keeping it in simple tables of rows and columns, with shared columns linking one table to another. Users could then ask for what they wanted without needing to know how the data was physically arranged. The paper became widely accepted as the foundation of relational databases. [History of Information, Codd's Relational Model]

SEQUEL at IBM

Codd's model needed a language people could actually use. IBM researchers Donald Chamberlin and Raymond Boyce created one, publishing their 1974 paper "SEQUEL: A structured English query language." It was designed for System R, IBM's research database and the first to put Codd's relational model into practice. The name was a nod to QUEL, a rival query language developed at Berkeley for the Ingres database. The language was later known simply as SQL. [The Register, Fifty Years of SQL]

The First Commercial Products

Other companies saw the potential too. Larry Ellison founded Software Development Laboratories in 1977, inspired by Codd's paper. The company was renamed Relational Software in 1979 and released its first relational database, Oracle V2. There was no version 1, to give the impression of reliability. [History of Information, Software Development Laboratories] Oracle adopted the SEQUEL approach and claimed to be the first commercially available relational system, while IBM introduced its own DB2 database in late 1983. [The Register, Fifty Years of SQL]

Becoming a Standard

As more vendors adopted SQL, a common standard became important so that people and programs could move between products. SQL was standardized as ANSI X3.135 in 1986, and the International Organization for Standardization adopted it as ISO 9075 in 1987. The standard has been revised many times since, with editions in 1989, 1992, 2003, 2008, 2011, 2016 and 2023. Since 2003 it has been divided into nine parts, and the 2023 edition is the most recent. In practice every database adds its own extras, so SQL from one product often needs small changes to run on another. [ANSI Blog, SQL Standard ISO/IEC 9075]

SQLite and SQL Everywhere

The engine in this tool, SQLite, was started in May 2000. Unlike most databases, it has no separate server. It reads and writes ordinary files, and its code is in the public domain, free for any purpose. [SQLite, About SQLite] SQLite's own project estimates it is probably the most widely deployed database engine in the world. It is built into every Android and iOS device, every Mac, every Windows 10 and 11 installation, and the Chrome, Firefox and Safari browsers, among countless other programs. [SQLite, Most Widely Deployed SQL Database Engine]

What SQL Is Used For

• Business records: customers, orders, invoices, inventory and payroll are typically kept in relational tables and answered with SQL.
• Reporting and analysis: grouping, counting, averaging and comparing data is the everyday work of analysts, and most reporting tools generate SQL behind the scenes.
• Websites and apps: web applications commonly keep their accounts, posts and settings in a database and fetch them with SQL queries.
• Data science: SQL is often the first step for pulling the right rows out of a large data store before analysis in another tool.
• Everyday software: phone apps, browsers and desktop programs use small embedded databases such as SQLite to store their own data.
• Learning and testing: because the language is small and readable, it is a popular first step for anyone learning how data is organized, which is exactly what a tool like this is for.

Why SQL Has Lasted

SQL is declarative: you describe the result you want, and the database works out how to get it. That is why a query written decades ago still reads clearly today, and why skills learned on one database mostly carry over to another. More than fifty years after Codd's paper, it remains one of the most widely used languages for working with data.