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

It counts all rows including NULLs

Answer

It returns NULL

Answer

It raises an ORA error

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

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

Answer

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

Answer

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

Question 3

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

Accepted Answer

The alphabetically first value

Answer

An error, because MIN() only works on numbers

Answer

The shortest string

Answer

The last inserted 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

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

Answer

SELECT department_id, SUM(salary) FROM employees HAVING department_id = 10

Answer

SELECT SUM(salary), department_id FROM employees GROUP BY SUM(salary)

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

SELECT COUNT(job_id) FROM employees

Answer

SELECT COUNT(*) FROM employees

Answer

SELECT DISTINCT COUNT(job_id) FROM employees

Question 6

Which clause determines how rows are divided into groups before aggregation?

Accepted Answer

GROUP BY

Answer

ORDER BY

Answer

PARTITION

Answer

CLUSTER BY