Your First SELECT

Time to talk to the database. This lesson teaches the one statement that starts everything — SELECT * FROM table; — and, just as importantly, how to read what comes back: the result grid, the row count, and your first error message (calmly). By the end, the playground stops being a demo and becomes your instrument.

Anatomy of a statement

The café database from the last two lessons is loaded below. Here's the whole first move:

SELECT * FROM menu_items;

 id |      name       | category | price | in_stock
----+-----------------+----------+-------+----------
  1 | Espresso        | coffee   |  2.50 | t
  2 | Cappuccino      | coffee   |  3.80 | t
  3 | Flat White      | coffee   |  4.00 | t
  4 | Cold Brew       | coffee   |  4.50 | f
  5 | Earl Grey       | tea      |  3.00 | t
  6 | Sencha          | tea      |  3.40 | f
  7 | Croissant       | pastry   |  3.20 | t
  8 | Cinnamon Roll   | pastry   |  4.10 | t
  9 | Banana Bread    | pastry   |  3.60 | t
 10 | Sparkling Water | drinks   |  2.20 | t
(10 rows)

Four pieces, left to right:

• SELECT — the verb. Give me data back. You'll also meet INSERT, UPDATE, and friends much later; for all of Part I, reading is the game.
• * — the shorthand for every column. Fine for exploring a table you've just met; lesson 2.1 explains why polished queries name their columns instead.
• FROM menu_items — which table to read.
• ; — the period at the end of the sentence. It's how the database knows your statement is complete — essential the moment a script has two statements. Habit from day one: every statement ends with one.

Two vocabulary notes that make everything else readable. SELECT and FROM are keywords — words the language owns. menu_items, name, price are identifiers — names you (or the schema's author) chose. SQL keywords are case-insensitive (select works fine), but there's a near-universal convention that makes queries scannable: keywords UPPERCASE, identifiers lowercase_snake_case. This course follows it everywhere, and so should your muscle memory.

Reading the grid

Everything the database says back has the same shape, so learn it once:

• The header row names the columns you asked for — with *, that's the table's schema, in order.
• Each row is one record that survived your question (here: all of them — no filter yet).
• The (10 rows) footer is not decoration. It's the first thing professionals check: is the count roughly what I expected? A join that returns 70,000 rows when you expected 700 is a bug announcing itself in the footer (a story for Section 5).
• Numbers arrive right-aligned, text left-aligned, and booleans as t/f — Postgres's compact spelling of true and false.

Try it — and try variations at the sql> prompt (SELECT * FROM menu_items again, any column names you can see in the schema bar):

Reading your first error without panic

Errors from the database are not scoldings — they're the parse step doing its job, and they're precise. Misspell the verb:

SELEC * FROM menu_items;

ERROR:  syntax error at or near "SELEC"
LINE 1: SELEC * FROM menu_items
        ^

Three lines, three gifts: what kind of problem (syntax error), where (LINE 1, with a caret pointing at the exact word), and the offending text itself. Now misspell the table instead — the grammar is fine, so this one survives parsing and fails when the database looks for a table that isn't there:

SELECT * FROM menu;

ERROR:  relation "menu" does not exist
LINE 1: SELECT * FROM menu
                      ^

("Relation" is the relational model's formal word for a table — lesson 1.2's vocabulary showing up in the wild.) The reading ritual, for every error this course will show you: first line for the what, caret for the where. Do that calmly and no error message will ever cost you more than a minute.

Comments

Two ways to leave notes the database ignores, both already in the playground above:

-- a line comment: everything after the two dashes is ignored
SELECT * FROM menu_items;  -- they work at the end of a line, too
 
/* a block comment,
   for longer explanations */
SELECT name, price FROM menu_items WHERE category = 'coffee';

That second query is sneak-previewing the next two lessons: naming your columns, and filtering rows with WHERE. You've already read its grid in your head — which means the instrument is working. On to choosing columns.