Oracle 1Z0-071: Single-Row Functions

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 Single-Row Functions to Customize Output

The employees table contains last_name and job_id. Values in job_id may be stored in any letter case.

You need a query that:

• returns only employees whose job_id is SA_REP, regardless of stored case
• displays each last_name in initial-capital format

Which SQL statement is the best solution?

Options:

• A. SELECT UPPER(last_name) FROM employees WHERE job_id = 'sa_rep';

• B. SELECT LOWER(last_name) FROM employees WHERE INITCAP(job_id) = 'Sa_Rep';

• C. SELECT INITCAP(last_name) FROM employees WHERE UPPER(job_id) = 'SA_REP';

• D. SELECT INITCAP(last_name) FROM employees WHERE LOWER(job_id) = 'SA_REP';

Best answer: C

Explanation: The correct statement uses two character functions for different purposes: INITCAP to format the displayed name, and UPPER to normalize job_id before comparison. That satisfies both the output-format requirement and the case-insensitive filter requirement.

UPPER, LOWER, and INITCAP are single-row character functions used to transform text values. Here, the filter must match job_id regardless of how the value is stored, so the column value should be normalized with UPPER(job_id) and compared to an uppercase literal such as 'SA_REP'. For the displayed result, INITCAP(last_name) converts names to initial-capital form.

This separates the two tasks correctly:

• use a case-conversion function in the WHERE clause for matching
• use INITCAP in the SELECT list for presentation

A query that formats the name incorrectly or compares mismatched letter cases does not meet all stated requirements.

• Uppercase output fails because converting last_name with UPPER does not produce initial-capital format.
• Mismatched comparison fails because LOWER(job_id) must be compared to a lowercase literal, not 'SA_REP'.
• Wrong display format fails because LOWER(last_name) produces all lowercase, not initial caps, even though the filter expression can normalize case.

Question 2

Topic: Use Single-Row Functions to Customize Output

You need a query against EMPLOYEES, where HIRE_DATE is a DATE column. It must:

• return employees hired in the last 30 days
• display HIRE_DATE as DD-MON-YYYY
• sort by the most recent actual hire date first

Which SQL statement best meets these requirements?

Options:

• A. SELECT last_name, TO_CHAR(hire_date,‘DD-MON-YYYY’) hire_dt FROM employees WHERE hire_date >= SYSDATE - 30 ORDER BY TO_CHAR(hire_date,‘DD-MON-YYYY’) DESC

• B. SELECT last_name, TO_CHAR(hire_date,‘DD-MON-YYYY’) hire_dt FROM employees WHERE hire_date >= SYSDATE - 30 ORDER BY hire_date DESC

• C. SELECT last_name, TO_CHAR(hire_date,‘DD-MON-YYYY’) hire_dt FROM employees WHERE hire_date = SYSDATE - 30 ORDER BY hire_date DESC

• D. SELECT last_name, TO_CHAR(hire_date,‘DD-MON-YYYY’) hire_dt FROM employees WHERE TO_CHAR(hire_date,‘DD-MON-YYYY’) >= TO_CHAR(SYSDATE - 30,‘DD-MON-YYYY’) ORDER BY hire_date DESC

Best answer: B

Explanation: Oracle date filtering should use date arithmetic on DATE values, such as SYSDATE - 30. TO_CHAR is appropriate for formatting the output, not for deciding which rows fall within the last 30 days.

The key distinction is between working with a DATE value and displaying a DATE value. To find rows from the last 30 days, Oracle should compare HIRE_DATE directly to SYSDATE - 30, because both expressions are dates. To show the result in DD-MON-YYYY format, use TO_CHAR(hire_date, 'DD-MON-YYYY') in the SELECT list. For sorting, use the underlying DATE column so rows are ordered chronologically.

If you convert dates to characters in the WHERE clause, Oracle is no longer doing true date arithmetic; it is comparing text values instead. Likewise, ordering by a formatted date string can produce alphabetical or lexical order rather than real date order. The safe pattern is: compare dates as dates, format dates only for display.

• String comparison fails because converting both sides with TO_CHAR compares text, not actual date values.
• String sort fails because ordering by DD-MON-YYYY sorts formatted text, not true chronological dates.
• Exact-date match fails because = SYSDATE - 30 returns only one specific date, not the full last-30-days range.

Question 3

Topic: Use Single-Row Functions to Customize Output

Which expression returns a character value representing the date 30 days after the current date in DD-MON-YYYY format?

Options:

• A. TO_CHAR(SYSDATE, 'DD-MON-YYYY') + 30

• B. SYSDATE + TO_CHAR(30, 'DD-MON-YYYY')

• C. TO_CHAR(SYSDATE + 30, 'DD-MON-YYYY')

• D. TO_DATE(SYSDATE + 30, 'DD-MON-YYYY')

Best answer: C

Explanation: In Oracle, add days to SYSDATE while it is still a DATE, then use TO_CHAR if you need formatted text output. TO_CHAR converts a DATE to character data, while TO_DATE converts character data to a DATE.

The key rule is to separate date arithmetic from date formatting. SYSDATE is a DATE value, and adding a number to a DATE adds that many days. If you need the result displayed as text, apply TO_CHAR after the arithmetic is complete.

So the correct pattern is:

• start with SYSDATE
• add the number of days
• format the resulting DATE with TO_CHAR

TO_CHAR(SYSDATE + 30, 'DD-MON-YYYY') therefore returns character data for the date 30 days from today. By contrast, TO_DATE is used to convert character strings into DATE values, not to format a DATE for display. The closest trap is formatting first and then trying to add days, which no longer works as DATE arithmetic because the value has already become character data.

• Using TO_DATE fails because TO_DATE is for converting text to a DATE, not for formatting a DATE as text.
• Formatting first fails because TO_CHAR(SYSDATE, ...) returns character data, so adding 30 is no longer valid date arithmetic.
• Formatting the number fails because TO_CHAR(30, 'DD-MON-YYYY') does not produce a DATE value that can be added to SYSDATE.

Question 4

Topic: Use Single-Row Functions to Customize Output

Which statement correctly distinguishes Oracle date arithmetic from date formatting?

Options:

• A. TO_CHAR(start_date, 'DD') + 7 adds seven days to start_date.

• B. TO_CHAR(start_date, 'DD-MON-YYYY') returns a DATE value in a different format.

• C. You must apply TO_CHAR before subtracting two DATE values.

• D. SYSDATE - start_date returns the difference in days as a NUMBER.

Best answer: D

Explanation: In Oracle, subtracting one DATE from another performs date arithmetic and returns a numeric day difference. By contrast, TO_CHAR converts a date to character data for display, so it formats output rather than preserving a DATE datatype.

The core distinction is datatype and purpose. Oracle date arithmetic works directly on DATE values. When you subtract two DATE expressions, Oracle returns a NUMBER representing the difference in days, including fractional days if time components exist. SYSDATE is a DATE, so expressions like SYSDATE - start_date are valid date arithmetic.

TO_CHAR, however, is a formatting function. It converts a DATE to a VARCHAR2 using the format model you specify, such as 'DD-MON-YYYY'. Once formatted with TO_CHAR, the result is character data, not a DATE, so it is no longer the same kind of value used for normal date arithmetic.

The key takeaway is: use direct DATE expressions for arithmetic, and use TO_CHAR only when you need display formatting.

• Formatted date confusion fails because TO_CHAR changes a DATE into character data, not another DATE.
• Unnecessary conversion fails because Oracle can subtract DATE values directly without formatting them first.
• Day component mix-up fails because TO_CHAR(start_date, 'DD') extracts a character representation of the day of month, not the full date.

Question 5

Topic: Use Single-Row Functions to Customize Output

A report should show employees who have completed 6 months of service and the first Friday after each employee’s 6-month anniversary. hire_date values contain no time component.

The current query returns employees hired only 7 days ago, and review_date is often only one week after hire_date:

SELECT employee_id,
       NEXT_DAY(hire_date + 6, 'FRIDAY') AS review_date
FROM employees
WHERE TRUNC(SYSDATE) - hire_date >= 6;

Which change is the best fix?

Options:

• A. Use hire_date <= ADD_MONTHS(TRUNC(SYSDATE), -6) and NEXT_DAY(hire_date, 'FRIDAY').

• B. Use ROUND(MONTHS_BETWEEN(TRUNC(SYSDATE), hire_date)) >= 6 and NEXT_DAY(hire_date + 180, 'FRIDAY').

• C. Use MONTHS_BETWEEN(TRUNC(SYSDATE), hire_date) >= 6 and NEXT_DAY(ADD_MONTHS(hire_date, 6), 'FRIDAY').

• D. Use TRUNC(SYSDATE) - hire_date >= 180 and ADD_MONTHS(NEXT_DAY(hire_date, 'FRIDAY'), 6).

Best answer: C

Explanation: The problem is that adding 6 to a DATE means 6 days, not 6 months. The correct fix is to measure elapsed months with MONTHS_BETWEEN, shift the anniversary with ADD_MONTHS, and then apply NEXT_DAY to that anniversary date.

In Oracle, numeric DATE arithmetic is day-based, so both hire_date + 6 and TRUNC(SYSDATE) - hire_date >= 6 work in days, not months. For this requirement, the query must use calendar-month logic.

MONTHS_BETWEEN(TRUNC(SYSDATE), hire_date) checks whether at least 6 months of service have elapsed. ADD_MONTHS(hire_date, 6) computes the exact 6-month anniversary. NEXT_DAY(...) must then be applied to that anniversary date to return the first Friday after it.

Using 180 days is only an approximation of 6 months, and applying NEXT_DAY directly to hire_date finds a Friday after the hire date instead of after the 6-month milestone.

• 180-day shortcut fails because 180 days is not the same as 6 calendar months in Oracle date logic.
• Rounded month difference can include rows that have not yet completed 6 full months, and it still anchors the Friday to a day-count approximation.
• Correct filter, wrong anchor partially works for eligibility, but NEXT_DAY(hire_date, 'FRIDAY') is tied to the hire date, not the 6-month anniversary.
• Wrong order of functions shifts a Friday related to the hire date by 6 months, which is not the same as the first Friday after the 6-month anniversary.

Question 6

Topic: Use Single-Row Functions to Customize Output

A developer needs a query that returns these values in one row:

• 15.68 from 15.678 rounded to 2 decimal places
• 15.6 from 15.678 truncated to 1 decimal place
• 2 as the remainder of 17 divided by 5

Which SQL statement correctly applies Oracle numeric single-row functions?

Options:

• A. SELECT ROUND(15.678, 1), TRUNC(15.678, 2), MOD(17, 5) FROM dual;

• B. SELECT ROUND(15.678, 2), TRUNC(15.678, 1), MOD(17, 5) FROM dual;

• C. SELECT ROUND(15.678), TRUNC(15.678), MOD(17, 5) FROM dual;

• D. SELECT TRUNC(15.678, 2), ROUND(15.678, 1), MOD(5, 17) FROM dual;

Best answer: B

Explanation: Oracle applies ROUND(number, integer) to round to a specified decimal place, TRUNC(number, integer) to cut off digits without rounding, and MOD(m, n) to return the remainder. The statement using ROUND(15.678, 2), TRUNC(15.678, 1), and MOD(17, 5) matches all three required results.

The core rule is that Oracle numeric single-row functions each do a different job. ROUND(15.678, 2) returns 15.68 because the third decimal place causes the second decimal place to round up. TRUNC(15.678, 1) returns 15.6 because truncation simply removes digits after the first decimal place without rounding. MOD(17, 5) returns 2 because 17 divided by 5 leaves a remainder of 2.

When Oracle omits the second argument for ROUND or TRUNC, it defaults to 0, so the result is rounded or truncated to a whole number. Also, the order of arguments in MOD(m, n) matters because it means the remainder of m divided by n, not the other way around.

The correct statement is the one that matches each required function rule exactly.

• Swapped functions fails because truncating to 2 decimals gives 15.67, rounding to 1 decimal gives 15.7, and MOD(5, 17) is not the remainder of 17 divided by 5.
• Wrong precisions fails because ROUND(15.678, 1) gives 15.7 and TRUNC(15.678, 2) gives 15.67, so the decimal-place requirements are reversed.
• Default precision fails because omitting the second argument makes ROUND and TRUNC work at 0 decimal places, producing whole-number results instead of 15.68 and 15.6.

Question 7

Topic: Use Single-Row Functions to Customize Output

The employee_id value 245 must be displayed in a report as 00000245. The stored value must remain unchanged. Which Oracle SQL expression best matches this requirement?

Options:

• A. LPAD(employee_id, 8, '0')

• B. LTRIM(employee_id, '0')

• C. RPAD(employee_id, 8, '0')

• D. TRIM('0' FROM employee_id)

Best answer: A

Explanation: LPAD is the Oracle function used to add fill characters to the left side of a value until it reaches a specified length. Here, adding zeros on the left turns 245 into 00000245 without modifying the stored column value.

This requirement is about formatting output, not changing stored data. In Oracle SQL, LPAD adds characters to the left side of a string until the result reaches the requested length. So LPAD(employee_id, 8, '0') displays shorter IDs with leading zeros.

RPAD also pads, but it adds characters on the right. LTRIM and TRIM remove characters instead of adding them, so they cannot create leading zeros for display. This makes LPAD the only function that matches the stated formatting requirement.

The key takeaway is: use LPAD for leading fill, RPAD for trailing fill, and the trim functions for removing characters.

• Right-side padding fails because RPAD would produce 24500000, not a zero-filled value on the left.
• Removing leading zeros fails because LTRIM strips specified characters from the left; it does not add them.
• Removing both-end zeros fails because TRIM removes matching characters from the ends rather than padding the value.

Question 8

Topic: Use Single-Row Functions to Customize Output

In Oracle SQL, what is the result of the expression hire_date + 1 when hire_date is a DATE column?

Options:

• A. A TIMESTAMP value one second later

• B. A DATE value one day later

• C. The number of days since hire_date

• D. A DATE value one month later

Best answer: B

Explanation: Oracle DATE arithmetic treats a numeric value as a count of days. So adding 1 to a DATE returns another DATE that is exactly one day later.

The core rule is simple: in Oracle SQL, DATE + number adds days, and DATE - number subtracts days. The result remains a DATE value. Therefore, hire_date + 1 means one calendar day after hire_date, while hire_date - 7 would mean seven days earlier.

This is different from month arithmetic, which uses ADD_MONTHS, and from timestamp-focused interval arithmetic, which is used when you need units such as hours or seconds.

The key takeaway is that plain numeric arithmetic on an Oracle DATE uses days as the unit.

• Month confusion fails because months are not added with plain numeric DATE arithmetic; ADD_MONTHS is used for months.
• Timestamp confusion fails because DATE + 1 does not change the datatype to TIMESTAMP or mean one second.
• Numeric-result confusion fails because adding a number to a DATE returns another DATE, not an elapsed-day count.

Question 9

Topic: Use Single-Row Functions to Customize Output

A developer tests Oracle DATE arithmetic with this query:

SELECT TO_DATE('2026-04-18 18:00','YYYY-MM-DD HH24:MI')
     - TO_DATE('2026-04-17 06:00','YYYY-MM-DD HH24:MI') AS diff
FROM dual;

Which numeric value does diff return?

Options:

• A. 1

• B. 1.5

• C. 0.5

• D. 36

Best answer: B

Explanation: In Oracle, subtracting one DATE from another returns a NUMBER representing the difference in days. From 6:00 AM on April 17 to 6:00 PM on April 18 is 36 hours, which is 1.5 days.

The core rule is that Oracle DATE - DATE returns a numeric value in days, not hours and not an interval type. Because Oracle DATE stores both date and time to the second, the result can include a fractional day.

In this query:

• From April 17, 2026 06:00 to April 18, 2026 06:00 = 1 day
• From April 18, 2026 06:00 to April 18, 2026 18:00 = 12 hours
• 12 hours = 0.5 day
• Total = 1.5 days

A common mistake is to answer in hours, but Oracle does not automatically convert the subtraction result to hours. The returned value is the day difference as a number.

• Whole day only ignores the extra 12 hours, which adds another 0.5 day.
• Hours instead of days treats the result as 36, but Oracle returns day units for DATE subtraction.
• Half day only counts only the 12-hour portion and misses the full 24 hours before it.

Question 10

Topic: Use Single-Row Functions to Customize Output

You have this row in deliveries:

DELIVERY_ID  PICKUP_DT              DROPOFF_DT
100          10-MAR-2026 08:00:00   12-MAR-2026 20:00:00

You need a query that returns the elapsed time as a numeric number of days, preserving the time portion. Which SQL statement is best?

Options:

• A. SELECT dropoff_dt - pickup_dt AS elapsed_days FROM deliveries WHERE delivery_id = 100

• B. SELECT NUMTODSINTERVAL(dropoff_dt - pickup_dt, 'DAY') AS elapsed_days FROM deliveries WHERE delivery_id = 100

• C. SELECT TRUNC(dropoff_dt) - TRUNC(pickup_dt) AS elapsed_days FROM deliveries WHERE delivery_id = 100

• D. SELECT MONTHS_BETWEEN(dropoff_dt, pickup_dt) AS elapsed_days FROM deliveries WHERE delivery_id = 100

Best answer: A

Explanation: In Oracle, subtracting one DATE from another returns a numeric value in days. Because DATE stores time to the second, the 12 hours between 08:00 and 20:00 adds 0.5, so this row returns 2.5.

Oracle DATE arithmetic is direct: later_date - earlier_date returns the elapsed time as a number of days. That numeric result includes fractions when the time portions differ.

• March 10, 2026 08:00:00 to March 12, 2026 08:00:00 = 2 days
• March 12, 2026 08:00:00 to March 12, 2026 20:00:00 = 12 hours = 0.5 day
• Total = 2.5

So a plain subtraction of dropoff_dt - pickup_dt is the correct solution when the requirement is a numeric day value that preserves time. The closest trap is removing time with TRUNC, which changes the result.

• Removing time with TRUNC makes both values midnight dates, so the result becomes 2 instead of 2.5.
• Using months with MONTHS_BETWEEN returns elapsed months, not elapsed days.
• Changing datatype with NUMTODSINTERVAL returns an interval value, not the required numeric day result.

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
• Conversion and CASE
• 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.