1Z0-071 Aggregating Data with Group Functions 2

Question 1

What is the result of using COUNT(column_name) when some rows have NULL in that column?

Accepted Answer

It counts only non-NULL rows

Answer

COUNT(column_name) excludes NULL values, counting only rows where the column has a non-NULL value.

Question 2

Which query correctly uses HAVING to show departments with more than 5 employees?

Accepted Answer

SELECT dept_id, COUNT(*) FROM emp GROUP BY dept_id HAVING COUNT(*) > 5

Answer

HAVING must follow GROUP BY and can reference aggregate functions like COUNT(*) to filter groups.

Question 3

What does the MIN() function return when applied to a VARCHAR2 column?

Accepted Answer

The alphabetically first value

Answer

MIN() on a VARCHAR2 column returns the alphabetically (or collation-order) lowest string value.

Question 4

Which of the following is a valid use of group functions in Oracle?

Accepted Answer

SELECT SUM(salary) FROM employees HAVING SUM(salary) > 10000

Answer

A group function like SUM() can be used in HAVING but not in WHERE without a subquery.

Question 5

How do you find the number of distinct job titles in the EMPLOYEES table?

Accepted Answer

SELECT COUNT(DISTINCT job_id) FROM employees

Answer

COUNT(DISTINCT column) counts unique non-NULL values of that column.

1Z0-071 - Oracle Database SQL Certified Associate Practice Test