All standard SQL aggregate functions (COUNT, SUM, AVG, MIN, MAX) ignore NULL. The sole exception is COUNT(*), which counts every row regardless of NULL.

COUNT(*)            -- count all rows (including NULL)
COUNT(coupon_code)  -- count non-NULL values only
SUM(amount)         -- sum while excluding NULL rows
AVG(score)          -- average while excluding NULL rows (the denominator also drops them)

From the orders table, return the total number of orders (total_orders), the number of orders using a coupon (coupon_orders), and the coupon usage rate (%) (coupon_rate) in one row. Round coupon_rate to one decimal place.

Note: coupon_code = NULL means the coupon was not used. The difference between COUNT(*) and COUNT(coupon_code) is the key to the usage-rate calculation.

Expected output (one row):

SELECT
  COUNT(*)                         AS total_orders,  -- all 6 rows (including NULL)
  COUNT(coupon_code)               AS coupon_orders, -- exclude NULL → 3 rows
  ROUND(
    COUNT(coupon_code) * 100.0    -- convert to numeric with 100.0 (avoid integer division)
      / COUNT(*),
    1
  )                                AS coupon_rate
FROM  orders;

/*
  Execution order (SQL's logical evaluation order):
  1. FROM orders                            → read the rows
  2. COUNT(*)                               → count all rows
  3. COUNT(coupon_code)                     → count after excluding NULL rows
  4. COUNT(coupon_code) * 100.0 / COUNT(*)  → calculate the usage rate
  5. ROUND(..., 1)                          → round to one decimal place
  6. SELECT                                 → output 3 columns in one row
  */

COALESCE and NULLIF are functions for handling NULL safely. Both are standard NULL-handling patterns that appear frequently in production SQL.

-- COALESCE: evaluate from left to right and return the first non-NULL value
COALESCE(discount_price, price)
-- discount_price = NULL → use price (fallback)
-- discount_price = 800  → return 800 (ignore price)

-- NULLIF: return NULL when a = b; otherwise return a
NULLIF(stock, 0)
-- stock = 0  → NULL (avoid a division-by-zero error)
-- stock = 50 → 50 (return it unchanged)

From the products table, calculate the effective price (effective_price = discount_price when present, otherwise price) and the inventory turnover rate (sold ÷ stock → turnover_rate, NULL when stock is 0). Return the columns product_id, name, effective_price, turnover_rate in ascending product_id order. Round turnover_rate to two decimal places.

Note: product_id=3,5: stock=0 → turnover_rate should be NULL. product_id=2,5: discount_price=NULL → use the regular price.

Expected output (product_id ascending):

SELECT
  product_id,
  name,
  COALESCE(discount_price, price)   AS effective_price,  -- return the first non-NULL value
  ROUND(
    sold::numeric / NULLIF(stock, 0),  -- stock=0 → NULL (avoid division by zero)
    2
  )                                   AS turnover_rate
FROM  products
ORDER BY product_id;

/*
  Execution order (SQL's logical evaluation order):
  1. FROM products                      → read the rows
  2. COALESCE(discount_price, price)    → return the first non-NULL value
  3. NULLIF(stock, 0)                   → convert stock=0 to NULL
  4. sold::numeric / NULLIF(stock, 0)   → calculate turnover rate safely
  5. ROUND(..., 2) / SELECT / ORDER BY   → round and output
  */

Aggregate functions automatically exclude NULL when calculating, so the denominator of the result changes. This is the source of the bug where “the average quietly lies.”

-- When score = [80, NULL, 90, NULL]
AVG(score)                -- = (80 + 90) / 2 = 85.0  ← denominator is 2 (NULL excluded)
COUNT(score)              -- = 2  (non-NULL count)
COUNT(*)                  -- = 4  (total count)
COUNT(*) - COUNT(score)  -- = 2  (NULL count = unanswered count)

-- Include everyone in the average by treating NULL as 0
AVG(COALESCE(score, 0)) -- = (80 + 0 + 90 + 0) / 4 = 42.5

From the survey table, calculate the average score by category (avg_score), the number of respondents (responded), the number who did not respond (not_responded), and the response rate (%) (response_rate). Return the columns category, avg_score, responded, not_responded, response_rate in ascending category order. Round avg_score and response_rate to one decimal place.

Note: score/submitted_at = NULL means unanswered. Because AVG excludes NULL, it becomes the “average among respondents.”

Expected output (category ascending):

A: score=[80,NULL,90,NULL] → AVG=(80+90)/2=85.0 (the denominator excludes NULL, so it is 2). B: score=[70,NULL,NULL,60] → AVG=(70+60)/2=65.0.

SELECT
  category,
  ROUND(AVG(score), 1)                         AS avg_score,     -- average excluding NULL
  COUNT(score)                                   AS responded,    -- non-NULL count
  COUNT(*) - COUNT(score)                        AS not_responded,-- NULL count
  ROUND(COUNT(score) * 100.0 / COUNT(*), 1)     AS response_rate -- response rate (%)
FROM  survey
GROUP BY category
ORDER BY category;

/*
  Execution order (SQL's logical evaluation order):
  1. FROM survey                         → read the rows
  2. GROUP BY category                   → group by category
  3. AVG(score)                          → average after excluding NULL (respondent average)
  4. COUNT(score)                        → count respondents
  5. COUNT(*)                            → count the population
  6. COUNT(*) - COUNT(score)             → calculate non-respondents
  7. COUNT(score) * 100.0 / COUNT(*)     → calculate response rate
  8. ROUND / SELECT / ORDER BY category  → round and output
  */

LEFT JOIN keeps every row from the left table and fills every column from the right table with NULL when no matching row exists. This property enables an anti-join that extracts rows with no match.

SELECT c.name, o.order_id
FROM   customers c
LEFT JOIN orders o
  ON c.customer_id = o.customer_id

-- customer_id=2,4: no match in orders → o.order_id = NULL
WHERE o.order_id IS NULL  -- let only unmatched (no-purchase) rows pass

LEFT JOIN the customers and orders tables to find customers who have never placed an order (customers with no purchases). Return the columns customer_id, name in ascending customer_id order.

Note: customer_id=2 (Suzuki) and 4 (Ito) have no order records in the orders table. Detect them with IS NULL after the LEFT JOIN.

Expected output (customer_id ascending):

SELECT
  c.customer_id,
  c.name
FROM  customers c
LEFT JOIN orders o
  ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL   -- right table is NULL = no match (customer with no purchase)
ORDER BY c.customer_id;

/*
  Execution order (SQL's logical evaluation order):
  1. FROM customers c                         → read the rows
  2. LEFT JOIN orders o                       → keep all left-table rows while joining
  3. WHERE o.order_id IS NULL                 → pass only rows where the right side is NULL
  4. SELECT c.customer_id, c.name / ORDER BY  → output in ascending customer_id order
  */