OCP-1Z0-051 第21题 LONG类型

一、原题

Examine the description of the EMP_DETAILS table given below:
name           NULL          TYPE
EMP_ID         NOT NULL     NUMBER
EMP_NAME       NOT NULL     VARCHAR2 (40)
EMP_IMAGE                    LONG
Which two statements are true regarding SQL statements that can be executed on the EMP_DETAIL table? (Choose two.)
A. An EMP_IMAGE column can be included in the GROUP BY clause.
B. An EMP_IMAGE column cannot be included in the ORDER BY clause.
C. You cannot add a new column to the table with LONG as the data type.
D. You can alter the table to include the NOT NULL constraint on the EMP_IMAGE column.

答案：BC

二、题目翻译

看下面EMP_DETAILS表的结构
关于能在EMP_DETAILS 表上执行的SQL语句，哪两句话是正确的？（选择两个）
A. EMP_IMAGE列可以包含在GROUP BY子句中
B. EMP_IMAGE列不可以包含在ORDER BY子句中
C. 不能添加一个新的LONG数据类型列
D. 可以把EMP_IMAGE列转换成NOT NULL约束

三、题目解析

A选项不正确，因为LONG列不能包含在GROUP BY子句中，当然也不能包含在ORDER BY或者DISTINCT或者CONNECT BY子句中。
D选项不正确，因为如果列中已经有值可能会包含空值，这样就不能设置为NOT NULL。因为LONG是oracle将要废弃的类型，所

以在10g和11g中都没有查到相关资料，在9i中找到了相关说明：
http://docs.oracle.com/cd/B10501_01/appdev.920/a96590/adfnstyp.htm#434992
LONG columns cannot appear in certain parts of SQL statements:
    1) GROUP BY clauses, ORDER BY clauses, or CONNECT BY clauses or with the DISTINCT operator in SELECT statements
    2) The UNIQUE operator of a SELECT statement
    3) The column list of a CREATE CLUSTER statement
    4) The CLUSTER clause of a CREATE MATERIALIZED VIEW statement
    5) SQL built-in functions, expressions, or conditions
    6) SELECT lists of queries containing GROUP BY clauses
    7) SELECT lists of subqueries or queries combined by the UNION, INTERSECT, or MINUS set operators
    8) SELECT lists of CREATE TABLE ... AS SELECT statements
    9) ALTER TABLE ... MOVE statements
    10) SELECT lists in subqueries in INSERT statements