Question 1

Which two statements best describe the benefits of using the WITH clause? (Choose two.)

Options :

A : It can improve the performance of a large query by storing the result of a query block having the WITH clause in the session-s temporary tablespace.

B : It enables sessions to reuse the same query block in a SELECT statement, if it occurs more than once in a complex query.

C : It enables sessions to store a query block permanently in memory and use it to create complex queries.

D : It enables sessions to store the results of a query permanently.

Answer: A,B

Question 2

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 3

View the Exhibit and examine the data in EMP and DEPT tables.

In the DEPT table, DEPTNO is the PRIMARY KEY.

In the EMP table, EMPNO is the PRIMARY KEY and DEPTNO is the FOREIGN KEY referencing the

DEPTNO column in the DEPT table.

What would be the outcome of the following statements executed in the given sequence?

DROP TABLE emp;

FLASHBACK TABLE emp TO BEFORE DROP;

INSERT INTO emp VALUES (2, ‘SCOTT’, 10);

INSERT INTO emp VALUES (3, ‘KING’, 55);

Options :

A : Both the INSERT statements would fail because the constraints are automatically retrieved when the table is flashed back.

B : Both the INSERT statements would succeed because none of the constraints on the table are automatically retrieved when the table is flashed back.

C : Only the first INSERT statement would succeed because all constraints except the primary key constraint are automatically retrieved after a table is flashed back.

D : Only the SECOND INSERT statement would succeed because all the constraints except referential integrity constraints that reference other tables are retrieved automatically after the table is flashed back.

Answer: D

Question 4

Examine this query and its output:

Examine this query with an incomplete was WHERE clause:

Which two are true about operators that can be used In the WHERE clause?

Options :

A : Using not in or <> any will give the same result.

B : Using in will display all the product names

C : Using not in or <> all will give the same result

D : Using <> any will display all the product names except the product named Fork

E : Using <> any will display all the product names.

Answer: C,D

Question 5

View the exhibit and examine the descriptions of the DEPT and LOCATIONS tables.

You want to update the CITY column of the DEPT table for all the rows with the corresponding value

in the CITY column of the LOCATIONS table for each department.

Which SQL statement would you execute to accomplish the task?

Options :

A : UPDATE dept dSET city = ALL (SELECT cityFROM locations lWHERE d.location_id = l.location_id);

B : UPDATE dept dSET city = (SELECT cityFROM locations l)WHERE d.location_id = l.location_id;

C : UPDATE dept dSET city = ANY (SELECT cityFROM locations l)

D : UPDATE dept dSET city = (SELECT cityFROM locations lWHERE d.location_id = l.location_id);

Answer: D

Free 1Z0-071 Mock Exam – Practice Online Confidently

Buying Options: