Bird
0
0

Which of the following SQL queries correctly calculates the average of the score column, treating NULL values as zero?

easy📝 Syntax Q3 of 15
SQL - Aggregate Functions
Which of the following SQL queries correctly calculates the average of the score column, treating NULL values as zero?
ASELECT AVG(COALESCE(score, 0)) FROM results;
BSELECT AVG(NULLIF(score, 0)) FROM results;
CSELECT AVG(score) FROM results WHERE score IS NOT NULL;
DSELECT AVG(score) FROM results;
Step-by-Step Solution
Solution:

  1. Step 1: Replace NULLs with zero using COALESCE

    COALESCE(score, 0) replaces NULL values with 0 before averaging.

  2. Step 2: Check other options

    SELECT AVG(NULLIF(score, 0)) FROM results; replaces zeros with NULL, not what we want. SELECT AVG(score) FROM results WHERE score IS NOT NULL; excludes NULLs but does not treat them as zero. SELECT AVG(score) FROM results; averages ignoring NULLs.

  3. Final Answer:

    SELECT AVG(COALESCE(score, 0)) FROM results; -> Option A

  4. Quick Check:

    Use COALESCE to treat NULL as zero in AVG = D [OK]
Quick Trick: Use COALESCE(column, 0) to treat NULL as zero in aggregates [OK]
Common Mistakes:
MISTAKES
  • Using NULLIF instead of COALESCE
  • Ignoring NULLs instead of replacing them
  • Filtering out NULLs instead of treating as zero

Want More Practice?

15+ quiz questions · All difficulty levels · Free

Free Signup - Practice All Questions
More SQL Quizzes
GROUP BY and HAVING - GROUP BY with NULL values behavior - Quiz 4medium INNER JOIN - Joining on primary key to foreign key - Quiz 3easy INNER JOIN - Self join concept - Quiz 7medium LEFT and RIGHT JOIN - Why outer joins are needed - Quiz 7medium Subqueries - Subquery with EXISTS operator - Quiz 5medium Subqueries - Why subqueries are needed - Quiz 1easy Table Constraints - Constraint naming conventions - Quiz 9hard Table Relationships - ER diagram to table mapping - Quiz 8hard Table Relationships - One-to-one relationship design - Quiz 13medium Views - Dropping and altering views - Quiz 14medium