Online Access Free 1z0-071 Practice Test

Examine the structure of the members table:

Which query can be used to display the last names and city names only for members from the states MO and MI?

• A.SELECT last_name, city FROM members WHERE state LIKE 'M%';
• B.SELECT last_name, city FROM members WHERE state IN ('MO', 'MI');
• C.SELECT last_name, city FROM members WHERE state ='MO' AND state ='MI';
• D.SELECT DISTINCT last_name, city FROM members WHERE state ='MO' OR state
  ='MI';

Examine the description of the SALEStable:

The SALEStable has 55,000 rows.
Examine this statement:

Which two statements are true? (Choose two.)

• A.SALES1 is created with no rows.
• B.SALES1 has PRIMARY KEY and UNIQUE constraints on any selected columns which had those constraints in the SALES table.
• C.SALES1 is created with 55,000 rows.
• D.SALES1 is created with 1 row.
• E.SALES1 has NOT NULL constraints on any selected columns which had those constraints in the SALES table.

The first DROP operation is performed on PRODUCTS table using the following command:
DROP TABLE products PURGE;
Then you performed the FLASHBACK operation by using the following command:
FLASHBACK TABLE products TO BEFORE DROP;
Which statement describes the outcome of the FLASHBACK command?

• A.It is not possible to recover the table structure, data, or the related indexes.
• B.It recovers the table structure and data but not the related indexes.
• C.It recovers only the table structure.
• D.It recovers the table structure, data, and the indexes.

View the Exhibit and examine PRODUCTS and ORDER_ITEMS tables.

You executed the following query to display PRODUCT_NAME and the number of times the product has been ordered:
SELECT p.product_name, i.item_cnt
FROM (SELECT product_id, COUNT (*) item_cnt
FROM order_items
GROUP BY product_id) i RIGHT OUTER JOIN products p
ON i.product_id = p.product_id;
What would happen when the above statement is executed?

• A.The statement would not execute because the GROUP BY clause cannot be used in the inline view.
• B.The statement would execute successfully to produce the required output.
• C.The statement would not execute because the ITEM_CNT alias cannot be displayed in the outer query.
• D.The statement would not execute because inline views and outer joins cannot be used together.

Examine the structure of the SHIPMENTS table:

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:

Which statement is true regarding the above commands?

• A.Only the first query executes successfully but gives a wrong result.
• B.Only the second query executes successfully but gives a wrong result.
• C.Both execute successfully and give correct results.
• D.Only the second query executes successfully and gives the correct result.
• E.Only the first query executes successfully and gives the correct result.