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)?
- 05 March
- 5 March
- 5 March
- 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;- Returns none
- Returns NULL
- Raises ORA-01722: invalid number
- 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;- none
- some
- NULL
- 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;- MID
- HIGH
- LOW
- 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.