What SQL is and how relational databases work

Programs that remember things, such as orders, train bookings, exam marks or chat messages, keep them in a database, and the most widely used databases are worked with in SQL. This lesson explains what a relational database stores, what SQL is and how standard it is, which engines this track uses and how they differ, and then runs your first query.

Tables, rows and columns

A relational database keeps its data in tables. A table has a name and a fixed list of columns. Each column has a name and a data type, the kind of value it holds, such as whole numbers or text. Each row is one thing the table describes, and every row of a table has the same columns. (“Relation” is the mathematical word for a table, which is where the name comes from.) SQLite is more relaxed about types than most engines: it treats a column’s type as a preference rather than a rule, which the module on data types explains.

Here is a table of five products, made and read with SQL:

-- A small table of products. CREATE TABLE names the table and its columns,
-- INSERT adds five rows, and SELECT * asks for every column of every row.
CREATE TABLE products (
  product_id  INTEGER PRIMARY KEY,  -- the key: one number per product
  name        TEXT    NOT NULL,
  category    TEXT    NOT NULL,
  price_paise INTEGER NOT NULL      -- 100 paise = 1 rupee
);

INSERT INTO products (product_id, name, category, price_paise) VALUES
  (1, 'Basmati rice 1 kg', 'Grains',    14999),
  (2, 'Toor dal 500 g',    'Pulses',     8950),
  (3, 'Masala chai 250 g', 'Beverages', 12000),
  (4, 'Coconut oil 1 L',   'Oils',      21500),
  (5, 'Ragi flour 1 kg',   'Grains',     6800);

SELECT * FROM products;

┌────────────┬───────────────────┬───────────┬─────────────┐
│ product_id │       name        │ category  │ price_paise │
├────────────┼───────────────────┼───────────┼─────────────┤
│ 1          │ Basmati rice 1 kg │ Grains    │ 14999       │
│ 2          │ Toor dal 500 g    │ Pulses    │ 8950        │
│ 3          │ Masala chai 250 g │ Beverages │ 12000       │
│ 4          │ Coconut oil 1 L   │ Oils      │ 21500       │
│ 5          │ Ragi flour 1 kg   │ Grains    │ 6800        │
└────────────┴───────────────────┴───────────┴─────────────┘

The script has three statements, and each ends with a semicolon:

1. CREATE TABLE products (…) makes an empty table with four columns and says what each one holds.
2. INSERT INTO products … VALUES … adds five rows.
3. SELECT * FROM products asks for every column (*) of every row. The engine answers with a result, which is a table of its own, drawn here as a grid.

Read the grid like a spreadsheet: the top line has the column names, and each line under it is one row. Two details of this table come back again and again in this track:

• Prices are whole numbers of paise, and 100 paise make a rupee. Whole numbers add up exactly, which numbers with a fraction do not always do inside a computer, so this track keeps amounts of money that way.
• product_id is the table’s key. Every product has a number of its own, so product 3 is always the masala chai, even if two products one day have the same name. Keys are also how one table points to rows of another; the module on joins covers them properly.

Rows have no order of their own

The products came back in the order they were added, but SQL does not promise that. Unless a query asks for an order (with ORDER BY, in the next module), the engine may return the rows in whatever order is quickest for it. SQLite even has a switch for testing this: PRAGMA reverse_unordered_selects makes most queries without ORDER BY return their rows the other way round.

CREATE TABLE products (product_id INTEGER PRIMARY KEY, name TEXT NOT NULL);
INSERT INTO products (product_id, name) VALUES
  (1, 'Basmati rice 1 kg'),
  (2, 'Toor dal 500 g'),
  (3, 'Masala chai 250 g');

SELECT name FROM products;

-- A switch SQLite has for testing: most queries without ORDER BY now
-- return their rows the other way round.
PRAGMA reverse_unordered_selects = ON;

SELECT name FROM products;

┌───────────────────┐
│       name        │
├───────────────────┤
│ Basmati rice 1 kg │
│ Toor dal 500 g    │
│ Masala chai 250 g │
└───────────────────┘
┌───────────────────┐
│       name        │
├───────────────────┤
│ Masala chai 250 g │
│ Toor dal 500 g    │
│ Basmati rice 1 kg │
└───────────────────┘

The table and the query are the same both times; only the order changed. SQLite’s documentation explains why the switch exists: an order that stays the same from one run to the next tempts programs to rely on it, and they break when it changes. When the order matters, ask for it.

What SQL is

SQL, which stands for Structured Query Language, is the language of relational databases. With it you create tables, add, change and delete rows, and, most of all, ask questions about the data.

SQL is declarative: a query describes the result you want, not the steps to compute it. “The names of the products that cost less than 100 rupees” is a complete question in SQL; you do not say whether to read the whole table or look the prices up in an index, or which row to check first. A part of the engine called the query planner decides that. This is why adding an index can make a query much faster without a word of the query changing, as the module on indexes shows.

SQL statements come in a few families, and later modules of this track cover each one:

• Queries read data: SELECT.
• Data changes add, change and remove rows: INSERT, UPDATE and DELETE.
• Definitions create and change tables and other objects: CREATE TABLE, ALTER TABLE and DROP TABLE.
• Control groups changes into transactions (BEGIN, COMMIT, ROLLBACK) and grants permissions (GRANT, REVOKE).

One standard, many dialects

SQL is an international standard, ISO/IEC 9075, “Database Language SQL”. It is revised from time to time, and the edition that the PostgreSQL 18 manual compares itself with is SQL:2023. Engines implement much of it but not all: that manual counts at least 170 of the 177 mandatory “Core” features as supported, and notes that no engine claims full Core conformance. Every engine also adds features the standard does not have, such as its own functions and data types.

The SQL that one engine understands is its dialect. The basics in this module work the same in PostgreSQL, MySQL and SQLite, but details differ, even in arithmetic:

-- Two whole numbers, then a whole number and a decimal one.
SELECT 7 / 2 AS whole_numbers, 7 / 2.0 AS with_a_decimal;

┌───────────────┬────────────────┐
│ whole_numbers │ with_a_decimal │
├───────────────┼────────────────┤
│ 3             │ 3.5            │
└───────────────┴────────────────┘

SQLite divides one whole number by another to a whole number, cut toward zero, so 7 / 2 is 3. With a decimal point on either side, the answer keeps its fraction. PostgreSQL does the same with whole numbers, but in MySQL / keeps the fraction, so the same query there gives 3.5000. Whenever behaviour like this differs, this track says which engine does what, and every recorded output on a page names the engine that printed it.

Three engines, two designs

This track teaches the SQL of the three databases named most often in the 2025 Stack Overflow Developer Survey, by people who had done extensive development work with them in the year before: PostgreSQL (55.6% of the 26,083 who answered the database question), MySQL (40.5%) and SQLite (37.5%). The three are built in two different ways.

• PostgreSQL and MySQL are client-server engines. The database server is a program of its own, often on another computer, and it alone opens the data files. Applications connect to it and send SQL; the server checks who may do what, lets many users read and write at the same time, and sends the results back.
• SQLite is a library. It runs inside your application, with no server to install or configure, and a whole database is one ordinary file that you can copy, send or back up like any other. The SQLite project describes it as storage for one application or device, not a shared store of data for a whole organisation.

The next lesson shows how to run all three: SQLite in your browser and on your own computer, and PostgreSQL and MySQL on your computer.

Your first query

Every query starts with SELECT, and the simplest ones do not even need a table:

SELECT 'namaste' AS greeting, 2 + 3 AS total;

┌──────────┬───────┐
│ greeting │ total │
├──────────┼───────┤
│ namaste  │ 5     │
└──────────┴───────┘

• 'namaste' is text. Text in SQL goes between single quotes.
• 2 + 3 is an expression, and the engine works out its value.
• AS greeting and AS total give the result’s columns their names.
• The semicolon ends the statement.

A SELECT without FROM has no table to read, so it returns exactly one row; PostgreSQL, MySQL and SQLite all allow it. Press Run, then Edit: change the text, add a third column such as 10 * 4 AS forty, and run it again.

A common first mistake: text without quotes

Leave out the quotes and the word is no longer text:

-- The same greeting without its single quotes.
SELECT namaste AS greeting;

Parse error near line 2: no such column: namaste

Without quotes, SQLite reads namaste as a name, the name of a column, and there is no column called that, so it stops with an error. When you see “no such column” for a word you meant as text, look for missing single quotes. Putting text in double quotes is a different trap, which the lesson on SELECT and FROM explains.

Key takeaways

• A relational database stores data in tables. Each column has a name and a type, each row is one thing, and rows have no order unless a query asks for one.
• SQL describes the result you want; the engine’s query planner decides how to get it.
• The standard is ISO/IEC 9075, currently SQL:2023. Each engine speaks its own dialect of it, so this track names the engine whenever behaviour differs.
• PostgreSQL and MySQL are servers that applications connect to; SQLite is a library that keeps a whole database in one file.
• Text goes between single quotes; a word without quotes is read as a name.