I85PREP
⌘ K
Oracle 1Z0-071 study materials
Oracle 1Z0-071 study resources

Oracle 1Z0-071 practice questions

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

First 50 of 199 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 1Relational Database Fundamentals and SQL SELECT Basics

In a relational table, which property must hold for a column defined as a primary key?

  1. It must contain a unique, non-null value in every row Correct answer.
  2. It must be defined with a numeric data type
  3. It must be the first column of the table
  4. It must reference another table's primary key

Explanation

A primary key constraint enforces both uniqueness and the NOT NULL rule, so no two rows can share a key value and no row may leave it empty. The constraint is about data integrity, not column position or data type. Referencing another table describes a foreign key instead.

Question 2Relational Database Fundamentals and SQL SELECT Basics

Which statement about NULL values in Oracle SQL is correct?

  1. NULL represents an unknown value and comparisons with it yield UNKNOWN Correct answer.
  2. NULL is equivalent to an empty string stored in a NUMBER column
  3. NULL sorts before every other value in a DESC ORDER BY
  4. NULL is equal to zero when compared with the = operator

Explanation

NULL means an unknown or inapplicable value, so any comparison using = or <> against NULL produces UNKNOWN, and rows are returned only when the WHERE condition evaluates to TRUE. Testing for NULL requires IS NULL or IS NOT NULL. NULL is not a zero and is not treated as an ordinary sortable value in the same way as other data.

Question 3Relational Database Fundamentals and SQL SELECT Basics

You execute the statement: SELECT ename, sal * 12 AS annual FROM emp WHERE sal * 12 > 60000; Which clause is evaluated to determine which rows are returned?

  1. The SELECT list, because the alias annual is used first
  2. The ORDER BY clause, because it runs before filtering
  3. The FROM clause only, because arithmetic is not allowed in WHERE
  4. The WHERE clause, because it filters rows before the SELECT list is projected Correct answer.

Explanation

Logical processing order places FROM first, then WHERE, then the SELECT list, and finally ORDER BY. The WHERE clause filters individual rows, so the multiplication is evaluated there to decide row inclusion. The alias annual cannot be referenced in WHERE because the SELECT list is processed later.

Question 4Relational Database Fundamentals and SQL SELECT Basics

Which character matches any single character in an Oracle LIKE pattern?

  1. Underscore (_) Correct answer.
  2. Question mark (?)
  3. Asterisk (*)
  4. Percent sign (%)

Explanation

In Oracle LIKE patterns, the underscore matches exactly one character, while the percent sign matches any sequence of characters including none. The asterisk and question mark are wildcards in shell or file globbing, not in SQL LIKE. To match a literal underscore you would need the ESCAPE clause.

Question 5Relational Database Fundamentals and SQL SELECT Basics

Which operator is used to test whether a value falls within an inclusive range in a WHERE clause?

  1. LIKE
  2. IN (...)
  3. IS NULL
  4. BETWEEN ... AND ... Correct answer.

Explanation

BETWEEN low AND high returns TRUE when the value is greater than or equal to low and less than or equal to high, so both endpoints are included. IN tests membership in a list, LIKE performs pattern matching, and IS NULL tests for the absence of a value.

Question 6Relational Database Fundamentals and SQL SELECT Basics

Examine these statements: SELECT ename, deptno FROM emp; Which clause restricts the rows returned to employees in department 20?

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

Explanation

The WHERE clause filters individual rows before any projection or grouping, so WHERE deptno = 20 returns only department 20 rows. HAVING filters groups after aggregation and is not a substitute for row-level filtering. ORDER BY only sorts and GROUP BY only forms groups.

Question 7Relational Database Fundamentals and SQL SELECT Basics

Which expression returns the department number even when the commission column comm is NULL?

  1. NULLIF(comm, deptno)
  2. NVL(comm, deptno)
  3. NVL2(comm, deptno, NULL)
  4. COALESCE(comm, deptno) Correct answer.

Explanation

COALESCE returns the first non-null argument, so when comm is NULL it returns deptno. NVL(comm, deptno) would return deptno only in that same null case, but it is a two-argument substitution rather than a list-based function; the question asks for the expression that yields deptno when comm is NULL, which COALESCE satisfies by returning the first non-null value. NULLIF compares two values and returns NULL when they are equal.

Question 8Relational Database Fundamentals and SQL SELECT Basics

You run: SELECT deptno, COUNT(*) AS c FROM emp GROUP BY deptno HAVING COUNT(*) > 3; What does the HAVING clause do in this query?

  1. It sorts the groups in descending count order
  2. It removes individual rows where the count is 3 or fewer
  3. It limits the output to the first three groups
  4. It removes entire groups whose aggregate count is 3 or fewer Correct answer.

Explanation

HAVING is evaluated after GROUP BY and filters groups based on aggregate results, so only departments with more than three employees survive. Row-level filtering belongs in WHERE, which runs before grouping. Sorting and row limiting are handled by ORDER BY and FETCH FIRST.

Question 9Relational Database Fundamentals and SQL SELECT Basics

Which statement correctly returns each department's average salary rounded to two decimal places, labelled avg_sal?

  1. SELECT deptno, AVG(sal, 2) AS avg_sal FROM emp GROUP BY deptno;
  2. SELECT deptno, ROUND(AVG(sal), 2) AS avg_sal FROM emp GROUP BY deptno; Correct answer.
  3. SELECT deptno, AVG(ROUND(sal, 2)) AS avg_sal FROM emp GROUP BY deptno;
  4. SELECT deptno, ROUND(AVG(sal)) AS avg_sal FROM emp;

Explanation

The aggregate AVG(sal) is computed per group, and ROUND with a second argument of 2 rounds the result to two decimal places. The query groups by deptno so each department produces one row. Placing GROUP BY on deptno is required because deptno appears in the SELECT list.

Question 10Relational Database Fundamentals and SQL SELECT Basics

What is the result of the expression NULL || 'ABC' in Oracle SQL?

  1. NULL
  2. 'ABC' Correct answer.
  3. 'NULLABC'
  4. An error is raised

Explanation

The concatenation operator treats NULL as an empty string, so NULL || 'ABC' evaluates to 'ABC'. This behavior differs from arithmetic, where any operation involving NULL yields NULL. The result is a character string, not the literal text NULL.

Question 11Relational Database Fundamentals and SQL SELECT Basics

Which query returns employees whose name begins with the letter A, ignoring case?

  1. SELECT ename FROM emp WHERE ename = 'A%';
  2. SELECT ename FROM emp WHERE ename LIKE '%A';
  3. SELECT ename FROM emp WHERE UPPER(ename) LIKE 'A%'; Correct answer.
  4. SELECT ename FROM emp WHERE ename LIKE 'A%';

Explanation

Applying UPPER to the column converts every name to upper case before pattern matching, so both 'adam' and 'Adam' match 'A%'. A plain LIKE 'A%' is case-sensitive and would miss lower-case names. The equality test with 'A%' looks for that literal string, and '%A' matches names ending in A.

Question 12Relational Database Fundamentals and SQL SELECT Basics

You need to display each employee's salary, showing 0 instead of NULL, and also flag whether a commission exists. Which pair of expressions achieves this?

  1. NVL(sal, 0) and NVL2(comm, 'YES', 'NO') Correct answer.
  2. NVL(sal, 0) and NULLIF(comm, 'YES')
  3. NVL2(sal, 0, sal) and NVL(comm, 'YES')
  4. COALESCE(sal) and NVL2(comm, 'NO', 'YES')

Explanation

NVL(sal, 0) substitutes zero when salary is NULL, and NVL2(comm, 'YES', 'NO') returns YES when comm is not null and NO when it is null. Together they satisfy both requirements. The other combinations misuse the argument order or use functions that do not perform the intended substitution.

Question 13Relational Database Fundamentals and SQL SELECT Basics

Which statement about the ORDER BY clause is correct?

  1. It can reference a column alias defined in the SELECT list Correct answer.
  2. It can appear before the WHERE clause in the statement text
  3. It can only reference columns that appear in the SELECT list
  4. It always sorts NULL values first regardless of direction

Explanation

ORDER BY is processed after the SELECT list, so it may refer to a column alias defined there, as well as to column positions or underlying table columns. It must appear at the end of the statement after WHERE and GROUP BY. NULL ordering depends on ASC or DESC and on the NULLS FIRST or NULLS LAST specification.

Question 14Relational Database Fundamentals and SQL SELECT Basics

The EMP table has 14 rows and the DEPT table has 4 rows. You execute: SELECT e.ename, d.dname FROM emp e, dept d; How many rows does the result contain, and why?

  1. 14 rows, because each employee is matched to one department
  2. 4 rows, because only departments with employees are returned
  3. 56 rows, because every employee row is combined with every department row Correct answer.
  4. 18 rows, because the row counts of both tables are added

Explanation

A join with no join condition in the WHERE clause produces a Cartesian product, pairing every row of the first table with every row of the second. Fourteen employees multiplied by four departments yields 56 rows. Adding a proper join condition would restrict the result to meaningful matches.

Question 15Relational Database Fundamentals and SQL SELECT Basics

Which query correctly lists departments whose highest salary exceeds 5000, showing only those departments?

  1. SELECT deptno FROM emp GROUP BY deptno HAVING MAX(sal) > 5000; Correct answer.
  2. SELECT deptno FROM emp GROUP BY deptno WHERE MAX(sal) > 5000;
  3. SELECT deptno, MAX(sal) FROM emp HAVING MAX(sal) > 5000;
  4. SELECT deptno FROM emp WHERE MAX(sal) > 5000 GROUP BY deptno;

Explanation

Aggregate functions cannot appear in WHERE, because WHERE filters rows before aggregation. Conditions on aggregates belong in HAVING, which is evaluated after GROUP BY. The query therefore groups by deptno and applies HAVING MAX(sal) > 5000 to the groups.

Question 16Relational Database Fundamentals and SQL SELECT Basics

You need to return the three highest salaries from EMP, with ties handled so that employees sharing the third-highest salary are all included. Which clause accomplishes this in Oracle 12c and later?

  1. ORDER BY sal DESC FETCH FIRST 3 PERCENT ROWS ONLY
  2. FETCH FIRST 3 ROWS WITH TIES Correct answer.
  3. FETCH FIRST 3 ROWS ONLY
  4. WHERE ROWNUM <= 3

Explanation

FETCH FIRST n ROWS WITH TIES returns the requested number of rows plus any additional rows whose ORDER BY values equal those of the last row, so employees tied at the third salary are included. ROWS ONLY would cut off ties, and ROWNUM filters before sorting unless wrapped in a subquery. The PERCENT variant returns a percentage of rows rather than three rows.

Question 17Relational Database Fundamentals and SQL SELECT Basics

Examine this query: SELECT deptno, job, SUM(sal) FROM emp GROUP BY deptno, job HAVING SUM(sal) > 9000 ORDER BY deptno, job; Which statement about the result is correct?

  1. It returns one row per department, summing salaries across all jobs
  2. It fails because ORDER BY cannot reference columns used in GROUP BY
  3. It returns one row per department and job combination whose total salary exceeds 9000 Correct answer.
  4. It returns one row per employee whose salary exceeds 9000

Explanation

GROUP BY deptno, job creates one group for each distinct department-and-job pair, and SUM(sal) totals salaries within each group. HAVING then keeps only groups whose total exceeds 9000, and ORDER BY sorts the surviving groups. Referencing grouping columns in ORDER BY is valid because they are available after grouping.

Question 18Restricting and Sorting Data

You run the statement SELECT last_name, salary FROM employees WHERE salary BETWEEN 3000 AND 6000;. Which rows are returned?

  1. Rows where salary is between 3000 and 6000 but excluding both boundary values
  2. Rows where salary is greater than or equal to 3000 only
  3. Rows where salary is greater than 3000 and less than 6000
  4. Rows where salary is 3000 or 6000 or any value in between, inclusive Correct answer.

Explanation

BETWEEN a AND b is a closed-range test: it is equivalent to salary >= 3000 AND salary <= 6000, so both boundary values qualify. The column's data type governs comparison, so numeric bounds compare numerically.

Question 19Restricting and Sorting Data

Examine this statement: SELECT employee_id, last_name FROM employees WHERE last_name LIKE '_a%';. Which last_name values satisfy the predicate?

  1. Names whose second character is 'a' and that have at least two characters Correct answer.
  2. Names that contain the substring 'a' anywhere
  3. Names whose last character is 'a'
  4. Names that begin with the letter 'a'

Explanation

The underscore wildcard matches exactly one character, so '_a%' requires any single first character, then 'a', then zero or more characters. The name must therefore be at least two characters long with 'a' in the second position.

Question 20Restricting and Sorting Data

Which clause orders query results by the alias annual_sal in descending order, with NULLs listed last?

  1. ORDER BY DESC annual_sal NULLS LAST
  2. ORDER BY annual_sal NULLS LAST DESC
  3. ORDER BY annual_sal DESC NULLS FIRST
  4. ORDER BY annual_sal DESC NULLS LAST Correct answer.

Explanation

ORDER BY accepts a column alias, and the NULLS LAST clause places null values after all non-null values regardless of ascending or descending direction. The direction keyword follows the sort expression, and NULLS LAST follows the direction.

Question 21Restricting and Sorting Data

Which operator lets you test whether a column value matches any value in an explicit list, such as department_id 10, 20, or 30?

  1. IS ANY
  2. LIKE
  3. BETWEEN
  4. IN Correct answer.

Explanation

The IN operator compares a value against a parenthesized list of literals or subquery results and returns TRUE when a match is found. It is a shorthand for a chain of equality tests joined by OR.

Question 22Restricting and Sorting Data

You write WHERE commission_pct = NULL. What is the result?

  1. An error is raised because NULL cannot be compared
  2. Rows where commission_pct is not null are returned
  3. Rows where commission_pct is null are returned
  4. No rows are returned because the predicate evaluates to UNKNOWN Correct answer.

Explanation

Any comparison with NULL yields UNKNOWN, and the WHERE clause keeps only rows whose predicate is TRUE. Because UNKNOWN is treated as not satisfied, the query returns no rows rather than an error. Null testing requires IS NULL.

Question 23Restricting and Sorting Data

You need employees whose salary is at least 5000 and whose job_id is either 'IT_PROG' or 'SA_REP'. Which WHERE clause is correct?

  1. WHERE salary >= 5000 AND (job_id = 'IT_PROG' OR job_id = 'SA_REP') Correct answer.
  2. WHERE (salary >= 5000 AND job_id = 'IT_PROG') OR job_id = 'SA_REP'
  3. WHERE salary >= 5000 AND job_id IN 'IT_PROG', 'SA_REP'
  4. WHERE salary >= 5000 AND job_id = 'IT_PROG' OR job_id = 'SA_REP'

Explanation

AND has higher precedence than OR, so the salary condition must be grouped with parentheses around the entire job_id test. Without grouping, the OR branch would admit SA_REP rows with any salary.

Question 24Restricting and Sorting Data

Which statement about using a column alias in the ORDER BY clause is true?

  1. An alias defined in the SELECT list can be referenced in ORDER BY Correct answer.
  2. An alias can be referenced in GROUP BY only if it is uppercase
  3. An alias must be enclosed in double quotation marks to be used in ORDER BY
  4. An alias can be referenced in the WHERE clause of the same query

Explanation

ORDER BY is evaluated after the SELECT list, so column aliases defined there are visible to it. WHERE is processed before the SELECT list is resolved, so aliases cannot be used there.

Question 25Restricting and Sorting Data

Evaluate: SELECT last_name FROM employees WHERE salary > 10000 AND job_id LIKE 'SA%' OR department_id = 90;. Which rows can be returned?

  1. Only rows with salary above 10000 and a job_id starting with SA
  2. Rows satisfying salary > 10000 AND job_id LIKE 'SA%', plus any row in department 90 Correct answer.
  3. Rows with salary above 10000 and either a job_id starting with SA or department 90
  4. Only rows in department 90

Explanation

AND is evaluated before OR, so the condition is effectively (salary > 10000 AND job_id LIKE 'SA%') OR department_id = 90. Any department 90 row qualifies even if the salary or job test fails.

Question 26Restricting and Sorting Data

Which ORDER BY clause sorts employees by department_id ascending and, within each department, by salary from highest to lowest?

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

Explanation

The first sort key is the primary sort, so department_id ascending must come first, and salary DESC as the second key orders salaries high to low inside each department. Direction defaults to ascending when omitted.

Question 27Restricting and Sorting Data

Which WHERE clause returns employees whose commission_pct is neither null nor below 0.15?

  1. WHERE commission_pct >= 0.15 Correct answer.
  2. WHERE commission_pct > 0.15
  3. WHERE commission_pct IS NOT NULL AND commission_pct < 0.15
  4. WHERE commission_pct IS NULL OR commission_pct >= 0.15

Explanation

A comparison such as commission_pct >= 0.15 evaluates to UNKNOWN for null values, and UNKNOWN rows are filtered out, so nulls are automatically excluded. The predicate therefore returns only non-null values of 0.15 or more.

Question 28Restricting and Sorting Data

Which clause returns job_id values that begin with 'A' followed by exactly two characters and nothing else?

  1. WHERE job_id LIKE 'A%'
  2. WHERE job_id LIKE '%A__'
  3. WHERE job_id LIKE 'A__' Correct answer.
  4. WHERE job_id LIKE 'A_'

Explanation

Each underscore matches exactly one character, so 'A__' requires an 'A' plus exactly two more characters, with no percent sign to allow extra characters. The pattern is therefore a fixed length of three.

Question 29Restricting and Sorting Data

You write WHERE last_name LIKE 'Gr%' ESCAPE '\'. Which statement is true?

  1. The escape character makes '%' match a literal percent sign in the data Correct answer.
  2. The escape character is ignored because '%' has no special meaning
  3. The escape character changes '%' into an underscore wildcard
  4. The escape character makes the pattern case-insensitive

Explanation

The ESCAPE clause designates a character that removes the special meaning of the following wildcard, so the escape character before '%' lets the pattern match a literal percent sign. Without it, '%' would match any sequence of characters.

Question 30Restricting and Sorting Data

Which ORDER BY clause lists employees so that those with a null manager_id appear first, then the rest sorted by hire_date from newest to oldest?

  1. ORDER BY manager_id DESC NULLS LAST, hire_date DESC
  2. ORDER BY manager_id NULLS FIRST, hire_date DESC Correct answer.
  3. ORDER BY hire_date DESC, manager_id NULLS FIRST
  4. ORDER BY manager_id, hire_date DESC

Explanation

NULLS FIRST on the manager_id key forces null manager rows to the top of the result, and hire_date DESC orders the remaining rows newest first. The NULLS clause must follow the sort direction for that key.

Question 31Restricting and Sorting Data

Evaluate: SELECT last_name, salary FROM employees WHERE salary NOT IN (SELECT salary FROM employees WHERE department_id = 50);. Some rows in department 50 have a null salary. What happens?

  1. Rows with a salary not equal to any department 50 salary are returned
  2. No rows are returned because the NOT IN list contains a null Correct answer.
  3. All employees are returned because NOT IN ignores nulls
  4. An error is raised because a subquery cannot contain nulls

Explanation

NOT IN is equivalent to a series of inequality tests joined by AND. Comparing any value with NULL yields UNKNOWN, so the whole conjunction becomes UNKNOWN and no row satisfies the predicate.

Question 32Restricting and Sorting Data

Which expression returns the string 'ABC' when the column code holds 'abc' in a case-sensitive database?

  1. LOWER(code)
  2. TRIM(code)
  3. UPPER(code) Correct answer.
  4. INITCAP(code)

Explanation

UPPER converts every alphabetic character to uppercase, so 'abc' becomes 'ABC'. This is a single-row character function usable in the SELECT list or a WHERE predicate.

Question 33Restricting and Sorting Data

You need rows where the value of col is either 5 or null. Which predicate achieves this?

  1. WHERE col IN (5, NULL)
  2. WHERE col = 5 OR col = NULL
  3. WHERE NVL(col, 5) = 5 AND col IS NOT NULL
  4. WHERE col = 5 OR col IS NULL Correct answer.

Explanation

Null cannot be tested with equality, so the null case must use IS NULL combined with OR to the equality test. The result keeps rows where col is 5 and rows where col is null.

Question 34Restricting and Sorting Data

Which ORDER BY clause correctly sorts by the second column of the SELECT list without naming it, in descending order?

  1. ORDER BY 2 DESC Correct answer.
  2. ORDER BY COLUMN2 DESC
  3. ORDER BY DESC 2
  4. ORDER BY #2 DESC

Explanation

Oracle allows positional sort using an integer that refers to the position of an expression in the SELECT list, so ORDER BY 2 DESC sorts by the second selected expression in descending order. The direction keyword follows the position number.

Question 35Single-Row Functions

In Oracle SQL, which expression returns the number of characters in the string 'Oracle' as stored in the database character set?

  1. WIDTH('Oracle')
  2. SIZE('Oracle')
  3. COUNT('Oracle')
  4. LENGTH('Oracle') Correct answer.

Explanation

LENGTH returns the number of characters in a character string, so LENGTH('Oracle') evaluates to 6. It operates on a single string value, not on rows, which distinguishes it from aggregate functions.

Question 36Single-Row Functions

Evaluate this expression: SELECT UPPER(SUBSTR('database', 5, 3)) FROM dual; What is the result?

  1. base
  2. BAS
  3. BASED
  4. ASE Correct answer.

Explanation

SUBSTR starts at position 5 of 'database', which is the character 'a', and takes 3 characters, giving 'ase'. UPPER then converts that substring to 'ASE'.

Question 37Single-Row Functions

Which statement about the INSTR function is true when it is given a negative starting position?

  1. It raises ORA-01428 because negative positions are invalid
  2. It searches backward from the end of the string toward the beginning Correct answer.
  3. It treats the negative value as an absolute position counted from the start
  4. It always returns 0 because the search direction is undefined

Explanation

When the start position passed to INSTR is negative, Oracle searches backward through the string beginning at that position counted from the end. The returned position is still measured from the start of the string.

Question 38Single-Row Functions

You run: SELECT TRIM(BOTH 'x' FROM 'xxOraclexx') FROM dual; What is returned?

  1. xxOracle
  2. Oracle Correct answer.
  3. xxOraclexx
  4. Oraclexx

Explanation

TRIM with BOTH removes all leading and trailing occurrences of the specified trim character 'x'. Only the interior text remains, so the result is 'Oracle'.

Question 39Single-Row Functions

Which function would you use to replace every occurrence of the substring 'cat' inside a column value with 'dog'?

  1. REPLACE(column_name, 'cat', 'dog') Correct answer.
  2. TRANSLATE(column_name, 'cat', 'dog')
  3. SUBSTR(column_name, 'cat', 'dog')
  4. INSTR(column_name, 'cat', 'dog')

Explanation

REPLACE searches a string for a specified substring and substitutes every occurrence with a replacement string. TRANSLATE performs character-by-character mapping instead of substring replacement.

Question 40Single-Row Functions

What does SELECT TRANSLATE('1A2B3C', '123', 'XYZ') FROM dual; return?

  1. XYZABC
  2. XA2B3C
  3. XAYBZC Correct answer.
  4. 1A2B3C

Explanation

TRANSLATE replaces each character in the first argument that appears in the 'from' string with the corresponding character in the 'to' string. So 1 becomes X, 2 becomes Y, and 3 becomes Z, leaving letters A, B, and C untouched.

Question 41Single-Row Functions

Which expression correctly rounds the number 45.926 to two decimal places, giving 45.93?

  1. ROUND(45.926, 2) Correct answer.
  2. MOD(45.926, 2)
  3. CEIL(45.926)
  4. TRUNC(45.926, 2)

Explanation

ROUND with a precision of 2 rounds the value at the third decimal place; since the digit is 6, the second decimal increases, producing 45.93. TRUNC would instead cut off the extra digits without rounding.

Question 42Single-Row Functions

Evaluate SELECT TRUNC(789.456, -2) FROM dual. What is the result?

  1. 789
  2. 800
  3. 700 Correct answer.
  4. 789.45

Explanation

A negative precision tells TRUNC to truncate digits to the left of the decimal point. With -2 the value is cut down to the nearest hundred without rounding, giving 700.

Question 43Single-Row Functions

What is the result of SELECT MOD(17, 5) FROM dual?

  1. 3.4
  2. 2 Correct answer.
  3. 0
  4. 3

Explanation

MOD returns the remainder after dividing the first argument by the second. 17 divided by 5 is 3 with a remainder of 2, so MOD returns 2.

Question 44Single-Row Functions

Which statement correctly describes the difference between CEIL and FLOOR?

  1. CEIL returns the smallest integer greater than or equal to the value, while FLOOR returns the largest integer less than or equal to it Correct answer.
  2. CEIL and FLOOR both round to the nearest integer and differ only for negative values
  3. CEIL returns the largest integer less than or equal to the value, while FLOOR returns the smallest integer greater than or equal to it
  4. CEIL truncates toward zero, while FLOOR truncates away from zero

Explanation

CEIL always moves up to the next whole integer when the value has a fractional part, and FLOOR always moves down. For example, CEIL(4.1) is 5 and FLOOR(4.9) is 4.

Question 45Single-Row Functions

Evaluate SELECT CEIL(-4.7) FROM dual. What is returned?

  1. 5
  2. -4.7
  3. -5
  4. -4 Correct answer.

Explanation

CEIL returns the smallest integer that is greater than or equal to the argument. Because -4 is greater than -4.7 and is the nearest integer above it, the result is -4.

Question 46Single-Row Functions

Which function returns the current database server date and time, including the time zone of the database server?

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

Explanation

SYSTIMESTAMP returns the system date and time of the database server, including its time zone and fractional seconds. SYSDATE returns only date and time without time zone information.

Question 47Single-Row Functions

You need the last day of the month for the date value hire_date. Which expression returns that date?

  1. NEXT_DAY(hire_date, 'LAST')
  2. LAST_DAY(hire_date) Correct answer.
  3. TRUNC(hire_date, 'MM')
  4. ADD_MONTHS(hire_date, 1)

Explanation

LAST_DAY returns the date of the last day of the month containing the supplied date. It accounts automatically for months of different lengths, including February in leap years.

Question 48Single-Row Functions

Evaluate SELECT MONTHS_BETWEEN(DATE '2024-03-15', DATE '2024-01-15') FROM dual. What is the result?

  1. 1.94
  2. 60
  3. 2.5
  4. 2 Correct answer.

Explanation

MONTHS_BETWEEN returns the number of months between two dates based on their day-of-month components. Both dates fall on the 15th, so the difference is exactly 2 months.

Question 49Single-Row Functions

What does SELECT NEXT_DAY(DATE '2024-05-01', 'FRIDAY') FROM dual return, given that 1 May 2024 is a Wednesday?

  1. 08-MAY-24
  2. 30-APR-24
  3. 03-MAY-24 Correct answer.
  4. 01-MAY-24

Explanation

NEXT_DAY returns the first date after the supplied date that falls on the named weekday. The first Friday after Wednesday 1 May 2024 is 3 May 2024.

Question 50Single-Row Functions

Which expression converts the string '2024-07-04' into a DATE value using an explicit format model?

  1. TO_NUMBER('2024-07-04', 'YYYY-MM-DD')
  2. CAST('2024-07-04' AS TIMESTAMP)
  3. TO_DATE('2024-07-04', 'YYYY-MM-DD') Correct answer.
  4. TO_CHAR('2024-07-04', 'YYYY-MM-DD')

Explanation

TO_DATE converts a character string into a DATE value and accepts a format model that describes the layout of the input. The format 'YYYY-MM-DD' matches the string exactly.