A language for asking questions
Imagine you keep a small library's catalogue in a program. To answer "which books in Spanish do we have more than one copy of, newest first?", you would write a loop over a list, an if inside it, and a sort at the end. Every new question means a new loop. If the catalogue grows to ten million books, you also need to decide how to find the Spanish ones quickly without reading every record, and what to do when two librarians edit the catalogue at the same moment.
SQL (Structured Query Language) takes a different approach. You state the result you want:
SELECT title, year
FROM book
WHERE language = 'Spanish' AND copies > 1
ORDER BY year DESC;and the database decides how to produce it. Whether it scans the whole table, uses an index, or reads data from memory or from disk is its business, not yours. This style is called declarative: the program declares what is wanted, not the steps to compute it. A Python loop is imperative: it lists the steps.
Declarative does not mean magic. You still need to know what the query means precisely, and later in this course you will learn to read the plan the database chooses, so that you can help it when it chooses badly. But the separation is powerful. The same query keeps working, unchanged, when the table grows a thousandfold, when someone adds an index, or when the data moves to a different machine. Fifty years after it was invented, SQL is still the way most of the world's records are stored and questioned, from phone apps to banks to astronomical surveys.
From Codd's relations to today's standard
In 1970 Edgar F. Codd, a mathematician at IBM's research laboratory in San Jose, published "A Relational Model of Data for Large Shared Data Banks". Databases of the time made programmers navigate from record to record along stored pointers, so programs broke whenever the storage layout changed. Codd proposed storing data as mathematical relations (tables) and asking questions with a language based on logic, leaving the storage layout to the system. He called this goal data independence.
At IBM, a team built a prototype of Codd's ideas called System R. For it, Donald Chamberlin and Raymond Boyce designed a query language that read more like English than Codd's mathematical notation, described in 1974 as SEQUEL. The name was later shortened to SQL because of a trademark conflict, which is why people still say both "S-Q-L" and "sequel". Both pronunciations are fine.
The first commercial SQL database was sold in 1979 by the company that became Oracle; IBM followed with SQL/DS and DB2. SQL became an ANSI standard in 1986 and an ISO standard (ISO/IEC 9075) in 1987. The standard has been revised many times since. A few milestones you will meet in this course:
| Revision | Notable additions |
|---|---|
| SQL-92 | Explicit JOIN syntax, CASE, much of everyday SQL as we know it |
| SQL:1999 | Recursive queries (WITH RECURSIVE), triggers, booleans |
| SQL:2003 | Window functions, MERGE, sequences |
| SQL:2011 | Temporal (time-versioned) tables |
| SQL:2016 | JSON functions, row pattern recognition |
| SQL:2023 | A JSON data type and property-graph queries |
No product implements the whole standard, and every product adds its own extensions, so in practice SQL comes in dialects. The ones you are most likely to meet are:
- SQLite (since 2000): a library linked into the program that uses it, storing a whole database in one file. It runs inside every Android and iOS phone, every major web browser and countless desktop programs, and is probably the most widely deployed database engine.
- PostgreSQL: a server database descended from the Postgres project at the University of California, Berkeley; SQL support arrived in 1995. It follows the standard closely and is the usual choice for new server applications.
- MySQL (1995) and its fork MariaDB: server databases long popular for websites.
- Microsoft SQL Server (its dialect is called T-SQL) and Oracle Database (PL/SQL for its procedural code), common in large companies.
- Analytical engines such as DuckDB, BigQuery and Snowflake, built for scanning billions of rows.
This course teaches standard SQL and runs every example on SQLite, because it needs no server and the test suite can run each program in a fresh, empty database. Where PostgreSQL, MySQL and SQLite differ, the lesson says so and shows the other versions in plain code blocks.
The relational model in five words
Definition— Relation, tuple, attribute, domain
A relation is a set of tuples that all have the same attributes. Each attribute has a name and a domain, the set of values it may take (whole numbers, dates, short strings). In SQL the relation is a table, a tuple is a row, and an attribute is a column with a type. The schema of a table is its name and the list of its columns with their types.
Definition— Key
A key is a set of attributes whose values identify each tuple uniquely: no two tuples may agree on all of them. A table may have several candidate keys; the one chosen as the main identifier is the primary key. A foreign key is a column whose values must appear as keys in another table, which is how relations refer to each other.
Because a relation is a set, the mathematical model has two properties that matter every day:
- Tuples have no order. There is no "first row" of a relation. A query result comes in a defined order only if you ask for one with
ORDER BY. - There are no duplicate tuples. A set cannot contain the same element twice.
SQL departs from the pure model in three ways, deliberately or not. Tables may contain duplicate rows unless a key forbids them (SQL tables are multisets, also called bags). Columns are ordered, so SELECT * returns them in the order they were declared. And SQL has NULL, a marker for a missing value, which brings its own logic; the third lesson is about it. Codd himself criticised some of these choices, and the debate continues, but they are the language as it is. Knowing where SQL agrees with the mathematics is what lets you reason about queries rather than guess.
If you have studied sets and first-order logic, you already know the theory: a query is a description of a set, written with conditions that are true or false of each row.
Installing SQLite and using the shell
SQLite is a C library, and the sqlite3 shell is a small program that lets you type SQL at it. On macOS and most Linux distributions it is already installed; type sqlite3 --version in a terminal to check. Otherwise, download the precompiled "bundle of command-line tools" for your system from sqlite.org/download.html and put sqlite3 somewhere on your PATH. If you would rather not install anything yet, the official SQLite Fiddle runs the same engine in a browser tab.
Start the shell with a file name and it opens that database, creating the file if it does not exist:
$ sqlite3 library.db
SQLite version 3.37.2 2022-01-06 13:25:41
Enter ".help" for usage hints.
sqlite>The special name :memory: opens a temporary database that lives only in memory and disappears when the shell exits. Inside the shell you type two different kinds of thing:
- SQL statements, such as
SELECT 1 + 1;. A statement may span several lines and ends with a semicolon. Until you type the semicolon, the prompt changes to...>to show it is waiting for more. - Dot-commands, such as
.tables,.schema book,.mode column,.headers on,.read script.sqland.quit. These are instructions to the shell program, not SQL. They must start at the beginning of a line, take no semicolon, and do not exist in any other database..modeand.headerschange how results are printed: the default "list" mode separates columns with|, while "column" mode lines them up under headings.
You can also feed the shell a whole script, which is how every checked program in this course is run:
sqlite3 -bail :memory: < main.sqlThat means: open a fresh in-memory database, read statements from main.sql, print each query's results, and stop with an error status at the first error (-bail). Each program you see therefore starts from an empty database, creates its own tables and fills them.
Other databases have their own shells with their own commands. PostgreSQL's is psql (its meta-commands start with a backslash: \dt lists tables, \d book describes one), MySQL's is mysql (where SHOW TABLES; and DESCRIBE book; are SQL statements). Graphical tools such as DBeaver or the database panels in code editors do the same job with a window around it. Professionals use both: a shell for quick questions and scripts, a graphical tool for exploring an unfamiliar schema.
Your first table and queries
Here is a complete program. It creates a table for a library's catalogue, inserts eight books, and asks for two columns of every row.
-- A small library catalogue. Lines starting with -- are comments.
CREATE TABLE book (
id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
author TEXT NOT NULL,
year INTEGER,
language TEXT,
copies INTEGER
);
INSERT INTO book (id, title, author, year, language, copies) VALUES
(1, 'Don Quixote', 'Miguel de Cervantes', 1605, 'Spanish', 2),
(2, 'Pride and Prejudice', 'Jane Austen', 1813, 'English', 3),
(3, 'War and Peace', 'Leo Tolstoy', 1869, 'Russian', 1),
(4, 'Gitanjali', 'Rabindranath Tagore', 1910, 'Bengali', 1),
(5, 'Ficciones', 'Jorge Luis Borges', 1944, 'Spanish', 2),
(6, 'Things Fall Apart', 'Chinua Achebe', 1958, 'English', 4),
(7, 'Season of Migration to the North', 'Tayeb Salih', 1966, 'Arabic', 1),
(8, 'One Hundred Years of Solitude', 'Gabriel García Márquez', 1967, 'Spanish', 3);
SELECT title, year FROM book;Don Quixote|1605
Pride and Prejudice|1813
War and Peace|1869
Gitanjali|1910
Ficciones|1944
Things Fall Apart|1958
Season of Migration to the North|1966
One Hundred Years of Solitude|1967Three statements, three ideas:
CREATE TABLEdefines a schema: the table's name and, for each column, a name, a type and optionally constraints.INTEGER PRIMARY KEYmakesidthe key;NOT NULLsays a book must have a title and an author. The next lesson covers types and constraints properly.INSERT INTO … VALUESadds rows. Naming the columns explicitly, as here, keeps the statement correct even if someone later adds a column to the table. Strings are written in single quotes.SELECT … FROM …is a query. It returns a new table, here with two columns, which the shell prints. In the default list mode the columns are separated by a vertical bar and there are no headings.
The output came back in the order the rows were inserted. That is an accident of how SQLite stores this small table, not a promise: without ORDER BY, a database may return rows in any order, and real databases do change the order when tables grow, indexes appear or queries run in parallel.
Expressions, aliases, filtering and sorting
The SELECT list can hold any expression, not just column names, and AS gives a result column a name (an alias). WHERE keeps only the rows for which a condition is true, ORDER BY sorts, and LIMIT caps the number of rows. The shell commands at the top switch to column mode, which also turns headings on.
.mode column
.headers on
CREATE TABLE book (
id INTEGER PRIMARY KEY, title TEXT NOT NULL, author TEXT NOT NULL,
year INTEGER, language TEXT, copies INTEGER
);
INSERT INTO book VALUES
(1, 'Don Quixote', 'Miguel de Cervantes', 1605, 'Spanish', 2),
(2, 'Pride and Prejudice', 'Jane Austen', 1813, 'English', 3),
(3, 'War and Peace', 'Leo Tolstoy', 1869, 'Russian', 1),
(4, 'Gitanjali', 'Rabindranath Tagore', 1910, 'Bengali', 1),
(5, 'Ficciones', 'Jorge Luis Borges', 1944, 'Spanish', 2),
(6, 'Things Fall Apart', 'Chinua Achebe', 1958, 'English', 4),
(7, 'Season of Migration to the North', 'Tayeb Salih', 1966, 'Arabic', 1),
(8, 'One Hundred Years of Solitude', 'Gabriel García Márquez', 1967, 'Spanish', 3);
-- Expressions and aliases: how old is each book in 2026?
SELECT title, 2026 - year AS age
FROM book
WHERE language = 'Spanish'
ORDER BY age DESC;
-- The three books with the most copies; ties broken by title.
SELECT title, copies
FROM book
ORDER BY copies DESC, title
LIMIT 3;
-- A SELECT without FROM evaluates expressions once.
SELECT 7 / 2 AS int_div, 7 / 2.0 AS real_div, 'Lagos' || ', ' || 'Nigeria' AS place;title age
----------------------------- ---
Don Quixote 421
Ficciones 82
One Hundred Years of Solitude 59
title copies
----------------------------- ------
Things Fall Apart 4
One Hundred Years of Solitude 3
Pride and Prejudice 3
int_div real_div place
------- -------- --------------
3 3.5 Lagos, NigeriaA few details to notice. ORDER BY sorts ascending unless you write DESC, and you can list several keys: rows that tie on the first are ordered by the second. || is the standard operator that joins strings. And 7 / 2 is 3: when both sides are integers, SQLite and PostgreSQL do integer division, while MySQL's / always gives a decimal result (3.5000); write 7 / 2.0, or convert with CAST(7 AS REAL), when you want a fraction everywhere.
LIMIT is not in the standard, though SQLite, PostgreSQL and MySQL all accept it. The standard spelling, which PostgreSQL, Oracle and SQL Server understand, is:
SELECT title, copies FROM book
ORDER BY copies DESC, title
FETCH FIRST 3 ROWS ONLY;The order a query is evaluated in
A SELECT statement is written in one order but means something in another. Logically, the database:
- takes the table or tables in
FROM, - keeps the rows that satisfy
WHERE, - groups rows (
GROUP BY) and filters groups (HAVING), which lesson 4 covers, - computes the
SELECTlist, - removes duplicates if you wrote
SELECT DISTINCT, - sorts with
ORDER BY, - and finally applies
LIMITorFETCH FIRST.
This explains rules that otherwise look arbitrary. ORDER BY age works because sorting happens after the SELECT list has defined age. In standard SQL and PostgreSQL, WHERE age > 100 fails, because WHERE runs before the alias exists; you must repeat the expression or use a subquery. (SQLite and MySQL bend this rule and accept the alias in WHERE, which is convenient but not portable.) The order is logical: the database may physically do things differently, as long as the result is the same.
Note
SQL keywords are not case-sensitive: select, SELECT and Select are the same. Most teams write keywords in capitals and names in lower case, as in this course, because it makes long queries easier to scan, and use a formatter to keep everyone consistent. String contents are a different matter: whether 'spanish' = 'Spanish' is true depends on the database and its collation (its rules for comparing text). In SQLite and PostgreSQL it is false by default; in MySQL, with its usual collations, it is true.
Quotes, names and other first surprises
Misconception
"Double quotes and single quotes are interchangeable, as in Python." In standard SQL, single quotes make a string and double quotes make an identifier, a name of a table or column. "year" is the column; 'year' is four letters. Double quotes let you use names that would otherwise be illegal, such as "order" (a keyword) or "first name" (with a space). SQLite, for compatibility with old code, treats "hello" as a string if no column called hello exists, which hides typos until the day someone adds that column. MySQL treats double quotes as strings by default and uses backticks, `order`, for identifiers. Always use single quotes for strings.
The program below shows the difference, and how to put an apostrophe inside a string: write it twice.
CREATE TABLE "order" (id INTEGER PRIMARY KEY, "first name" TEXT);
INSERT INTO "order" VALUES (1, 'Ngozi'), (2, 'Seán');
SELECT "first name" FROM "order" ORDER BY id;
SELECT 'first name' FROM "order" ORDER BY id;
SELECT 'O''Connor''s bookshop' AS shop;Ngozi
Seán
first name
first name
O'Connor's bookshopThe first query reads the column; the second returns the same string literal once per row. Naming a table order was only to make the point. In real schemas choose names that never need quoting: lower case, words joined with underscores (first_name, book_order).
Misconception
"SELECT * is the normal way to query." It is handy at the shell for a quick look, but in programs and saved queries, list the columns you need. SELECT * returns columns you do not use (which costs time and memory), and its result changes shape when someone adds or reorders columns, which can silently break code that reads columns by position.
Misconception
"Without ORDER BY the rows come out in insertion order." Often they do in a small SQLite table, which is exactly what makes the assumption dangerous. The relational model has no row order, and no database promises one without ORDER BY. If the order matters, say so.
How professionals work with SQL
Most SQL in the world is not typed into a shell. It lives in application code, sent to the database through a driver (Python's sqlite3 and psycopg, Java's JDBC, Go's database/sql) with parameters for the values that change: SELECT title FROM book WHERE language = ?. The driver sends the value separately from the query text, so a malicious value cannot change the query's meaning. Building queries by pasting user input into strings is the cause of SQL injection, still one of the most common security flaws on the web. You will see parameters again in the project.
SQL also lives in files under version control: migrations that create and alter tables step by step, and analytical queries that run on a schedule and feed dashboards. Data analysts and data engineers may write more SQL than any other language. Wherever it lives, good SQL reads like good prose: one clause per line, consistent capitals, meaningful aliases, and comments that say why.
The habits worth starting today:
- Name the columns in
INSERTand inSELECT. - Use single quotes for strings and never rely on SQLite's double-quote fallback.
- Add
ORDER BYwhenever order matters, and a tie-breaking key when values can tie. - Keep a small script that builds a sample database, like the programs in this lesson, so you can experiment freely and start again from nothing.
Exercises
Use the sqlite3 shell or SQLite Fiddle to try your answers, but think first: predicting what a query returns is the skill.
1. First steps: relations and your first queries
Warm-upHumanWhat does it mean to say that SQL is a declarative language?
2. First steps: relations and your first queries
Warm-upComputationalWhat does this program print? Write the lines separated by single spaces.
CREATE TABLE stall (name TEXT, city TEXT, rating INTEGER);
INSERT INTO stall VALUES
('Mama Put', 'Ibadan', 4),
('Taco Lindo', 'Puebla', 5),
('Pho Hoa', 'Hue', 3),
('Chai Point', 'Pune', 5);
SELECT name FROM stall WHERE rating >= 4 ORDER BY rating DESC, name LIMIT 2;
3. First steps: relations and your first queries
CoreComputationalUsing the eight-book book table from the lesson (Don Quixote 1605 with 2 copies, Pride and Prejudice 1813 with 3, War and Peace 1869 with 1, Gitanjali 1910 with 1, Ficciones 1944 with 2, Things Fall Apart 1958 with 4, Season of Migration to the North 1966 with 1, One Hundred Years of Solitude 1967 with 3), how many rows does this query return?
SELECT title FROM book WHERE copies >= 2 AND year < 1950;
4. First steps: relations and your first queries
CoreComputationalIn the sqlite3 shell's default list mode, what does this statement print? Write it exactly as the shell would.
SELECT 17 / 5, 17 % 5, 17 / 5.0;
5. First steps: relations and your first queries
CoreComputationalWhat does this program print in SQLite? Write the lines separated by single spaces.
CREATE TABLE t (word TEXT);
INSERT INTO t VALUES ('tea'), ('coffee');
SELECT 'word' FROM t;
SELECT word || '!' FROM t WHERE word = 'tea';
6. First steps: relations and your first queries
Warm-upHumanWhich of these are commands to the sqlite3 shell rather than SQL statements? Choose all that apply.
7. First steps: relations and your first queries
CoreHumanThis query works in SQLite but fails in PostgreSQL with an error saying the column age does not exist. Why?
SELECT title, 2026 - year AS age
FROM book
WHERE age > 100;
Further reading and resources
- A Relational Model of Data for Large Shared Data Banks · E. F. Codd · 1970The paper that introduced the relational model. Short and still readable.
- Command Line Shell For SQLiteEvery dot-command of the sqlite3 shell, with examples.
- SQLite download page
- SQLite FiddleSQLite running in the browser, for trying queries without installing anything.
- PostgreSQL tutorial: The SQL LanguageThe PostgreSQL manual's own gentle introduction, useful once you install a server database.