Multiple Columns and Aliases

A SELECT always produces a table, even when it never touches one. This lesson is about controlling the shape of that table: how many columns a row carries, how each value is computed, and what the column is called.

One row, several columns

Separate expressions with commas, and each one becomes a column of the same row:

SELECT 'Ada', 1815, 3 * 12;

Columns come out in the order you wrote them. This course's runner prints values only -- no header line -- and joins the columns of one row with a |, so that statement prints:

Ada|1815|36

You will also meet SELECT *, meaning "every column". It is fine while exploring and poor in real code: the day the table gains a column, every * query silently changes shape. Name what you need.

Several statements, several lines

A script is a list of statements, each ended by ; (lesson 1). Each SELECT runs in turn and prints its own rows, so a script with two statements prints two lines:

SELECT 'first';
SELECT 10 - 4;

first
6

That is how a script produces more than one line of output before you have any tables.

Computing the values

Any expression is allowed in the column list. Arithmetic, the scalar functions, and || to join text:

|| is the concatenation operator, and it converts a number to text for you:

SELECT 'Rows: ' || 12;

Rows: 12

That conversion is what lets a function returning a number drop straight into a sentence you are assembling, without any cast: the number becomes its digits and the pieces join up.

Two arithmetic notes while you are here. Dividing two integers truncates in SQLite -- 7 / 2 is 3, not 3.5 and not 4 -- so write 7 / 2.0 when you want decimals. And + is arithmetic only: it is not a second spelling of ||.

Naming a column: AS

A computed column gets an ugly automatic name (the text of the expression itself). AS renames it:

SELECT 'Ada' AS first_name, LENGTH('Lovelace') AS surname_length;

The AS keyword is optional (LENGTH('Lovelace') surname_length parses too), but write it -- the explicit form is the convention everywhere. An alias with a space in it needs double quotes, AS "surname length", because double quotes mark identifiers.

Two honest notes about aliases here. The grader compares values, and this runner prints no header row, so an alias never shows up in graded output -- you alias for the code that reads the query, not for the grader. And the payoff comes later: ORDER BY and GROUP BY can refer to an alias instead of repeating the whole expression.

Common mistakes

• + where you meant ||. SELECT 'Ada' + 'Lovelace'; does not concatenate; + coerces both operands to numbers, neither looks like one, and the line prints 0. No error, no warning.
• The space is missing from a literal, not from SQL. 'Ada' || 'Lovelace' prints AdaLovelace. Spaces live inside your quotes; when output is one character off, that is where to look.
• Double quotes around text. SELECT "Ada"; asks for a column named Ada; SQLite falls back to treating it as the string, so it prints and hides the bug, while Postgres rejects it outright.
• One statement, one line of output. If your script is missing a ; the two statements merge into one and refuse to parse; if you leave a placeholder SELECT in place you print a line that was never asked for. The line count has to match exactly.

Your exercise

Build each output line out of pieces joined with || instead of typing the finished text as one literal -- that is the skill being graded, and a line that is right by accident still teaches you nothing. The starter holds two placeholder statements; replace each one. The likeliest failure is a space that ended up outside a quote, or + typed out of habit.