COALESCE(val1, val2, ...) evaluates its arguments left to right and returns the first non-NULL value. It is the most basic function for setting a substitute value for NULL. NULLIF(val1, val2) is the inverse: it returns NULL when val1 and val2 are equal (converting a specific value to NULL).

COALESCE(NULL, 'Not set')   -- → 'Not set'  (replaces NULL)
COALESCE('Tanaka', 'Not set') -- → 'Tanaka'   (non-NULL kept as is)
COALESCE(NULL, NULL, 0)   -- → 0          (multiple candidates allowed)
NULLIF(0, 0)              -- → NULL       (0=0, so converted to NULL)
NULLIF(42, 0)             -- → 42         (42≠0, so kept as is)

The users table has rows with NULL in nickname and score. Using COALESCE, produce (1) a display name (nickname if present, otherwise name) and (2) a score (0 when NULL). Also add a score_nullif column that uses NULLIF to convert a score of 0 back to NULL.

Expected output:

Row 3: score=0 → NULLIF(0,0)=NULL. Row 4: score=NULL → COALESCE(NULL,0)=0 → NULLIF(0,0)=NULL.

SELECT
  user_id,
  COALESCE(nickname, name) AS display_name,  -- Use name when nickname is NULL
  COALESCE(score, 0)       AS score,         -- Use 0 when score is NULL
  NULLIF(score, 0)         AS score_nullif   -- Convert score=0 to NULL (inverse of COALESCE)
FROM   users
ORDER BY user_id;

/*
  Logical execution order:
  1. FROM users                 → Read all 4 rows
  2. COALESCE(nickname, name)   → Evaluate left to right, return the first non-NULL value
     Tanaka (non-NULL)          → Tanaka
     NULL, Sato Hanako          → Sato Hanako (nickname is NULL, so name is used)
     Suzuki (non-NULL)          → Suzuki
     NULL, Yamada Jiro          → Yamada Jiro (same as above)
  3. COALESCE(score, 0)         → 0 when NULL, otherwise unchanged (0 is not NULL)
  4. NULLIF(score, 0)           → NULL when score=0, otherwise return score unchanged
  5. ORDER BY user_id           → Output in ascending user_id
*/

The SQL CASE expression branches per row and returns different values. There are two ways to write it.

-- (1) Simple CASE (direct value comparison)
CASE status
  WHEN 'completed' THEN 'Completed'
  WHEN 'pending'   THEN 'Pending'
  ELSE 'Other'   -- Without ELSE, NULL is returned
END

-- (2) Searched CASE (arbitrary conditions)
CASE
  WHEN amount >= 10000 THEN 'high'
  WHEN amount >=  5000 THEN 'medium'  -- Evaluated top to bottom; returns the THEN of the first TRUE
  ELSE                   'low'
END

Add a status_label column that converts status (English codes) in the orders table into readable labels, and an amount_rank column that ranks by the size of amount (10000 or more: 'high', 5000 or more: 'medium', otherwise: 'low').

Expected output:

SELECT
  order_id, user_id, amount,
  CASE status                       -- Simple CASE: direct value comparison
    WHEN 'completed' THEN 'Completed'
    WHEN 'pending'   THEN 'Pending'
    WHEN 'cancelled' THEN 'Cancelled'
    ELSE                  'Unknown'      -- Unexpected values become 'Unknown'
  END                    AS status_label,
  CASE                               -- Searched CASE: decide by range conditions
    WHEN amount >= 10000 THEN 'high'
    WHEN amount >=  5000 THEN 'medium'  -- Evaluated only for rows where the WHEN above is FALSE
    ELSE                    'low'
  END                    AS amount_rank
FROM   orders
ORDER BY order_id;

/*
  Logical execution order:
  1. FROM orders                  → Read all 5 rows
  2. CASE status WHEN ... END     → Compare each row's status against WHENs top to bottom and convert
  3. CASE WHEN amount >= ... END  → Evaluate each row's amount top to bottom and rank
  4. ORDER BY order_id            → Output in ascending order_id
  */

User input and external data frequently contain mixed case and leading/trailing spaces. Let's cover the main string-normalization functions.

TRIM('  hello  ')       -- → 'hello'    Removes leading/trailing spaces
LTRIM('  hello  ')      -- → 'hello  '  Removes left side only
RTRIM('  hello  ')      -- → '  hello'  Removes right side only
LOWER('Hello@WORLD.COM') -- → 'hello@world.com'
UPPER('hello')          -- → 'HELLO'
LENGTH('hello')         -- → 5  (character count)

The user_inputs table has email and username values with inconsistent case and leading/trailing spaces. Retrieve the following columns. email: (1) after TRIM (email_trimmed), (2) TRIM + lowercase (email_normalized), (3) character count after TRIM (email_length); username: (1) TRIM (username_trimmed), (2) TRIM + uppercase (username_upper).

Expected output:

SELECT
  id,
  TRIM(email)                AS email_trimmed,    -- Remove leading/trailing spaces
  LOWER(TRIM(email))        AS email_normalized, -- (1) TRIM removes whitespace → (2) LOWER lowercases
  LENGTH(TRIM(email))       AS email_length,    -- Character count after TRIM
  TRIM(username)             AS username_trimmed, -- Remove leading/trailing spaces
  UPPER(TRIM(username))     AS username_upper    -- Uppercase after TRIM
FROM   user_inputs
ORDER BY id;

/*
  Logical execution order:
  1. FROM user_inputs            → Read all 3 rows (leading/trailing spaces and mixed case intact)
  2. TRIM(email)                 → Remove leading/trailing spaces from each row's email
  3. LOWER(TRIM(email))          → Lowercase the TRIM result (evaluated inside out)
  4. LENGTH(TRIM(email))         → Count characters after TRIM
  5. TRIM(username), UPPER(...)  → Convert username the same way
  6. ORDER BY id                 → Output in ascending id
  */

There are two ways to concatenate strings: the || operator (SQL standard) and the CONCAT function. The key difference is their behavior toward NULL.

'Tanaka' || ' ' || 'Taro'        -- → 'Tanaka Taro'  (SQL standard)
CONCAT('Tanaka', ' ', 'Taro')     -- → 'Tanaka Taro'  (function version)
CONCAT(NULL, 'Taro')           -- → 'Taro'          (treats NULL as empty string)
NULL || 'Taro'                  -- → NULL            (NULL propagates)
LPAD('7', 4, '0')             -- → '0007'          (pad the left with '0')
RPAD('ABC', 6, '-')           -- → 'ABC---'        (pad the right with '-')

From the customers table, retrieve full_name using last_name || ' ' || first_name, a CONCAT-based full_name_v2, full_address using pref || city, and a customer_label that includes a zero-padded 4-digit customer number via LPAD (e.g., 'Customer No. 0001').

Expected output:

SELECT
  customer_id,
  last_name || ' ' || first_name         AS full_name,     -- Concatenate strings with || (SQL standard)
  CONCAT(last_name, ' ', first_name)    AS full_name_v2,  -- CONCAT function version (NULL-safe)
  pref || city                           AS full_address,  -- Prefecture + city
  'Customer No. ' ||
    LPAD(customer_id::TEXT, 4, '0')   AS customer_label -- Cast to ::TEXT, then zero-pad
FROM   customers
ORDER BY customer_id;

/*
  Logical execution order:
  1. FROM customers                      → Read all 3 rows
  2. last_name || ' ' || first_name      → Concatenate strings in order with ||
  3. CONCAT(last_name, ' ', first_name)  → Function version (treats NULL as empty string)
  4. pref || city                        → Concatenate prefecture and city
  5. LPAD(customer_id::TEXT, 4, '0')     → (1) cast integer to TEXT (2) pad left with '0' to 4 digits
     'Customer No. ' || ...              → Concatenate with the label string
  6. ORDER BY customer_id                → Output in ascending customer_id
*/