81.
A constraint is created with the DEFERRABLE INITIALLY IMMEDIATE clause. What does this
mean?
- [A]Constraint checking is done only at commit time.
- [B]Constraint checking is done after each SQL statement is executed, but you can change this behavior by specifying SET CONSTRAINTS ALL DEFERRED.
- [C]Existing rows in the table are immediately checked for constraint violation.
- [D]The constraint is immediately checked in a DML operation, but subsequent constraint verification is done at commit time.
- Answer & Explanation
- Report
Answer : [B]
Explanation :
Explanation :
| DEFERRABLE specifies that the constraint can be deferred using the SET CONSTRAINTS command. INITIALLY IMMEDIATE specifies that the constraint's default behavior is to validate the constraint for each SQL statement executed. |
82.
Consider the datatypes DATE, TIMESTAMP (TS), TIMESTAMP WITH LOCAL TIME
ZONE (TSLTZ), INTERVAL YEAR TO MONTH (IY2M), and INTERVAL DAY TO
SECOND (ID2S). Which operations are not allowed by Oracle Database 11g? (Choose all
that apply.)
- [A]DATE+DATE
- [B]TSLTZ–DATE
- [C]TSLTZ+IY2M
- [D]TS*5
- [E]ID2S/2
- [F]IY2M+IY2M
- [G]ID2S+IY2M
- [H]DATE–IY2M
- Answer & Explanation
- Report
Answer : [A, D, G]
Explanation :
Explanation :
| You cannot add two DATE datatypes, but you can subtract to find the difference in days. Multiplication and division operators are permitted only on INTERVAL datatypes. When adding or subtracting INTERVAL datatypes, both INTERVAL datatypes should be of the same category. |
83.
Which of the following check constraints will raise an error? (Choose all that apply.)
- [A]CONSTRAINT ck_gender CHECK (gender IN ('M', 'F'))
- [B]CONSTRAINT ck_old_order CHECK (order_date > (SYSDATE - 30))
- [C]CONSTRAINT ck_vendor CHECK (vendor_id IN (SELECT vendor_id FROM vendors))
- [D]CONSTRAINT ck_profit CHECK (gross_amt > net_amt)
- Answer & Explanation
- Report
Answer : [B,C]
Explanation :
Explanation :
| Check constraints cannot reference the SYSDATE function or other tables. |
84.
Which of the following statements will create a primary key for the CITY table with the columns
STATE_CD and CITY_CD?
- [A]CREATE PRIMARY KEY ON CITY (STATE_CD, CITY_CD);
- [B]CREATE CONSTRAINT PK_CITY PRIMARY KEY ON CITY (STATE_CD, CITY_CD);
- [C]ALTER TABLE CITY ADD CONSTRAINT PK_CITY PRIMARY KEY (STATE_CD, CITY_CD);
- [D]ALTER TABLE CITY ADD PRIMARY KEY (STATE_CD, CITY_CD);
- [E]ALTER TABLE CITY ADD PRIMARY KEY CONSTRAINT PK_CITY ON (STATE_CD, CITY_CD);
- Answer & Explanation
- Report
Answer : [C, D]
Explanation :
Explanation :
| The ALTER TABLE statement is used to create and remove constraints. CREATE PRIMARY KEY and CREATE CONSTRAINT are invalid statements. A constraint is always added to an existing table using the ALTER TABLE statement. |
85.
Which actions are allowed on a table that is marked as read-only? (Choose all that apply.)
- [A]Truncating a table
- [B]Inserting new data
- [C]Dropping a constraint
- [D]Dropping an index
- [E]Dropping a table
- Answer & Explanation
- Report
Answer : [C, D, E]
Explanation :
Explanation :
| All actions that do not modify the data in the table are permitted on a read-only table. The actions of creating/dropping a constraint, creating/dropping an index, and dropping a table are allowed. Though truncating is a DDL action, it is not permitted since the data in the table is affected. |
|
|
||||||||||||||||||||||||||||
