Aggregating Data with Group Functions Flashcards
6 cards from real 1Z0-071 practice questions. Tap to flip, then mark Knew It or Still Learning — missed cards come back until you master them.
Read the first 6 Aggregating Data with Group Functions flashcards as text
What is the result of using COUNT(column_name) when some rows have NULL in that column?
Answer: It counts only non-NULL rows
COUNT(column_name) excludes NULL values, counting only rows where the column has a non-NULL value.
Which query correctly uses HAVING to show departments with more than 5 employees?
Answer: SELECT dept_id, COUNT(*) FROM emp GROUP BY dept_id HAVING COUNT(*) > 5
HAVING must follow GROUP BY and can reference aggregate functions like COUNT(*) to filter groups.
What does the MIN() function return when applied to a VARCHAR2 column?
Answer: The alphabetically first value
MIN() on a VARCHAR2 column returns the alphabetically (or collation-order) lowest string value.
Which of the following is a valid use of group functions in Oracle?
Answer: SELECT SUM(salary) FROM employees HAVING SUM(salary) > 10000
A group function like SUM() can be used in HAVING but not in WHERE without a subquery.
How do you find the number of distinct job titles in the EMPLOYEES table?
Answer: SELECT COUNT(DISTINCT job_id) FROM employees
COUNT(DISTINCT column) counts unique non-NULL values of that column.
Which clause determines how rows are divided into groups before aggregation?
Answer: GROUP BY
GROUP BY divides the rows of a result set into groups for aggregation by group functions.