Expressions, CASE, functions & data types

A SELECT list can contain far more than stored columns. Arithmetic, string operations, CASE expressions, casts, and scalar functions let SQL transform data before it leaves the database.

Arithmetic and calculated columns

SELECT id,
       quantity,
       unit_price,
       quantity * unit_price AS line_total
FROM order_items
ORDER BY id;

Run the query to see the result.

Be explicit about units and currencies. Adding two monetary values that represent different currencies is syntactically valid SQL but semantically wrong.

CASE for conditional logic

CASE is SQL's general conditional expression. It is useful for labels, bucketing, conditional aggregates, and safe transformations.

SELECT id,
       total,
       CASE
         WHEN total >= 200 THEN 'large'
         WHEN total >= 100 THEN 'medium'
         ELSE 'small'
       END AS order_size
FROM orders
ORDER BY id;

Run the query to see the result.

Order WHEN clauses from the most specific/highest-priority rule to the fallback rule.

COALESCE and NULL handling

COALESCE returns the first non-NULL argument and is broadly portable.

SELECT id,
       COALESCE(email, '[missing email]') AS email,
       COALESCE(phone, '[missing phone]') AS phone
FROM users
ORDER BY id;

Run the query to see the result.

Do not replace NULL with a sentinel merely to make a query look cleaner unless that replacement is meaningful to the consumer.

String and numeric functions

Function names differ more than basic SELECT syntax. SQLite provides functions such as length, lower, upper, round, substr, and printf; PostgreSQL has a much larger function catalog.

SELECT id,
       upper(first_name) AS first_name_upper,
       length(COALESCE(email, '')) AS email_length
FROM users
ORDER BY id;

Run the query to see the result.

Dates and timestamps are dialect-sensitive

The sample runner stores ISO-formatted dates/timestamps as text, which sort correctly when formats are consistent. Real databases usually use DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or vendor equivalents. Date arithmetic and truncation syntax vary significantly between engines.

A robust learning rule is: understand the concept (comparison, extraction, interval, timezone) and then consult the documentation for your target engine's exact function.

CAST and type conversion

Explicit casts document assumptions and prevent accidental string/numeric comparisons. PostgreSQL supports CAST(value AS type) and a :: shorthand; the former is more portable.

Practice: classify employees as high score (>= 90) or standard, and return the label alongside name and score.