Your First SELECT

What a SELECT asks for

A database holds tables. A table is a grid: named columns across the top, one row per thing it is about. Asking a database for data means naming the table and naming the columns you want out of it, and SELECT is how you write that.

CREATE TABLE birds (id INTEGER PRIMARY KEY, species TEXT, seen INTEGER);
INSERT INTO birds (species, seen) VALUES ('wren', 4), ('heron', 1);

SELECT species FROM birds;

species
-------
wren
heron
(2 rows)

Read it backwards and it makes more sense than left to right: FROM birds says where the rows come from, and species says which part of each row you want back. The (2 rows) line underneath is the playground counting the result for you; it is not part of your data.

SELECT * is the shortcut for "every column". It is genuinely useful while you are poking around an unfamiliar table, and a poor habit afterwards, because it hands you columns you never asked for and quietly changes what your query returns the day somebody adds one.

Two things about this playground

This is real SQLite running inside your browser, and every run starts with a brand-new, empty database. Nothing carries over from the last time you pressed Run, and nothing is saved anywhere.

That is why the editor already contains a CREATE TABLE and an INSERT: they build the data your query reads, in the same script. Statements run top to bottom, separated by semicolons, and only the ones that return columns print anything.

A worked example

Here is the same idea on a different table, so the one in the editor stays yours to finish:

CREATE TABLE plots (id INTEGER PRIMARY KEY, holder TEXT, crop TEXT, area_m2 INTEGER);
INSERT INTO plots (holder, crop, area_m2) VALUES
  ('Asha',  'garlic',   12),
  ('Bruno', 'potatoes', 30),
  ('Cleo',  'herbs',    7);

SELECT * FROM plots;

SELECT crop, holder FROM plots;

id | holder | crop     | area_m2
---+--------+----------+--------
 1 | Asha   | garlic   |      12
 2 | Bruno  | potatoes |      30
 3 | Cleo   | herbs    |       7
(3 rows)

crop     | holder
---------+-------
garlic   | Asha
potatoes | Bruno
herbs    | Cleo
(3 rows)

Two queries, so two tables, printed in the order the statements ran with a blank line between them.

Three rows either way — a plain SELECT with no conditions never adds or drops rows — but two columns instead of four, in the order the query asked for them. Notice as well that numbers are pushed to the right of their column and text to the left, which makes a value of the wrong type easy to spot.

Your turn

The editor holds a five-row tools table from a community tool library, and a query that asks for everything: SELECT * FROM tools;

Press Run before changing anything. You should see four columns and five rows.

Now rewrite that last line so it returns exactly two columns — the shelf first, then the name — and press Run again. Nothing above it needs touching.

Then try SELECT name, deposit FROM tools; just to see it work, and put your answer back.

If something goes wrong

If SQLite says no such column, check the spelling against the CREATE TABLE above: the columns are id, name, shelf and deposit, all lowercase.

If it says no such table: tools, the CREATE TABLE line has been changed or deleted. Every run here starts from an empty database, so that statement is what makes the table exist.

If you get five rows of the words shelf and name rather than the data, the column names have quotes round them. Single quotes make text; column names go bare.

Nothing here can break. The database is thrown away the moment the run finishes, so change it, run it, and change it again.