Build a Null-Safe Display Name
Build display_name from lowercase first_name and last_name, using 'unknown' for a NULL last_name.
- String functions
- NULL handling
- Sorting
Exercise brief
Understand the request
Customer identity engineer A display-name export must not collapse to NULL when a family name is missing.
A display-name export must not collapse to NULL when a family name is missing. Build display_name from lowercase first_name and last_name, using 'unknown' for a NULL last_name.
Return
- Return record_id and display_name.
- Separate the two name components with one space and order by record_id.
Constraints
- Use COALESCE before concatenation.
- Do not filter out records with a NULL last_name.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
function_cases
record_idINTEGERfirst_nameTEXTlast_nameTEXTlabelTEXTemailTEXTbackup_emailTEXTphoneTEXTraw_unitsTEXTraw_quantityTEXTraw_statusTEXTactual_valueREALtarget_valueREALcompleted_unitsREALelapsed_hoursREAL
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
A NULL operand can nullify operator-based concatenation.
Hint 2
Convert the missing last name to 'unknown' before joining the strings.
Hint 3
Apply LOWER to both name components and concatenate with one space.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT record_id, LOWER(first_name) || ' ' || LOWER(COALESCE(last_name, 'unknown')) AS display_name FROM function_cases ORDER BY record_id;Why this works
Explicitly handling NULL before concatenation avoids dialect-dependent CONCAT behavior and preserves every source row.
Success check
All eight rows have a non-NULL display name, including record 102.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| record_id | display_name |
|---|---|
| 101 | alice ng |
| 102 | bob unknown |
| 103 | carol o'neil |
| 104 | dave smith |
| 105 | eve li |
| 106 | frank miller |
| 107 | grace kim |
| 108 | heidi brown |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Build the next SQL skill
SELECT Statements
Select columns, filter rows, remove duplicates, and order query results.
CASE Statements & Conditional Logic
Build NULL-aware classifications, precedence-safe decisions, flags, scores, and guarded calculations with portable CASE expressions.
Date Operations & Time-Based Analytics
Practice date arithmetic, safe timestamp ranges, calendar bucketing, dense time series, rolling windows, growth, and cohort analysis.
Open the interactive workspace and practice across SQL topics.