You have a table employees with a nullable bonus column. You want to calculate the total bonus, treating NULL as zero, but only for employees with a salary above 50000. Which query correctly does this?
ASELECT SUM(COALESCE(bonus, 0)) FROM employees WHERE salary > 50000;
BSELECT SUM(bonus) FROM employees WHERE COALESCE(salary, 0) > 50000;
CSELECT SUM(COALESCE(bonus, 0)) WHERE salary > 50000 FROM employees;
DSELECT SUM(bonus) FROM employees WHERE salary > 50000;
Step-by-Step Solution
Solution:
Step 1: Use COALESCE to treat NULL bonus as zero
SUM(COALESCE(bonus, 0)) replaces NULL bonuses with 0 before summing.
Step 2: Filter employees with salary > 50000
The WHERE clause correctly filters rows before aggregation.
Step 3: Check query syntax
SELECT SUM(COALESCE(bonus, 0)) FROM employees WHERE salary > 50000; has correct syntax.
Final Answer:
SELECT SUM(COALESCE(bonus, 0)) FROM employees WHERE salary > 50000; -> Option A
Quick Check:
Use COALESCE in SUM and filter with WHERE [OK]
Quick Trick:Use COALESCE in SUM and filter rows with WHERE [OK]
Common Mistakes:
MISTAKES
Placing WHERE clause after FROM incorrectly
Not using COALESCE to handle NULLs
Filtering on COALESCE(salary, 0) unnecessarily
Master "Aggregate Functions" in SQL
9 interactive learning modes - each teaches the same concept differently
