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?

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

Correct Answer: CDE

You execute the following commands: SQL > DEFINE hiredate = '01-APR-2011'
SQL >SELECT employee_id, first_name, salary FROM employees
WHERE hire_date > '&hiredate' AND manager_id >&mgr_id;
For which substitution variables are you prompted for the input?

1. A. none, because no input required
2. B. both the substitution variables ''hiredate' and 'mgr_id'.
3. C. only hiredate'
4. D. only 'mgr_id'

Correct Answer: D

Which two partitioned table maintenance operations support asynchronous Global Index Maintenance in Oracle database 12c?

1. A. ALTER TABLE SPLIT PARTITION
2. B. ALTER TABLE MERGE PARTITION
3. C. ALTER TABLE TRUNCATE PARTITION
4. D. ALTER TABLE ADD PARTITION
5. E. ALTER TABLE DROP PARTITION
6. F. ALTER TABLE MOVE PARTITION

Correct Answer: CE

Examine the structure of the BOOKS_TRANSACTIONS table:

You want to display the member IDs, due date, and late fee as $2 for all transactions. Which SQL statement must you execute?

1. A. SELECT member_id AS "MEMBER ID", due_date AS "DUE DATE", $2 AS "LATE FEE" FROM BOOKS_TRANSACTIONS
2. B. SELECT member_id AS "MEMBER ID", due_date AS "DUE DATE", '$2' AS "LATE FEE" FROM BOOKS_TRANSACTIONS
3. C. SELECT member_id 'MEMBER ID', due_date 'DUE DATE', '$2 AS LATE FEE' FROM BOOKS_TRANSACTIONS;
4. D. SELECT member_id AS MEMBER_ID, due_date AS DUE_DATE, $2 AS LATE_FEE FROM BOOKS_TRANSACTIONS

Correct Answer: B

Examine the command:
SQL> ALTER TABLE books_transactions
ADD CONSTRAINT fk_book_id FOREIGN KEY (book_id) REFERENCES books (book_id) ON DELETE CASCADE; What does ON DELETE CASCADE imply?

1. A. When the BOOKS table is dropped, the BOOK_TRANSACTIONS table is dropped.
2. B. When the BOOKS table is dropped, all the rows in the BOOK_TRANSACTIONS table are deleted butthe table structure is retained.
3. C. When a row in the BOOKS table is deleted, the rows in the BOOK_TRANSACTIONS table whose BOOK_ID matches that of the deleted row in the BOOKS table are also deleted.
4. D. When a value in the BOOKS.BOOK_ID column is deleted, the corresponding value is updated in the BOOKS_TRANSACTIONS.BOOK_ID column.

Correct Answer: C