Single-Row Functions practice questions

Worked examples for Single-Row Functions

1. Question 1

   Only the row shown below is relevant: ``` EMP_ID FIRST_NAME HIRE_DATE ------ ---------- ---------- 102 Carol 2019-07-22 ``` `hire_date` is a `DATE` whose time component is midnight. What value does the following query return (shown as `YYYY-MM-DD`)? ```sql SELECT ROUND(hire_date, 'MM') AS result FROM employees WHERE emp_id = 102 ```

   1. A. 2019-07-01

      Applies TRUNC semantics to ROUND — assumes the 'MM' model always drops back to the first day of the date's own month. ROUND only does that when the day of month is 15 or earlier.

   2. B. 2019-07-31

      Treats month rounding as snapping to the nearest month boundary expressed as the last day of the month, i.e. confuses ROUND(d,'MM') with LAST_DAY(d). Oracle's date rounding always lands on the first day of a month, never the last.

   3. C. 2019-07-22

      Assumes a format model only affects the time-of-day portion, so a DATE already at midnight is returned unchanged. The format model selects the unit that is rounded, and 'MM' rounds the month, changing the date part.

   4. D. 2019-08-01Correct answer

      With the 'MM' format model the rounding pivot is the 16th day of the month: day 22 is on or after the 16th, so the value rounds up to the first day of the next month.

   Explanation

   ROUND on a DATE with a format model rounds the value to the unit that model names, and for 'MM' the result is always the first day of some month. The documented pivot is the sixteenth day: days 1 through 15 round back to the first of the same month, while day 16 onward rounds forward to the first of the following month. Truncating instead of rounding would always give the first of the current month, and neither operation ever produces a month-end date.

2. Question 2

   What value does the following query return? ```sql SELECT TRUNC(-7.567, 2) AS a, TRUNC(-7.567) AS b, ROUND(-7.565, 2) AS c FROM dual; ```

   1. A. -7.57, -8, -7.57

      Treats TRUNC as if it rounded (or as if it moved toward negative infinity). TRUNC discards the digits beyond the requested precision without any rounding and always moves the value toward zero, so -7.567 truncates to -7.56 at two places and to -7 with no precision argument.

   2. B. -7.56, -7, -7.56

      Assumes ROUND breaks a tie toward zero for negative numbers. Oracle's ROUND rounds a half-way value away from zero regardless of sign, so -7.565 at two decimal places becomes -7.57, not -7.56.

   3. C. -7.56, -7.567, -7.57

      Assumes the omitted second argument leaves the value at its own scale. When the precision argument is omitted, TRUNC defaults to 0 decimal places, so TRUNC(-7.567) returns -7 rather than the original value.

   4. D. -7.56, -7, -7.57Correct answer

      TRUNC(-7.567, 2) drops everything past two decimal places, moving toward zero, giving -7.56; TRUNC with the precision argument omitted defaults to 0 decimal places, giving -7; ROUND breaks the half-way value away from zero, so ROUND(-7.565, 2) is -7.57.

   Explanation

   TRUNC on a number chops off the digits beyond the requested precision without inspecting them, so it always moves the value toward zero; when the precision argument is omitted it defaults to 0 decimal places. ROUND, by contrast, examines the discarded digits and breaks an exact half-way value away from zero, which for a negative operand means moving to the more negative value. Those two rules — truncation toward zero with a default precision of 0, and rounding half away from zero — decide each column independently.

3. Question 3

   A report must show every employee's LAST_NAME folded to uppercase and padded on the right with period characters so that each value is exactly 8 characters wide — for example the name `King` must appear as `KING....` and the name `Petrov` must appear as `PETROV..` (no last name in the table is longer than 8 characters). Which query produces that result?

   1. A. SELECT RPAD(UPPER(last_name), 8, '.') AS padded_name FROM employees;Correct answer

      RPAD(expr1, n, expr2) returns expr1 right-padded with expr2 to a TOTAL length of n, so UPPER('King') padded to 8 with '.' is 'KING....' and UPPER('Petrov') is 'PETROV..' (SQL Language Reference, RPAD).

   2. B. SELECT LPAD(UPPER(last_name), 8, '.') AS padded_name FROM employees;

      Confuses the padding direction: LPAD(expr1, n, expr2) pads on the LEFT, so 'King' becomes '....KING' — the periods land before the name instead of after it.

   3. C. SELECT RPAD(UPPER(last_name), 8) AS padded_name FROM employees;

      Omits the pad-character argument. When expr2 is omitted RPAD defaults to a single blank, so 'King' becomes 'KING ' — right width, but spaces rather than periods.

   4. D. SELECT RPAD(UPPER(last_name), LENGTH(last_name) + 8, '.') AS padded_name FROM employees;

      Reads RPAD's n argument as the NUMBER OF PAD CHARACTERS TO ADD rather than the total length of the result, so it appends 8 periods to every name — 'King' becomes the 12-character 'KING........'.

   Explanation

   RPAD pads on the right and LPAD pads on the left, and in both the second argument is the total length of the value returned, not the number of pad characters appended. The pad-character argument is optional and defaults to a single blank, so it must be supplied explicitly when a different filler is wanted. Because UPPER only changes letters, folding the name to uppercase before padding gives an uppercase name followed by enough periods to reach the requested width.

4. Question 4

   Single-row functions can be nested to any depth, and Oracle evaluates a nested expression from the innermost level outward. What value does the following query return? ```sql SELECT NVL(TO_CHAR(NULLIF(LENGTH(TRIM(' Sales ')), 5)), 'MATCH') AS result FROM dual; ```

   1. A. MATCHCorrect answer

      Innermost-first: TRIM(' Sales ') = 'Sales', LENGTH = 5, NULLIF(5, 5) = NULL because the arguments are equal, TO_CHAR(NULL) = NULL, and NVL then substitutes its second argument, 'MATCH'.

   2. B. 5

      Inverts NULLIF: it assumes NULLIF returns expr1 when the two arguments are equal. NULLIF returns NULL when expr1 = expr2 and returns expr1 only when they differ, so the 5 never reaches TO_CHAR.

   3. C. 9

      Evaluates the nest outer-to-inner, measuring the untrimmed literal: ' Sales ' is 9 characters, so NULLIF(9, 5) would return 9. Oracle evaluates innermost-first, so TRIM runs before LENGTH and the length is 5, not 9.

   4. D. NULL

      Assumes NVL cannot replace a NULL that was produced by an expression rather than read from a column. NVL substitutes whenever its first argument evaluates to NULL, regardless of how that NULL arose, so the result is the replacement string.

   Explanation

   A nested single-row function expression is resolved from the deepest level outward, and each level sees only the value the level below it produced. Here trimming happens before the length is measured, so the length equals the value NULLIF is comparing against; NULLIF returns NULL for equal arguments, that NULL survives the character conversion, and the outermost NVL replaces it with its substitution string. Measuring the literal before trimming, or reading NULLIF as returning its first argument on a match, each break the chain at a different level.

5. Question 5

   What value does this query return? ```sql SELECT ROUND(-45.678, 2), TRUNC(-45.678, 2), ROUND(-45.5) FROM dual ```

   1. A. -45.68, -45.67, -45

      Assumes ROUND resolves an exact half toward zero (or simply drops the fraction). Oracle's ROUND breaks a tie away from zero, so ROUND(-45.5) is -46, not -45.

   2. B. -45.68, -45.67, -46Correct answer

      ROUND(-45.678, 2) inspects the third decimal (8) and rounds the magnitude up to -45.68; TRUNC(-45.678, 2) discards everything past two decimals without rounding, giving -45.67; ROUND(-45.5) is a tie and Oracle rounds halves away from zero, giving -46.

   3. C. -45.67, -45.68, -46

      Swaps the two functions' behaviour at the second decimal — treats ROUND as discarding the extra digits and TRUNC as rounding them. ROUND rounds; TRUNC never does.

   4. D. -45.68, -45.68, -46

      Treats TRUNC(n, i) as rounding to i places. TRUNC only cuts the digits beyond position i, leaving the retained digit untouched, so it yields -45.67.

   Explanation

   ROUND(n, i) and TRUNC(n, i) both keep i decimal places, but only ROUND consults the discarded digits: it adjusts the last retained digit, and an exact half is resolved away from zero, so a negative half becomes more negative. TRUNC simply drops the digits beyond position i regardless of their value, which for a negative number moves the result toward zero. Applying each rule to the same operand is what makes the two results differ in the final digit.

6. Question 6

   What value does the following query return? ```sql SELECT MONTHS_BETWEEN(DATE '2020-02-29', DATE '2019-02-28') FROM dual; ```

   1. A. 12Correct answer

      2019-02-28 is the last day of February 2019 and 2020-02-29 is the last day of February 2020 (leap year). MONTHS_BETWEEN specifies that if both dates are the last days of their months the result is an integer, so the differing day numbers contribute no fraction and the answer is exactly 12.

   2. B. 12.0322580645161

      Applies the general fractional formula (12 months + (29 - 28)/31) while ignoring the documented exception: when BOTH arguments are the last day of their respective months, MONTHS_BETWEEN skips the day-fraction term entirely and returns a whole number.

   3. C. 366

      Confuses MONTHS_BETWEEN with the date-subtraction operator: DATE '2020-02-29' - DATE '2019-02-28' yields 366 days. MONTHS_BETWEEN is documented to return a number of months, not days.

   4. D. -12

      Reverses the argument order. MONTHS_BETWEEN(date1, date2) evaluates date1 minus date2, so the later date must be the first argument to get a positive result; here the later date already is first.

   Explanation

   MONTHS_BETWEEN normally computes whole months from the year and month components and then adds a fraction of (day1 - day2)/31 for the leftover days. That fractional term is suppressed by an explicit rule: when both arguments fall on the last day of their own month, the result is an integer regardless of the differing day numbers, which is what makes a February 28 to February 29 comparison come out clean. Argument order matters too — the function returns the first date minus the second — and its unit is months, unlike the subtraction operator, which returns days.

7. Question 7

   The string `'ABABABAB'` contains the substring `'AB'` starting at character positions 1, 3, 5 and 7. What value does this query return? ```sql SELECT INSTR('ABABABAB', 'AB', -1, 2) AS result FROM dual ```

   1. A. 3

      Counts occurrences from the left even though the position argument is negative. A negative position reverses the scan direction, so occurrence 2 is the second match found going backward, not the second match from the start of the string.

   2. B. 5Correct answer

      A negative position makes Oracle count back from the end (position 8 here) and search BACKWARD from there. Scanning backward, occurrence 1 is the match at 7 and occurrence 2 is the match at 5; INSTR reports the position of that match measured from the beginning of the string, so the result is 5.

   3. C. 7

      Applies the backward scan but drops the occurrence argument, returning the first match encountered going backward. INSTR's fourth argument selects which match in the scan direction to report, so the second one — not the first — is required.

   4. D. 0

      Assumes a negative position means 'no valid starting point' and therefore no match. A negative position is legal: Oracle counts backward from the end of the string to derive the starting point, and 0 is returned only when the requested occurrence genuinely does not exist.

   Explanation

   INSTR's third argument is a starting position and its fourth is an occurrence number, and the two interact. When the position is negative, Oracle counts backward from the end of the string to locate the start point and then searches backward from it, so the occurrence number is applied in right-to-left order — occurrence 1 is the rightmost qualifying match, occurrence 2 the next one to its left, and so on. Whichever match is selected, the value returned is always its position counted from the beginning of the string, which is why a backward search still yields a positive left-based index.

8. Question 8

   For every row in EMPLOYEES you must build a LOGIN value that is the first letter of FIRST_NAME followed by the complete LAST_NAME, with the whole thing in uppercase and padded on the right with period characters until it is exactly 10 characters wide (for example, first name `Alice` and last name `King` must yield `AKING.....`). No last name is long enough for the result to need truncating. Which query produces that value for every employee?

   1. A. SELECT RPAD(UPPER(SUBSTR(first_name, 1, 1) || last_name), 10, '.') AS login FROM employeesCorrect answer

      SUBSTR(first_name, 1, 1) takes one character starting at the 1-based first position, || joins it to LAST_NAME, UPPER folds the joined string, and RPAD(expr1, 10, '.') appends periods on the right until the total length is 10 — 'AKING.....' (RPAD reference: expr2 is appended to the right of expr1 until the result is n characters long).

   2. B. SELECT LPAD(UPPER(SUBSTR(first_name, 1, 1) || last_name), 10, '.') AS login FROM employees

      Reads LPAD as naming the side the value ends up on rather than the side the padding is added to. LPAD(expr1, n, expr2) prepends expr2, so Alice King becomes '.....AKING' — the periods lead instead of trail.

   3. C. SELECT RPAD(INITCAP(SUBSTR(first_name, 1, 1) || last_name), 10, '.') AS login FROM employees

      Treats INITCAP as an uppercasing function. INITCAP capitalizes the first letter of each word and forces every remaining letter to lowercase, so 'AKing' becomes 'Aking' and the result is 'Aking.....', not the required all-uppercase form.

   4. D. SELECT RPAD(UPPER(SUBSTR(first_name, 1, 1) || last_name), 10) AS login FROM employees

      Assumes RPAD's pad argument is optional only in syntax and that the padding character is inferred. When expr3 is omitted it defaults to a single blank, so this pads with spaces ('AKING ') and never emits a period.

   Explanation

   Building a fixed-width identifier requires three separate decisions, and each is governed by its own rule: SUBSTR uses 1-based positions to pull the leading initial, the case function must be UPPER because INITCAP lowercases every character after the first letter of each word, and the padding function must be the one that appends on the right — RPAD — with its pad character stated explicitly, since omitting that argument makes the pad a single blank rather than the requested character. Getting any one of the three wrong changes the produced string even though the query still runs without error.