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