SQL Functions
What the domain tests
The SQL Functions domain checks your ability to use MySQL 8.0 built-in functions correctly in SELECT lists, WHERE clauses, and grouping. It covers string, numeric, date and time, control-flow, aggregate, and window functions, with emphasis on NULL propagation and return types.
Core rules
- CONCAT returns NULL if any argument is NULL. CONCAT_WS returns NULL only if the separator is NULL and skips NULL arguments. Use COALESCE or IFNULL to substitute defaults.
- IF(expr, a, b), CASE, IFNULL, and NULLIF are control-flow functions. CASE stops at the first true branch.
- Date arithmetic uses DATE_ADD or DATE_SUB with INTERVAL units, TIMESTAMPDIFF, DATEDIFF, LAST_DAY, and STR_TO_DATE or DATE_FORMAT. Format specifiers are case-sensitive: %m is month, %i is minutes, and %s is seconds.
- Aggregate functions ignore NULL except COUNT(*). GROUP_CONCAT is limited by group_concat_max_len and can use ORDER BY and SEPARATOR.
- Window functions such as ROW_NUMBER, RANK, DENSE_RANK, LEAD, LAG, NTILE, and SUM OVER require an OVER clause. RANK leaves gaps after ties; DENSE_RANK does not; ROW_NUMBER is arbitrary among ties unless the ORDER BY is unique.
Worked example
Scores 100, 100, and 90 ordered descending: ROW_NUMBER gives 1, 2, 3; RANK gives 1, 1, 3; DENSE_RANK gives 1, 1, 2. The reasoning is tie handling: RANK skips the positions consumed by ties, while DENSE_RANK does not.
Traps
- Forgetting that a single NULL in CONCAT makes the whole result NULL.
- Using %m for minutes or %i for month in DATE_FORMAT.
- Aggregating without GROUP BY and then selecting a nonaggregated column, which ONLY_FULL_GROUP_BY rejects.
- Assuming GROUP_CONCAT returns unlimited length.
- Using an aggregate function where a window function is needed, collapsing rows unintentionally.