Databricks Certified Data Engineer - Associate

Explanation

Let's analyze each option:

Option A: SELECT count(member_id) FROM my_table;

• This returns the count of non-null values in the member_id column, not null values.
• The COUNT() function in SQL excludes NULL values by default.

Option B: SELECT count(member id)-count null(member_id) FROM my_table;

• This has syntax errors:
  • member id should be member_id (missing underscore)
  • count null() is not a valid SQL function
  • Even if corrected, this would try to subtract an invalid function call

Option C: SELECT count_if(member_id IS NULL) FROM my_table; ✓ CORRECT

• count_if() is a Spark SQL function that counts rows where the condition is true.
• member_id IS NULL correctly identifies null values.
• This will return the exact count of null values in the member_id column.

Option D: SELECT null member_jd) FROM my_table;

• This has multiple syntax errors:
  • member_jd appears to be a typo (should be member_id)
  • Missing opening parenthesis
  • null is not a function
  • This is not valid SQL syntax

Option E: SELECT count null(member id) FROM my_table;

• This has syntax errors:
  • member id should be member_id (missing underscore)
  • count null() is not a valid SQL function
  • Should be count_if() or use COUNT(*) with a WHERE clause

Alternative Correct Approaches

In Spark SQL, you could also use:

1. SELECT COUNT(*) FROM my_table WHERE member_id IS NULL;
2. SELECT SUM(CASE WHEN member_id IS NULL THEN 1 ELSE 0 END) FROM my_table;

Key Learning Points

• COUNT(column_name) excludes NULL values
• COUNT(*) counts all rows including NULLs
• count_if(condition) counts rows where the condition evaluates to TRUE
• IS NULL is the correct operator to check for null values in SQL