Oracle 1Z0-071: Conversion and CASE

Topic snapshot

Sample questions

These questions are original IT Mastery practice items aligned to this topic area. They are designed for self-assessment and are not official exam questions.

Question 1

Topic: Use Conversion Functions and Conditional Expressions

A developer passes the value '2026-04-18' as a character string. The orders.order_date column is DATE, and user sessions may have different NLS_DATE_FORMAT settings. Which WHERE clause explicitly converts that character value to DATE instead of relying on Oracle’s implicit conversion?

Options:

• A. WHERE order_date = TO_DATE('2026-04-18','YYYY-MM-DD')

• B. WHERE order_date = '2026-04-18'

• C. WHERE TO_CHAR(order_date,'YYYY-MM-DD') = '2026-04-18'

• D. WHERE order_date = DATE '2026-04-18'

Best answer: A

Explanation: Explicit conversion in Oracle SQL uses a conversion function such as TO_DATE with a format model. Comparing a DATE column to a character literal without that function causes Oracle to attempt an implicit conversion based on session settings.

The key rule is that implicit conversion happens automatically when Oracle must reconcile unlike data types, but its result can depend on session settings such as NLS_DATE_FORMAT. In this case, a character value is being compared to a DATE column, so the safest and clearest approach is to explicitly convert the string with TO_DATE and a matching format model.

TO_DATE('2026-04-18','YYYY-MM-DD') tells Oracle exactly how to interpret the incoming character data before the comparison. That makes the statement stable across sessions. Converting the column with TO_CHAR changes the comparison into character logic instead, and a date literal is valid syntax but is not converting the passed character string.

• Bare string comparison relies on Oracle to implicitly convert the character literal to DATE, so it can fail or change behavior with different NLS_DATE_FORMAT values.
• Converting the column with TO_CHAR is explicit conversion, but it converts DATE to character rather than converting the incoming character value to DATE.
• Date literal syntax is NLS-independent and valid, but it is not an explicit conversion of the supplied character string by a conversion function.

Question 2

Topic: Use Conversion Functions and Conditional Expressions

A report must return one character value in this exact format:

last_name - salary - hire_date
King - 24000 - 17-JUN-03

The source columns are last_name (VARCHAR2), salary (NUMBER), and hire_date (DATE). The output must be stable and not depend on session NLS settings. Which expression correctly applies Oracle SQL conversion rules?

Options:

• A. last_name || ' - ' || salary || ' - ' || hire_date

• B. TO_CHAR(last_name) || ' - ' || salary || ' - ' || hire_date

• C. last_name || ' - ' || TO_DATE(salary) || ' - ' || TO_NUMBER(hire_date)

• D. last_name || ' - ' || TO_CHAR(salary) || ' - ' || TO_CHAR(hire_date, 'DD-MON-RR')

Best answer: D

Explanation: When a result must be character data and must not vary by session settings, Oracle SQL should use explicit conversion. Converting the NUMBER and DATE values with TO_CHAR, and supplying a date format model, gives predictable output.

The core rule is to use explicit conversion when mixing character, number, and date values in a character result, especially when formatting matters. In concatenation, Oracle can implicitly convert NUMBER and DATE values to character data, but the resulting text can depend on session NLS settings. That makes the output unpredictable across sessions.

Here, the target output is a text string, so salary and hire_date should be converted with TO_CHAR. For the date, a format model such as DD-MON-RR is needed to guarantee the displayed form. Using only implicit conversion might still run, but it does not guarantee the required format.

The key takeaway is that explicit TO_CHAR conversion is the safe rule when building stable text from numeric and date columns.

• The expression using plain concatenation relies on implicit conversion for the number and date, so the displayed text can change with session NLS settings.
• The expression converting only last_name leaves the number and date still subject to implicit conversion, so it does not solve the real requirement.
• The expression using TO_DATE on a number and TO_NUMBER on a date applies the wrong conversion direction for a character output.

Question 3

Topic: Use Conversion Functions and Conditional Expressions

The EMPLOYEES table contains LAST_NAME and COMMISSION_PCT. You need a query that returns every employee and displays 0 when COMMISSION_PCT is NULL, without changing stored data. Which SQL statement should you use?

Options:

• A. SELECT last_name, commission_pct FROM employees WHERE commission_pct IS NOT NULL;

• B. SELECT last_name, NVL(commission_pct, 0) AS commission_pct FROM employees;

• C. SELECT last_name, NVL(0, commission_pct) AS commission_pct FROM employees;

• D. SELECT last_name, NULLIF(commission_pct, 0) AS commission_pct FROM employees;

Best answer: B

Explanation: NVL replaces a NULL expression with a stated default in query output. Using NVL(commission_pct, 0) shows 0 only for employees whose commission is NULL, and it does so without excluding rows or modifying table data.

The core concept is Oracle’s NVL function, which has the form NVL(expr1, expr2). If expr1 is NULL, Oracle returns expr2; otherwise it returns expr1. In this case, commission_pct is the value being checked, and 0 is the required display default.

Because the requirement is to return every employee and only change the displayed output, the NVL call belongs in the SELECT list:

• check commission_pct
• show 0 when it is NULL
• keep non-NULL commission values unchanged
• return all rows from EMPLOYEES

A filter that removes NULL rows does not meet the row-set requirement, and reversing the NVL arguments changes the result for every row.

• Reversed arguments fail because NVL(0, commission_pct) always returns 0; the first argument is never NULL.
• Wrong function fails because NULLIF(commission_pct, 0) compares values and can return NULL; it does not replace NULL with 0.
• Wrong row set fails because filtering with WHERE commission_pct IS NOT NULL removes employees whose commission is NULL instead of displaying 0 for them.

Question 4

Topic: Use Conversion Functions and Conditional Expressions

Which expression is a valid Oracle SQL searched CASE expression for returning 'HIGH', 'MEDIUM', or 'LOW' based on each row’s salary value?

Options:

• A. CASE salary >= 10000 THEN 'HIGH' WHEN salary >= 5000 THEN 'MEDIUM' ELSE 'LOW' END

• B. CASE WHEN salary >= 10000 THEN 'HIGH' WHEN salary >= 5000 THEN 'MEDIUM' ELSE 'LOW' END

• C. CASE salary WHEN >= 10000 THEN 'HIGH' WHEN >= 5000 THEN 'MEDIUM' ELSE 'LOW' END

• D. IF salary >= 10000 THEN 'HIGH' WHEN salary >= 5000 THEN 'MEDIUM' ELSE 'LOW' END

Best answer: B

Explanation: A searched CASE expression evaluates Boolean conditions in each WHEN clause. It returns the THEN value for the first true condition, and ELSE handles rows that match none of the conditions.

Oracle SQL supports two main CASE forms: simple and searched. A searched CASE is used when each branch is based on a condition such as salary >= 10000 or commission_pct IS NULL. Its pattern is CASE WHEN condition THEN value ... ELSE value END.

In this item, the requirement is to classify each row by salary range, so each WHEN must contain a Boolean test. Oracle checks the conditions from top to bottom and returns the first matching result. If no condition is true, the ELSE value is returned; if ELSE is omitted, Oracle returns NULL.

The closest confusion is the simple CASE form, which compares one expression to specific values rather than evaluating full conditions in each WHEN.

• Missing WHEN after CASE fails because searched CASE requires CASE WHEN condition THEN ... syntax.
• Simple CASE confusion fails because CASE salary WHEN ... compares salary to values, not to conditional operators like >= 10000.
• IF confusion fails because IF ... THEN is not valid in a SQL expression; Oracle SQL uses CASE for conditional output.

Question 5

Topic: Use Conversion Functions and Conditional Expressions

A developer runs this query against staging_orders, where order_dt_txt is VARCHAR2(10) and a sample value is 2026/04/18:

SELECT TO_DATE(order_dt_txt, 'DD-MON-YYYY')
FROM staging_orders;

Oracle returns ORA-01861: literal does not match format string. What is the best fix?

Options:

• A. Use a matching format model such as YYYY/MM/DD.

• B. Replace TO_DATE with CAST(order_dt_txt AS DATE).

• C. Sort the rows before applying TO_DATE.

• D. Add DISTINCT to remove rows causing the error.

Best answer: A

Explanation: This failure occurs because the source text does not match the format model passed to TO_DATE. The value 2026/04/18 is year-slash-month-slash-day, not day-month-name-year.

Oracle conversion functions require the source value to match the expected format. Here, TO_DATE(order_dt_txt, 'DD-MON-YYYY') expects values like 18-APR-2026, but the actual text is 2026/04/18. That mismatch causes ORA-01861.

The correct troubleshooting step is to align the format model with the stored character data, for example TO_DATE(order_dt_txt, 'YYYY/MM/DD'), or standardize the source data to the expected pattern. Changing row order or removing duplicates does not affect conversion, and CAST does not solve a text-format mismatch when the source string is not in the expected date representation.

The key takeaway is that explicit conversion works only when the input text matches the format mask.

• Using CAST is tempting, but it does not fix mismatched date text in the source value.
• Removing duplicates does not address why a character value fails date conversion.
• Sorting first changes presentation order only; it has no effect on format matching during conversion.

Question 6

Topic: Use Conversion Functions and Conditional Expressions

The orders table has order_date defined as DATE. A developer must compare order_date to the character value 2026-04-18. The predicate must be reliable across sessions with different NLS_DATE_FORMAT settings and must keep a DATE-to-DATE comparison. Which WHERE clause is the safest choice?

Options:

• A. WHERE order_date = ‘2026-04-18’

• B. WHERE TO_CHAR(order_date,‘YYYY-MM-DD’) = ‘2026-04-18’

• C. WHERE TO_DATE(order_date,‘YYYY-MM-DD’) = ‘2026-04-18’

• D. WHERE order_date = TO_DATE(‘2026-04-18’,‘YYYY-MM-DD’)

Best answer: D

Explanation: Explicit conversion is safer here because Oracle would otherwise have to implicitly convert the character literal to a DATE using session settings. Converting the literal with TO_DATE and a format model makes the comparison stable and keeps both sides as DATE values.

When Oracle compares a DATE column to a character literal, it may perform an implicit conversion from character to DATE. That conversion depends on session settings such as NLS_DATE_FORMAT, so the same SQL can behave differently in different environments. The safer rule is to explicitly convert the character value with the correct format model.

Using TO_DATE('2026-04-18','YYYY-MM-DD') makes the intended datatype and format unambiguous, so the predicate remains a DATE-to-DATE comparison. That is more stable than relying on Oracle to guess the format, and it is also cleaner than converting the column itself to character data. The key takeaway is: explicitly convert literals when datatype interpretation could vary.

• Comparing order_date directly to '2026-04-18' depends on implicit character-to-date conversion and can fail or change behavior with different session formats.
• Converting the column with TO_CHAR changes the comparison to character data instead of keeping a DATE-to-DATE predicate.
• Applying TO_DATE to the DATE column is incorrect usage because TO_DATE is meant for character input, not an existing DATE value.

Question 7

Topic: Use Conversion Functions and Conditional Expressions

Which searched CASE expression correctly classifies each row in employees as HIGH for salaries of 10,000 or more, MEDIUM for salaries from 5,000 through 9,999, and LOW for all other salaries?

Options:

• A. CASE WHEN salary >= 5000 THEN 'MEDIUM' WHEN salary >= 10000 THEN 'HIGH' ELSE 'LOW' END

• B. CASE WHEN salary >= 10000 THEN 'HIGH' WHEN salary >= 5000 THEN 'MEDIUM' ELSE 'LOW' END

• C. CASE WHEN salary BETWEEN 5000 AND 10000 THEN 'MEDIUM' WHEN salary > 10000 THEN 'HIGH' ELSE 'LOW' END

• D. CASE WHEN salary < 5000 THEN 'LOW' WHEN salary >= 5000 THEN 'MEDIUM' ELSE 'HIGH' END

Best answer: B

Explanation: A searched CASE returns the result from the first WHEN condition that is true. To classify overlapping thresholds correctly, the test for salaries of 10,000 or more must appear before the test for salaries of 5,000 or more.

Oracle uses searched CASE when each WHEN clause contains a condition, such as salary >= 10000. The conditions are evaluated from top to bottom, and evaluation stops at the first true condition. Because every salary of 10,000 or more also satisfies salary >= 5000, the higher threshold must be tested first. The ELSE clause supplies the default value for rows that do not match any earlier condition.

So the correct pattern is:

• test the most specific or highest range first
• test the next range second
• use ELSE for the remaining rows

The main trap is writing valid-looking conditions in the wrong order, which changes the returned value even though the expression is syntactically acceptable.

• Wrong order: checking salary >= 5000 first causes salaries of 10,000 or more to return MEDIUM before Oracle can reach the HIGH test.
• Boundary error: using BETWEEN 5000 AND 10000 puts a salary of exactly 10,000 into MEDIUM, which violates the stated rule.
• Unreachable high band: testing salary >= 5000 after salary < 5000 leaves no row for ELSE 'HIGH', so high salaries still return MEDIUM.

Question 8

Topic: Use Conversion Functions and Conditional Expressions

Which expression raises an Oracle error because the source value does not match the specified format model?

Options:

• A. TO_DATE('18/04/2026','DD-MON-YYYY')

• B. TO_DATE('2026-04-18','YYYY-MM-DD')

• C. TO_NUMBER('15250','99999')

• D. TO_TIMESTAMP('18-04-2026 14:30:00','DD-MM-YYYY HH24:MI:SS')

Best answer: A

Explanation: Oracle conversion functions that use explicit format models require the source text to match the model element by element. The date value 18/04/2026 does not match DD-MON-YYYY because MON expects a character month abbreviation, not a numeric month.

The core rule is that an explicit format model controls how Oracle interprets a character value during conversion. Every part of the source value must match the expected element type and position in the model.

In TO_DATE('18/04/2026','DD-MON-YYYY'), DD can match 18 and YYYY can match 2026, but MON requires a three-letter month name such as APR. Because the source contains 04 instead, Oracle cannot parse the string according to that model and raises a conversion error. The other expressions use compatible source values and format models, so their conversions succeed.

A common mistake is assuming that any recognizable date string will convert if the separators look reasonable; Oracle still checks the format elements exactly.

• YYYY-MM-DD matches 2026-04-18 exactly, so that date conversion succeeds.
• DD-MM-YYYY HH24:MI:SS matches the timestamp text element by element, including the time portion.
• 99999 matches the five-digit character value 15250, so the numeric conversion succeeds.

Question 9

Topic: Use Conversion Functions and Conditional Expressions

A developer wants to replace repeated nested NVL calls such as NVL(col1, NVL(col2, col3)). The requirement is to return the first non-NULL value from col1, col2, and col3 in that order. Which expression is the cleanest Oracle SQL choice?

Options:

• A. CASE WHEN col1 IS NOT NULL THEN col1 WHEN col2 IS NOT NULL THEN col2 ELSE col3 END

• B. COALESCE(col1, col2, col3)

• C. DECODE(col1, NULL, col2, col3)

• D. NVL(col1, col2, col3)

Best answer: B

Explanation: Use COALESCE when you need the first non-NULL value from multiple expressions. It is the direct Oracle SQL construct for this pattern and avoids the repeated nesting required by NVL.

NVL works with only two arguments, so handling more than one fallback value forces nested calls such as NVL(col1, NVL(col2, col3)). When the requirement is simply “take the first non-NULL value in order,” COALESCE is the cleaner choice because it accepts a list of expressions and returns the first one that is not NULL.

This makes the intent easier to read:

• check col1 first
• if col1 is NULL, check col2
• if col2 is also NULL, return col3

A CASE expression can also implement the logic, but it is more verbose than the function built specifically for first non-NULL selection.

• The explicit CASE expression can work, but it is more verbose than the function meant for first non-NULL evaluation.
• The three-argument NVL form fails because NVL accepts exactly two expressions.
• The DECODE form does not express first non-NULL logic across all three values and is not the right construct here.

Question 10

Topic: Use Conversion Functions and Conditional Expressions

The employees table has a nullable commission_pct column. A report must display 0 when commission_pct is NULL, but it must not change the stored data. Which expression should be placed in the SELECT list to meet this requirement?

Options:

• A. NVL(commission_pct, 0)

• B. NVL(0, commission_pct)

• C. NULLIF(commission_pct, 0)

• D. NVL2(commission_pct, 0, commission_pct)

Best answer: A

Explanation: Use NVL when you need a default value in query output for a nullable expression. NVL(commission_pct, 0) returns 0 only when commission_pct is NULL; otherwise it returns the actual commission value.

The core rule is that NVL(expr1, expr2) checks expr1 for NULL. If expr1 is NULL, Oracle returns expr2; otherwise Oracle returns expr1. In this case, the report should show a default only in the result set, so NVL(commission_pct, 0) is the correct expression for the SELECT list.

This affects output only; it does not modify the table data. For example, a row with commission_pct = NULL displays as 0, while a row with commission_pct = 0.15 still displays as 0.15.

A common mistake is reversing the arguments or choosing a different null-related function that applies a different rule.

• Reversed arguments with NVL(0, commission_pct) fail because 0 is not NULL, so Oracle always returns 0.
• Wrong function with NULLIF(commission_pct, 0) compares values and can return NULL; it does not substitute a default for an existing NULL.
• Reversed NVL2 logic with NVL2(commission_pct, 0, commission_pct) returns 0 when the column is not NULL, which is the opposite of the requirement.

Continue with full practice

Use the Oracle 1Z0-071 Practice Test page for the full IT Mastery route, mixed-topic practice, timed mock exams, explanations, and web/mobile app access.

Try Oracle 1Z0-071 on Web View Oracle 1Z0-071 Practice Test

Related focused pages

• Free Oracle 1Z0-071 Full-Length Practice Exam
• Relational Database Concepts
• SQL SELECT
• Restrict and Sort Data
• Single-Row Functions
• Group Functions
• Display Data from Multiple Tables
• Use Subqueries to Solve Queries
• Use Set Operators
• Manage Tables by Using DML Statements
• Indexes and Sequences
• DDL Tables and Relationships
• Manage Views
• Control User Access
• Data Dictionary Views
• Manage Data in Different Time Zones

Free review resource

Read the Oracle 1Z0-071 Cheat Sheet on Tech Exam Lexicon, then return to IT Mastery for timed practice.