nerdexam
Oracle

1Z0-060 · Question #155

Which two statements are true regarding the count function?

The correct answer is B. Count (*) returns the number of rows including duplicate rows and rows containing null value in any of D. Count (distinct inv_amt) returns the number of rows excluding rows containing duplicates and NULL values. COUNT(*) counts every row including those with NULLs and duplicates, while COUNT(DISTINCT col) excludes both duplicate values and NULL values from the count.

Upgrading to Oracle Database 12c

Question

Which two statements are true regarding the count function?

Options

  • AThe count function can be used only for CHAR, VARCHAR2, and NUMBER data types.
  • BCount (*) returns the number of rows including duplicate rows and rows containing null value in any of
  • CCount (cust_id) returns the number of rows including rows with duplicate customer IDs and NULL value
  • DCount (distinct inv_amt) returns the number of rows excluding rows containing duplicates and NULL values
  • EA select statement using the COUNT function with a DISTINCT keyword cannot have a where clause.

How the community answered

(28 responses)
  • A
    4% (1)
  • B
    89% (25)
  • C
    7% (2)

Why each option

COUNT(*) counts every row including those with NULLs and duplicates, while COUNT(DISTINCT col) excludes both duplicate values and NULL values from the count.

AThe count function can be used only for CHAR, VARCHAR2, and NUMBER data types.

COUNT is a generic aggregate function that can be applied to any data type, including DATE, BLOB references, and user-defined types, not only CHAR, VARCHAR2, and NUMBER.

BCount (*) returns the number of rows including duplicate rows and rows containing null value in any ofCorrect

COUNT(*) evaluates each row as a unit rather than examining column values, so it includes all rows regardless of duplicate data or NULL values in any column.

CCount (cust_id) returns the number of rows including rows with duplicate customer IDs and NULL value

COUNT(cust_id) ignores rows where cust_id IS NULL and counts only non-NULL values; the statement is false because NULL rows are excluded, not included.

DCount (distinct inv_amt) returns the number of rows excluding rows containing duplicates and NULL valuesCorrect

COUNT(DISTINCT inv_amt) applies the DISTINCT keyword to eliminate duplicate inv_amt values first, and then - like all column-based COUNT expressions - automatically excludes any remaining NULL values before returning the final count.

EA select statement using the COUNT function with a DISTINCT keyword cannot have a where clause.

There is no Oracle restriction preventing a SELECT statement that uses COUNT with the DISTINCT keyword from also including a WHERE clause; both can coexist freely.

Concept tested: COUNT aggregate function behavior with NULL and DISTINCT

Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/COUNT.html

Topics

#COUNT function#aggregate functions#NULL handling#DISTINCT

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full 1Z0-060 Practice