OCP-1Z0-051-V9.02-31题

31. Evaluate the following SQL commands:

SQL>CREATE SEQUENCE ord_seq    

INCREMENT BY 10    

START WITH 120    

MAXVALUE 9999    

NOCYCLE;

SQL>CREATE TABLE ord_items       

(ord_no NUMBER(4)DEFAULT ord_seq.NEXTVAL NOT NULL,        

item_no NUMBER(3),        

qty NUMBER(3) CHECK (qty BETWEEN 100 AND 200),

  

expiry_date date CHECK (expiry_date > SYSDATE),        

CONSTRAINT it_pk PRIMARY KEY (ord_no,item_no),        

CONSTRAINT ord_fk FOREIGN KEY(ord_no) REFERENCES orders(ord_no));

The command to create a table fails. Identify the reason for the SQL statement failure? (Choose all that

apply.)

A. You cannot use SYSDATE in the condition of a CHECK constraint.

B. You cannot use the BETWEEN clause in the condition of a CHECK constraint.

C. You cannot use the NEXTVAL sequence value as a DEFAULT value for a column.

D. You cannot use ORD_NO and ITEM_NO columns as a composite primary key because ORD_NO is

also the FOREIGN KEY.

Answer: AC

 

答案解析：

scott@TESTDB> create sequence ord_seq

  2  increment by 10

  3  start with 120

  4  maxvalue 9999

  5  nocycle;

 

Sequence created.

 

scott@TESTDB>  CREATE TABLE ord_items

  2  (expiry_date date CHECK (expiry_date > SYSDATE));

(expiry_date date CHECK (expiry_date > SYSDATE))

                                       *

ERROR at line 2:

ORA-02436: date or system variable wrongly specified in CHECK constraint

A 、SYSDATE不能用在CHECK约束中，对

 

scott@TESTDB>  CREATE TABLE ord_items

  2  (qty NUMBER(3) CHECK (qty BETWEEN 100 AND 200));

 

Table created.

B、BETWEEN可以用在约束中，B错

 

 

scott@TESTDB>  CREATE TABLE ord_items

  2  (ord_no NUMBER(4)DEFAULT ord_seq.NEXTVAL NOT NULL);

(ord_no NUMBER(4)DEFAULT ord_seq.NEXTVAL NOT NULL)

                         *

ERROR at line 2:

ORA-00984: column not allowed here

 

C、NEXTVAL不能用作默认值，C对

 

 

sh@TESTDB> create table  orders

  2  (ord_no number primary key);

 

Table created.

 

sh@TESTDB> create table ord_items

  2  (ord_no number not null,

  3  item_no number,

  4  constraint ord_fk foreign key(ord_no) references orders(ord_no),

  5  constraint it_pk primary key(ord_no,item_no));

 

Table created.

 

D、外键约束和字段可以和本表组合成主键，D错。