Exam 1z0-061 topic 1 question 23 discussion

Actual exam question from Oracle's 1z0-061

Question #: 23
Topic #: 1

Evaluate the following SQL commands:

The command to create a table fails. Identify the two reasons for the SQL statement failure?

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.

Show Suggested Answer

Suggested Answer: AC 🗳️
CHECK Constraint -
The CHECK constraint defines a condition that each row must satisfy. The condition can use the same constructs as the query conditions, with the following exceptions:
References to the CURRVAL, NEXTVAL, LEVEL, and ROWNUM pseudocolumns
Calls to SYSDATE, UID, USER, and USERENV functions
Queries that refer to other values in other rows
A single column can have multiple CHECK constraints that refer to the column in its definition.
There is no limit to the number of CHECK constraints that you can define on a column.
CHECK constraints can be defined at the column level or table level.

CREATE TABLE employees -
(...
Salary NUMBER(8, 2) CONSTRAINT emp_salary_min
CHECK (salary > 0),

by Sugar at July 24, 2019, 11:16 a.m.

Comments

Submit Cancel

Lif

5 years, 6 months ago

NEXTVAL cannot sequence value as a default value for a column is wrong for oracle 12 C but in oracle 11g it's true.

upvoted 2 times

...

[Removed]

5 years, 8 months ago

May be question is incorrect? Only A answer is correct. --test --create sequence create sequence ord_seq increment by 1 start with 1 maxvalue 9999 nocycle; --create table for reference create table ord_items_2 (ord_no number, constraint ord_id_pk primary key (ord_no)); -- fail create table with SYSDATE in check constraint 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 ord_items_2(ord_id)); -- success 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, constraint it_pk primary key (ord_no,item_no), constraint ord_fk foreign key (ord_no) references ord_items_2(ord_no));

upvoted 2 times

...

Sugar

5 years, 9 months ago

Again, incorrect information. NEXTVAL cannot be used in CHECK CONSTRAINT, but order sequence can be used as default value for column Please go ahead and try this create sequence ord_seq increment by 1 start with 1 maxvalue 9999 nocycle; create table ordtest ( ord_id number default ord_seq.nextval ) insert into ordtest (ord_id) values(default) Its good thing that i tried your free exam test before purchasing. this is not so reliable

upvoted 1 times

...

Exam 1z0-061 All Questions

View all questions & answers for the 1z0-061 exam

Exam 1z0-061 topic 1 question 23 discussion

Comments

Lif

[Removed]

Sugar

SY0-701