SELECT is the verb that the SQL uses for reading. It lists what you want, it says where the rows come from, and it hands back a table. A statement is a few hard-ordered clauses, a semicolon, and case-insensitive keywords. The two mistakes that almost everybody makes first have their receipts below.
The previous lesson in this module covered "How Do You Set Up a Practice Database Locally?", and the next lesson in this module is about "How Do You Choose Columns and Rename Output?".
What does a complete SELECT statement look like?
Take a small tool shop table as today's subject. Six rows are enough to see every shape that a result can take. The comment about the weight in grams is plain text to the engine, and it is purely there for the human reader.
CREATE TABLE tools (
id INTEGER PRIMARY KEY,
name TEXT,
weight REAL, -- grams, as sold
price REAL,
stock INTEGER
);
INSERT INTO tools (name, weight, price, stock) VALUES
('Ball-peen hammer', 480, 17.90, 22),
('Torpedo level', 210, 12.60, 15),
('Pliers set', 640, 24.30, 11),
('Chalk line', 95, 8.95, 30),
('Caulking gun', 380, 11.20, 18),
('Wire strippers', 150, 9.40, 26);Around that data, the plainest readable query in the language runs and it answers with everything, borrowing the same * shorthand that you saw for a full dump.
SELECT * FROM tools;id name weight price stock
-- ---------------- ------ ----- -----
1 Ball-peen hammer 480.0 17.9 22
2 Torpedo level 210.0 12.6 15
3 Pliers set 640.0 24.3 11
4 Chalk line 95.0 8.95 30
5 Caulking gun 380.0 11.2 18
6 Wire strippers 150.0 9.4 26Read the statement in the order that the engine does. The word SELECT names the columns to project, the word FROM names the table to read, and the semicolon says that the statement is complete. Those two clause positions are fixed. Swapping the clause order is always an error, while the keyword case is never one.
select name, stock from tools where stock >= 26;name stock
-------------- -----
Chalk line 30
Wire strippers 26You can see the lower-case keywords and the mixed-case table names in one single query, and that is one result. The conventional style keeps the keywords in capitals so that the eye can find the verbs quickly. That is a matter of taste and of agreement, and not a matter of grammar.
The comparison with 26 filtered the six rows down to two. Module 2 dissects the filters clause by clause, so you can treat this one as a preview that shows you that computing from a table can apply the conditions too.
How do you ask for exactly the columns that you need?
The real reports almost never want everything. You name the columns, and the engine projects just them. The order that you give is the order that comes back.
SELECT name, weight FROM tools;name weight
---------------- ------
Ball-peen hammer 480.0
Torpedo level 210.0
Pliers set 640.0
Chalk line 95.0
Caulking gun 380.0
Wire strippers 150.0Every SELECT produces a brand new result table, and the tools table itself never changed. That read-only character is what makes the SELECT safe to run all day in the production. The worst outcome is a slow report and never a lost row.
You are also free to list the columns in any order. The analytics browsers love the reversed order, because they show the counts before the labels.
SELECT stock, price, name FROM tools LIMIT 3;stock price name
----- ----- ----------------
22 17.9 Ball-peen hammer
15 12.6 Torpedo level
11 24.3 Pliers setThe LIMIT 3 kept the demo short, and lesson 9 gives it a full treatment. The important habit to build now is this one. Your projected order rules your output order, so you pick it consciously.
What errors ambush most of the beginners in the first hour?
Two stand out, and both of them fail in a misleadingly quiet way.
The first one is a forgotten comma. You put a space where the comma belongs, and the engine assumes that you named a column alias. The name price quietly becomes the name column, renamed price.
SELECT name price
FROM tools;price
----------------
Ball-peen hammer
Torpedo level
Pliers set
Chalk line
Caulking gun
Wire strippersNotice the header. The engine did not fetch a missing price column. It renamed name to price. One missing comma, and the report lies. Lesson 4 explains the aliases in full precisely because that trap behaves like a feature.
The second error is the semicolon. A single statement without one still runs, because the shell reads to the end of the input. You put two statements in, and the first one without a terminator hits the second one, and the engine gives up.
Parse error near line 1: near "SELECT": syntax error
SELECT id FROM tools SELECT name FROM tools
^--- error hereYou end every statement with a semicolon and both of the failure modes vanish. The interactive SQL editors in the web consoles, MySQL Workbench and the PostgreSQL psql all read one statement at a time in the same way, so the habit transfers intact.
How do you catch these two mistakes early?
You read the queries aloud using the engine's order. You say "select these columns, from this table". Then you run the smallest version first.
There is a trick that works surprisingly well. You delete half of your query, you run it, you confirm that it works, and then you restore one clause at a time. The alias trap announces itself in the output header, so you glance at the headers before you celebrate the data. The semicolon trap announces itself only when the statements stack, so you make one statement the unit of work for ever.
Quick reference
- A SELECT has SELECT, then FROM, then optionally more clauses, and it ends in a semicolon.
- The keywords are case-insensitive. You capitalise them by convention, so that the structure stays visible.
- A SELECT without a FROM-listed change can read but it can never modify, so the reads are safe to repeat.
- A missing comma silently renames a column, and a missing semicolon should fail loudly when more statements follow.
Practice: write three queries against the tools table. One that projects every column, one that projects only the name and the price, and one, deliberately, with a missing comma so that the header shows the alias effect. You re-run the two-statement parse error and you match the engine's offending position marker against your own statement.