when treating missing discount as zero is appropriate.

Mistake 4:

Using COALESCE blindly.

Replacing every NULL with "0" can distort analysis.

For example:

Missing salary → 0

does not mean the employee earns zero.

The correct replacement depends on the business meaning of the missing value.

🎤 SQL Interview Questions

Q1. What is NULL?

NULL represents a missing, unknown, or unavailable value.

Q2. How do you check for NULL?

WHERE column_name IS NULL;

Q3. How do you check for non-NULL values?

WHERE column_name IS NOT NULL;

Q4. Why doesn't "= NULL" work?

Because NULL represents an unknown value and comparisons with NULL do not evaluate to TRUE in the normal way. SQL provides IS NULL and IS NOT NULL specifically for this purpose.

Q5. What does COALESCE() do?

It returns the first non-NULL expression.

COALESCE(phone, email, 'No Contact')

Q6. What does NULLIF() do?

It returns NULL when two expressions are equal.

NULLIF(value1, value2)

Q7. Difference between COUNT(*) and COUNT(column)?

COUNT(*) counts rows.

COUNT(column) counts non-NULL values in that column.

Q8. How can you prevent division by zero?

revenue / NULLIF(orders, 0)

Q9. Does AVG() normally include NULL values?

No. NULL values are generally ignored when calculating the average.

Q10. What is the difference between NULL and 0?

"0" is an actual numeric value.

"NULL" represents an unknown or missing value.

📝 Practice Questions

Practice 1

Find customers whose email is missing.

SELECT *
FROM customers
WHERE email IS NULL;

Practice 2

Display "Unknown" when a customer's city is NULL.

SELECT
    customer_name,
    COALESCE(city, 'Unknown') AS city
FROM customers;

Practice 3

Calculate final price assuming a missing discount means zero.

SELECT
    price - COALESCE(discount, 0) AS final_price
FROM orders;

Practice 4

Calculate revenue per order without dividing by zero.

SELECT
    revenue / NULLIF(order_count, 0) AS revenue_per_order
FROM sales;

Practice 5

Count how many customers have a phone number.

SELECT COUNT(phone) AS customers_with_phone
FROM customers;

🧪 Mini SQL Challenge

You have a table:

sales

sale_id
revenue
discount
orders

Write a query that returns:

• sale_id

• revenue

• discount, treating NULL as 0

• revenue after discount

• revenue per order

• safely handle "orders = 0"

Solution:

SELECT
    sale_id,
    revenue,
    COALESCE(discount, 0) AS discount,

    revenue - COALESCE(discount, 0)
        AS revenue_after_discount,

    COALESCE(
        revenue / NULLIF(orders, 0),
        0
    ) AS revenue_per_order

FROM sales;

Double Tap ❤️ For Part-9