Advanced

Computing inside the query

I pulled raw columns and did the arithmetic in another language. SQL can do that arithmetic itself, and doing it there means fewer round trips and one place to keep it correct.

What you will see

name ADA GRACE H. ALAN EDITH LINUS

The full result is shown at the end of the exercise below.

About 1 min of reading

Try It

Compute values inside SELECT and name them, without touching the table.

Double a column in the answer. The table stays as it was.

SELECT id * 2 FROM users;

Before you run it, say out loud what it will print. Then open the answer.

What it prints

id * 2 2 4 6 8 10 12 sqlite>

Name the computed column so the header is readable.

SELECT id * 2 AS twice FROM users;

Before you run it, say out loud what it will print. Then open the answer.

What it prints

twice 2 4 6 8 10 12 sqlite>

Change case of a text column with a built-in function.

SELECT UPPER(name) FROM users;

Before you run it, say out loud what it will print. Then open the answer.

What it prints

name ADA GRACE H. ALAN EDITH LINUS GUIDO sqlite>

Data Flow

SELECT holds an expression, the database computes it per row, and the computed value is returned without storing it.

1 SELECT holds an expression

↓

2 Database reads each row

↓

3 Expression computed per row

↓

4 Result returned

Why this works

SELECT is an expression

Between SELECT and FROM you can write arithmetic (+ - * /), comparisons, and function calls, not only column names. The database computes that expression for each row and returns the result. Nothing is stored, the value exists only in the answer, which makes it safe to try.

Common functions

sqlite has a small but useful set:

UPPER(x) LOWER(x) change case
LENGTH(x) character count
ROUND(x) ABS(x) number helpers
COALESCE(a, b) pick the first that is not blank

They take a value and return a value, so you can nest them. UPPER(LOWER(x)) is legal and reads inside out.

Examples

Math in SELECT

SELECT id * 2 FROM users;

SELECT can hold expressions, not just column names. This doubles every id in the answer. The table is unchanged, only the returned value is computed.

Built-in text tools

SELECT UPPER(name) FROM users;

sqlite ships functions like UPPER, LOWER, and LENGTH. You wrap a column in one and get a transformed value back, one row at a time.

Give it a name

SELECT id * 2 AS twice FROM users;

AS names the computed column so the header reads twice instead of an expression. This is how a computed answer becomes readable.

Words this lesson uses

Each one is defined here on this site. Open the entry for the full definition and the other words it points at.

← Counting per group

Previous Lesson

Counting per group

Next Lesson

The blank that is not zero

The blank that is not zero →