The idea
SQLite offers built-in functions such as upper(), length(), round() and coalesce(). A scalar function produces a value for each row; aggregates such as count() combine rows. SQLite does not support PostgreSQL's CREATE FUNCTION ... LANGUAGE SQL syntax. Custom functions require the host database API.
Worked example
SELECT upper('coding') AS label, length('coding') AS letters, round(3.14159, 2) AS rounded, coalesce(NULL, 'unknown') AS fallback;What to check
The values are CODING, 6, 3.14 and unknown. coalesce chooses the first non-NULL argument, not the first nonempty string.
Practice task
Compare coalesce('', 'fallback') with coalesce(NULL, 'fallback'). Explain their different results.
Check your understanding
Explain the result in your own words before changing the example. Change one thing at a time, compare the result with your prediction, and keep the version that demonstrates the concept clearly.