Question 1

View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables.

There is only one customer with the cust_last_name column having value Roberts. Which INSERT

statement should be used to add a row into the ORDERS table for the customer whose

CUST_LAST_NAME is Roberts and CREDIT_LIMIT is 600?

Options :

A : INSERT INTO ordersVALUES(1, ’10-mar-2007’, ‘direct’,(SELECT customer_idFROM customersWHERE cust_last_name=’Roberts’ ANDcredit_limit=600), 1000);

B : INSERT INTO orders (order_id, order_data, order_mode,(SELECT customer_idFROM customersWHERE cust_last_name=’Roberts’ ANDcredit_limit=600), order_total)VALUES(1, ’10-mar2007’, ‘direct’, &&customer_id, 1000);

C : INSERT INTO(SELECT o.order_id, o.order_date, o.order_mode, c.customer_id, o.order_totalFROM orders o, customers cWHERE o.customer_id = c.customer_idAND c.cust_last_name=’Roberts’ AND c.credit_limit=600 )VALUES (1, ’10-mar-2007’, ‘direct’, (SELECT customer_idFROM customersWHERE cust_last_name=’Roberts’ ANDcredit_limit=600), 1000);

D : INSERT INTO orders (order_id, order_data, order_mode,(SELECT customer_idFROM customersWHERE cust_last_name=’Roberts’ ANDcredit_limit=600), order_total)VALUES (1, ’10-mar2007’, ‘direct’, &customer_id, 1000).

Answer: A

Question 2

.No user-defined locks are used in your database.

Which three are true about Transaction Control Language (TCL)?

Options :

A : COMMIT erases all the transaction-s savepoints and releases its locks.

B : COMMIT ends the transaction and makes all its changes permanent.

C : ROLLBACK without the TO SAVEPOINT clause undoes all the transaction-s changes but does not release its locks.

D :

ROLLBACK to SAVEPOTNT undoes the transaction-s changes made since the named savepoint and then ends the transaction.

E : ROLLBACK without the TO SAVEPOINT clause undoes alt the transaction-s changes, releases its locks, and erases all its savepoints.

F : ROLLBACK without the TO SAVEPOINT clause undoes all the transaction-s changes but does not erase its savepoints.

Answer: A,B,E

Question 3

The ORDERS table has a column ORDER_DATE of date type DATE The default display format for a date is DD-MON-RR Which two WHERE conditions demonstrate the correct usage of conversion functions?

Options :

A : WHERE ordet_date> TO_CHAR(ADD_MONTHS(SYSDATE, 6),'MON DD YYYY')

B : WHERE TO_CHAR(order_date,'MON DD YYYY') ='JAN 20 2019';

C : WHERE order_date> TO_DATE('JUL 10 2018','MON DD YYYY');

D :

WHERE order_date IN (TO_DATE ('Oct 21 2018','MON DD YYYY'), TO_CHAR('Nov 21 2018','MON DD YYYY'));

E : WHERE order_date> TO_DATE(ADD_MONTHS(SYSDATE,6),'MON DD YYYY');

Answer: B,C

Question 4

Use HR has CREATE SESSION, CREATE ANY TABLE and UNLIMITED TABLESPACE privileges.

User SCOTT has CREAT SESSION, CREATE TABLE and UNLIMITED TABLESPACE Privileges

HR successfully executes this statement: