OCP-1Z0-051 补充题库 第34题 COUNT函数

一、原题

Which two statements are true regarding the COUNT function? (Choose two.)

A. COUNT(*) returns the number of rows including duplicate rows and rows containing NULL

value in any of the columns

B. COUNT(cust_id) returns the number of rows including rows with duplicate customer IDs and

NULL value in the CUST_ID column

C. COUNT(DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates

and NULL values in the INV_AMT column

D. A SELECT statement using COUNT function with a DISTINCT keyword cannot have a WHERE

clause

E. The COUNT function can be used only for CHAR, VARCHAR2 and NUMBER data types

答案: A,C

二、题目翻译

关于count函数，下面哪两个说法是正确的?(选择两项)

A.count(*)返回包含重复行和null值行在内的所有行的数量。

B.COUNT(cust_id)返回包含了重复行和CUST_ID列为null值的行的数量。

C.COUNT(DISTINCT inv_amt)返回去除掉重复行和null值行的数量。

D.一个有count函数中有distinct的select语句，不能有where子句。

E.COUNT函数，只能用在CHAR, VARCHAR2 and NUMBER这些数据类型上。

三、题目解析

count是组函数，后面是具体的某一列时，忽略null值行，如果是count(*)则包括null值行，是所有的行。

count函数，很多类型都可以，除了上面说的以外，比如，还有date类型等。

四、测试

SQL> select * from emp;

     EMPNO ENAME                JOB                       MGR HIREDATE            SAL       COMM     DEPTNO

---------- -------------------- ------------------ ---------- ------------ ---------- ---------- ----------

      7369 SMITH                CLERK                    7902 17-DEC-80           800                    20

      7499 ALLEN                SALESMAN                 7698 20-FEB-81          1600        300         30

      7521 WARD                 SALESMAN                 7698 22-FEB-81          1250        500         30

      7566 JONES                MANAGER                  7839 02-APR-81          2975                    20

      7654 MARTIN               SALESMAN                 7698 28-SEP-81          1250       1400         30

      7698 BLAKE                MANAGER                  7839 01-MAY-81          2850                    30

      7782 CLARK                MANAGER                  7839 09-JUN-81          2450                    10

      7788 SCOTT                ANALYST                  7566 19-APR-87          3000                    20

      7839 KING                 PRESIDENT                     17-NOV-81          5000                    10

      7844 TURNER               SALESMAN                 7698 08-SEP-81          1500          0         30

      7876 ADAMS                CLERK                    7788 23-MAY-87          1100                    20

      7900 JAMES                CLERK                    7698 03-DEC-81           950                    30

      7902 FORD                 ANALYST                  7566 03-DEC-81          3000                    20

      7934 MILLER               CLERK                    7782 23-JAN-82          1300                    10

14 rows selected.

SQL> select count(*) from emp;

  COUNT(*)

----------

        14

SQL> select count(comm) from emp;

COUNT(COMM)

-----------

          4

SQL> desc emp

 Name                                                                                Null?    Type

 ----------------------------------------------------------------------------------- -------- ------------

 EMPNO                                                                               NOT NULL NUMBER(4)

 ENAME                                                                                        VARCHAR2(10)

 JOB                                                                                          VARCHAR2(9)

 MGR                                                                                          NUMBER(4)

 HIREDATE                                                                                     DATE

 SAL                                                                                          NUMBER(7,2)

 COMM                                                                                         NUMBER(7,2)

 DEPTNO                                                                                       NUMBER(2)

SQL> select count(hiredate) from emp;

COUNT(HIREDATE)

---------------

             14