1Z0-071 Aggregating Data with Group Functions 1 — Questions and Answers
Question 1: Which GROUP BY function returns the total sum of a numeric column?
- COUNT()
- AVG()
- SUM() (Correct answer)
- MAX()
Correct answer: SUM()
SUM() adds up all non-NULL numeric values in the specified column.
Question 2: What happens to NULL values when using the AVG() function in Oracle SQL?
- They are counted as zero
- They are ignored entirely (Correct answer)
- They cause an error
- They are replaced with 1
Correct answer: They are ignored entirely
Oracle's AVG() function ignores NULL values and calculates the average of only non-NULL rows.
Question 3: Which clause is used to filter the results of a GROUP BY query?
- WHERE
- HAVING (Correct answer)
- FILTER
- LIMIT
Correct answer: HAVING
The HAVING clause filters groups after aggregation, unlike WHERE which filters rows before grouping.
Question 4: What does the COUNT(*) function return?
- Number of non-NULL values in a column
- Sum of all rows
- Total number of rows including NULLs (Correct answer)
- Number of distinct values
Correct answer: Total number of rows including NULLs
COUNT(*) counts every row in the result set regardless of NULL values in any column.
Question 5: Which statement about the GROUP BY clause is TRUE?
- Columns in SELECT not in an aggregate must appear in GROUP BY (Correct answer)
- GROUP BY must always be followed by HAVING
- NULL values cannot appear in a GROUP BY column
- GROUP BY eliminates duplicate rows without aggregation
Correct answer: Columns in SELECT not in an aggregate must appear in GROUP BY
Any non-aggregated column in the SELECT list must also appear in the GROUP BY clause.
Question 6: Which function returns the highest value in a set of values?
- UPPER()
- GREATEST()
- MAX() (Correct answer)
- TOP()
Correct answer: MAX()
MAX() is a group function that returns the maximum value across all rows in the group.
1Z0-071 Aggregating Data with Group Functions 1
Which GROUP BY function returns the total sum of a numeric column?