Single-Row Functions practice questions

Worked examples for Single-Row Functions

1. Question 1

   Which query returns the date **01-OCT-2020** (the first day of the fourth quarter of 2020)?

   1. A. SELECT TRUNC(DATE '2020-11-23', 'MONTH') FROM DUAL

      The 'MONTH' model truncates to the first day of the current month, giving 01-NOV-2020, not the start of the quarter.

   2. B. SELECT TRUNC(DATE '2020-11-23', 'Q') FROM DUALCorrect answer

      TRUNC with the 'Q' format model truncates to the first day of the quarter. November falls in Q4 (Oct-Dec), so the result is 01-OCT-2020.

   3. C. SELECT TRUNC(DATE '2020-11-23', 'YEAR') FROM DUAL

      The 'YEAR' model truncates to the first day of the year, giving 01-JAN-2020, one quarter boundary too far back.

   4. D. SELECT ROUND(DATE '2020-11-23', 'Q') FROM DUAL

      ROUND('Q') rounds up on the 16th day of the quarter's second month (16-Nov). The 23rd is past that threshold, so it rounds up to the next quarter start, 01-JAN-2021.

   Explanation

   The date format model passed to TRUNC and ROUND controls the granularity: 'Q' operates on quarter boundaries, 'MONTH' on month boundaries, and 'YEAR' on year boundaries. TRUNC always moves down to the first day of that unit, whereas ROUND moves to the nearest boundary, and for 'Q' it rounds up starting from the 16th day of the quarter's middle month. Choosing the wrong model or confusing TRUNC with ROUND lands on a different date.

2. Question 2

   Which query returns, for every employee, the portion of LAST_NAME that begins at the fourth character and extends to the end of the string?

   1. A. SELECT SUBSTR(last_name, 1, 4) FROM employees

      Transposes the intent of the second and third arguments. SUBSTR(string, 1, 4) extracts the FIRST four characters (position 1, length 4), not the substring that starts at position 4 and continues to the end of the string.

   2. B. SELECT SUBSTR(last_name, -4) FROM employees

      A negative position counts backward from the right end of the string, returning the last four characters — not the characters starting at the fourth position from the left. For 'Petrov' (6 chars), this returns 'trov', not 'rov'.

   3. C. SELECT SUBSTR(last_name, 4, 0) FROM employees

      When the length argument is less than 1, Oracle SUBSTR returns NULL for every row rather than an empty string. A length of 0 does not mean 'no limit' — it triggers the documented NULL-return rule, producing NULL for all eight employees.

   4. D. SELECT SUBSTR(last_name, 4) FROM employeesCorrect answer

      SUBSTR(string, position) with no third argument returns every character from position onward to the end of the string. A positive position counts from the left, so position 4 begins extraction at the fourth character and runs to the end.

   Explanation

   SUBSTR(string, position) with the length argument omitted returns all characters from position to the end of the string. A positive position counts from the beginning of the string; a negative position counts backward from the right end. Supplying a length value less than 1 — including zero — causes the function to return NULL rather than an empty string, a distinct behavior from the two-argument form that must not be confused with 'no length limit'.

3. Question 3

   Which query correctly returns the number of **complete** months elapsed between 1 March 2019 and 20 October 2021?

   1. A. SELECT MONTHS_BETWEEN(DATE '2021-10-20', DATE '2019-03-01') FROM DUAL

      MONTHS_BETWEEN without any truncation returns the exact fractional value (≈ 31.613), not a whole-month count. The question asks for complete months, which requires discarding the fractional part with TRUNC.

   2. B. SELECT TRUNC(MONTHS_BETWEEN(DATE '2019-03-01', DATE '2021-10-20')) FROM DUAL

      Reversing the arguments makes the earlier date the first argument, so MONTHS_BETWEEN returns a negative value (≈ −31.613). TRUNC truncates toward zero, giving −31 — the correct magnitude but the wrong sign for an elapsed duration.

   3. C. SELECT TRUNC(MONTHS_BETWEEN(DATE '2021-10-20', DATE '2019-03-01')) FROM DUALCorrect answer

      MONTHS_BETWEEN(DATE '2021-10-20', DATE '2019-03-01') = (2021−2019)×12 + (10−3) + (20−1)/31 = 31 + 19/31 ≈ 31.613. TRUNC discards the fractional part, returning 31 — the exact count of fully elapsed months. Argument order (later date first) produces a positive result.

   4. D. SELECT ROUND(MONTHS_BETWEEN(DATE '2021-10-20', DATE '2019-03-01')) FROM DUAL

      MONTHS_BETWEEN returns ≈ 31.613. Because the fractional part (≈ 0.613) exceeds 0.5, ROUND rounds up to 32 — crediting one month that has not fully elapsed. Counting complete months requires TRUNC, which always discards the fraction regardless of its size.

   Explanation

   MONTHS_BETWEEN(date1, date2) returns a signed fractional value; when the day components differ, the fractional part is (day1 − day2) / 31. To count only fully elapsed months the fractional part must be discarded, which requires TRUNC — not ROUND, which can round up when the fraction exceeds 0.5. Argument order also governs the sign: placing the later date first produces a positive result; reversing the arguments negates it.

4. Question 4

   What value does the following query return? ```sql SELECT REPLACE('A-B-C-D', '-', '+') FROM DUAL ```

   1. A. A+B+C+DCorrect answer

      REPLACE substitutes EVERY non-overlapping occurrence of the search string with the replacement string, so all three hyphens become '+'.

   2. B. A+B-C-D

      Assumes REPLACE changes only the first match. REPLACE is global — it replaces all occurrences, not just the first.

   3. C. ABCD

      Confuses 3-argument REPLACE with the 2-argument form: with only two arguments REPLACE removes the search string, but here a replacement string is supplied so the hyphens are substituted, not deleted.

   4. D. A-B-C-D

      Assumes REPLACE acts only when the search string matches the whole expression; in fact it matches and replaces every embedded occurrence.

   Explanation

   REPLACE(char, search_string, replacement_string) scans the source and replaces every occurrence of the search string with the replacement string. Because a replacement string is supplied, each matched substring is substituted rather than removed, and the substitution applies to all matches, not merely the first.

5. Question 5

   Which of the following queries returns the date **28-FEB-2021** (28 February 2021)?

   1. A. SELECT DATE '2021-03-31' - 30 FROM DUAL

      Subtracts a fixed 30 days with date arithmetic instead of one calendar month, landing on 01-MAR-2021 rather than the end of February.

   2. B. SELECT ADD_MONTHS(DATE '2021-03-31', -1) FROM DUALCorrect answer

      31-Mar-2021 is the last day of March, so ADD_MONTHS applies its last-day rule and returns the last day of the target month; one month earlier is February 2021 (non-leap), whose last day is the 28th.

   3. C. SELECT TRUNC(DATE '2021-02-28', 'MM') FROM DUAL

      TRUNC(date, 'MM') snaps a date back to the first day of its month, returning 01-FEB-2021, not the month-end that ADD_MONTHS produces.

   4. D. SELECT ADD_MONTHS(DATE '2021-02-28', 1) FROM DUAL

      Shifts a month in the wrong direction (+1); because 28-Feb-2021 is itself the last day of February, the last-day rule returns the last day of March, 31-MAR-2021.

   Explanation

   ADD_MONTHS shifts a date by a whole number of months and, when the input is the last day of its month, returns the last day of the resulting month rather than the same day number; a negative count moves backward. This calendar-aware shift differs from subtracting a fixed number of days and from TRUNC, which only snaps a date to the start of a period.

6. Question 6

   What value does the following query return? ```sql SELECT RPAD('Oracle', 4, '*') FROM DUAL ```

   1. A. Oracle

      This assumes RPAD leaves the string unchanged when the target length is smaller than the string. Instead the function forces the result to exactly the requested length, truncating the input when that length is shorter than the original.

   2. B. Orac**

      This treats the second argument as a count of pad characters to append, producing a six-character result. The second argument is the total target length of the result, not the number of pad characters; here it is 4, which is shorter than the input.

   3. C. Ora*

      This truncates to three characters and appends one pad character. When the target length is shorter than the input, RPAD simply cuts the string to that length and adds no padding at all, because there is no room left to pad.

   4. D. OracCorrect answer

      The second argument is the total target length of the result. Because 4 is less than the length of 'Oracle' (6), there is nothing to pad; RPAD instead truncates the input to the first 4 characters, yielding 'Orac'. The pad character is never used.

   Explanation

   The second argument to RPAD (and LPAD) is the total length of the returned string, not a count of characters to add. When that target length is greater than the input, the pad character fills the remaining positions; when it equals or exceeds the input length the string is padded or returned as-is. When the target length is shorter than the input, the function truncates the string to that length and the pad character plays no role at all.

7. Question 7

   Which of the following queries returns the value **1400**?

   1. A. SELECT TRUNC(1350, -2) FROM DUAL

      TRUNC with -2 chops toward zero to the nearest lower hundred rather than rounding, giving 1300 — it never rounds the tie up.

   2. B. SELECT ROUND(1350, -3) FROM DUAL

      -3 rounds to the nearest thousand, not hundred; 1350 is closer to 1000 than to 2000, so this returns 1000.

   3. C. SELECT ROUND(1350, 2) FROM DUAL

      A positive second argument sets decimal places to the right of the point; rounding an integer to 2 decimals leaves it unchanged at 1350.

   4. D. SELECT ROUND(1350, -2) FROM DUALCorrect answer

      A negative second argument rounds to the left of the decimal point: -2 rounds to the nearest hundred. 1350 is exactly halfway between 1300 and 1400, and ROUND breaks ties away from zero, so the result is 1400.

   Explanation

   ROUND and TRUNC accept a negative second argument to operate to the left of the decimal point: -2 works to the nearest hundred and -3 to the nearest thousand. ROUND moves to the nearest multiple, breaking ties away from zero, while TRUNC drops the lower digits toward zero; a positive argument instead fixes decimal places and leaves an integer untouched.

8. Question 8

   What value does the following query return? ```sql SELECT REPLACE('MISSISSIPPI', 'S') FROM dual ```

   1. A. MISSISSIPPI

      Assumes REPLACE requires a third argument and leaves the string unchanged when it is missing; Oracle instead deletes the search string.

   2. B. MISISSIPPI

      Assumes REPLACE changes only the first match, as a non-global replace in some languages would; Oracle acts on every occurrence.

   3. C. NULL

      Assumes the missing (NULL) replacement propagates and makes the whole result NULL; a NULL or omitted replacement instead simply removes the search string.

   4. D. MIIIPPICorrect answer

      With the replacement_string omitted, REPLACE removes every occurrence of the search string, deleting all four 'S' characters from 'MISSISSIPPI' and leaving 'MIIIPPI'.

   Explanation

   REPLACE takes an optional replacement string; when it is omitted, every occurrence of the search string is deleted rather than substituted. REPLACE is also global, acting on all matches instead of just the first. Treating the missing argument as a no-op, as a NULL that propagates to the whole result, or as a single-match replacement each misreads one of these rules.