- SELECT
- FROM
- WHERE
- ORDER BY
- LIMIT
SELECT — Retrieve, Filter, Sort
Scene: 8:55am, five minutes before the desk opens. The head of trading leans over: "Pull me AAPL's last ten closes — newest on top. I want to eyeball whether last night's data actually loaded." You have one table with five tickers and two years of daily prices stacked together. Your job is to carve out exactly the AAPL rows, put the freshest date first, and stop at ten. Three tiny clauses do all of that. This is the single most common thing a data analyst does, and by the end of this lesson it will feel automatic.
🧠 The mental model: a librarian, not a magician
Think of `equity_prices` as a giant filing cabinet where every drawer is jumbled together — AAPL next to SPY next to JPM, in no useful order. A SELECT query is you instructing a very literal librarian:
- ›FROM — which cabinet? (`equity_prices`)
- ›SELECT — which fields do I want copied onto the index card? (`trade_date, close_price, volume`)
- ›WHERE — only pull the folders matching this label (`ticker = 'AAPL'`)
- ›ORDER BY — hand them to me sorted this way (newest date first)
- ›LIMIT — just the top ten, I'm in a hurry
The librarian never guesses. Leave out WHERE and you get every ticker. Leave out ORDER BY and the rows come back in whatever order they happen to sit on disk — usually oldest-first, which is the opposite of what a trader wants.
The four clauses, in the order they run
SQL does not execute top-to-bottom the way you read it. The librarian works in this order:
| Step | Clause | What it does | Our value |
|---|---|---|---|
| 1 | `FROM` | pick the table | equity_prices |
| 2 | `WHERE` | throw away non-matching rows | ticker = 'AAPL' |
| 3 | `SELECT` | keep only chosen columns | trade_date, close_price, volume |
| 4 | `ORDER BY` | sort what survived | trade_date DESC |
| 5 | `LIMIT` | cut to the top N | 10 |
Two details that trip up beginners:
- ›Strings need single quotes. `ticker = 'AAPL'` is correct; `ticker = AAPL` (no quotes) makes SQL hunt for a column named AAPL and error out. Double quotes mean something different again — always use single quotes for text values.
- ›DESC vs ASC. `ORDER BY trade_date` sorts ascending (oldest → newest) by default. Add `DESC` to flip it: newest → oldest. For a trader confirming last night's data, newest-first is the only order that makes sense.
Table: equity_prices
``` ticker | trade_date | open_price | high_price | low_price | close_price | volume -------+------------+------------+------------+-----------+-------------+---------- AAPL | 2022-01-03 | 177.83 | 182.88 | 177.71 | 182.01 | 104487900 MSFT | 2022-01-03 | 296.24 | 302.53 | 295.05 | 300.35 | 29382100 JPM | 2022-01-03 | 158.29 | 159.55 | 157.36 | 158.74 | 10502700 ... | ... | ... | ... | ... | ... | ... ```
Your Task
The starter already writes the hard parts — `SELECT trade_date, close_price, volume` and `LIMIT 10`. But the placeholder has no ticker filter and sorts oldest-first, so it hands back a mess: 2022 rows from all five tickers. Your job is the pedagogical heart of the query — the WHERE + ORDER BY block:
- ›A WHERE line that keeps only `ticker = 'AAPL'` (mind the single quotes).
- ›An ORDER BY trade_date line ending in DESC so the newest close sits on top.
Predict before you run: the data ends in December 2023. If your filter and sort are right, what year and month should appear in the very first row? (Hint: newest AAPL date.) Run the broken placeholder first and watch it return 2022 rows from every ticker — then fix the block and watch that top row jump forward to late 2023, AAPL only. That flip is the whole lesson.
💡 Finance tip: Analysts always verify the most recent price first — a stale top row means last night's data feed failed, and every downstream P&L number is wrong.