1Z0-051-1题

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.
Answer: B
解析：

SQL> create table SALES(prod_id number not null,cust_id number not null,time_id date not null,channel_id number not null,promo_id number not null,quantity_sold number(10,2) not null);

Table created.

SQL> CREATE TABLE new_sales(prod_id, cust_id, order_date DEFAULT SYSDATE) AS SELECT prod_id, cust_id, time_id FROM sales;

Table created.

SQL> DESC new_sales;
 Name        Null?    Type
 ----------------------------------------- -------- ----------------------------
 PROD_ID       NOT NULL NUMBER
 CUST_ID       NOT NULL NUMBER
 ORDER_DATE       NOT NULL DATE
可以看出NOT NULL约束可以传递给子表；