Skip to content
LumenaryAcademy
Foundations 55 min

First steps: relations and your first queries

Where SQL came from, the relational model it rests on, the sqlite3 shell, and your first CREATE, INSERT and SELECT.

Code tested · 3AI-draftedAwaiting review by a computer scientist
MotivationH

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:

sql
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.

History & global context

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:

RevisionNotable additions
SQL-92Explicit JOIN syntax, CASE, much of everyday SQL as we know it
SQL:1999Recursive queries (WITH RECURSIVE), triggers, booleans
SQL:2003Window functions, MERGE, sequences
SQL:2011Temporal (time-versioned) tables
SQL:2016JSON functions, row pattern recognition
SQL:2023A 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.

DefinitionsH

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:

  1. 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.
  2. 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.

ComputationC

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:

text
$ 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.sql and .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. .mode and .headers change 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:

bash
sqlite3 -bail :memory: < main.sql

That 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.

Worked examplesH

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.

sql
-- 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;
text
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|1967

Three statements, three ideas:

  • CREATE TABLE defines a schema: the table's name and, for each column, a name, a type and optionally constraints. INTEGER PRIMARY KEY makes id the key; NOT NULL says a book must have a title and an author. The next lesson covers types and constraints properly.
  • INSERT INTO … VALUES adds 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.

sql
.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;
text
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, Nigeria

A 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:

sql
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:

  1. takes the table or tables in FROM,
  2. keeps the rows that satisfy WHERE,
  3. groups rows (GROUP BY) and filters groups (HAVING), which lesson 4 covers,
  4. computes the SELECT list,
  5. removes duplicates if you wrote SELECT DISTINCT,
  6. sorts with ORDER BY,
  7. and finally applies LIMIT or FETCH 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.

Common misconceptionsH

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.

sql
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;
text
Ngozi
Seán
first name
first name
O'Connor's bookshop

The 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.

ExplanationH

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 INSERT and in SELECT.
  • Use single quotes for strings and never rely on SQLite's double-quote fallback.
  • Add ORDER BY whenever 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

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-upHuman

What does it mean to say that SQL is a declarative language?

Choose one

2. First steps: relations and your first queries

Warm-upComputational

What 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

CoreComputational

Using 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

CoreComputational

In 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

CoreComputational

What 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-upHuman

Which of these are commands to the sqlite3 shell rather than SQL statements? Choose all that apply.

Choose all that apply

7. First steps: relations and your first queries

CoreHuman

This 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;
Choose one

Further reading and resources