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
2increment by 10
3start with 120
4maxvalue 9999
5nocycle;
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 tableorders
2(ord_no number primary key);
Table created.
sh@TESTDB> create table ord_items
2(ord_no number not null,
3item_no number,
4constraint ord_fk foreign key(ord_no) references orders(ord_no),
5constraint it_pk primary key(ord_no,item_no));
Table created.
D、外键约束和字段可以和本表组合成主键，D错。
版权声明：本文为博主原创文章，未经博主允许不得转载。
页:
[1]