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.
Explanation:
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),
option A and D is the correct answer for this question not A and C. I tried to create a table using the check constraint with a “sysdate” function and it does not work. Check constraints using “between” and the “sequence_name.nextval” as a default works fine. So since the question is about how sql statement failed is because A:You cannot use SYSDATE in the condition of a check constraint and D.You cannot use ORD_NO and ITEM_NO columns as a composite primary key because
ORD_NO is also the foreign key.
Correct me if im wrong. 🙂
Mac,
what about relational tables? In that kind of tables you would define a primary key consisting of two foreign keys.
The only other one that would have failed before 12c is the default NEXTVAL. But since they implemented it on 12c.
The BETWEEN has never been a problem, so that is not an error.
This question seems to have only one error and that is the fact that you can’t use a SYSDATE inside of a CHECK constraint.
Only A is wrong
Restriction on Default Column Values A DEFAULT expression cannot contain references to PL/SQL functions or to other columns, the pseudocolumns CURRVAL, NEXTVAL, LEVEL, PRIOR, and ROWNUM, or date constants that are not fully specified.
That used to be true for 11g but nof for a database 12C the database tested by 1Z0-061.
It is now possible to specify the CURRVAL and NEXTVAL sequence pseudocolumns as the default values for a column.
Check this out https://oracle-base.com/articles/12c/default-values-for-table-columns-enhancements-12cr1
Greetings
Thank you Fredy
C is correct in 12C
Tested, A and D are the correct answers.
Prior to 12c, we can t use CURRVAL or NEXTVAL after DEFAULT clause.
Now in 12c it s possible.
I am not sure if A and D are the correct answers since the below create worked for me
create table ord_items1(
ord_no number(4),
item_no number(3),
qty number(3),
constraint it_pk primary key (ord_no,item_no),
constraint ord_fk foreign key (ord_no) references orders(ord_no));
A is the correct answer, but D may not be the right one. What are the 2 options then?
Apparently, only A is correct
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 orders(ord_no));
Table ORD_ITEMS creado.