Stepwise-Lab

Single-row functions: 1Z0-171 practice questions

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

What this topic covers

  • Manipulating strings with character functions in SELECT and WHERE
  • Performing arithmetic with date data
  • ROUND, TRUNC and MOD
  • Manipulating dates with date functions

Practice questions

1. What does SUBSTR('ORACLE SQL', -3, 2) return?

  1. QL
  2. SQ
  3. LE
  4. NULL
Show the answer

Answer: B

A negative start position counts from the end: -3 is the S in SQL. Two characters from there are SQ. The length still counts to the right; it does not run backwards, so QL is wrong.

2. What does INSTR('BANANA', 'AN', 1, 2) return?

  1. 2
  2. 3
  3. 4
  4. 0
Show the answer

Answer: C

INSTR(string, search, start, occurrence): starting at position 1, find the 2nd occurrence of AN. The first is at 2, the second at 4. If not found, INSTR returns 0.

3. What does ADD_MONTHS(DATE '2024-01-31', 1) return?

  1. 2024-02-29
  2. 2024-03-02
  3. 2024-02-28
  4. An error
Show the answer

Answer: A

If the start date is the last day of a month, or the target month is shorter, ADD_MONTHS returns the last day of the target month. 2024 is a leap year, so the result is 2024-02-29. Compare with adding INTERVAL '1' MONTH, which raises ORA-01839.

4. What is returned?

SELECT MONTHS_BETWEEN(DATE '2024-03-31', DATE '2024-02-29') FROM dual;
  1. 1
  2. About 1.0645
  3. Slightly less than 1
  4. -1
Show the answer

Answer: A

When both dates are the last day of their months (or the same day of the month), MONTHS_BETWEEN returns an integer. Otherwise the fractional part is based on a 31-day month. The first argument later than the second gives a positive result.