Tables, Rows, and Your First Query

Welcome

A database is a program whose whole job is to store data and answer questions about it. Your bank uses one to remember your balance. Instagram uses one to remember who follows whom. Almost every app you have ever used sits on top of a database.

The most common kind is a relational database, and it stores everything in tables. A table looks like a simple grid:

• Each column is one kind of fact, with a name. A pets table might have columns name, species, and age.
• Each row is one record: one pet, one user, one order.
• Each cell holds a single value, like 'Mochi' or 5.

That is the entire mental model. A database is a collection of tables, a table is rows and columns, and you talk to it with a language called SQL (Structured Query Language, often said "sequel").

Running SQL in this course

Every code block in this course is real SQL for SQLite, a small, real database engine. Each block is self-contained: it creates a table, fills it with rows, then queries it. Three statements to recognize:

• CREATE TABLE makes a new empty table and names its columns.
• INSERT INTO ... VALUES (...) adds one row.
• SELECT ... FROM ... asks a question and prints the answer.

Every statement ends with a semicolon ;. Text values go in single quotes ('Mochi'), numbers do not (5).

One thing about the output: each result row is printed on its own line, with a | between values and no header row. So the row for Mochi prints as Mochi|cat|5.

Read the code below. SELECT * FROM pets means "give me every column of every row" (* is shorthand for all columns).

A table from scratch, then a query

The three statements in order: build a pets table, add three rows, then select everything. Match the output against the grid in the figure above.

CREATE TABLE only names the columns and their types, it stores no data. Each INSERT INTO adds exactly one row. SELECT * FROM pets then reads them all back, in the order they were inserted.

CREATE TABLE pets (
  name TEXT,
  species TEXT,
  age INTEGER
);

INSERT INTO pets VALUES ('Biscuit', 'dog', 3);
INSERT INTO pets VALUES ('Mochi', 'cat', 5);
INSERT INTO pets VALUES ('Ziggy', 'parrot', 2);

SELECT * FROM pets;

Output

Biscuit|dog|3
Mochi|cat|5
Ziggy|parrot|2

Naming the four pieces

In the pets table, ('Mochi', 'cat', 5) is a row, one complete record describing a single pet.

The four words are worth keeping straight, because the rest of the course leans on them:

A row cuts across all the columns, a column runs down through all the rows.

Adding a fourth row

The same script with one more INSERT, so the table also holds a 1-year-old dog named Rex, and the same SELECT at the end so all four rows print.

Rex appears last in the output because he was inserted last. Nothing in the script asks for an order, and without an explicit ORDER BY a database makes no promise about the order rows come back in, so do not rely on insertion order once tables get large.

CREATE TABLE pets (
  name TEXT,
  species TEXT,
  age INTEGER
);

INSERT INTO pets VALUES ('Biscuit', 'dog', 3);
INSERT INTO pets VALUES ('Mochi', 'cat', 5);
INSERT INTO pets VALUES ('Ziggy', 'parrot', 2);

INSERT INTO pets VALUES ('Rex', 'dog', 1);

SELECT * FROM pets;

Output

Biscuit|dog|3
Mochi|cat|5
Ziggy|parrot|2
Rex|dog|1

Three rules the new INSERT obeys

• Text values need single quotes, as in 'Rex' and 'dog'. The age 1 is a number and takes no quotes. SQL uses single quotes for text, not the double quotes you would reach for in Python or JavaScript.
• The values must appear in column order, the order set by CREATE TABLE, so ('Rex', 'dog', 1). Swapping two of them would happily store a species named Rex.
• Every statement ends with a semicolon, which is how the engine knows one statement has finished and the next has begun.