3.3Built-in Functions (Aggregate, String, and Date/Time)

SQL's real value lies not just in returning raw data, but in shaping it into something meaningful via functions. Functions that aggregate many rows into one, functions that format strings, and functions that compute or format dates are all used constantly in real-world reporting and data cleansing—and this is a classic OSS-DB Silver exam area (S3.2).

3.3.1Aggregate and arithmetic functions

• count counts rows (count(*) counts every row; count(column) counts non-NULL values). sum totals a numeric column (NULLs are ignored). avg computes the average (NULLs are excluded from the denominator). max/min give the maximum/minimum. All are commonly combined with GROUP BY for per-group aggregation.
• Arithmetic operators: + (addition), - (subtraction), * (multiplication), / (division—integer division truncates the result to an integer), % (modulo). Operations between columns of different types sometimes require implicit or explicit casting (e.g., ::numeric).

3.3.2String and date/time functions

• length/char_length return a string's length (length(name)). lower/upper convert to lowercase/uppercase. substring extracts part of a string (substring(name from 1 for 3)). replace substitutes text (replace(text, 'old', 'new')). trim strips leading/trailing whitespace (or specified characters).
• The concatenation operator || joins strings (first_name || ' ' || last_name). The LIKE predicate filters by pattern (WHERE name LIKE 'A%' matches names starting with "A"; % matches any string of any length, _ matches exactly one character). A case-insensitive variant ILIKE also exists.
• now/current_timestamp return the current date and time (with timezone). current_date returns just today's date. age returns the difference between two timestamps as an interval like "N years, M months, D days" (age(timestamp1, timestamp2); omitting one argument compares against now).
• extract pulls out a specific component (year, month, day, day-of-week, etc.) from a date/time (extract(year FROM order_date)). to_char converts a date/time (or number) into a custom-formatted string (to_char(order_date, 'YYYY-MM-DD')), commonly used for display formatting.

Consider a monthly-reporting scenario and see how the functions work together. "Show this month's order count, total amount, and average order amount in one row" calls for SELECT count(*), sum(amount), avg(amount) FROM orders WHERE extract(month FROM order_date) = extract(month FROM current_date);—using extract to narrow to the target month while listing three aggregate functions in the same SELECT. If some amount rows are NULL, note that count(*) still counts those rows, while sum(amount) and avg(amount) exclude NULLs from the calculation—what each function is actually counting differs subtly, even on the same table. To format a name for display, combining substring extraction, case conversion, and the || concatenation operator as in upper(substring(last_name from 1 for 1)) || lower(substring(last_name from 2)) produces a "capitalize only the first letter" style. To show elapsed time since signup as something like "3 years, 2 months," age (age(now(), signup_date)) is convenient; when you need full control over display formatting (e.g., a localized style like "2026年07月"), use to_char (to_char(signup_date, 'YYYY"年"MM"月"')). To search for "members whose email ends in gmail.com," use the LIKE wildcard % as in WHERE email LIKE '%@gmail.com', switching to ILIKE when case should be ignored.

3.3.3Section summary

• count(*) = all rows; count(col)/sum/avg exclude NULLs. Watch for arithmetic operators—integer division truncates
• Strings: length/lower/upper/substring/replace/trim/|| (concatenation)/LIKE (% = any length, _ = one character)
• Date/time: now/current_date/current_timestamp (current date-time); age (interval difference); extract (component extraction); to_char (format conversion)