A percentage calculation divides achieved_sales by target_sales, but some targets are zero. How would you prevent a divide-by-zero error while preserving those rows?
Interview preparation resource from Gate Smashers.
Wrap the denominator with NULLIF(target_sales, 0) so a zero denominator becomes NULL and the division returns NULL instead of causing an error. Multiply by 100.0 to promote decimal arithmetic. Use COALESCE only if the business explicitly wants an alternative display value (e.g., 0).
SELECT employee_id, achieved_sales * 100.0 / NULLIF(target_sales, 0) AS achievement_pct
FROM Sales_Targets;Direct fix
Prevent divide-by-zero by converting a zero denominator into NULL using NULLIF(target_sales, 0). The division then yields NULL rather than raising an error, and the row remains in the result set.
- Denominator conversion: Use NULLIF(target_sales, 0) so zero becomes NULL for the division.
Why rows are preserved
Because NULL is a valid value in a result set, rows with target_sales = 0 are retained. The computed percentage becomes NULL, which accurately signals that the percentage cannot be calculated for that row.
- Signal of missing value: NULL indicates the percentage is undefined rather than hiding or removing the row.
Decimal arithmetic
Multiplying by 100.0 (a floating/decimal literal) promotes decimal arithmetic in many databases so you get a fractional percentage rather than integer truncation.
- Precision: Use 100.0 (not 100) to ensure non-integer division results where supported.
Using COALESCE only when needed
If the business prefers a specific display value instead of NULL (for example, show 0%), wrap the result with COALESCE to substitute that value. Do this only when a substitute is a deliberate business requirement.
- Alternative display: Example pattern: COALESCE( /* division expression that may yield NULL */ , 0) — use only if you want to display 0 instead of NULL.