Oracle 1Z0-071: Group Functions

Topic snapshot

Sample questions

These questions are original IT Mastery practice items aligned to this topic area. They are designed for self-assessment and are not official exam questions.

Question 1

Topic: Report Aggregated Data by Using Group Functions

In the EMPLOYEES table, which statement returns one row per DEPARTMENT_ID showing the average SALARY only for departments whose average salary is greater than 10000?

Options:

• A. SELECT department_id, AVG(salary) FROM employees WHERE AVG(salary) > 10000 GROUP BY department_id;

• B. SELECT department_id, AVG(salary) FROM employees HAVING AVG(salary) > 10000 GROUP BY department_id;

• C. SELECT department_id, AVG(salary) FROM employees GROUP BY department_id HAVING salary > 10000;

• D. SELECT department_id, AVG(salary) FROM employees GROUP BY department_id HAVING AVG(salary) > 10000;

Best answer: D

Explanation: To restrict grouped results in Oracle SQL, use HAVING, not WHERE. WHERE filters rows before grouping, while HAVING filters the groups created by GROUP BY based on aggregate results such as AVG(salary).

The key concept is the difference between row filtering and group filtering. In this query, Oracle must first group rows by DEPARTMENT_ID, then calculate AVG(salary) for each group, and only after that remove groups whose average is not above 10000.

The normal processing logic is:

• FROM reads the table.
• WHERE filters individual rows.
• GROUP BY forms groups.
• HAVING filters those groups.

Because AVG(salary) is an aggregate value, it cannot be used to filter rows in WHERE. The closest wrong idea is using HAVING with salary > 10000, but that tests a non-grouped, non-aggregated column instead of the department average.

• Aggregate in WHERE fails because WHERE cannot filter on AVG(salary).
• Wrong clause order fails because HAVING must come after GROUP BY.
• Detail column in HAVING fails because salary is neither grouped nor aggregated in that condition.

Question 2

Topic: Report Aggregated Data by Using Group Functions

A developer runs this query on table sales_bonus:

SELECT SUM(bonus_amt) AS total_bonus,
       AVG(bonus_amt) AS avg_bonus
FROM sales_bonus;

Data in sales_bonus:

Which result correctly applies Oracle SQL rules for SUM and AVG with NULL values?

Options:

• A. SUM = 500, AVG = 500

• B. SUM = 1500, AVG = 375

• C. SUM = 1500, AVG = 750

• D. SUM = NULL, AVG = NULL

Best answer: C

Explanation: In Oracle SQL, SUM and AVG ignore NULL values rather than treating them as zero. Here, only 1000 and 500 participate, so the sum is 1500 and the average is 1500 / 2 = 750.

The core rule is that Oracle group functions such as SUM and AVG skip NULL values. They do not add NULL as zero, and AVG divides by the count of non-NULL rows.

For the given data, the calculation is based only on 1000 and 500:

• SUM(bonus_amt) = 1500
• AVG(bonus_amt) = 1500 / 2 = 750

If the requirement were to treat missing values as zero, the query would need an expression like AVG(NVL(bonus_amt,0)), but that is a different business rule and a different result.

• Counted all rows fails because AVG does not divide by 4 when two rows are NULL.
• Assumed NULL propagates fails because these aggregate functions ignore NULL unless all input rows are NULL.
• Dropped a non-NULL value fails because both 1000 and 500 are included in the aggregate.

Question 3

Topic: Report Aggregated Data by Using Group Functions

Examine the query:

SELECT department_id, COUNT(*) emp_count, AVG(salary) avg_sal
FROM employees
WHERE salary > 5000
GROUP BY department_id
HAVING COUNT(*) >= 3;

Which statement best describes how Oracle applies the two filters?

Options:

• A. WHERE filters employee rows before grouping, and HAVING filters department groups after grouping.

• B. Both WHERE and HAVING are applied only after GROUP BY completes.

• C. HAVING filters employee rows before grouping, and WHERE filters grouped department results.

• D. The COUNT(*) >= 3 condition could be moved to WHERE without changing the query meaning.

Best answer: A

Explanation: In Oracle aggregate queries, WHERE restricts source rows before groups are formed, while HAVING restricts the groups produced by GROUP BY. Here, only employees with salary above 5,000 are counted, and only departments with at least three such employees remain.

The core rule is that WHERE works at the row level and HAVING works at the group level. In this query, Oracle first removes any employee rows where salary <= 5000. It then groups the remaining rows by department_id, calculates COUNT(*) and AVG(salary) for each department, and finally applies HAVING COUNT(*) >= 3 to keep only groups meeting that aggregate condition.

A useful way to think about it is:

• WHERE: filter rows before aggregation
• GROUP BY: form groups
• HAVING: filter groups after aggregation

The closest wrong idea is that an aggregate condition can simply go in WHERE; that is not valid because WHERE cannot test group results such as COUNT(*).

• Reversed timing fails because HAVING does not evaluate individual source rows before GROUP BY.
• Same phase is wrong because the two clauses operate at different stages of query processing.
• Move aggregate to WHERE fails because COUNT(*) is an aggregate result available only after grouping.

Question 4

Topic: Report Aggregated Data by Using Group Functions

Table orders has columns order_id, customer_id, order_total, and status.

Which query returns one row per customer_id for only SHIPPED orders, shows the total shipped amount per customer, and includes only customers whose shipped total is greater than 1000?

Options:

• A. SELECT customer_id, SUM(order_total) FROM orders WHERE status = ‘SHIPPED’ GROUP BY customer_id HAVING SUM(order_total) > 1000

• B. SELECT customer_id, SUM(order_total) FROM orders WHERE status = ‘SHIPPED’ AND order_total > 1000 GROUP BY customer_id

• C. SELECT customer_id, SUM(order_total) FROM orders GROUP BY customer_id HAVING status = ‘SHIPPED’ AND SUM(order_total) > 1000

• D. SELECT customer_id, SUM(order_total) FROM orders WHERE status = ‘SHIPPED’ GROUP BY customer_id, order_total HAVING SUM(order_total) > 1000

Best answer: A

Explanation: To get one row per customer, the query must group only by customer_id. In Oracle SQL, WHERE filters individual rows before grouping, and HAVING filters the aggregated groups after SUM(order_total) is calculated.

The core rule is that GROUP BY defines the level of aggregation. Because the requirement is one result row per customer_id, only customer_id should be in the GROUP BY clause.

• Use WHERE status = 'SHIPPED' to keep only shipped orders.
• Use GROUP BY customer_id to form one group per customer.
• Use SUM(order_total) to calculate each customer’s shipped total.
• Use HAVING SUM(order_total) > 1000 to keep only qualifying groups.

If you group by extra columns such as order_total, you no longer get one row per customer. If you filter order_total > 1000 in WHERE, you test each row, not the grouped total.

• Extra grouping column: grouping by both customer_id and order_total can split one customer into multiple groups.
• Wrong filter level: applying order_total > 1000 in WHERE checks individual orders instead of each customer’s summed total.
• Invalid grouped condition: using status = 'SHIPPED' in HAVING without grouping or aggregating status is not valid for this grouped query.

Question 5

Topic: Report Aggregated Data by Using Group Functions

In the EMPLOYEES table, department_id identifies the department and manager_id can be NULL. A report must return one row per department_id and show how many different managers are represented in that department, ignoring NULL manager values. Which aggregate expression should be used in the SELECT list?

Options:

• A. COUNT(manager_id)

• B. COUNT(*)

• C. COUNT(DISTINCT manager_id)

• D. MAX(manager_id)

Best answer: C

Explanation: The requirement has two parts: ignore NULL values and count only unique manager IDs within each department. In Oracle SQL, COUNT(DISTINCT expression) is the aggregate that applies both rules at once.

Oracle aggregate functions handle NULL values differently depending on the function and expression used. COUNT(expression) counts only non-NULL values, but it still counts duplicates. When the report asks for the number of different managers, duplicates must be removed before counting, so DISTINCT is required inside the COUNT.

Used with GROUP BY department_id, the correct expression is:

COUNT(DISTINCT manager_id)

This returns the number of unique, non-NULL manager IDs in each department. The key takeaway is that COUNT(*) counts rows, COUNT(column) counts non-NULL values, and COUNT(DISTINCT column) counts unique non-NULL values.

• Missing DISTINCT: the option using COUNT(manager_id) ignores NULLs, but it still counts repeated manager IDs more than once.
• Counts rows instead: the option using COUNT(*) counts every row in the group, even when manager_id is NULL.
• Wrong summary type: the option using MAX(manager_id) returns the highest manager ID, not the number of managers.

Question 6

Topic: Report Aggregated Data by Using Group Functions

In an Oracle SQL query that uses GROUP BY, which selected columns must also be listed in the GROUP BY clause?

Options:

• A. Every selected column not used in a group function

• B. Only selected columns that are part of a primary key

• C. Only selected numeric columns

• D. Only selected columns referenced in ORDER BY

Best answer: A

Explanation: In Oracle, GROUP BY defines the level at which rows are grouped before group functions such as SUM or COUNT are applied. Any selected column that is not inside a group function must appear in the GROUP BY list.

The core rule is: when a query uses GROUP BY, every selected item must be either aggregated by a group function or included in the grouping columns. This is because Oracle needs to know which single value to return for each grouped row.

For example, if a query selects department_id and AVG(salary), then department_id must appear in GROUP BY because it is a nonaggregated selected column. But AVG(salary) does not belong in GROUP BY because it is already summarized by a group function.

A useful shortcut is: selected nonaggregated columns must be grouped; selected aggregated columns do not. That is the rule the distractors miss.

• ORDER BY confusion fails because sorting rules are separate from grouping rules.
• Numeric-only idea fails because data type does not determine whether a selected column must be grouped.
• Primary key idea fails because grouping depends on selected nonaggregated columns, not on key status.

Question 7

Topic: Report Aggregated Data by Using Group Functions

Examine the statement:

SELECT department_id,
       NVL(commission_pct, 0) AS comm_pct,
       SUM(salary) AS total_sal
FROM employees
GROUP BY ...

The developer wants the query to run successfully and return one total per displayed grouping. Which selected items must be included in the GROUP BY clause?

Options:

• A. department_id and salary

• B. department_id, commission_pct, and SUM(salary)

• C. department_id and NVL(commission_pct, 0)

• D. department_id and commission_pct

Best answer: C

Explanation: Oracle requires every selected item that is not inside a group function to appear in the GROUP BY clause. In this query, department_id and NVL(commission_pct, 0) are the nonaggregated selected expressions, while SUM(salary) is the aggregate.

GROUP BY defines the grouping key before Oracle computes aggregate functions such as SUM. Any expression in the SELECT list that is not aggregated must be part of that grouping key. Here, department_id is selected directly, and NVL(commission_pct, 0) is also selected as an expression, so both must appear in GROUP BY.

SUM(salary) does not belong in GROUP BY because it is the aggregate result calculated for each group. Also, grouping by only commission_pct is not enough when the SELECT list uses NVL(commission_pct, 0). Oracle matches the nonaggregated selected expression, not just the underlying column name.

The practical rule is to group by every nonaggregated selected column or expression exactly as needed for the displayed result.

• Grouping by commission_pct alone fails because the select list uses NVL(commission_pct, 0), which Oracle treats as the selected expression.
• Grouping by salary is wrong because salary is only used inside SUM, not as a displayed grouping key.
• Including SUM(salary) in GROUP BY misuses an aggregate, since aggregates are calculated after groups are formed.

Question 8

Topic: Report Aggregated Data by Using Group Functions

You need an Oracle SQL report from employees that:

• returns one row per department and job
• displays department_id, job_id, and the total salary
• excludes rows where department_id is null
• returns only groups with total salary greater than 20,000

Which SQL statement is the best solution?

Options:

• A. SELECT department_id, job_id, SUM(salary) FROM employees WHERE department_id IS NOT NULL GROUP BY department_id, job_id WHERE SUM(salary) > 20000

• B. SELECT department_id, job_id, SUM(salary) FROM employees WHERE department_id IS NOT NULL GROUP BY department_id, job_id, salary HAVING SUM(salary) > 20000

• C. SELECT department_id, job_id, SUM(salary) FROM employees WHERE department_id IS NOT NULL GROUP BY department_id HAVING SUM(salary) > 20000

• D. SELECT department_id, job_id, SUM(salary) FROM employees WHERE department_id IS NOT NULL GROUP BY department_id, job_id HAVING SUM(salary) > 20000

Best answer: D

Explanation: In a grouped Oracle query, every selected item that is not inside an aggregate function must appear in the GROUP BY clause. Here, department_id and job_id are selected directly, so both must be grouped, and the total-salary filter must be applied with HAVING.

Oracle requires all selected expressions that are not aggregated to be included in the GROUP BY clause. In this report, SUM(salary) is the aggregate, so it does not belong in GROUP BY. The selected columns department_id and job_id do belong there because the result must return one row for each department-and-job combination.

The filters split into two types:

• WHERE department_id IS NOT NULL removes detail rows before grouping.
• HAVING SUM(salary) > 20000 removes groups after aggregation.

Including salary in the GROUP BY would change the grouping level and produce totals by salary value, not just by department and job. Using an aggregate condition in WHERE is invalid because WHERE is evaluated before grouping.

• Missing grouped column fails because job_id is selected but not included in GROUP BY.
• Over-grouping fails because adding salary to GROUP BY changes the result to smaller groups.
• Wrong clause for aggregate fails because SUM(salary) cannot be filtered in a second WHERE clause; it belongs in HAVING.

Question 9

Topic: Report Aggregated Data by Using Group Functions

Which statement correctly distinguishes WHERE from HAVING in an Oracle aggregate query?

Options:

• A. WHERE filters rows before grouping, and HAVING filters groups after grouping.

• B. HAVING must appear before GROUP BY, and WHERE must appear after GROUP BY.

• C. WHERE filters aggregate results, and HAVING filters individual rows.

• D. WHERE and HAVING are interchangeable whenever GROUP BY is used.

Best answer: A

Explanation: In Oracle SQL, WHERE is evaluated before rows are grouped, so it removes individual rows from consideration. HAVING is evaluated after GROUP BY, so it restricts the grouped results, often using aggregate functions such as SUM or COUNT.

The key distinction is what each clause filters and when it does so. WHERE filters source rows before Oracle forms groups, so it is used for row-level conditions such as department, date, or status. HAVING filters the results of grouping, so it is used when the condition depends on an aggregate or on the grouped set itself.

For example, WHERE salary > 5000 removes rows before grouping, while HAVING COUNT(*) > 3 keeps only groups whose aggregated result meets the condition. In practice, row-level conditions belong in WHERE, and grouped-result conditions belong in HAVING.

A common confusion is thinking both clauses do the same job once GROUP BY appears, but they operate at different stages of query processing.

• Aggregate mix-up The option reversing the clauses is wrong because aggregates are evaluated after grouping, not during row filtering.
• Interchangeable myth The option claiming both clauses are interchangeable ignores that they act on different query-processing stages.
• Clause order confusion The option about HAVING appearing before GROUP BY is syntactically incorrect in Oracle SQL.

Question 10

Topic: Report Aggregated Data by Using Group Functions

A developer runs this query to produce one row per department showing average salary:

SELECT department_id, last_name, AVG(salary)
FROM employees
GROUP BY department_id;

Oracle returns ORA-00979: not a GROUP BY expression.

What is the best fix?

Options:

• A. Remove last_name from the SELECT list.

• B. Replace GROUP BY department_id with ORDER BY department_id.

• C. Add last_name to the GROUP BY clause.

• D. Move AVG(salary) from SELECT to HAVING.

Best answer: A

Explanation: In a grouped query, every selected column must either appear in the GROUP BY clause or be inside an aggregate function. Because the report must return one row per department, last_name should not be selected here.

Oracle raises ORA-00979 when a query with GROUP BY includes a selected column that is neither grouped nor aggregated. In this statement, department_id is the grouping column and AVG(salary) is valid because it is an aggregate. last_name is the problem because it is a detail-level column from individual rows.

Since the requirement is one row per department, the select list should contain only department-level columns and aggregate expressions. Removing last_name fixes the error without changing the intended result set.

Adding last_name to GROUP BY would make the query valid syntactically, but it would change the report grain from department-level to department-and-employee-level.

• Adding last_name to GROUP BY fixes the syntax error but changes the row grouping and likely increases the row count.
• Moving AVG(salary) to HAVING is incorrect because HAVING filters groups; it does not repair an invalid select list.
• Replacing grouping with ordering only sorts rows and removes the department average calculation entirely.

Continue with full practice

Use the Oracle 1Z0-071 Practice Test page for the full IT Mastery route, mixed-topic practice, timed mock exams, explanations, and web/mobile app access.

Try Oracle 1Z0-071 on Web View Oracle 1Z0-071 Practice Test

Related focused pages

• Free Oracle 1Z0-071 Full-Length Practice Exam
• Relational Database Concepts
• SQL SELECT
• Restrict and Sort Data
• Single-Row Functions
• Conversion and CASE
• Display Data from Multiple Tables
• Use Subqueries to Solve Queries
• Use Set Operators
• Manage Tables by Using DML Statements
• Indexes and Sequences
• DDL Tables and Relationships
• Manage Views
• Control User Access
• Data Dictionary Views
• Manage Data in Different Time Zones

Free review resource

Read the Oracle 1Z0-071 Cheat Sheet on Tech Exam Lexicon, then return to IT Mastery for timed practice.