Conversion Functions and Conditional Expressions practice questions

Worked examples for Conversion Functions and Conditional Expressions

1. Question 1

   A report must render the clock time five minutes past eight in the evening as the character string `08:05 PM`. Which query returns exactly that string?

   1. A. SELECT TO_CHAR(TO_DATE('20:05', 'HH24:MI'), 'HH24:MI AM') FROM dual

      Assumes the AM meridian element makes the hour render in 12-hour form. The meridian indicator only appends AM or PM; HH24 still formats the hour as 0-23, so this produces '20:05 PM'.

   2. B. SELECT TO_CHAR(TO_DATE('20:05', 'HH:MI'), 'HH:MI AM') FROM dual

      Assumes HH in a conversion format model accepts a 24-hour value. HH means HH12 and constrains the hour to 1-12, so parsing '20' raises ORA-01849: hour must be between 1 and 12 and the query returns nothing.

   3. C. SELECT TO_CHAR(TO_DATE('08:05', 'HH24:MI'), 'HH:MI AM') FROM dual

      Assumes the meridian element in the output model can assign the afternoon half of the day. The meridian is derived from the stored hour, and HH24 read '08' as 8 in the morning, so this produces '08:05 AM'.

   4. D. SELECT TO_CHAR(TO_DATE('20:05', 'HH24:MI'), 'HH:MI AM') FROM dualCorrect answer

      Correct. HH24 on input accepts the hour 20, and on output HH (a synonym for HH12) renders it as 08 while the AM element supplies the correct meridian indicator PM, giving '08:05 PM'.

   Explanation

   Hour format elements are directional in effect but identical in meaning on both sides of a conversion: HH and HH12 cover 1-12 and HH24 covers 0-23. A 24-hour source string must therefore be parsed with HH24, or the value is rejected as out of range, while the character output must use HH or HH12 for a two-digit 12-hour clock. The AM/PM meridian element never rescales the hour — it merely reports which half of the day the stored time falls in — so pairing it with HH24 leaves the hour unchanged.

2. Question 2

   The only EMPLOYEES row involved is shown below. ``` EMP_ID FIRST_NAME MANAGER_ID ------ ---------- ---------- 106 Grace (null) ``` What value does the following query return? ```sql SELECT COALESCE(NULLIF(first_name, 'Grace'), NVL2(manager_id, 'MANAGED', 'TOP')) AS result FROM employees WHERE emp_id = 106; ```

   1. A. Grace

      Inverts NULLIF: it assumes NULLIF returns expr1 when the two arguments are equal and NULL when they differ. NULLIF returns NULL precisely because 'Grace' = 'Grace', so the first COALESCE argument is NULL and the name is never returned.

   2. B. TOPCorrect answer

      NULLIF('Grace','Grace') returns NULL because the arguments are equal, so COALESCE moves to its next argument; MANAGER_ID is null, so NVL2 returns its third argument, TOP, which is the first non-null value COALESCE finds.

   3. C. MANAGED

      Swaps NVL2's second and third arguments, reading NVL2(expr1, expr2, expr3) as returning expr2 when expr1 IS NULL. MANAGER_ID is NULL for this row, so NVL2 returns its third argument, not its second.

   4. D. NULL

      Assumes COALESCE propagates NULL — that the whole expression is NULL as soon as an argument evaluates to NULL, the way an arithmetic expression would. COALESCE instead skips NULL arguments and returns the first non-null one, so evaluation continues to the next argument.

   Explanation

   NULLIF is the mirror image of the usual NULL substitution functions: it manufactures a NULL when its two arguments are equal, and returns the first argument only when they differ. COALESCE then evaluates its arguments left to right and returns the first one that is not null, so a NULL produced upstream does not poison the result — it simply advances evaluation to the next argument. That next argument is a three-argument NVL2, which returns its second argument only when the tested expression is not null and its third argument when it is null. Chaining these correctly requires applying each function's own NULL rule in order rather than assuming NULL propagates through the whole expression.

3. Question 3

   The `EMPLOYEES` table has a numeric `COMMISSION` column that is NULL for some employees. Which query returns one row per employee containing `EMP_ID` and a character column holding `'YES'` when that employee's commission is not NULL and `'NO'` when it is NULL?

   1. A. SELECT emp_id, NVL2(commission, 'YES', 'NO') AS has_comm FROM employees ORDER BY emp_id;Correct answer

      NVL2(expr1, expr2, expr3) returns expr2 if expr1 is not null and expr3 if expr1 is null, so a non-NULL commission yields 'YES' and a NULL commission yields 'NO'. expr2 and expr3 are both character, so no conversion error arises and every row is returned.

   2. B. SELECT emp_id, NVL(commission, 'NO') AS has_comm FROM employees ORDER BY emp_id;

      Assumes NVL can substitute a string for a numeric expression. Because expr1 is numeric, Oracle implicitly converts the other argument to that numeric type, and converting 'NO' to NUMBER raises ORA-01722; NVL also cannot produce a different value for the non-NULL case.

   3. C. SELECT emp_id, NVL2(commission, 'NO', 'YES') AS has_comm FROM employees ORDER BY emp_id;

      Reads NVL2's second argument as the value used when expr1 IS NULL. NVL2(expr1, expr2, expr3) returns expr2 when expr1 is NOT NULL, so this labels commissioned employees 'NO' — the exact inverse of the requirement.

   4. D. SELECT emp_id, COALESCE(commission, 'YES', 'NO') AS has_comm FROM employees ORDER BY emp_id;

      Treats COALESCE as a three-way NVL2. COALESCE returns the first non-NULL argument and requires all arguments to be mutually comparable; mixing NUMBER with character literals raises ORA-00932, and even if it ran it could never return 'YES' for a non-NULL commission.

   Explanation

   NVL2 is the two-outcome null test: it evaluates its first argument and returns the second expression when that argument is NOT NULL, the third when it IS NULL — so the non-NULL label must come first. NVL only substitutes a value for NULL and leaves non-NULL values untouched, and COALESCE simply returns the first non-NULL argument; neither can map a value to one label and NULL to another. Both of those also impose data type compatibility on their arguments, so pairing a numeric column with character literals forces an implicit conversion that fails.

4. Question 4

   The `employees` table has a `first_name` column of type `VARCHAR2(30)` and a `commission` column of type `NUMBER(4,2)` that is NULL for some employees. You must produce one row per employee containing `first_name` and a second column that shows the employee's commission rendered as text when a commission is recorded, and the literal text `NONE` when the commission is NULL. The statement must run without raising an error. Which query does this?

   1. A. SELECT first_name, NVL(TO_CHAR(commission), 'NONE') AS comm_text FROM employeesCorrect answer

      TO_CHAR(commission) makes expr1 character data (and yields NULL for a NULL commission, since TO_CHAR of NULL is NULL), so NVL converts expr2 to expr1's character type and legally substitutes 'NONE' only for the NULL rows.

   2. B. SELECT first_name, NVL2(commission, 'NONE', TO_CHAR(commission)) AS comm_text FROM employees

      Reverses NVL2's argument order. NVL2(expr1, expr2, expr3) returns expr2 when expr1 is NOT NULL and expr3 when expr1 IS NULL, so this prints 'NONE' for employees who DO have a commission and NULL for those who do not — the exact inverse of the requirement.

   3. C. SELECT first_name, NVL(commission, 'NONE') AS comm_text FROM employees

      Assumes NVL converts expr1 to match expr2. The conversion runs the other way: because expr1 (commission) is numeric, Oracle implicitly converts expr2 to NUMBER, and converting the character literal 'NONE' to a number fails at run time with ORA-01722.

   4. D. SELECT first_name, COALESCE(commission, 'NONE') AS comm_text FROM employees

      Assumes COALESCE mixes datatypes freely. Every argument after the first must be implicitly convertible to the datatype of the first expression, so a character literal supplied against a NUMBER first argument is rejected as an inconsistent datatype rather than substituted.

   Explanation

   NVL(expr1, expr2) does not coerce expr1 — it coerces expr2 to expr1's datatype, using the argument of higher numeric precedence when expr1 is numeric. Substituting a character default for a numeric column therefore requires converting the column to character first; otherwise the default is forced through a number conversion and fails. NVL2 is a three-argument test whose second argument is the NOT-NULL result and whose third is the NULL result, so swapping those two inverts the output without raising any error.

5. Question 5

   A session has `NLS_NUMERIC_CHARACTERS` set to `'.,'` (decimal character `.`, group separator `,`). Which query returns the character string `1,234.50` exactly — eight characters, with no leading blank?

   1. A. SELECT TO_CHAR(1234.5, 'FM9,999.00') FROM dualCorrect answer

      Correct. FM (fill mode) suppresses the blank held for the sign, the comma in the model emits the group separator, and the 0 elements after the decimal character force a digit in each position — so the single stored decimal digit is padded to '1,234.50' (Number Format Models: FM returns a value with no leading or trailing blanks; 0 returns leading and trailing zeros).

   2. B. SELECT TO_CHAR(1234.5, '9,999.99') FROM dual

      Assumes a positive number is formatted flush left. Without fill mode or an explicit sign element (S, MI, PR), TO_CHAR reserves one leading position for the sign and fills it with a blank for a positive value, so this returns ' 1,234.50' — nine characters, with a leading blank.

   3. C. SELECT TO_CHAR(1234.5, 'FM9,999') FROM dual

      Treats TO_CHAR as truncating the fractional part. When the model supplies fewer decimal positions than the value has, TO_CHAR rounds rather than truncates, so this returns '1,235' — no decimal places at all.

   4. D. SELECT TO_CHAR(1234.5, 'FM9999.00') FROM dual

      Assumes the group separator is inserted automatically from NLS_NUMERIC_CHARACTERS. The separator appears only where the format model itself contains a comma (or a G element), so this returns '1234.50' with no comma.

   Explanation

   A number format model is applied literally, position by position. A positive value keeps one leading blank for its sign unless fill mode or an explicit sign element removes it; a group separator appears only where the model contains a comma or G; and the number of decimal positions in the model decides the output, with the value rounded — never truncated — to fit. The 0 element differs from 9 in that it forces a digit in its position, which is what preserves a trailing zero in the fractional part.

6. Question 6

   `EMPLOYEES.COMMISSION` is declared as `NUMBER(4,2)` and is NULL for employees who earn no commission. What happens when this statement is executed? ```sql SELECT emp_id, COALESCE(commission, 'None') AS comm FROM employees ORDER BY emp_id; ```

   1. A. The statement succeeds, returning the commission value where it is not null and the text None otherwise.

      Assumes COALESCE only has to find the first non-null argument, with no data type requirement among its arguments. COALESCE is evaluated as a CASE expression, whose result expressions must share a data type, so the mixed NUMBER/CHAR argument list is rejected before any row is produced.

   2. B. ORA-01722: invalid number

      Treats COALESCE like NVL, which implicitly converts its second argument to the first argument's data type and fails at run time converting the string 'None' to a number. COALESCE does not perform that implicit conversion; it enforces type agreement and fails at parse time with a different error.

   3. C. The statement succeeds, returning NULL in COMM for every employee whose commission is null.

      Assumes an argument that cannot be converted to the return type is silently skipped, leaving the result null. Oracle never silently discards a COALESCE argument — an incompatible argument type is an error, not a no-op.

   4. D. ORA-00932: inconsistent datatypesCorrect answer

      COALESCE is equivalent to a searched CASE expression, and all of its arguments must be of the same data type (or implicitly convertible under CASE's rules). Mixing the NUMBER column with the character literal 'None' raises ORA-00932: inconsistent datatypes: expected NUMBER got CHAR.

   Explanation

   COALESCE is defined as shorthand for a searched CASE expression, so it inherits CASE's requirement that all result expressions share one data type; it does not do the one-way implicit conversion that NVL performs on its second argument. Supplying a character literal as the fallback for a NUMBER column therefore fails the type check outright rather than failing later on a value conversion, and the statement never returns rows. To default a numeric column to text, convert the column explicitly first — for example COALESCE(TO_CHAR(commission), 'None').

7. Question 7

   A report must show one row per employee containing the last name and a pay-type label: the literal `SALARIED` for every employee whose `commission` column is null, and the literal `COMMISSIONED` for every other employee. Which query produces exactly that labelling?

   1. A. SELECT last_name, DECODE(commission, NULL, 'SALARIED', 'COMMISSIONED') AS pay_type FROM employeesCorrect answer

      DECODE is the one comparison construct in which Oracle considers two nulls to be equivalent, so the NULL search value matches every row whose commission is null and yields SALARIED; all remaining rows fall to the trailing default and yield COMMISSIONED (DECODE, Oracle SQL Language Reference).

   2. B. SELECT last_name, CASE commission WHEN NULL THEN 'SALARIED' ELSE 'COMMISSIONED' END AS pay_type FROM employees

      Assumes a simple CASE `WHEN NULL` matches null rows. A simple CASE compares the selector to each WHEN operand with equality, and `commission = NULL` is UNKNOWN — never TRUE — so every row, null commission included, falls to ELSE and is labelled COMMISSIONED.

   3. C. SELECT last_name, NVL2(commission, 'SALARIED', 'COMMISSIONED') AS pay_type FROM employees

      Reads NVL2's second argument as the null-case result. NVL2(expr1, expr2, expr3) returns expr2 when expr1 is NOT null and expr3 when it is null, so this labels commissioned employees SALARIED and vice versa — the labels are reversed.

   4. D. SELECT last_name, DECODE(commission, NULL, 'SALARIED') AS pay_type FROM employees

      Omits DECODE's optional default, assuming a non-matching row keeps its original value. When no search value matches and no default is supplied, DECODE returns null, so employees who do have a commission get a null label instead of COMMISSIONED.

   Explanation

   DECODE and simple CASE part company exactly on nulls. DECODE treats two nulls as equivalent, so a NULL search value genuinely matches rows whose expression is null; a simple CASE compares its selector to each WHEN operand with equality, and comparing anything to NULL yields UNKNOWN, so a `WHEN NULL` branch can never fire and every row drops to ELSE. DECODE's final argument is an optional default: leave it out and unmatched rows return null rather than a label. NVL2 is not a null test in the same direction — it returns its second argument when the first is not null.

8. Question 8

   A salary report must show one row per employee with two columns: `EMP_ID` and a `BAND` column holding `HIGH` when the employee's salary is 8000 or more, `MID` when the salary is at least 6000 but less than 8000, and `LOW` when the salary is below 6000. Salary is never null. Which query produces exactly that classification for every employee?

   1. A. SELECT emp_id, CASE WHEN salary >= 6000 THEN 'MID' WHEN salary >= 8000 THEN 'HIGH' ELSE 'LOW' END AS band FROM employees

      Assumes a searched CASE picks the 'best' or most specific matching WHEN. Oracle evaluates the conditions top to bottom and stops at the FIRST one that is TRUE, so a salary of 9000 satisfies `salary >= 6000` first and is labelled MID; the `>= 8000` branch is unreachable and HIGH is never returned.

   2. B. SELECT emp_id, DECODE(SIGN(salary - 8000), 1, 'HIGH', DECODE(SIGN(salary - 6000), 1, 'MID', 'LOW')) AS band FROM employees

      Treats DECODE(SIGN(x)) as a `>=` test, forgetting that DECODE matches by equality only and SIGN returns 0 — not 1 — at the boundary. An employee earning exactly 6000 gives SIGN(0) = 0, matches neither search value, and falls through to the LOW default instead of MID (the same flaw would mislabel a salary of exactly 8000).

   3. C. SELECT emp_id, CASE WHEN salary >= 8000 THEN 'HIGH' WHEN salary >= 6000 THEN 'MID' ELSE 'LOW' END AS band FROM employeesCorrect answer

      A searched CASE returns the result of the first WHEN condition that evaluates to TRUE, so ordering the conditions from the highest threshold down makes each band exclusive: `>= 8000` claims the HIGH rows before the `>= 6000` branch is reached, and the ELSE supplies LOW for everything under 6000.

   4. D. SELECT emp_id, CASE WHEN salary >= 8000 THEN 'HIGH' WHEN salary >= 6000 THEN 'MID' END AS band FROM employees

      Assumes a CASE with no ELSE falls back to some default value. When no condition is TRUE and ELSE is omitted, the CASE expression returns NULL, so every employee below 6000 gets a null BAND rather than the required LOW.

   Explanation

   A searched CASE evaluates its WHEN conditions in the order written and returns the result of the first condition that is TRUE, so overlapping range tests must be ordered from the most restrictive threshold downward or the broader condition swallows the narrower one. When no condition is TRUE, the ELSE result is returned, and omitting ELSE yields NULL rather than any implicit default. DECODE cannot express a range test directly because it compares only for equality — wrapping the comparison in SIGN turns a `>=` boundary into the unmatched value 0.