I85PREP
⌘ K
1Z0-149 study materials
1Z0-149 study resources

1Z0-149 practice questions

One question at a time. Build your 1Z0-149 understanding.

First 50 of 200 questions. Each one lists the choices, the correct answer, and an explanation. The checker at the top of this page saves progress in this browser when it is available.

Continue in the practice tool above to load more questions, 50 at a time.

Question 1Retrieving Data Using the SQL SELECT Statement

In a SELECT list, which expression returns the department name with any trailing spaces removed and the result labeled DEPT_NAME?

  1. SUBSTR(department_name, 1, LENGTH(department_name)) AS dept_name
  2. RTRIM(department_name) AS dept_name Correct answer.
  3. TRIM(department_name, 'BOTH') AS dept_name
  4. LTRIM(department_name) AS dept_name

Explanation

RTRIM removes trailing blanks from a character value and the AS clause supplies a column alias, so the heading becomes DEPT_NAME. TRIM with 'BOTH' alone is not a valid argument form for removing blanks, and LTRIM only strips leading characters.

Question 2Retrieving Data Using the SQL SELECT Statement

You run SELECT ename, sal * 12 annual FROM emp; Which statement about the column heading for the second expression is true?

  1. The heading displays as SAL*12 because aliases are ignored for arithmetic expressions.
  2. The heading displays as annual only if the alias is enclosed in double quotation marks.
  3. The heading displays as ANNUAL because the alias overrides the expression text. Correct answer.
  4. The heading displays as "sal * 12 annual" because the alias must be quoted.

Explanation

An unquoted column alias replaces the default heading derived from the expression, so the report shows ANNUAL. Quotation marks are needed only when the alias must preserve case, spaces, or special characters.

Question 3Retrieving Data Using the SQL SELECT Statement

Which SELECT statement returns every row from the employees table, including rows where the commission column is null?

  1. SELECT * FROM employees WHERE commission_pct = 0;
  2. SELECT * FROM employees WHERE commission_pct IS NULL; Correct answer.
  3. SELECT * FROM employees WHERE NVL(commission_pct, 0) = 0;
  4. SELECT * FROM employees WHERE commission_pct = NULL;

Explanation

Null is not equal to anything, so comparison with = NULL never evaluates to true. The IS NULL predicate is the correct test for missing values and returns exactly the rows where the column is null.

Question 4Retrieving Data Using the SQL SELECT Statement

Which clause is used to filter groups after aggregation in a SELECT statement?

  1. WHERE
  2. HAVING Correct answer.
  3. GROUP BY
  4. ORDER BY

Explanation

HAVING restricts which groups are returned after GROUP BY has formed them and after aggregate functions are evaluated. WHERE filters individual rows before grouping, and ORDER BY only sorts the final result.

Question 5Retrieving Data Using the SQL SELECT Statement

Which operator or construct concatenates the ename and job columns into a single character result in Oracle SQL?

  1. ename || job Correct answer.
  2. ename + job
  3. CONCAT(ename, job) only, since the || operator is not supported
  4. CONCATENATE(ename, job)

Explanation

The double pipe || is the Oracle string concatenation operator and joins two character expressions into one result. CONCAT also works but accepts only two arguments, and the plus sign performs numeric addition rather than concatenation.

Question 6Retrieving Data Using the SQL SELECT Statement

Evaluate this statement: SELECT ename, sal FROM emp WHERE sal BETWEEN 1000 AND 3000 AND deptno = 20 ORDER BY sal DESC; Which rows appear first and which are excluded?

  1. Rows with sal = 1000 and sal = 3000 are included, and the lowest salary in department 20 appears first.
  2. Rows with sal = 1000 and sal = 3000 are excluded, and the lowest salary in department 20 appears first.
  3. Rows with sal = 1000 and sal = 3000 are excluded, and the highest salary in department 20 appears first.
  4. Rows with sal = 1000 and sal = 3000 are included, and the highest salary in department 20 appears first. Correct answer.

Explanation

BETWEEN is inclusive of both boundary values, so salaries of exactly 1000 and 3000 qualify. ORDER BY sal DESC sorts from highest to lowest, so the largest qualifying salary in department 20 is displayed first.

Question 7Retrieving Data Using the SQL SELECT Statement

A query uses WHERE job LIKE 'S_LE%'. Which job value is matched by this pattern?

  1. S_LE
  2. SALESMAN Correct answer.
  3. SALES
  4. SALE

Explanation

The underscore wildcard matches exactly one character and the percent wildcard matches any sequence including none, so the pattern requires an S, one character, LE, then anything. SALESMAN fits because A occupies the underscore position and MAN follows LE.

Question 8Retrieving Data Using the SQL SELECT Statement

Which expression returns the year portion of the hire_date column as a four-digit number?

  1. TO_CHAR(hire_date, 'YYYY')
  2. EXTRACT(YEAR FROM hire_date) Correct answer.
  3. TRUNC(hire_date, 'YEAR')
  4. hire_date.year

Explanation

EXTRACT(YEAR FROM date_value) returns a numeric value for the year component of a date. TO_CHAR with YYYY returns a character string, and TRUNC returns a date truncated to the first day of the year rather than a number.

Question 9Retrieving Data Using the SQL SELECT Statement

You issue SELECT DISTINCT deptno, job FROM emp; What does the result contain?

  1. Only the distinct values of deptno, with job taken from the first matching row
  2. All rows of the table because DISTINCT applies only to a single column
  3. Only the distinct values of job, with deptno taken from the first matching row
  4. Every distinct combination of deptno and job that exists in the table Correct answer.

Explanation

When DISTINCT precedes multiple columns, it eliminates duplicate rows across the entire selected column list. Each surviving row is a unique pairing of department number and job, not a distinct list of either column alone.

Question 10Retrieving Data Using the SQL SELECT Statement

Which statement correctly returns employees whose commission is null, displaying zero in place of the null?

  1. SELECT ename, NVL(commission_pct, 0) FROM emp WHERE commission_pct = NULL;
  2. SELECT ename, NVL(commission_pct, 0) FROM emp WHERE commission_pct IS NULL; Correct answer.
  3. SELECT ename, COALESCE(commission_pct) FROM emp WHERE commission_pct IS NULL;
  4. SELECT ename, commission_pct + 0 FROM emp WHERE commission_pct IS NULL;

Explanation

IS NULL identifies the rows with missing commission, and NVL substitutes zero for the null in the output. Using = NULL in the WHERE clause would return no rows because null comparisons are unknown.

Question 11Retrieving Data Using the SQL SELECT Statement

Evaluate: SELECT ename, sal FROM emp WHERE sal > 2000 OR deptno = 10 AND job = 'CLERK' ORDER BY ename; Which condition must be true for a row to be returned?

  1. Salary greater than 2000, or department 10 combined with job CLERK Correct answer.
  2. Salary greater than 2000 and department 10, regardless of job
  3. Salary greater than 2000 combined with department 10, or job CLERK
  4. Salary greater than 2000 combined with job CLERK, in department 10

Explanation

AND has higher precedence than OR, so the department and job conditions are evaluated together first. A row qualifies if its salary exceeds 2000 or if it is a clerk in department 10.

Question 12Retrieving Data Using the SQL SELECT Statement

Which query returns the number of employees and the average salary per department, showing only departments with more than five employees?

  1. SELECT deptno, COUNT(*), AVG(sal) FROM emp GROUP BY deptno HAVING COUNT(*) > 5; Correct answer.
  2. SELECT deptno, COUNT(*), AVG(sal) FROM emp GROUP BY deptno WHERE COUNT(*) > 5;
  3. SELECT deptno, COUNT(*), AVG(sal) FROM emp HAVING COUNT(*) > 5;
  4. SELECT deptno, COUNT(*), AVG(sal) FROM emp WHERE COUNT(*) > 5 GROUP BY deptno;

Explanation

Group functions belong in the SELECT list and in HAVING, while GROUP BY forms the departments. HAVING then filters the groups so only those with more than five employees are returned.

Question 13Retrieving Data Using the SQL SELECT Statement

You need a single query listing each employee with the name of that employee's department, including employees who are not yet assigned to any department. Which join type fits?

  1. A right outer join from employees to departments
  2. A left outer join from employees to departments Correct answer.
  3. An inner join between employees and departments
  4. A cross join between employees and departments

Explanation

A left outer join keeps every row of the employees table and supplies department data where a match exists, leaving nulls for unassigned employees. An inner join would drop those employees because they have no matching department.

Question 14Retrieving Data Using the SQL SELECT Statement

Evaluate: SELECT ename, sal, CASE WHEN sal >= 3000 THEN 'HIGH' WHEN sal >= 1500 THEN 'MID' ELSE 'LOW' END band FROM emp; An employee earns exactly 1500. Which band is returned?

  1. NULL
  2. MID Correct answer.
  3. HIGH
  4. LOW

Explanation

CASE evaluates its conditions in order and returns the result of the first true condition. Since 1500 is not greater than or equal to 3000 but is greater than or equal to 1500, the second branch yields MID.

Question 15Retrieving Data Using the SQL SELECT Statement

Evaluate: SELECT ename, NVL2(commission_pct, 'HAS COMM', 'NO COMM') status FROM emp; What is returned for an employee whose commission_pct is null?

  1. NULL
  2. An error, because NVL2 cannot test for null
  3. NO COMM Correct answer.
  4. HAS COMM

Explanation

NVL2 takes three arguments and returns the second when the first is not null and the third when the first is null. Since the commission is null, the third argument NO COMM is returned.

Question 16Retrieving Data Using the SQL SELECT Statement

Consider SELECT ename, sal FROM emp ORDER BY 2 DESC, ename; What is the effect of the numeric literal in the ORDER BY clause?

  1. It sorts by the second column of the select list, which is sal, in descending order. Correct answer.
  2. It raises an error because ORDER BY accepts only column names.
  3. It sorts by a constant value 2, leaving the row order unchanged.
  4. It sorts by the second column of the emp table in the underlying table definition.

Explanation

A numeric literal in ORDER BY refers to the position of that column in the select list, so 2 means the second selected column, sal. The sort is descending on salary, with ename ascending as the tiebreaker.

Question 17Restricting and Sorting Data

In a SELECT statement, which clause is evaluated to filter individual rows before any grouping takes place?

  1. GROUP BY
  2. WHERE Correct answer.
  3. ORDER BY
  4. HAVING

Explanation

The WHERE clause filters rows before grouping, so it cannot contain group functions such as SUM or AVG. HAVING filters groups after GROUP BY, and ORDER BY only sorts the final result set.

Question 18Restricting and Sorting Data

You want rows where the commission value is unknown. Which condition returns exactly the rows whose COMM column is NULL?

  1. WHERE comm = 'NULL'
  2. WHERE NVL(comm,0) IS NULL
  3. WHERE comm IS NULL Correct answer.
  4. WHERE comm = NULL

Explanation

NULL represents an unknown value, so it can never satisfy an equality test; comparisons with NULL yield unknown. The IS NULL predicate is the only way to test for the absence of a value.

Question 19Restricting and Sorting Data

Which ORDER BY clause sorts employees by salary from highest to lowest, placing employees with no salary last?

  1. ORDER BY salary ASC NULLS LAST
  2. ORDER BY salary DESC
  3. ORDER BY salary DESC NULLS LAST Correct answer.
  4. ORDER BY salary NULLS LAST DESC

Explanation

DESC reverses the default ordering, and in Oracle Database the default for descending order is NULLS FIRST. Adding NULLS LAST keeps the descending salary order while pushing unknown salaries to the end.

Question 20Restricting and Sorting Data

Evaluate this condition: WHERE salary BETWEEN 3000 AND 5000. Which set of salaries satisfies it?

  1. Only salaries strictly greater than 3000 and strictly less than 5000
  2. Salaries from 3001 through 4999 only
  3. Only salaries equal to 3000 or equal to 5000
  4. Salaries from 3000 through 5000 inclusive Correct answer.

Explanation

BETWEEN a AND b is a shorthand for a range test that includes both boundary values, equivalent to salary >= 3000 AND salary <= 5000. Salaries exactly equal to 3000 or 5000 therefore qualify.

Question 21Restricting and Sorting Data

Which predicate finds last names whose second character is the letter 'a'?

  1. WHERE last_name LIKE '_a%' Correct answer.
  2. WHERE last_name LIKE '%a%'
  3. WHERE last_name LIKE '%a_'
  4. WHERE last_name LIKE 'a_%'

Explanation

In a LIKE pattern the underscore matches exactly one character and the percent sign matches any sequence. Placing the underscore first fixes the position of the second character, and the trailing percent allows any remaining characters.

Question 22Restricting and Sorting Data

A query contains WHERE job_id = 'SA_REP' AND salary > 5000 OR department_id = 90. Which rows does it return?

  1. All employees earning more than 5000 regardless of job or department
  2. Only sales representatives earning more than 5000 in department 90
  3. Only employees in department 90 who earn more than 5000
  4. Sales representatives earning more than 5000, plus every employee in department 90 Correct answer.

Explanation

AND has higher precedence than OR, so the expression is evaluated as (job_id = 'SA_REP' AND salary > 5000) OR department_id = 90. Any department 90 employee qualifies even without meeting the job or salary conditions.

Question 23Restricting and Sorting Data

Which statement about using a column alias in the WHERE clause of the same SELECT statement is true?

  1. The alias can be referenced in WHERE only when it is enclosed in double quotation marks
  2. The alias can be referenced in WHERE because it is defined in the SELECT list
  3. The alias can be referenced in WHERE only when the query has no GROUP BY clause
  4. The alias cannot be referenced in WHERE because WHERE is processed before the SELECT list Correct answer.

Explanation

Row filtering in WHERE is performed before the select list is evaluated, so a column alias defined there is not yet available. The alias can be used in ORDER BY, which is evaluated after the select list.

Question 24Restricting and Sorting Data

Which operator lets you specify an explicit list of values in a WHERE clause, such as job identifiers 'IT_PROG', 'SA_REP', and 'FI_ACCOUNT'?

  1. EXISTS
  2. LIKE
  3. BETWEEN
  4. IN Correct answer.

Explanation

The IN operator tests membership in a list of values and is equivalent to a chain of equality tests joined by OR. It keeps the predicate short and readable when several discrete values are acceptable.

Question 25Restricting and Sorting Data

Which ORDER BY clause sorts employees by department number ascending and, within each department, by hire date with the most recent hire first?

  1. ORDER BY department_id, hire_date DESC Correct answer.
  2. ORDER BY department_id DESC, hire_date
  3. ORDER BY hire_date DESC, department_id
  4. ORDER BY department_id, hire_date

Explanation

The first sort key is the primary ordering, so department_id ascending groups rows by department. The second key, hire_date DESC, orders rows inside each department from newest to oldest.

Question 26Restricting and Sorting Data

Which expression correctly returns the string 'UNKNOWN' whenever the COMM column holds NULL, and otherwise returns the stored commission value?

  1. NVL(comm,'UNKNOWN')
  2. DECODE(comm,NULL,'UNKNOWN',comm) Correct answer.
  3. CASE comm WHEN NULL THEN 'UNKNOWN' END
  4. COALESCE('UNKNOWN',comm)

Explanation

DECODE treats two NULLs as equivalent, so it matches a NULL commission and returns 'UNKNOWN'; otherwise it returns the commission value. This NULL-equals-NULL behavior is specific to DECODE and differs from standard CASE comparison.

Question 27Restricting and Sorting Data

Which WHERE clause returns employees whose salary is at least 2000 but not more than 6000, and who also have a commission value recorded?

  1. WHERE salary > 2000 AND salary < 6000 AND comm IS NULL
  2. WHERE salary BETWEEN 2000 AND 6000 AND comm IS NOT NULL Correct answer.
  3. WHERE salary IN (2000,6000) AND comm IS NOT NULL
  4. WHERE salary BETWEEN 2000 AND 6000 OR comm IS NOT NULL

Explanation

BETWEEN is inclusive, so it captures both 2000 and 6000, and the AND joins it with a test that the commission is present. The combination requires both conditions to be true for a row to be returned.

Question 28Restricting and Sorting Data

Which statement about the escape character in a LIKE pattern is correct?

  1. The escape character can only precede the percent sign
  2. The backslash is always the escape character and cannot be changed
  3. The ESCAPE clause defines a character that makes the following wildcard literal Correct answer.
  4. The ESCAPE clause disables pattern matching and forces an exact match

Explanation

The ESCAPE clause nominates a character so that the wildcard immediately after it is treated as an ordinary character. For example, ESCAPE '\' lets you search for a literal underscore in a name.

Question 29Restricting and Sorting Data

A query uses WHERE UPPER(last_name) = 'KING'. What is the main consequence of this predicate for indexing and performance?

  1. The predicate forces a full table scan only when last_name is the primary key
  2. The predicate is invalid because functions cannot appear in a WHERE clause
  3. The predicate can use a normal index on last_name efficiently
  4. The predicate prevents efficient use of a plain index on last_name unless a function-based index exists Correct answer.

Explanation

Applying a function to the column changes the value being compared, so an ordinary index on last_name cannot be used to locate matching rows. A function-based index on UPPER(last_name) would allow efficient access.

Question 30Restricting and Sorting Data

Which clause sorts result rows by the alias ANNUAL_SAL defined as salary*12 in the select list?

  1. ORDER BY ANNUAL_SAL Correct answer.
  2. HAVING ANNUAL_SAL > 0
  3. GROUP BY ANNUAL_SAL
  4. WHERE ANNUAL_SAL > 0

Explanation

ORDER BY is evaluated after the select list, so a column alias defined there is available for sorting. WHERE and GROUP BY are processed earlier and cannot see the alias.

Question 31Restricting and Sorting Data

Evaluate this predicate: WHERE NOT (salary > 5000 OR comm IS NULL). Which rows satisfy it?

  1. Rows with salary of 5000 or less and a non-NULL commission Correct answer.
  2. Rows with salary greater than 5000 and a NULL commission
  3. Rows with salary of 5000 or less regardless of commission
  4. Rows with salary greater than 5000 or a non-NULL commission

Explanation

De Morgan's law turns the negated OR into an AND of negated conditions, giving salary <= 5000 AND comm IS NOT NULL. A row must satisfy both parts to be returned.

Question 32Restricting and Sorting Data

Which comparison returns rows where the string column CODE begins with the two characters 'AB'?

  1. WHERE CODE LIKE 'A_B'
  2. WHERE CODE LIKE 'AB%' Correct answer.
  3. WHERE CODE LIKE '%AB'
  4. WHERE CODE LIKE '%AB%'

Explanation

A LIKE pattern anchors at the start of the string unless it begins with a percent sign. Writing 'AB%' requires the first two characters to be A and B, followed by any sequence.

Question 33Using Single-Row Functions to Customize Output

You query a table containing a DATE column named HIRE_DATE. Which expression returns the hire date with the time component removed, producing a value of data type DATE at midnight?

  1. ROUND(hire_date)
  2. TO_CHAR(hire_date, 'DD-MON-YYYY')
  3. TRUNC(hire_date) Correct answer.
  4. EXTRACT(DAY FROM hire_date)

Explanation

TRUNC on a DATE truncates the time portion to midnight while keeping the DATE data type, so comparisons and date arithmetic still work. ROUND would also return a DATE but rounds to the nearest day, which can move the date forward when the time is past noon.

Question 34Using Single-Row Functions to Customize Output

Which single-row function returns the number of characters in a character expression, counting a multi-byte character as one character rather than by its byte length?

  1. INSTR
  2. LENGTHB
  3. LENGTH Correct answer.
  4. VSIZE

Explanation

LENGTH returns the number of characters in the string, so a multi-byte character counts as one. LENGTHB returns the byte length instead, and VSIZE reports the internal storage size in bytes.

Question 35Using Single-Row Functions to Customize Output

Evaluate this expression: NVL(NULL, 'X') || NVL('', 'Y'). Assuming the empty string literal is treated as NULL, what is the result?

  1. X
  2. Y
  3. XY Correct answer.
  4. NULL

Explanation

In Oracle, an empty string literal is NULL, so NVL('', 'Y') returns 'Y', and NVL(NULL, 'X') returns 'X'. The concatenation operator joins the two non-null results into 'XY'.

Question 36Using Single-Row Functions to Customize Output

A column COMM_PCT contains values such as 0.125. Which expression displays it as '12.5%' with a single percent sign and no leading space?

  1. TO_CHAR(comm_pct, 'FM990.9') || '%'
  2. TO_CHAR(comm_pct, '990.9%')
  3. comm_pct || '%'
  4. TO_CHAR(comm_pct * 100, '990.9') || '%' Correct answer.

Explanation

Multiplying by 100 converts the fraction to a percentage value, and the format model 990.9 renders 12.5; concatenating '%' appends the sign. The percent sign inside a format model is a literal that is repeated for each power of ten in the value, which produces multiple percent signs.

Question 37Using Single-Row Functions to Customize Output

Which function returns the position of a substring within a string, returning 0 when the substring is not found?

  1. REPLACE
  2. TRANSLATE
  3. INSTR Correct answer.
  4. SUBSTR

Explanation

INSTR searches a string for a substring and returns its numeric position, or 0 if it does not appear. SUBSTR extracts characters starting at a position, which is the reverse relationship.

Question 38Using Single-Row Functions to Customize Output

You need to replace every occurrence of the substring 'abc' inside a column with 'XY'. Which function performs this substitution?

  1. SUBSTR(col, 'abc', 'XY')
  2. TRANSLATE(col, 'abc', 'XY')
  3. REPLACE(col, 'abc', 'XY') Correct answer.
  4. INSTR(col, 'abc', 'XY')

Explanation

REPLACE searches for a substring and returns the string with every occurrence replaced by the replacement string. TRANSLATE works character by character, so it cannot map a three-character sequence onto a two-character sequence as a unit.

Question 39Using Single-Row Functions to Customize Output

Which expression returns 'Oracle' when the input string is 'ORACLE'?

  1. LPAD('ORACLE', 6, 'x')
  2. UPPER('ORACLE')
  3. INITCAP('ORACLE') Correct answer.
  4. LOWER('ORACLE')

Explanation

INITCAP capitalizes the first letter of each word and converts the remaining letters to lowercase, so 'ORACLE' becomes 'Oracle'. UPPER and LOWER leave the whole string in one case.

Question 40Using Single-Row Functions to Customize Output

Evaluate: ROUND(45.926, 2) and TRUNC(45.926, 2). What are the two results?

  1. 45.93 and 45.92 Correct answer.
  2. 45.93 and 45.93
  3. 45.92 and 45.93
  4. 45.92 and 45.92

Explanation

ROUND with a precision of 2 looks at the third decimal digit, which is 6, and rounds up to 45.93. TRUNC simply discards digits beyond the second decimal place, giving 45.92 without any rounding.

Question 41Using Single-Row Functions to Customize Output

Which expression returns the current date and time including the session time zone?

  1. CURRENT_TIMESTAMP Correct answer.
  2. CURRENT_DATE
  3. LOCALTIMESTAMP
  4. SYSDATE

Explanation

CURRENT_TIMESTAMP returns a TIMESTAMP WITH TIME ZONE value reflecting the session time zone. SYSDATE and CURRENT_DATE return DATE values without a time zone component.

Question 42Using Single-Row Functions to Customize Output

A query uses MONTHS_BETWEEN(DATE '2024-06-15', DATE '2024-01-15') and ADD_MONTHS(DATE '2024-01-31', 1). What are the two results?

  1. 5 and 29-FEB-2024 Correct answer.
  2. 5.0 and 31-MAR-2024
  3. 5 and 28-FEB-2024
  4. 4 and 29-FEB-2024

Explanation

MONTHS_BETWEEN returns the number of months between two dates, which is 5 for these same-day dates. ADD_MONTHS adds a month to January 31; because 2024 is a leap year, the result is clamped to the last day of February, 29-FEB-2024.

Question 43Using Single-Row Functions to Customize Output

Which function returns the last day of the month containing a given date?

  1. MONTHS_BETWEEN
  2. NEXT_DAY
  3. ADD_MONTHS
  4. LAST_DAY Correct answer.

Explanation

LAST_DAY takes a date and returns the date of the final day of that month, automatically accounting for leap years and varying month lengths. NEXT_DAY returns the next date matching a specified weekday.

Question 44Using Single-Row Functions to Customize Output

You want to return 'Unknown' when a column MANAGER_ID is NULL, otherwise return the manager id converted to a character string. Which expression achieves this?

  1. NVL(manager_id, 'Unknown')
  2. NVL(TO_CHAR(manager_id), 'Unknown') Correct answer.
  3. COALESCE(manager_id, 'Unknown')
  4. DECODE(manager_id, NULL, 'Unknown')

Explanation

NVL requires both arguments to be compatible in data type, so the numeric manager_id must be converted with TO_CHAR before the character literal is supplied. This returns 'Unknown' for NULLs and the string form of the id otherwise.

Question 45Using Single-Row Functions to Customize Output

Which expression returns the value of the second argument when the first argument is NULL, and otherwise returns the first argument, but also accepts more than two arguments?

  1. NVL2
  2. NVL
  3. NULLIF
  4. COALESCE Correct answer.

Explanation

COALESCE returns the first non-null expression among its arguments and accepts a variable number of them. NVL is limited to exactly two arguments, and NVL2 tests for nullness rather than returning the first non-null value.

Question 46Using Single-Row Functions to Customize Output

Evaluate NULLIF(10, 10) and NULLIF(10, 20). What are the results?

  1. 10 and NULL
  2. NULL and NULL
  3. 10 and 20
  4. NULL and 10 Correct answer.

Explanation

NULLIF returns NULL when the two arguments are equal and returns the first argument otherwise. Since 10 equals 10, the first call yields NULL, while 10 differs from 20, so the second call returns 10.

Question 47Using Single-Row Functions to Customize Output

Which expression returns the greatest value among the three arguments 5, NULL, and 9?

  1. GREATEST(5, NULL, 9) Correct answer.
  2. NVL(GREATEST(5, NULL, 9), 5)
  3. LEAST(5, NULL, 9)
  4. COALESCE(5, NULL, 9)

Explanation

GREATEST returns the largest value in its argument list, and it ignores NULL arguments, so it returns 9 here. LEAST would return the smallest non-null value, and COALESCE returns the first non-null argument, which is 5.

Question 48Using Single-Row Functions to Customize Output

A column contains the string ' Oracle Database '. Which expression removes leading and trailing spaces but preserves the internal single space?

  1. RTRIM(col)
  2. LTRIM(col)
  3. TRIM(col) Correct answer.
  4. REPLACE(col, ' ', '')

Explanation

TRIM with no specification removes both leading and trailing spaces while leaving internal characters untouched, so the internal space between the words remains. LTRIM and RTRIM remove spaces from only one side.

Question 49Using Single-Row Functions to Customize Output

You need to pad the string 'AB' on the left with zeros to a total length of 5. Which expression returns '000AB'?

  1. RPAD('AB', 5, '0')
  2. RPAD('AB', 5)
  3. LPAD('AB', 5, '0') Correct answer.
  4. LPAD('AB', 5)

Explanation

LPAD pads on the left up to the specified total length using the given pad string, producing '000AB'. RPAD pads on the right, and omitting the pad string defaults to a single space.

Question 50Using Single-Row Functions to Customize Output

Which expression extracts the substring 'Database' from the string 'Oracle Database'?

  1. SUBSTR('Oracle Database', 8, 6)
  2. SUBSTR('Oracle Database', 1, 7)
  3. SUBSTR('Oracle Database', 7)
  4. SUBSTR('Oracle Database', 8) Correct answer.

Explanation

Position 8 is the letter D, and specifying only the start position extracts everything from there to the end, giving 'Database'. Supplying a length of 6 would truncate the result to 'Databa'.