1Z0-051 QUESTION 1 利用CREATE TABLE AS创建表

QUESTION 1
View the Exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTS, and TIMES
tables.
The PROD_ID column is the foreign key in the SALES table, which references the PRODUCTS table.
Similarly, the CUST_ID and TIME_ID columns are also foreign keys in the SALES table referencing the
CUSTOMERS and TIMES tables, respectively.
Evaluate the following CREATE TABLE command:
CREATE TABLE new_sales(prod_id, cust_id, order_date DEFAULT SYSDATE)
AS
SELECT prod_id, cust_id, time_id
FROM sales;

Which statement is true regarding the above command?

A. The NEW_SALES table would not get created because the DEFAULT value cannot be specified in the
column definition.
B. The NEW_SALES table would get created and all the NOT NULL constraints defined on the specified
columns would be passed to the new table.
C. The NEW_SALES table would not get created because the column names in the CREATE TABLE
command and the SELECT clause do not match.
D. The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the
specified columns would be passed to the new table.

答案：Ｂ

解析：

首先，新表是可以创建成功的，排除了AC，而BD中，在使用CREATE　TABLE　AS创建表时会把NOT　NULL约束带到新表，而主键，唯一，外键，check约束，分区，索引以及列的默认值不会带到新表。

可参考oracle联机文档CREATETABLE 的 AS subquery ：

http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_7002.htm#SQLRF54626