Expressions, Aliases, and Functions

Lora is planning matinee pricing: every afternoon ticket at 20% off. The discounted prices exist nowhere in the database — and they don't need to. A query can compute while it reads: show each screening's stored price and, right next to it, the matinee price derived on the fly, under a readable column name.

That is this page's one idea, worn three ways. An expression is a small computation written where a column name would go. An alias gives the computed column a name worth reading. And functions are the built-in helpers — rounding, changing case, measuring length — that expressions call on. Together they mean queries don't just fetch facts; they derive conclusions from them.

Arithmetic While You Read

In English: show each screening's price, and next to it the price times 0.8. The multiplication sits right in the SELECT list, where a column name would normally be:

SELECT screening_id, price, price * 0.8
FROM screenings;

The database evaluated price * 0.8 once per row and put the result in the answer. Nothing was stored: the screenings table still holds price and only price. Ask again tomorrow and the matinee numbers are derived again, fresh, from whatever the stored prices are then. A receipt at a till is the right picture — the shelf price is what's stored, and the line total is computed at the till, every time, from the same stored facts. Map the picture, drop it: derived values live in answers, stored values live in tables.

AS Gives It a Name

That third column heading — price * 0.8 — is what the database shows when you don't name a computed column, and no one wants to read it on a schedule board. The keyword AS attaches an alias, a name that exists only in this answer:

SELECT screening_id, price, price * 0.8 AS matinee_price
FROM screenings;

One honest footnote: in most engines the word AS is optional — price * 0.8 matinee_price works too — but always writing it is kinder to the next reader, and the next reader is usually you. Aliases also rename ordinary columns (runtime_min AS minutes) whenever a report needs friendlier headings.

A Tour of Everyday Functions

A function takes a value in and hands a value back: you write its name, then the input in parentheses. ROUND(9.6, 1) rounds to one decimal place; UPPER(title) and LOWER(email) shift text to one case; LENGTH(title) measures how long a piece of text is (with one engine wrinkle — MySQL counts bytes here, where CHAR_LENGTH gives the character count). Like arithmetic, a function in the SELECT list runs once per row against a copy of the value — the marquee sign needs shouting capitals is one function call away:

SELECT UPPER(title) AS sign_title,
       ROUND(runtime_min / 60.0, 1) AS hours
FROM films;

And to be clear about what did not happen: the films table still says "The Long Rain", in ordinary case. UPPER shouted at a copy in the answer; the stored title never heard it. Functions in a SELECT transform the copy being examined, never the data itself.

Dates get functions too — extract the year from joined_on, subtract two dates to count days — and here comes the engine-honesty sentence: date function names vary annoyingly between engines, so the exact spelling is a thing you look up per engine, while the idea — dates are values you can compute with — is universal.

Functions in WHERE Too

Everything above works in a WHERE clause as well, which resolves last topic's loose end about text case. Lora wants the customers with Gmail addresses, however their emails happen to be capitalized. Lowercase each email before testing it, and match the pattern anything, then @gmail.com:

SELECT name, email
FROM customers
WHERE LOWER(email) LIKE '%@gmail.com';

Two new things in one line, so read it slowly. LOWER(email) normalizes each row's email to lowercase for the test — the stored emails keep their capitals. And LIKE compares against a pattern instead of an exact value, where % means "anything here": the pattern reads anything, ending in @gmail.com. Consider LIKE a cameo appearance — pattern matching is a deeper toolbox than one page can hold — but the lowercase-before-comparing trick is the everyday standard for case-proof text tests, and it's yours now.