Conversion Functions and Conditional Expressions practice questions

Worked examples for Conversion Functions and Conditional Expressions

1. Question 1

   Which query returns exactly the character string `0.08`?

   1. A. SELECT TO_CHAR(0.075, 'FM9.99') FROM DUAL

      Treats '9' and '0' as interchangeable in the integer position: the value rounds to 0.08, but a '9' element is left blank when its digit is a leading zero, so the result is .08 with no leading 0.

   2. B. SELECT TO_CHAR(0.075, 'FM0.0') FROM DUAL

      Assumes the mask does not drive rounding: with a single decimal place the value is rounded to one decimal, so 0.075 becomes 0.1, not 0.08.

   3. C. SELECT TO_CHAR(0.075, 'FM0.00') FROM DUALCorrect answer

      The value is rounded to the two decimal places in the mask (half away from zero), so 0.075 becomes 0.08; the '0' in the integer position forces the leading zero to appear, and FM only removes padding blanks, so the result is exactly 0.08.

   4. D. SELECT TO_CHAR(0.075, 'FM0.000') FROM DUAL

      Assumes FM trims to two decimals: three decimal places preserve the value in full as 0.075, and FM does not strip a significant trailing digit.

   Explanation

   TO_CHAR rounds a number to the count of digits that follow the decimal point in the format model, so the number of decimal places in the mask determines the value produced. A '0' element forces a digit to appear (including a leading zero), whereas a '9' element is blank when that position holds a leading zero. FM removes only padding blanks and does not trim digits that a '0' element or the value itself requires.

2. Question 2

   Which query labels every employee who has no recorded commission with `'None'` and every employee who earns a commission with `'Earns'`?

   1. A. SELECT first_name, CASE commission WHEN NULL THEN 'None' ELSE 'Earns' END AS label FROM employees ORDER BY emp_id

      A simple CASE tests commission = NULL, which evaluates to UNKNOWN for every row (including the NULL ones), so no WHEN matches and all eight rows fall to ELSE, labeling everyone 'Earns'. Simple CASE cannot detect a NULL selector.

   2. B. SELECT first_name, DECODE(commission, NULL, 'None') AS label FROM employees ORDER BY emp_id

      DECODE matches the NULL commissions to 'None', but with no default argument the commissioned rows return NULL instead of 'Earns', so half the table is left unlabeled.

   3. C. SELECT first_name, DECODE(commission, NULL, 'Earns', 'None') AS label FROM employees ORDER BY emp_id

      This matches NULL correctly via DECODE, but the result and default expressions are swapped, so employees with no commission are labeled 'Earns' and the rest 'None' — the inverse of the requirement.

   4. D. SELECT first_name, DECODE(commission, NULL, 'None', 'Earns') AS label FROM employees ORDER BY emp_idCorrect answer

      DECODE compares the expression against each search value using an equality that treats two NULLs as equal, so a NULL commission matches the NULL search and returns 'None', while every commissioned row falls through to the default 'Earns'. This NULL-matching is unique to DECODE.

   Explanation

   A simple CASE tests its selector with equality (selector = comparison_expr), and because NULL = NULL is UNKNOWN rather than TRUE, a simple CASE can never match a NULL selector. DECODE is the exception: it considers two NULLs equivalent, so it alone can map a NULL input to a chosen value, with its final bare argument supplying the default for unmatched rows. Correctly labeling an absent commission therefore requires DECODE's NULL-aware match plus a default in the right position.

3. Question 3

   The exhibit shows the relevant columns for one employee. What value does the query return? **Exhibit — employees (emp_id = 106):** ``` | emp_id | first_name | commission | manager_id | dept_id | |--------|------------|------------|------------|---------| | 106 | Grace | .2 | NULL | 30 | ``` ```sql SELECT COALESCE(manager_id, commission * 100, dept_id) FROM employees WHERE emp_id = 106; ```

   1. A. 20Correct answer

      COALESCE returns the first non-null expression in its list, evaluated left to right. manager_id is NULL, so evaluation advances to the second argument: commission * 100 = 0.20 * 100 = 20. Because 20 is not NULL, COALESCE returns 20 without examining dept_id at all.

   2. B. 30

      30 is dept_id, the third argument. COALESCE reaches the third argument only when all earlier arguments evaluate to NULL. Here commission * 100 = 20, which is not NULL, so the third argument is never consulted.

   3. C. .2

      COALESCE evaluates the full expression commission * 100, not the raw commission column. The value .2 is what commission itself holds; but Oracle computes 0.20 * 100 = 20 before testing for NULL, so 20 — not the unevaluated column value — is what COALESCE selects and returns.

   4. D. NULL

      COALESCE does not halt when it encounters a NULL argument; it advances to the next expression in the list. Because commission * 100 evaluates to 20 (a non-null value), COALESCE returns 20, not NULL.

   Explanation

   COALESCE scans its argument list left to right and returns the first expression that evaluates to a non-null value; encountering a NULL for one argument does not stop evaluation. Equally important, each argument is a full SQL expression that Oracle evaluates before testing for NULL. With manager_id NULL, evaluation moves to the second argument: the expression commission * 100 is computed as 0.20 × 100 = 20. Because 20 is non-null, COALESCE returns 20 and never reaches dept_id.

4. Question 4

   What value does this query return? ```sql SELECT TO_CHAR(-1234.5, 'FM9999.9MI') FROM DUAL ```

   1. A. 1234.5-Correct answer

      MI prints a trailing minus sign for a negative value, and FM removes the padding blanks, so -1234.5 formats as the digits, decimal, and a trailing minus: 1234.5-.

   2. B. -1234.5

      Assumes the sign keeps its default leading position; MI places the sign at the trailing edge, so a leading minus is wrong.

   3. C. 1234.5

      Assumes MI affects only positive values (adding a trailing blank) and drops the sign for negatives; in fact MI is exactly what prints the trailing minus on a negative number.

   4. D. 1234.5MI

      Treats MI as literal text to be echoed; MI is a format element that controls sign placement, not characters printed verbatim.

   Explanation

   In a number format model, MI is a sign element that must appear in the last position: it prints a trailing minus for a negative value and a trailing blank for a positive one, instead of the default leading sign. FM strips the padding blanks the model would otherwise add, leaving just the digits, the decimal point, and the trailing minus.

5. Question 5

   The exhibit shows the relevant columns for one employee. What value does the query return? **Exhibit — employees (emp_id = 105):** ``` | emp_id | first_name | commission | manager_id | dept_id | |--------|------------|------------|------------|---------| | 105 | Frank | NULL | 106 | 30 | ``` ```sql SELECT COALESCE(commission, manager_id - 106, dept_id) FROM employees WHERE emp_id = 105; ```

   1. A. 30

      This answer treats the integer 0 as though it were NULL and skips past it to dept_id = 30. COALESCE tests only for NULLness: zero is a valid non-NULL number and is returned as soon as it is encountered, before dept_id is evaluated.

   2. B. 106

      This answer returns the raw column value of manager_id (106) instead of the result of the expression manager_id - 106. COALESCE evaluates each argument as a full expression; the second argument is the subtraction manager_id - 106, which equals 0, not 106.

   3. C. NULL

      COALESCE does not propagate NULL the way arithmetic does: it is explicitly designed to skip NULL arguments and return the first non-NULL result. Commission is NULL (skipped), but the second argument evaluates to manager_id - 106 = 0, which is not NULL, so the function returns 0.

   4. D. 0Correct answer

      COALESCE evaluates arguments left to right: commission is NULL (skipped), then manager_id - 106 = 106 - 106 = 0. Zero is not NULL, so COALESCE returns 0 immediately without evaluating the third argument, dept_id.

   Explanation

   COALESCE returns the first non-NULL result among its arguments, evaluating them strictly left to right. The decisive trap is the second argument: manager_id - 106 evaluates to the integer 0, and zero is not NULL. COALESCE applies only a NULLness test — it cannot distinguish zero from any other non-NULL number — so it stops at the second argument and returns 0 without reaching dept_id.

6. Question 6

   Which query returns exactly the character string `1.23E+03`, with no leading or trailing spaces?

   1. A. SELECT TO_CHAR(1234, '9.99EEEE') FROM dual

      Without FM the format reserves a leading blank for the sign, so this returns ' 1.23E+03' with a leading space, which is not an exact match for '1.23E+03'.

   2. B. SELECT TO_CHAR(1234, 'FM9.999EEEE') FROM dual

      Three 9s after the decimal keep three mantissa digits, so the exact value 1.234 needs no rounding and the result is '1.234E+03', not '1.23E+03'.

   3. C. SELECT TO_CHAR(1234, 'FM9EEEE') FROM dual

      With no decimal point in the mantissa the value is rounded to a single significant digit, so 1.234 becomes '1E+03'.

   4. D. SELECT TO_CHAR(1234, 'FM9.99EEEE') FROM dualCorrect answer

      EEEE renders scientific notation; 1234 = 1.234 x 10^3. The two 9s after the decimal give two mantissa digits, so 1.234 rounds to 1.23, and FM strips the leading sign blank — yielding '1.23E+03'.

   Explanation

   The EEEE element formats a number in scientific notation as a single-digit mantissa times a power of ten; the count of 9s after the decimal point fixes how many fractional mantissa digits appear, rounding the rest. FM removes the leading blank Oracle otherwise reserves for the sign. Changing the number of fractional 9s, or dropping FM, changes the exact characters returned.

7. Question 7

   Using the department-20 rows shown, which query returns the **total salary of all employees in department 20** (i.e. 18300)? **Exhibit — employees in dept 20:** ``` | emp_id | first_name | salary | dept_id | |--------|------------|--------|---------| | 101 | Bob | 6000 | 20 | | 102 | Carol | 7500 | 20 | | 103 | Dave | 4800 | 20 | ```

   1. A. SELECT SUM(CASE WHEN dept_id = 20 THEN 1 ELSE 0 END) FROM employees

      This sums 1 per department-20 row and 0 otherwise, so it COUNTS the department-20 employees (3), not their salaries. To total salaries the THEN branch must return salary, not the constant 1.

   2. B. SELECT SUM(salary) FROM employees

      This totals the salaries of ALL employees (51200), ignoring the department filter entirely. Without a CASE or WHERE restricting to dept_id = 20, every row's salary is added.

   3. C. SELECT SUM(CASE WHEN dept_id = 20 THEN salary ELSE 0 END) FROM employeesCorrect answer

      The CASE contributes each employee's salary when dept_id = 20 and 0 otherwise, and SUM adds those contributions across all rows: 6000 + 7500 + 4800 = 18300. This CASE-inside-aggregate pattern is the standard idiom for a conditional (pivot-style) total.

   4. D. SELECT SUM(CASE WHEN dept_id != 20 THEN salary ELSE 0 END) FROM employees

      The condition is inverted: this totals the salaries of employees NOT in department 20 (51200 − 18300 = 32900). Reversing the comparison selects the complement of the intended group.

   Explanation

   Embedding a CASE expression inside an aggregate is the idiomatic way to compute a conditional total. SUM(CASE WHEN dept_id = 20 THEN salary ELSE 0 END) adds each department-20 salary and 0 for every other row, yielding 6000 + 7500 + 4800 = 18300. Returning 1 instead of salary counts rows rather than summing pay; dropping the CASE sums every salary; and inverting the predicate sums the complementary group.

8. Question 8

   The following statement is executed against an Oracle database. What value does it return? ```sql SELECT NVL2('', 'not empty', 'empty') FROM DUAL ```

   1. A. emptyCorrect answer

      Oracle treats the zero-length string '' as NULL. NVL2(expr1, expr2, expr3) returns expr3 when expr1 IS NULL, so with '' behaving as NULL the function returns its third argument, 'empty'.

   2. B. not empty

      This assumes the empty string '' is a non-null value, which would select NVL2's second argument. In Oracle an empty string literal is treated as NULL, so the first argument IS NULL and NVL2 returns its third argument, not its second.

   3. C. The statement raises an error because '' is not allowed.

      An empty string literal is legal in Oracle; it is simply interpreted as NULL rather than as a distinct zero-length value. NVL2 accepts a NULL first argument and returns its third argument, so no error occurs.

   4. D. NULL

      NVL2 returns one of its explicit second or third arguments, both of which are non-null strings here. It does not propagate the NULL-ness of the first argument into the result; it uses it only to choose which branch to return — here the third argument 'empty'.

   Explanation

   Oracle's most notorious NULL quirk is that a zero-length string '' is stored and evaluated as NULL, not as an empty non-null value. NVL2(expr1, expr2, expr3) returns expr2 when expr1 IS NOT NULL and expr3 when expr1 IS NULL. Because '' is NULL, expr1 IS NULL, so NVL2 returns its third argument, 'empty'. Treating '' as a non-null value (yielding 'not empty') is the trap.