Which three statements indicate the end of a transaction? (Choose three.)

• A.after a COMMITis issued
• B.after a SAVEPOINTis issued
• C.after a ROLLBACKis issued
• D.after a SELECTstatement is issued
• E.after a CREATEstatement is issued

Comment: *

Name: *

Email: *

Verification: *

Examine the structure of the MEMBERS table: (Choose the best answer.)

Examine the SQL statement:
SQL > SELECT city, last_name LNAME FROM MEMBERS ORDER BY 1, LNAME DESC; What would be the result execution?

• A.It displays all cities in descending order, within which the last names are further sorted in descending order.
• B.It displays all cities in ascending order, within which the last names are further sorted in descending order.
• C.It fails because a column alias cannot be used in the ORDER BY clause.
• D.It fails because a column number and a column alias cannot be used together in the ORDER BY clause.

Comment: *

Name: *

Email: *

Verification: *

Examine the create table statements for the stores and sales tables.
SQL> CREATE TABLE stores(store_id NUMBER(4) CONSTRAINT store_id_pk PRIMARY KEY, store_name VARCHAR2(12), store_address VARCHAR2(20), start_date DATE); SQL> CREATE TABLE sales(sales_id NUMBER(4) CONSTRAINT sales_id_pk PRIMARY KEY, item_id NUMBER(4), quantity NUMBER(10), sales_date DATE, store_id NUMBER(4), CONSTRAINT store_id_fk FOREIGN KEY(store_id) REFERENCES stores(store_id)); You executed the following statement:
SQL> DELETE from stores
WHERE store_id=900;
The statement fails due to the integrity constraint error:
ORA-02292: integrity constraint (HR.STORE_ID_FK) violated
Which three options ensure that the statement will execute successfully?

• A.Disable the primary key in the STORES table.
• B.Create the foreign key in the SALES table on SALES_ID column with on DELETE CASCADE option.
• C.DELETE the rows with STORE_ID = 900 from the SALES table and then delete rows from STORES table.
• D.Disable the FOREIGN KEY in SALES table and then delete the rows.
• E.Use CASCADE keyword with DELETE statement.

Comment: *

Name: *

Email: *

Verification: *

Examine the description of the BOOKS table:

The table has 100 rows.
Examine this sequence of statements issued in a new session;
INSERT INTO BOOKS VALUES ('ADV112' , 'Adventures of Tom Sawyer', NULL, NULL);
SAVEPOINT a;
DELETE from books;
ROLLBACK TO SAVEPOINT a;
ROLLBACK;
Which two statements are true?

• A.The second ROLLBACK command does nothing.
• B.The second ROLLBACK command undoes the insert.
• C.The second ROLLBACK command replays the delete.
• D.The first ROLLBACK command restores the 101 rows that were deleted, leaving the inserted row still to be committed.
• E.The first ROLLBACK command restores the 101 rows that were deleted and commits the inserted row.

Comment: *

Name: *

Email: *

Verification: *

Which three statements are true about sequences in a single instance Oracle database? (Choose three.)

• A.A sequence can issue duplicate values
• B.A sequence's unallocated cached value are lost if the instance shuts down
• C.Sequences can always have gaps
• D.Two or more tables cannot have keys generated from the same sequence
• E.A sequence can only be dropped by a DBA
• F.A sequence number that was allocated can be rolled back if a transaction fails

Comment: *

Name: *

Email: *

Verification: *