Objective

Given a table named orders with the columns order_id, customer_name, discount, and total_amount, write an SQL query to retrieve all orders. Replace any NULL values in the discount column with 0. The result should be ordered by order_id in ascending order.

Additional information

• The orders table schema:

• Ensure that the discount column in the result does not contain any NULL values; use 0 instead where applicable.
• The output should include all columns: order_id, customer_name, discount, and total_amount, in that order.
• Order the results by order_id in ascending order.

SELECT
    order_id,
    customer_name,
    COALESCE(discount, 0) AS discount,
    total_amount
FROM
    orders
ORDER BY
    order_id

Explanation

The objective is to retrieve all orders while replacing any NULL discount values with 0, sorted by order ID.

Step 1: COALESCE - replace NULLs with zero

COALESCE returns the first non-NULL argument. Here it converts missing discounts to 0.

COALESCE(discount, 0) AS discount

• If discount has a value, that value is returned unchanged.
• If discount is NULL, 0 is returned instead.

Step 2: SELECT - choose all relevant columns

SELECT order_id, customer_name, COALESCE(discount, 0) AS discount, total_amount
FROM orders

Step 3: ORDER BY - sort by order ID ascending

ORDER BY order_id