Aggregate with NULL handling in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to count all non-NULL values in the column 'score'.

SQL

SELECT COUNT([1]) FROM results;

Drag options to blanks, or click blank then click option'

Ascore

B*

CDISTINCT score

DNULL

Attempts:

3 left

💡 Hint

Common Mistakes

Using COUNT(*) counts all rows, not just non-NULL values.

Using COUNT(NULL) returns zero always.

The COUNT function counts only non-NULL values when a column name is specified. Using 'score' counts all non-NULL scores.

2fill in blank

medium

Complete the code to calculate the average of 'score', ignoring NULL values.

SQL

SELECT AVG([1]) FROM results;

Drag options to blanks, or click blank then click option'

ADISTINCT score

B*

Cscore

DNULL

Attempts:

3 left

💡 Hint

Common Mistakes

Using AVG(*) causes syntax error.

Using AVG(NULL) returns NULL.

AVG(column) calculates the average ignoring NULLs. Using 'score' computes average of non-NULL scores.

3fill in blank

hard

Fix the error in the code to count all rows including those with NULL in 'score'.

SQL

SELECT COUNT([1]) FROM results;

Drag options to blanks, or click blank then click option'

Ascore

BNULL

CDISTINCT score

D*

Attempts:

3 left

💡 Hint

Common Mistakes

Using COUNT(score) counts only non-NULL scores, missing rows with NULL.

Using COUNT(NULL) returns zero.

COUNT(*) counts all rows regardless of NULLs in any column.

4fill in blank

hard

Fill both blanks to calculate the total sum of 'score' treating NULL as zero.

SQL

SELECT SUM(COALESCE([1], [2])) FROM results;

Drag options to blanks, or click blank then click option'

Ascore

B0

CNULL

D1

Attempts:

3 left

💡 Hint

Common Mistakes

Using COALESCE(score, NULL) does not replace NULLs.

Replacing NULL with 1 changes the sum incorrectly.

COALESCE replaces NULL with 0 so SUM adds zeros instead of ignoring NULLs.

5fill in blank

hard

Fill all three blanks to count distinct non-NULL 'score' values greater than 50.

SQL

SELECT COUNT(DISTINCT [1]) FROM results WHERE [2] > [3];

Drag options to blanks, or click blank then click option'

Ascore

C50

DNULL

Attempts:

3 left

💡 Hint

Common Mistakes

Using NULL in WHERE clause causes no rows to match.

Counting without DISTINCT counts duplicates.

COUNT(DISTINCT score) counts unique non-NULL scores. The WHERE clause filters scores greater than 50.