Oracle 1Z0-071 Quiz 22 Topic 2 Questions 106-110

Question: 1

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

q1_1Z0-071

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);

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

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

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

DOnly 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.

Show Answer

Question: 2

Which three tasks can be performed by DDL statements?

Aproviding an alternative name for a table

Bmodifying a table to prevent data that violate certain conditions from being entered in a column

Cpreventing any data modification to a table

Dpreventing data retrieval from a table outside of office hours

Ecreating multiple savepoints to enable partial rollback of a transaction

Show Answer

Question: 3

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

q3_1Z0-071

You are asked to retrieve the ORDER_ID, product_ID, and total price (UNIT_PRICE multiplied by QUANTITY), where the total price is greater than 50,000.

You executed the following SQL statement:

SELECT prder_id, product_id, unit_price*quantity ''Total Price''

FROM order_items

WHERE unit_price*quantity > 50000

NATURAL JOIN orders;

Which statement is true regarding the execution of the statement?

AThe statement would execute and provide the desired result.

BThe statement would not execute because the ON keyword is missing in the NATURAL JOIN clause.

CThe statement would not execute because the WHERE clause is before the NATURAL JOIN clause.

DThe statement would not execute because the USING keyword is missing in the NATURAL JOIN clause.

Show Answer

Question: 4

Which two statements are true regarding operators used with subqueries (Choose two.)

AThe NOT IN operator is equivalent to IS NULL.

BThe <ANY operator means less than the maximum.

C=ANY and =ALL operators have the same functionality.

DThe IN operator cannot be used in single-row subqueries.

EThe NOT operator can be used with IN, ANY and ALL operators.

Show Answer

Question: 5

Examine the structure of the SHIPMENTS table:

q5_1Z0-071

You want to generate a report that displays the PO_ID and the penalty amount to be paid if the SHIPMENT_DATE is later than one month from the PO_DATE. The penalty is $20 per day.

Evaluate the following two queries:

q5_1Z0-071

Which statement is true regarding the above commands?

ABoth execute successfully and give correct results.

BOnly the first query executes successfully but gives a wrong result.

COnly the first query executes successfully and gives the correct result.

DOnly the second query executes successfully but gives a wrong result.

EOnly the second query executes successfully and gives the correct result.

Show Answer

Oracle 1Z0-071 Quiz:22 Topic:2 Questions:106-110