Stepwise-Lab

Conversion and conditional: 1Z0-071 practice questions

Questions in the style of exam 1Z0-071 on this topic. Answer each one first, then open its answer and explanation.

What this topic covers

  • NVL, NULLIF and COALESCE
  • Implicit versus explicit data type conversion
  • TO_CHAR, TO_NUMBER and TO_DATE
  • Nesting multiple functions

Practice questions

1. What does TO_CHAR(DATE '2024-03-05', 'fmDD Month') return (English session)?

  1. 05 March
  2. 5 March
  3. 5 March
  4. 05 MARCH
Show the answer

Answer: B

fm (fill mode) removes leading zeros and the blank padding that Month adds to reach the longest month name (September, 9 characters); Day is padded the same way, to the length of Wednesday. Mixed-case Month gives mixed-case output, so MARCH would need MONTH.

2. commission_pct is NUMBER. What happens?

SELECT NVL(e.commission_pct, 'none')
FROM   employees e
WHERE  e.employee_id = 100;
  1. Returns none
  2. Returns NULL
  3. Raises ORA-01722: invalid number
  4. Returns 0
Show the answer

Answer: C

NVL converts the second argument to the data type of the first. 'none' cannot be converted to NUMBER. Use NVL(TO_CHAR(commission_pct), 'none').

3. DEPARTMENT_ID is NUMBER. What does this query return for a row whose department_id is NULL?

SELECT CASE e.department_id
         WHEN NULL THEN 'none'
         ELSE 'some'
       END
FROM   employees e;
  1. none
  2. some
  3. NULL
  4. An error
Show the answer

Answer: B

A simple CASE compares with =, and NULL = NULL is UNKNOWN, so the WHEN branch never matches. Use a searched CASE: CASE WHEN e.department_id IS NULL THEN ... DECODE, by contrast, treats two NULLs as equal.

4. What does this return?

SELECT CASE
         WHEN 24000 > 5000  THEN 'MID'
         WHEN 24000 > 10000 THEN 'HIGH'
         ELSE 'LOW'
       END
FROM   dual;
  1. MID
  2. HIGH
  3. LOW
  4. An error, because more than one WHEN condition is true
Show the answer

Answer: A

CASE evaluates the WHEN conditions in order and returns the result of the first one that is true; later branches are not considered. 24000 > 5000 is already true, so the result is MID. Put the most selective condition first.