Choosing columns, and AS

The star was training wheels. In lesson 1 you asked for every column because you had not seen the file yet. Now you have, and from here on a good query asks for exactly what it needs and nothing else. That habit pays off three ways: the result fits on a screen, the reader of your query learns what matters, and on a real file the engine skips columns it never has to touch.

The select list is a comma-separated list of things you want per row. Each thing can be a column name, or an expression built from column names, or a constant. They come back in the order you wrote them, which is not necessarily the order they sit in the file.

Columns you can name

The sales file has seven: date, region, product, channel, units, unit_price and revenue. Type them without quotes. You only need quotes when a name has a space or a capital in it that you want preserved, and then it is double quotes, never single. Single quotes mean a text value in SQL, so SELECT 'region' returns the literal word region a hundred times rather than the column. That one catches everybody once.

Columns that do not exist yet

A select list item does not have to be a column. It can be arithmetic: units * unit_price is evaluated once per row and comes back as a new column beside the real ones. String functions, date functions and conditional expressions all work in the same slot. This is where SQL stops feeling like a lookup language and starts feeling like a calculator with a hundred rows of memory.

Try that particular expression on this file and compare it to the revenue column. They match to the cent on all one hundred rows, because the file was built that way. Checking a computed column against a stored one is a genuinely useful habit: it is how you catch a spreadsheet where somebody typed over a formula.

AS, and why the header matters

Without a name, a computed column gets whatever header the engine feels like giving it, usually the expression itself. (units * unit_price) as a column heading is ugly in a grid and unusable downstream: if you export that result to a CSV, the next tool has to deal with a header containing spaces and an asterisk.

AS fixes it. Write units * unit_price AS line_total and the header is line_total. You can alias a plain column too, which is how you rename product to item on the way out.

The keyword is optional in most databases, so revenue amount aliases just as well as revenue AS amount. Do not take that shortcut. A missing comma then turns two columns into one silently renamed column, and it is a genuinely nasty bug to find by eye. Write the AS.

An alias is not usable in the WHERE clause of the same query in standard SQL, because WHERE is evaluated before the select list is. DuckDB is friendlier than the standard here and lets you, but code that relies on it will not move to Postgres unchanged. In this course, when it matters, the lesson will say so.

A word on reading your own query

Once a select list runs past three items, put each on its own line. The engine does not care. The person reviewing it does, and six months from now that person is you. Every query in this course is formatted the way I would actually write it in a pull request, so you can copy the layout along with the syntax.

What to look up next

Look up "SQL string functions" for your database of choice. Once you know that a select list item can be any expression, upper(), trim() and concat() turn a messy export into a clean one without leaving the query.