Database Structure and Core Vocabulary

Read one column of a table

A database is a place that holds tables. Each table has a name, a set of named columns, and rows of data. This is the whole artists table of our small music store.

Read across one row to see one artist. Read down one column to see the same fact for every artist. Where a row meets a column there is one box, and that box holds one value.

The basic shape of a query that reads one column looks like this:

SELECT column FROM table;

So, to see the text of every artist and nothing else, write this:

SELECT name
FROM artists;

The five rows are still there, and the column you did not ask for is gone. You name the column first and the table second.

Read two columns in order

Each column also has a data type, which is the kind of value the column may store. In the albums table below, title stores text, while album_id and artist_id store whole numbers.

To read two columns, write both after SELECT with a comma between them.

SELECT title, artist_id
FROM albums;

The columns come back in the order you wrote them. The rows are the same six albums as before, because asking for fewer columns never removes a row.

Find the table that holds a fact

Each table holds one kind of thing. The artists table holds artists, the albums table holds albums, and the tracks table holds songs. Before writing a query, find the table whose columns hold the fact you need. These are five songs of the store:

How long a song lasts is stored in the milliseconds column, and only the tracks table has that column. The shape is the one you already know, and the table you choose goes after FROM:

SELECT column FROM table;

So this query reads the songs:

SELECT name, milliseconds
FROM tracks;

A column name only works in the table that has it. Asking the artists table for milliseconds fails, because that table has no such column.

Spot a missing value

Sometimes a box holds no value at all. That empty box is called null, and it means the value is missing or unknown. The store never wrote down who composed the song Balls to the Wall, so its composer box is empty. These are three columns of the same five songs:

The query asks for two of them in the usual way:

SELECT name, composer
FROM tracks;

The row is still there and the song is still shown. Only that one box is null. A null box is not the number zero and not an empty piece of text; the next block of the course works with null in full.

Find the key column

One column of a table is often used to point at a single row. In artists that column is artist_id: the numbers 1 to 5 are never repeated, so the value 3 belongs to one row and no other. A column used in this way is called a key.

That table has only two columns, so naming both of them brings back all of it, with the key first:

SELECT artist_id, name
FROM artists;

Two artists could one day share the same text in name, and then only the key value would tell the two rows apart. That is why the rest of the store points at an artist by the key number and not by the text.

Follow a column to another table

A table can point at another table by storing its key. In albums, the artist_id column holds the key of the artist who made that album. The shape of the query does not change:

SELECT album_id, artist_id
FROM albums;

Album 5 stores the value 3, so you look in artists for the row whose artist_id is 3, which is Aerosmith. The two tables stay separate and the shared number is the link between them. Reading both tables in one query is called a join, and it comes later in the course.

Key takeaways

• A database holds tables; a table has named columns and rows, and each box holds one value.
• SELECT names the columns you want, FROM names the table, and the columns come back in the order you wrote them.
• Each column has a data type, and a column name only works in the table that has it.
• An empty box is null: the row is still there, but that value is missing.
• A key column points at one row, and another table can reach that row by storing the same key value.