Lesson 03 · SQL

SELECT: asking questions

SELECT is how you ask a table a question. Follow one question, the two most expensive coffees under 4.00, through a café's drinks table. You write the clauses SELECT, FROM, WHERE, ORDER BY and LIMIT in that order, but the database runs them as FROM, WHERE, SELECT, ORDER BY and LIMIT. Watch each clause keep, choose or sort rows, and see why LIMIT without ORDER BY gives no promised answer.

12 min10 steps5 questionsBeginner

Fig 3 · Interactive

Loading figure…

or ← →

Check yourself

5 quick questions

Pick an answer to see why. Your score saves with your progress.

01In which order does the DBMS run the clauses of SELECT name, price FROM drinks WHERE kind = 'coffee' ORDER BY price DESC LIMIT 2?

The PostgreSQL SELECT reference describes this logical order: first FROM picks the rows, WHERE keeps the matching ones, SELECT chooses the columns, ORDER BY sorts and LIMIT keeps the first few. The planner may take shortcuts, but the answer always matches this order.

02Which row does WHERE kind = 'coffee' AND price < 4 keep?

AND keeps a row only when both tests are true. Flat white is a coffee and costs less than 4. Chai latte is cheap but is tea; Mocha is a coffee but costs 4.20.

03In PostgreSQL, how do you write the text value coffee in a WHERE clause?

A text value goes in single quotes. Double quotes make a name, such as a column name, and a bare word is also read as a name, so PostgreSQL would look for a column called coffee.

04With the figure's drinks, what does the full query return?

WHERE keeps four coffees under 4.00, ORDER BY price DESC puts them highest first (Latte 3.80, Flat white 3.70, Cappuccino 3.50, Espresso 2.50), and LIMIT 2 keeps the first two.

05What happens if you remove ORDER BY but keep LIMIT 2?

Without ORDER BY rows come back in whatever order the DBMS finds fastest. The PostgreSQL docs warn that LIMIT without ORDER BY gives an unpredictable subset of the rows.

Key terms

Glossary

SELECT statement: A SQL command that asks one or more tables a question and returns the answer as a small table of rows.
Clause: A part of a statement that starts with a keyword, such as FROM drinks or WHERE price < 4.
FROM: The clause that names the table to read. It supplies the rows every other clause works on.
WHERE: The clause that tests every row and keeps only the rows where the test is true.
Select list: The columns after the word SELECT. They decide which columns come back; * means every column.
ORDER BY: The clause that sorts the result rows. ASC (the default) sorts lowest first and DESC sorts highest first.
LIMIT: The clause that keeps only the first n rows of the result. It runs last, after ORDER BY.
String constant: A text value written in single quotes, such as 'coffee'.

Fine print

Notes and sources

FROM, WHERE, SELECT, ORDER BY, LIMIT is the logical order the PostgreSQL SELECT reference describes. The query planner may do the work in another way (for example using an index), but the answer is always the one this order gives.
In PostgreSQL, single quotes make a text value ('coffee') and double quotes make a name ("kind" is the column). SQLite accepts a double-quoted text value as a fallback, but do not rely on it.
If two rows had the same price, ORDER BY price would not say which comes first. Add a second sort column, such as ORDER BY price DESC, id, to make the order complete.
ORDER BY may sort by a column that SELECT dropped, such as ORDER BY id. The figure sorts by price, which the answer keeps.
The prices are a made-up café menu. numeric(4,2) stores exact decimals, so 3.80 stays 3.80. Python’s sqlite3 hands them back as floats, so the example formats them with two decimals.
The Python example uses the sqlite3 module that ships with Python. Run it and it prints Latte 3.80 and Flat white 3.70, one per line.

Quiz Unlocks after the last step.