Handling Missing Data: The IFNULL Challenge

In many real-world datasets, information can be incomplete, leading to NULL values. When performing analysis or displaying data, these NULLs can cause unexpected results or be visually unappealing. This challenge focuses on a common SQL technique to gracefully handle these missing values by replacing them with a specified default.

Problem Description

Your task is to write a SQL query that retrieves data from a table containing potential NULL values and replaces those NULLs with a designated default value. This is crucial for ensuring data integrity, generating consistent reports, and preventing errors in downstream applications that might not handle NULLs properly.

Requirements:

• Query a table named products.
• The products table has the following columns:
  • product_id (integer, primary key)
  • product_name (string)
  • price (decimal)
  • discount_percentage (decimal, can be NULL)
• You need to select all columns from the products table.
• For any discount_percentage that is NULL, you must replace it with 0.0.
• The query should be written using a standard SQL IFNULL function (or its equivalent in other SQL dialects like COALESCE).

Expected Behavior:

The output should be a result set containing all records from the products table. Where discount_percentage was originally NULL, it should now show 0.0. All other columns should remain unchanged.

Edge Cases:

• Consider a scenario where the table is empty.
• Consider a scenario where all discount_percentage values are already non-NULL.

Examples

Example 1:

Input Table: products

Output:

Explanation: The NULL value in the discount_percentage for product_id 102 has been replaced with 0.0.

Example 2:

Input Table: products

Output:

Explanation: Both NULL values in discount_percentage have been replaced with 0.0.

Example 3: All Non-NULL Discounts

Input Table: products

Output:

Explanation: No NULL values were present, so the output is identical to the input for the discount_percentage column.

Constraints

• The products table will contain at least one column named discount_percentage of a numeric type (e.g., DECIMAL, FLOAT).
• The default replacement value for NULLs will always be 0.0.
• Your query should be a single SELECT statement.
• Assume standard SQL syntax for IFNULL or COALESCE.

Notes

• The IFNULL function (or COALESCE) takes two arguments: the expression to check for NULL, and the value to return if the expression is NULL.
• Consider how different SQL database systems might implement this (e.g., IFNULL in MySQL, ISNULL in SQL Server, COALESCE which is standard SQL and works in most systems). For this challenge, using IFNULL or COALESCE is acceptable.
• The goal is to demonstrate your understanding of NULL handling and the specific function used for replacement.