1Z0-071 Exam Prep
Chapters 4 & 5
Volume 1
Single-row functions are tested throughout the 1Z0-071 exam — not just in dedicated function questions but inside WHERE clauses, SELECT lists, and ORDER BY clauses across every topic. The exam expects you to know the exact syntax, argument order, and return values of the most common character, number, date, and conversion functions.
Argument order is where most candidates lose marks. SUBSTR, INSTR, ROUND, TRUNC, ADD_MONTHS, MONTHS_BETWEEN, TO_CHAR, TO_DATE — the exam will swap the arguments and ask whether the result is correct.
In this post
- Character functions — case and padding
- Character functions — searching and extracting
- Number functions — ROUND, TRUNC, MOD
- Date functions — SYSDATE, ADD_MONTHS, MONTHS_BETWEEN
- Date functions — NEXT_DAY, LAST_DAY, ROUND, TRUNC on dates
- Date arithmetic
- Conversion functions — TO_CHAR, TO_NUMBER, TO_DATE
- Format models for TO_CHAR and TO_DATE
- Conditional expressions — CASE and DECODE
- Quick reference
- Practice questions
01 — Character functions — case and padding
-- Case conversion UPPER('hello') -- Returns: 'HELLO' LOWER('HELLO') -- Returns: 'hello' INITCAP('hello world') -- Returns: 'Hello World' -- Padding LPAD('SQL', 7, '*') -- Returns: '****SQL' (left-pad to total length 7) RPAD('SQL', 7, '*') -- Returns: 'SQL****' (right-pad to total length 7) LPAD('SQL', 7) -- Returns: ' SQL' (default pad character is space) -- Trimming whitespace or characters TRIM(' hello ') -- Returns: 'hello' TRIM('H' FROM 'Hello') -- Returns: 'ello' (trims H from both ends) LTRIM('***SQL***', '*') -- Returns: 'SQL***' RTRIM('***SQL***', '*') -- Returns: '***SQL'
LPAD/RPAD argument order: The arguments are (string, total_length, pad_character). The total_length is the final length of the result — not how many characters to add. If the string is already longer than total_length, the string is truncated to that length.
02 — Character functions — searching and extracting
-- LENGTH: number of characters in a string LENGTH('Hello') -- Returns: 5 LENGTH(NULL) -- Returns: NULL -- SUBSTR: extract a substring -- SUBSTR(string, start_position, length) SUBSTR('ABCDEFG', 3, 4) -- Returns: 'CDEF' (start at pos 3, take 4 chars) SUBSTR('ABCDEFG', 3) -- Returns: 'CDEFG' (start at pos 3, take rest) SUBSTR('ABCDEFG', -3) -- Returns: 'EFG' (negative = count from end) -- INSTR: find position of a substring -- INSTR(string, search, start_position, occurrence) INSTR('HELLO WORLD', 'O') -- Returns: 5 (first O) INSTR('HELLO WORLD', 'O', 1, 2) -- Returns: 8 (second O, starting from pos 1) INSTR('HELLO WORLD', 'Z') -- Returns: 0 (not found — returns 0, not NULL) -- REPLACE: swap one string for another REPLACE('JACK AND JUE', 'J', 'BL') -- Returns: 'BLACK AND BLUE' -- CONCAT: join two strings (equivalent to ||) CONCAT('Hello', ' World') -- Returns: 'Hello World'
INSTR returns 0 when not found — not NULL: This catches candidates who check
WHERE INSTR(col, 'x') IS NULL to find non-matches. The correct check is WHERE INSTR(col, 'x') = 0. INSTR never returns NULL for a valid search (only if the string itself is NULL).
SUBSTR position 1 is the first character: Oracle strings are 1-indexed. Position 1 is the first character, not 0. A negative start position counts from the end: -1 is the last character, -2 is second to last.
03 — Number functions — ROUND, TRUNC, MOD
-- ROUND: round to specified decimal places -- ROUND(number, decimal_places) ROUND(45.926, 2) -- Returns: 45.93 (round to 2 decimal places) ROUND(45.926, 0) -- Returns: 46 (round to nearest integer) ROUND(45.926, -1) -- Returns: 50 (round to nearest ten) ROUND(45.926) -- Returns: 46 (default: round to 0 places) -- TRUNC: truncate (drop digits without rounding) -- TRUNC(number, decimal_places) TRUNC(45.926, 2) -- Returns: 45.92 (drop third decimal — no rounding) TRUNC(45.926, 0) -- Returns: 45 (drop all decimals) TRUNC(45.926, -1) -- Returns: 40 (truncate to nearest ten) -- MOD: remainder after division MOD(10, 3) -- Returns: 1 (10 divided by 3 = 3 remainder 1) MOD(10, 5) -- Returns: 0 (10 divided by 5 = 2 remainder 0)
ROUND vs TRUNC with negative places: Both accept negative values for decimal_places to work on the integer portion.
ROUND(45.9, -1) rounds to the nearest 10 (returns 50). TRUNC(45.9, -1) drops to the nearest 10 (returns 40). The exam tests negative decimal places directly.
04 — Date functions — SYSDATE, ADD_MONTHS, MONTHS_BETWEEN
Oracle's DATE type stores both date and time. SYSDATE returns the current database server date and time. All date arithmetic produces results in days.
-- SYSDATE: current date and time from the database server SELECT SYSDATE FROM dual; -- ADD_MONTHS: add or subtract a number of months -- ADD_MONTHS(date, number_of_months) ADD_MONTHS(SYSDATE, 6) -- 6 months from today ADD_MONTHS(SYSDATE, -3) -- 3 months ago ADD_MONTHS('28-FEB-24', 1) -- Returns: 31-MAR-24 (last day of March) -- MONTHS_BETWEEN: number of months between two dates -- MONTHS_BETWEEN(later_date, earlier_date) MONTHS_BETWEEN('01-SEP-24', '11-JAN-24') -- Returns: 7.677... (fractional months based on day difference) -- Positive when first date is later, negative when first date is earlier
MONTHS_BETWEEN argument order matters: The later date goes first.
MONTHS_BETWEEN(later, earlier) returns a positive number. Reversing them returns a negative number. The exam will swap the order and ask for the sign of the result.
05 — Date functions — NEXT_DAY, LAST_DAY, ROUND and TRUNC on dates
-- NEXT_DAY: date of the next specified weekday -- NEXT_DAY(date, day_of_week) NEXT_DAY(SYSDATE, 'FRIDAY') -- Next Friday from today NEXT_DAY('01-JAN-24', 'MONDAY') -- First Monday after 01-JAN-24 -- LAST_DAY: last day of the month containing the given date LAST_DAY(SYSDATE) -- Last day of the current month LAST_DAY('15-FEB-24') -- Returns: 29-FEB-24 (2024 is a leap year) -- ROUND on a date: rounds to the nearest unit ROUND(SYSDATE, 'MONTH') -- Rounds to first of current or next month ROUND(SYSDATE, 'YEAR') -- Rounds to Jan 1 of current or next year -- TRUNC on a date: truncates to the start of the unit TRUNC(SYSDATE, 'MONTH') -- First day of the current month TRUNC(SYSDATE, 'YEAR') -- Jan 1 of the current year TRUNC(SYSDATE) -- Midnight (removes the time portion)
06 — Date arithmetic
Oracle lets you add and subtract numbers from dates. The unit is always days. You can also subtract two dates from each other to get the number of days between them.
-- Adding and subtracting days SYSDATE + 7 -- 7 days from today SYSDATE - 30 -- 30 days ago SYSDATE + 1/24 -- 1 hour from now (1/24 of a day) SYSDATE + 1/1440 -- 1 minute from now (1/1440 of a day) -- Subtracting two dates returns number of days (can be fractional) SELECT SYSDATE - hire_date FROM employees; -- Returns a NUMBER — days since the employee was hired -- Fractional part represents partial days (hours and minutes)
| Operation | Result type | Example |
|---|---|---|
| date + number | DATE | SYSDATE + 7 → date 7 days later |
| date - number | DATE | SYSDATE - 1 → yesterday |
| date - date | NUMBER (days) | SYSDATE - hire_date → days employed |
| date + date | Error | Cannot add two dates together |
07 — Conversion functions — TO_CHAR, TO_NUMBER, TO_DATE
Conversion functions change a value from one data type to another. The exam tests the argument order and format model syntax for all three.
-- TO_CHAR: convert a number or date to a formatted string TO_CHAR(SYSDATE, 'DD-MON-YYYY') -- Returns: '27-MAR-2026' TO_CHAR(SYSDATE, 'DD/MM/YYYY HH24:MI') -- Returns: '27/03/2026 14:35' TO_CHAR(1234567.89, '$9,999,999.99') -- Returns: ' $1,234,567.89' TO_CHAR(0.25, '999.99') -- Returns: ' 0.25' -- TO_NUMBER: convert a string to a number TO_NUMBER('1,234.56', '9,999.99') -- Returns: 1234.56 TO_NUMBER('100') -- Returns: 100 (no format needed for simple numbers) -- TO_DATE: convert a string to a date TO_DATE('27-03-2026', 'DD-MM-YYYY') -- Returns: date value for 27 March 2026 TO_DATE('27-MAR-2026', 'DD-MON-YYYY') -- Same result, different format model
TO_DATE argument order: The string comes first, the format model comes second. Getting this backwards raises an error. The same order applies to TO_CHAR: value first, format model second.
08 — Format models for TO_CHAR and TO_DATE
| Format element | Meaning | Example output |
|---|---|---|
YYYY | 4-digit year | 2026 |
YY | 2-digit year | 26 |
MM | 2-digit month number | 03 |
MON | 3-letter month abbreviation | MAR |
MONTH | Full month name | MARCH |
DD | 2-digit day of month | 27 |
DY | 3-letter day abbreviation | FRI |
DAY | Full day name | FRIDAY |
HH24 | Hour in 24-hour format | 14 |
HH / HH12 | Hour in 12-hour format | 02 |
MI | Minutes | 35 |
SS | Seconds | 09 |
9 | Digit placeholder (suppresses leading zeros) | 1234 |
0 | Digit placeholder (forces leading zeros) | 01234 |
$ | Dollar sign prefix | $1,234 |
L | Local currency symbol | £1,234 |
, | Thousands separator | 1,234 |
. | Decimal point | 1.23 |
09 — Conditional expressions — CASE and DECODE
CASE and DECODE both return different values depending on a condition. CASE is ANSI standard and more flexible. DECODE is Oracle-specific and older — both appear on the exam.
-- Simple CASE: compares one expression against fixed values SELECT last_name, CASE job_id WHEN 'IT_PROG' THEN 'Developer' WHEN 'SA_REP' THEN 'Sales Rep' WHEN 'AD_PRES' THEN 'President' ELSE 'Other' END AS role FROM employees; -- Searched CASE: evaluates a condition per WHEN branch SELECT last_name, salary, CASE WHEN salary < 5000 THEN 'Low' WHEN salary < 10000 THEN 'Mid' ELSE 'High' END AS band FROM employees; -- DECODE: Oracle-specific equivalent of simple CASE -- DECODE(expression, search1, result1, search2, result2, ..., default) DECODE(job_id, 'IT_PROG', 'Developer', 'SA_REP', 'Sales Rep', 'AD_PRES', 'President', 'Other')
CASE stops at the first match: Once a WHEN condition is satisfied, Oracle returns that result and ignores all remaining WHEN branches. This matters when ranges overlap — put the most specific conditions first.
10 — Quick reference
| Function | Syntax | Key exam point |
|---|---|---|
SUBSTR | (string, start, length) | 1-indexed; negative start counts from end |
INSTR | (string, search, start, occurrence) | Returns 0 when not found — not NULL |
LPAD/RPAD | (string, total_length, pad_char) | Total_length is final length — truncates if string is longer |
ROUND (number) | (number, decimal_places) | Negative places round to tens, hundreds etc. |
TRUNC (number) | (number, decimal_places) | Drops digits without rounding |
MOD | (dividend, divisor) | Returns the remainder |
ADD_MONTHS | (date, months) | Negative months subtracts |
MONTHS_BETWEEN | (later_date, earlier_date) | Later date first for positive result |
NEXT_DAY | (date, weekday_name) | Returns next occurrence of that weekday after the given date |
LAST_DAY | (date) | Last day of the month |
TO_CHAR | (value, format_model) | Value first, format second |
TO_DATE | (string, format_model) | String first, format second |
| Date + number | — | Unit is days — use fractions for hours/minutes |
| Date - date | — | Returns NUMBER of days (can be fractional) |
11 — Practice questions
Q1 — What does this expression return?
SUBSTR('ORACLE DATABASE', 8, 4)Q2 — A developer needs to find how many months an employee has worked. The hire_date column holds the start date. Which expression is correct?
Q3 — What does
ROUND(156.748, -2) return?Oracle SQL 1Z0-071 Exam Prep Guide — Complete 4-Volume Set
19 chapters · 1,480 practice questions · 1,249 SQL examples
Chapters 4 and 5 cover every single-row function on the exam with argument-by-argument breakdowns and practice sets built around the most common exam traps. Instant PDF download.
Get the full guide — $25 →
Chapters 4 and 5 cover every single-row function on the exam with argument-by-argument breakdowns and practice sets built around the most common exam traps. Instant PDF download.
No comments:
Post a Comment