Your first query, line by line

Type this at the practice=# prompt and press Enter.

SELECT title, price FROM books;

You get twenty rows, two columns. Now let us take it apart, because these few words contain most of what a query is.

FROM books

Which table to read. Everything else operates on the rows this produces. Reading a query, FROM is the first thing to look at even though it is written second — it tells you what the rest is talking about.

SELECT title, price

Which columns you want back, and in which order they appear. Swap them and the output columns swap:

SELECT price, title FROM books;

To see every column, use *:

SELECT * FROM books;

The semicolon

It ends the statement. psql waits for it, which is why a forgotten semicolon leaves you staring at a prompt that has changed to practice-# and appears frozen. It is not frozen; it is waiting for you to finish the sentence. Type ; and press Enter.

Case and whitespace do not matter

These are all the same query:

SELECT title FROM books;
select title from books;
SELECT   title
FROM     books;

The convention in this course — and most codebases — is keywords in capitals and your own names in lower case, because it makes the shape of a query visible at a glance. Once queries get longer, put each clause on its own line. Your future self will thank you.

Now break it on purpose

Run this:

SELECT title FROM book;

Postgres replies:

ERROR:  relation "book" does not exist

"Relation" is the formal word for a table. So this says: I looked for a table called book and there isn't one. The table is books, plural.

Now try a column that does not exist:

SELECT titel FROM books;

ERROR:  column "titel" does not exist
LINE 1: SELECT titel FROM books;
               ^

Note the caret pointing at the exact spot. Postgres error messages are unusually good, and reading them properly is a skill worth building now rather than later. Most of the time the message tells you precisely what is wrong; the instinct to panic and re-read your whole query is what wastes the time.

Try these before moving on

1. List every author's name.
2. List the title, genre and published year of every book.
3. Show every column of the customers table.
4. Deliberately misspell a column name and read the error. Where does the caret point?