Bird
0
0

Which query achieves this?

hard📝 Application Q8 of 15
SQL - Aggregate Functions
You have a table orders with columns order_id and discount where discount can be NULL. You want to calculate the total discount given, treating NULL as zero, but only for orders with a discount greater than or equal to 0. Which query achieves this?
ASELECT SUM(COALESCE(discount, 0)) FROM orders WHERE discount >= 0;
BSELECT SUM(discount) FROM orders WHERE discount >= 0;
CSELECT SUM(COALESCE(discount, 0)) FROM orders WHERE COALESCE(discount, 0) >= 0;
DSELECT SUM(discount) FROM orders WHERE discount IS NOT NULL AND discount >= 0;
Step-by-Step Solution
Solution:
  1. Step 1: Replace NULL with zero before filtering

    Use COALESCE(discount, 0) to treat NULL as zero in both SUM and WHERE clause.
  2. Step 2: Filter discounts greater than or equal to 0 after replacement

    WHERE COALESCE(discount, 0) >= 0 ensures NULLs treated as zero are included since 0 >= 0.
  3. Final Answer:

    SELECT SUM(COALESCE(discount, 0)) FROM orders WHERE COALESCE(discount, 0) >= 0; -> Option C
  4. Quick Check:

    Use COALESCE in both SUM and WHERE to handle NULLs correctly = D [OK]
Quick Trick: Use COALESCE in WHERE and SUM to handle NULLs consistently [OK]
Common Mistakes:
MISTAKES
  • Filtering on discount >= 0 without handling NULLs
  • Using discount in WHERE but COALESCE in SUM only
  • Ignoring NULLs in filter condition

Want More Practice?

15+ quiz questions · All difficulty levels · Free

Free Signup - Practice All Questions
More SQL Quizzes