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

Oracle 1Z0-084 practice questions

One question at a time. Build your Oracle 1Z0-084 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 and Restricting Data with SELECT

Which SELECT clause is evaluated before the WHERE clause when Oracle processes a query against the employees table?

  1. FROM Correct answer.
  2. SELECT
  3. HAVING
  4. ORDER BY

Explanation

Oracle logically evaluates FROM first to identify and join the row sources, then applies WHERE to filter rows, then GROUP BY, HAVING, SELECT, and finally ORDER BY. The SELECT list is therefore populated only after filtering has already occurred.

Question 2Retrieving and Restricting Data with SELECT

You run SELECT last_name, salary FROM employees WHERE salary BETWEEN 5000 AND 10000;. Which rows are returned?

  1. Rows where salary is 5000 or 10000 only
  2. Rows where salary is greater than or equal to 5000 and strictly less than 10000
  3. Rows where salary is greater than 5000 and less than 10000
  4. Rows where salary is 5000, 10000, or any value in between, inclusive Correct answer.

Explanation

BETWEEN a AND b is an inclusive range test equivalent to salary >= 5000 AND salary <= 10000. Both boundary values satisfy the predicate and are returned.

Question 3Retrieving and Restricting Data with SELECT

Which character matches exactly one arbitrary character in a LIKE pattern?

  1. %
  2. *
  3. _ Correct answer.
  4. ?

Explanation

In an Oracle LIKE pattern the underscore represents exactly one character, while the percent sign represents any sequence of zero or more characters. The asterisk and question mark have no wildcard meaning in LIKE.

Question 4Retrieving and Restricting Data with SELECT

Which operator tests whether a column value is absent rather than equal to any value?

  1. <> NULL
  2. LIKE NULL
  3. IS NULL Correct answer.
  4. = NULL

Explanation

NULL represents an unknown value, so it can never be tested with an equality or inequality operator, which yields UNKNOWN. Only the IS NULL (or IS NOT NULL) predicate reliably detects missing values.

Question 5Retrieving and Restricting Data with SELECT

Which clause restricts groups after aggregation has taken place?

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

Explanation

WHERE filters individual rows before grouping, whereas HAVING filters the aggregated groups produced by GROUP BY. A condition on an aggregate such as SUM(salary) must therefore appear in HAVING.

Question 6Retrieving and Restricting Data with SELECT

Which statement about the default sort order of ORDER BY is true in Oracle?

  1. Numeric columns default to descending and character columns default to ascending
  2. The default is ascending, with nulls placed first
  3. The default is descending, with nulls placed first
  4. The default is ascending, with nulls placed last Correct answer.

Explanation

ORDER BY defaults to ASC regardless of data type, and Oracle places nulls last for ascending sorts unless NULLS FIRST is specified. Descending order reverses this so nulls appear first by default.

Question 7Retrieving and Restricting Data with SELECT

Which expression returns the first three characters of the string 'ORACLE'?

  1. SUBSTR('ORACLE', 3)
  2. INSTR('ORACLE', 3)
  3. TRIM('ORACLE', 3)
  4. SUBSTR('ORACLE', 1, 3) Correct answer.

Explanation

SUBSTR(string, position, length) starts at the given position and returns the requested number of characters, so SUBSTR('ORACLE',1,3) returns 'ORA'. Oracle string positions are one-based.

Question 8Retrieving and Restricting Data with SELECT

A query uses WHERE hire_date = '15-MAR-2023'. Why can this comparison fail to return matching rows?

  1. The literal must always be enclosed in double quotes
  2. The equals operator is invalid for DATE columns
  3. Oracle cannot compare a DATE column to a character literal at all
  4. The DATE column stores a time component, so the literal must match the exact time or use a range or TRUNC Correct answer.

Explanation

A DATE value includes a time-of-day component, and a character literal is converted using the session's date format model, which may also differ. Comparing with an exact date literal only matches rows whose time is midnight, so TRUNC(hire_date) or a range is safer.

Question 9Retrieving and Restricting Data with SELECT

Which predicate returns rows where commission_pct is either null or greater than 0.2?

  1. WHERE commission_pct > 0.2 AND commission_pct IS NULL
  2. WHERE commission_pct > 0.2
  3. WHERE NVL(commission_pct, 0) > 0.2
  4. WHERE commission_pct > 0.2 OR commission_pct IS NULL Correct answer.

Explanation

A comparison against a null value evaluates to UNKNOWN, so rows with a null commission are excluded by the simple predicate. Adding OR commission_pct IS NULL explicitly includes those rows.

Question 10Retrieving and Restricting Data with SELECT

Evaluate: SELECT last_name FROM employees WHERE last_name LIKE 'S%' AND salary > 8000 ORDER BY salary DESC;. What does this statement do?

  1. Returns employees whose name starts with S or whose salary exceeds 8000, sorted ascending
  2. Returns employees whose name starts with S and whose salary exceeds 8000, lowest salary first
  3. Returns employees whose name contains S anywhere and whose salary exceeds 8000, sorted descending
  4. Returns employees whose name starts with S and whose salary exceeds 8000, highest salary first Correct answer.

Explanation

The AND operator requires both predicates to be true, the S% pattern anchors the S at the start of the name, and DESC orders the surviving rows from highest to lowest salary.

Question 11Retrieving and Restricting Data with SELECT

Which query correctly returns the number of employees in each department, showing only departments with more than five employees?

  1. SELECT department_id, COUNT(*) FROM employees GROUP BY department_id WHERE COUNT(*) > 5
  2. SELECT department_id, COUNT(*) FROM employees WHERE COUNT(*) > 5 GROUP BY department_id
  3. SELECT department_id, COUNT(*) FROM employees GROUP BY department_id HAVING COUNT(*) > 5 Correct answer.
  4. SELECT department_id, COUNT(*) FROM employees HAVING COUNT(*) > 5

Explanation

Group functions cannot appear in WHERE because WHERE runs before aggregation; the group-level filter belongs in HAVING after GROUP BY. The query must also group by department_id to produce one row per department.

Question 12Retrieving and Restricting Data with SELECT

Which expression returns the current date with the time portion removed?

  1. SYSDATE - 1
  2. ROUND(SYSDATE)
  3. TO_CHAR(SYSDATE)
  4. TRUNC(SYSDATE) Correct answer.

Explanation

TRUNC on a DATE removes the time component by truncating to midnight of the current day. ROUND would round to the nearest day, which can move the value to the next day after noon.

Question 13Retrieving and Restricting Data with SELECT

Which query lists employees whose last_name begins with the letter A, ignoring case, and sorts the result alphabetically?

  1. SELECT last_name FROM employees WHERE UPPER(last_name) LIKE 'A%' ORDER BY last_name Correct answer.
  2. SELECT last_name FROM employees WHERE last_name = 'A%' ORDER BY last_name
  3. SELECT last_name FROM employees WHERE last_name LIKE 'A%' ORDER BY last_name DESC
  4. SELECT last_name FROM employees WHERE last_name LIKE '%A' ORDER BY last_name

Explanation

Applying UPPER to the column and comparing with an uppercase pattern makes the match case-insensitive, and ORDER BY last_name with the default ASC sorts alphabetically. The pattern A% anchors the letter at the beginning.

Question 14Retrieving and Restricting Data with SELECT

Which SELECT list is valid when the query also contains GROUP BY department_id?

  1. SELECT department_id, salary, COUNT(*) ... GROUP BY department_id
  2. SELECT department_id, last_name, COUNT(*) ... GROUP BY department_id
  3. SELECT last_name, COUNT(*) ... GROUP BY department_id
  4. SELECT department_id, COUNT(*) ... GROUP BY department_id Correct answer.

Explanation

Every nonaggregated column in the SELECT list must appear in the GROUP BY clause, while group functions such as COUNT(*) are allowed without being listed there. Selecting department_id with COUNT(*) therefore satisfies the rule.

Question 15Retrieving and Restricting Data with SELECT

Which condition finds employees hired in the year 2022 regardless of month and day?

  1. WHERE hire_date BETWEEN '01-JAN-2022' AND '31-DEC-2022'
  2. WHERE EXTRACT(YEAR FROM hire_date) = 2022 AND hire_date IS NOT NULL
  3. WHERE hire_date = '2022'
  4. WHERE TO_CHAR(hire_date, 'YYYY') = '2022' Correct answer.

Explanation

Formatting the date with the YYYY model converts each hire_date to a four-character year string that can be compared with '2022'. This avoids the time-component problem that affects exact date equality.

Question 16Retrieving and Restricting Data with SELECT

Which operator combination returns rows where a value falls outside an inclusive range without using NOT?

  1. value < low AND value > high
  2. value <= low OR value >= high
  3. value < low OR value > high Correct answer.
  4. value BETWEEN low AND high

Explanation

Being outside an inclusive range means the value is strictly less than the lower bound or strictly greater than the upper bound, so the two comparisons are joined with OR. AND would require a value to be simultaneously below and above the bounds, which is impossible.

Question 17Retrieving and Restricting Data with SELECT

Which expression returns 0 for a null commission_pct and the column value otherwise?

  1. NVL(commission_pct, 0) Correct answer.
  2. DECODE(commission_pct)
  3. NULLIF(commission_pct, 0)
  4. COALESCE(commission_pct)

Explanation

NVL takes two arguments and substitutes the second when the first is null, which is exactly the required behavior. COALESCE also handles nulls but requires at least two arguments.

Question 18Retrieving and Restricting Data with SELECT

A report must show departments whose average salary exceeds 6000, displaying the department and the average rounded to two decimals. Which query satisfies this?

  1. SELECT department_id, ROUND(AVG(salary), 2) FROM employees GROUP BY department_id HAVING AVG(salary) > 6000 Correct answer.
  2. SELECT department_id, AVG(salary) FROM employees HAVING ROUND(AVG(salary), 2) > 6000
  3. SELECT department_id, ROUND(AVG(salary), 2) FROM employees WHERE AVG(salary) > 6000 GROUP BY department_id
  4. SELECT department_id, ROUND(AVG(salary), 2) FROM employees GROUP BY department_id WHERE AVG(salary) > 6000

Explanation

The aggregate filter on AVG(salary) must be placed in HAVING because the condition applies to grouped results, not individual rows. ROUND(AVG(salary), 2) in the SELECT list formats the displayed average to two decimals.

Question 19Retrieving and Restricting Data with SELECT

Which query returns all employees whose manager_id is not 100, including employees with a null manager_id?

  1. SELECT last_name FROM employees WHERE manager_id <> 100 OR manager_id IS NULL Correct answer.
  2. SELECT last_name FROM employees WHERE manager_id <> 100
  3. SELECT last_name FROM employees WHERE manager_id NOT IN (100)
  4. SELECT last_name FROM employees WHERE NOT (manager_id = 100)

Explanation

A row with a null manager_id makes manager_id <> 100 evaluate to UNKNOWN, so it is filtered out by the simple predicate. Adding OR manager_id IS NULL explicitly includes those rows in the result.

Question 20Retrieving and Restricting Data with SELECT

Which statement about using a column alias in the WHERE clause is correct?

  1. A column alias defined in the SELECT list cannot be referenced in WHERE because WHERE is evaluated before the SELECT list Correct answer.
  2. A column alias can be referenced in WHERE only when it is enclosed in double quotes
  3. A column alias defined in the SELECT list can be referenced in WHERE because WHERE runs first
  4. A column alias can be referenced in WHERE only if it is also listed in ORDER BY

Explanation

Because WHERE is logically evaluated before the SELECT list is computed, an alias created in the SELECT list is not yet available for filtering. To filter on a computed expression you must repeat the expression in WHERE or wrap the query in an inline view.

Question 21Retrieving and Restricting Data with SELECT

Examine: SELECT department_id, COUNT(*) FROM employees WHERE salary > 4000 GROUP BY department_id HAVING COUNT(*) >= 3 ORDER BY COUNT(*) DESC;. Which rows appear in the output?

  1. All departments, sorted by total employee count, with no filtering by salary
  2. Only departments where every employee earns more than 4000 and the group has at least three rows
  3. Only departments whose total employee count is at least three, counting all salaries
  4. All departments, with a count of employees earning more than 4000, only where that count is at least three Correct answer.

Explanation

WHERE first removes employees earning 4000 or less, GROUP BY forms the remaining rows into departments, and HAVING keeps only groups with at least three such employees. ORDER BY then sorts those groups by the surviving count in descending order.

Question 22Retrieving and Restricting Data with SELECT

Which query correctly returns the second through fourth characters of the string 'DATABASE'?

  1. SUBSTR('DATABASE', 2, 4)
  2. SUBSTR('DATABASE', 2, 3) Correct answer.
  3. SUBSTR('DATABASE', 3, 2)
  4. SUBSTR('DATABASE', 2)

Explanation

SUBSTR(string, position, length) is one-based, so starting at position 2 with length 3 returns characters two, three, and four, which are 'ATA'. The length argument counts characters rather than naming the end position.

Question 23Retrieving and Restricting Data with SELECT

A query uses WHERE UPPER(last_name) = 'SMITH' and an index exists on last_name. What is the main performance consequence?

  1. The index is used only when the session is in a case-insensitive mode
  2. The query fails because functions cannot appear in WHERE
  3. The index on last_name is generally bypassed because the function is applied to the column Correct answer.
  4. The index is used normally because UPPER is applied to the literal only

Explanation

Applying a function to the indexed column prevents Oracle from using a plain index on that column, because the index stores the raw column values. A function-based index on UPPER(last_name) or a case-insensitive collation would restore index access.

Question 24Retrieving and Restricting Data with SELECT

Which expression returns the position of the substring 'TA' within 'DATABASE'?

  1. INSTR('DATABASE', 'TA') Correct answer.
  2. REPLACE('DATABASE', 'TA')
  3. SUBSTR('DATABASE', 'TA')
  4. LENGTH('DATABASE', 'TA')

Explanation

INSTR(string, substring) returns the one-based position where the substring first occurs, which is 3 for 'TA' in 'DATABASE'. SUBSTR extracts characters, and LENGTH and REPLACE have different purposes.

Question 25Using Single-Row and Conversion Functions

In Oracle SQL, which expression returns the number of characters in the string 'Oracle' using a single-row character function?

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

Explanation

LENGTH is the single-row character function that returns the number of characters in its string argument, so LENGTH('Oracle') evaluates to 6. It operates on one row at a time and can be nested inside other expressions.

Question 26Using Single-Row and Conversion Functions

Which statement about the NVL function is correct?

  1. NVL removes leading and trailing spaces from a string.
  2. NVL returns the second argument when the first argument is null. Correct answer.
  3. NVL returns null when either argument is null.
  4. NVL converts a number to a character string.

Explanation

NVL(expr1, expr2) substitutes expr2 when expr1 evaluates to null; otherwise it returns expr1. This is used to replace null values with a meaningful default in single-row expressions.

Question 27Using Single-Row and Conversion Functions

Evaluate this expression: TO_NUMBER('1,250.75', '9,999.99'). What is the result?

  1. It returns the string '1250.75'.
  2. It raises ORA-01722 because of the comma.
  3. It returns 125075 because the comma is ignored.
  4. It returns the number 1250.75. Correct answer.

Explanation

TO_NUMBER converts a character string to a NUMBER using the supplied format model. The format '9,999.99' tells Oracle that the comma is a group separator and the period is the decimal marker, so the result is the numeric value 1250.75.

Question 28Using Single-Row and Conversion Functions

Which function returns the current database server date and time as a DATE value?

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

Explanation

SYSDATE returns the current date and time of the database server as a DATE. SYSTIMESTAMP and CURRENT_TIMESTAMP return TIMESTAMP values, and LOCALTIMESTAMP returns the session-local timestamp.

Question 29Using Single-Row and Conversion Functions

What does the following return? SUBSTR('Database', 5, 3)

  1. 'bas' Correct answer.
  2. 'aba'
  3. 'ata'
  4. 'ase'

Explanation

SUBSTR(string, start, length) begins at position 5, which is the character 'b' in 'Database', and returns three characters: 'bas'. The start position is 1-based in Oracle SQL.

Question 30Using Single-Row and Conversion Functions

Which expression correctly returns the number of months between the dates 15-JAN-2024 and 15-APR-2024?

  1. MONTHS_BETWEEN(TO_DATE('15-APR-2024','DD-MON-YYYY'), TO_DATE('15-JAN-2024','DD-MON-YYYY')) Correct answer.
  2. DATE_DIFF('15-APR-2024','15-JAN-2024','MONTH')
  3. MONTHS_BETWEEN('15-APR-2024','15-JAN-2024')
  4. DATEDIFF(MONTH, '15-JAN-2024', '15-APR-2024')

Explanation

MONTHS_BETWEEN requires DATE arguments, so character literals must be converted with TO_DATE first. With proper DATE inputs it returns 3 for these two dates.

Question 31Using Single-Row and Conversion Functions

Which function should you use to replace every occurrence of 'X' with 'Y' inside a character string?

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

Explanation

REPLACE(string, search, replacement) substitutes all occurrences of the search string with the replacement string. TRANSLATE performs character-by-character substitution, which behaves differently for multi-character patterns.

Question 32Using Single-Row and Conversion Functions

Given NULL for the column COMM, what does NVL2(COMM, COMM, 0) return?

  1. NULL
  2. 0 Correct answer.
  3. An error is raised.
  4. COMM

Explanation

NVL2(expr1, expr2, expr3) returns expr2 when expr1 is not null and expr3 when expr1 is null. Because COMM is null here, the third argument 0 is returned.

Question 33Using Single-Row and Conversion Functions

Which statement about the COALESCE function is true?

  1. It returns null if any argument is null.
  2. It accepts only two arguments.
  3. It converts its argument to a DATE.
  4. It returns the first non-null argument in the list. Correct answer.

Explanation

COALESCE evaluates its arguments in order and returns the first one that is not null. It accepts two or more arguments, unlike NVL which takes exactly two.

Question 34Using Single-Row and Conversion Functions

What is the result of ROUND(45.926, 2)?

  1. 46
  2. 45.9
  3. 45.93 Correct answer.
  4. 45.92

Explanation

ROUND(number, n) rounds to n decimal places. Rounding 45.926 to two decimal places looks at the third digit (6), which rounds the second digit up, giving 45.93.

Question 35Using Single-Row and Conversion Functions

Which expression converts the string '2024-03-15' into a DATE using an explicit format model?

  1. TO_NUMBER('2024-03-15', 'YYYY-MM-DD')
  2. TO_DATE('2024-03-15', 'YYYY-MM-DD') Correct answer.
  3. TO_CHAR('2024-03-15', 'YYYY-MM-DD')
  4. CAST('2024-03-15' AS DATE)

Explanation

TO_DATE(char, fmt) converts a character string to a DATE using the given format model, so TO_DATE('2024-03-15','YYYY-MM-DD') is the explicit conversion. The format model matches the literal exactly.

Question 36Using Single-Row and Conversion Functions

Which function returns the position of the first occurrence of a substring within a string?

  1. INSTR Correct answer.
  2. SUBSTR
  3. LENGTH
  4. TRIM

Explanation

INSTR(string, substring) returns the numeric position where the substring first occurs, or 0 if it is not found. SUBSTR extracts characters, LENGTH counts characters, and TRIM removes characters.

Question 37Using Single-Row and Conversion Functions

Evaluate: TRUNC(1234.5678, 2). What is returned?

  1. 1234.57
  2. 1234
  3. 1234.56 Correct answer.
  4. 1200

Explanation

TRUNC(number, n) truncates to n decimal places without rounding. The digits beyond the second decimal place are simply dropped, giving 1234.56.

Question 38Using Single-Row and Conversion Functions

Which statement about implicit versus explicit data type conversion is correct?

  1. Implicit conversion is always preferable to explicit conversion.
  2. Oracle never performs implicit conversion between character and numeric types.
  3. Explicit conversion with functions such as TO_CHAR documents intent and avoids format ambiguity. Correct answer.
  4. TO_CHAR cannot convert a DATE value.

Explanation

Using explicit conversion functions such as TO_CHAR, TO_DATE, and TO_NUMBER makes the intended conversion clear and removes dependence on session NLS settings. Implicit conversion can produce unexpected results and errors.

Question 39Using Single-Row and Conversion Functions

What does TO_CHAR(SYSDATE, 'Month DD, YYYY') produce for a date of 05-MAR-2024?

  1. '2024-03-05'
  2. '03-05-2024'
  3. 'MARCH 05, 2024'
  4. 'March 05, 2024' Correct answer.

Explanation

The 'Month' format element returns the month name with padding to nine characters and capitalized as stored, and 'DD' returns the two-digit day. The result is 'March 05, 2024' with padding after the month.

Question 40Using Single-Row and Conversion Functions

Given that the column HIRE_DATE contains a DATE, which expression returns the day of the week as a number?

  1. TO_CHAR(HIRE_DATE, 'DD')
  2. TO_CHAR(HIRE_DATE, 'DAY')
  3. EXTRACT(DAY FROM HIRE_DATE)
  4. TO_CHAR(HIRE_DATE, 'D') Correct answer.

Explanation

The 'D' format element returns the day of the week as a number, depending on the NLS territory settings. 'DAY' returns the name, and 'DD' returns the day of the month.

Question 41Using Single-Row and Conversion Functions

Which function removes specified characters from both ends of a string?

  1. RTRIM
  2. LTRIM
  3. SUBSTR
  4. TRIM Correct answer.

Explanation

TRIM removes leading, trailing, or both leading and trailing characters from a string. LTRIM and RTRIM remove characters from only one side.

Question 42Using Single-Row and Conversion Functions

What is returned by LPAD('123', 5, '0')?

  1. '12300'
  2. '000123'
  3. '00123' Correct answer.
  4. '123'

Explanation

LPAD(string, n, pad) pads the string on the left with the pad characters until the total length is n. Here '123' is padded to length 5 with zeros on the left, giving '00123'.

Question 43Using Single-Row and Conversion Functions

Which expression returns the last day of the month containing the date 10-FEB-2024?

  1. ADD_MONTHS(TO_DATE('10-FEB-2024','DD-MON-YYYY'),1)
  2. LAST_DAY(TO_DATE('10-FEB-2024','DD-MON-YYYY')) Correct answer.
  3. MONTHS_BETWEEN(TO_DATE('10-FEB-2024','DD-MON-YYYY'), SYSDATE)
  4. NEXT_DAY(TO_DATE('10-FEB-2024','DD-MON-YYYY'),'SAT')

Explanation

LAST_DAY returns the date of the last day of the month that contains the supplied date. For February 2024, a leap year, it returns 29-FEB-2024.

Question 44Using Single-Row and Conversion Functions

A column STORED_AMT is VARCHAR2 and contains numeric characters. Which approach best avoids ORA-01722 when comparing it to a NUMBER?

  1. Convert the column explicitly with TO_NUMBER and ensure all values are valid numbers. Correct answer.
  2. Convert the NUMBER to VARCHAR2 with TO_CHAR and compare as strings for all cases.
  3. Rely on implicit conversion of the column to NUMBER in the WHERE clause.
  4. Use NVL on the column without any conversion.

Explanation

ORA-01722 occurs when implicit conversion of a character column to NUMBER encounters non-numeric data. Converting explicitly with TO_NUMBER makes the intent clear, but the underlying data must still be valid numeric text.

Question 45Reporting Aggregated Data Using Group Functions

You execute: SELECT COUNT(*), COUNT(commission_pct), COUNT(DISTINCT manager_id) FROM employees; What does the result describe?

  1. The total number of rows, the number of commission values including nulls, and the number of manager rows
  2. The number of distinct rows, the number of distinct commissions, and the number of managers
  3. The total number of rows, the number of non-null commission values, and the number of distinct non-null manager values Correct answer.
  4. The total number of rows, the number of null commission values, and the number of employees without a manager

Explanation

COUNT(*) counts every row regardless of nulls, while COUNT(expr) counts only rows where the expression is not null. COUNT(DISTINCT expr) adds a deduplication step and still ignores nulls, so it returns the number of different non-null manager values.

Question 46Reporting Aggregated Data Using Group Functions

Which group function can be applied directly to a column of any datatype, including DATE and VARCHAR2, without conversion?

  1. MIN Correct answer.
  2. STDDEV
  3. AVG
  4. SUM

Explanation

MIN and MAX work on character, date, and numeric data because they rely on the column's sort order. SUM, AVG, and STDDEV are numeric-only group functions and raise an error on non-numeric input.

Question 47Reporting Aggregated Data Using Group Functions

Evaluate: SELECT AVG(NVL(commission_pct,0)) FROM employees; How does this differ from AVG(commission_pct)?

  1. The two expressions always return identical results
  2. The NVL version ignores rows where commission_pct is null
  3. The NVL version divides only by rows with a null commission
  4. The NVL version divides by all rows, while AVG(commission_pct) divides only by rows with a non-null commission Correct answer.

Explanation

AVG ignores nulls in both the numerator and the denominator, so it divides the sum of non-null values by the count of non-null values. Wrapping the column in NVL(...,0) makes every row non-null, so the divisor becomes the total row count and the average is diluted.

Question 48Reporting Aggregated Data Using Group Functions

A query contains SELECT department_id, AVG(salary) FROM employees; with no GROUP BY clause. What happens?

  1. It raises ORA-00937 because a nonaggregated column is mixed with a group function Correct answer.
  2. It returns one row per distinct salary value
  3. It returns one row per department
  4. It returns the average salary for the whole table and ignores department_id

Explanation

When a SELECT list combines an aggregate with a plain column and no GROUP BY exists, Oracle cannot decide which department_id to display and raises ORA-00937 (not a single-group group function). Either add department_id to GROUP BY or remove it from the select list.

Question 49Reporting Aggregated Data Using Group Functions

Which clause restricts groups after aggregation rather than restricting individual rows before aggregation?

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

Explanation

WHERE filters rows before they are fed into the group functions, while HAVING filters the aggregated groups afterward. A condition on an aggregate result such as AVG(salary) > 5000 must therefore appear in HAVING.

Question 50Reporting Aggregated Data Using Group Functions

Which statement about using a group function inside a WHERE clause is true?

  1. It is allowed only with COUNT
  2. It is allowed if the function is wrapped in NVL
  3. It is allowed when the same function also appears in HAVING
  4. It raises an error because group functions cannot appear in WHERE Correct answer.

Explanation

WHERE is evaluated per row before groups exist, so an aggregate such as SUM(salary) has no value there and Oracle raises ORA-00934. The equivalent restriction on aggregated results belongs in HAVING.