Free 1Z0-071 Exam Dumps

Question 6

View the Exhibit and examine the structure of the SALES and PRODUCTS tables. (Choose two.)
1Z0-071 dumps exhibit
In the SALES table, PROD_ID is the foreign key referencing PROD_ID in the PRODUCTS table. You must list each product ID and the number of times it has been sold.
Examine this query which is missing a JOIN operator: SQL > SELECT p.prod_id, count(s.prod_id)
FROM products p sales s ON p.prod_id = s.prod_id
GROUP BY p.prod_id;
Which two JOIN operations can be used to obtain the required output?

A. FULL OUTER JOIN
B. JOIN
C. LEFT OUETR JOIN
D. RIGHT OUTER JOIN

Correct Answer:AC

Question 7

Which statement is true about transactions?

A. A set of Data Manipulation Language (DML) statements executed in a sequence ending with a SAVEPOINT forms a single transaction.
B. Each Data Definition Language (DDL) statement executed forms a single transaction.
C. A set of DDL statements executed in a sequence ending with a COMMIT forms a single transaction.
D. A combination of DDL and DML statements executed in a sequence ending with a COMMIT forms a single transaction.

Correct Answer:B
References:
https://docs.oracle.com/database/121/CNCPT/transact.htm#CNCPT038

Question 8

View the exhibits and examine the structures of the COSTS and PROMOTIONS tables.
1Z0-071 dumps exhibit

Evaluate the following SQL statement: SQL> SELECT prod_id FROM costs
WHERE promo_id IN (SELECT promo_id FROM promotions WHERE promo_cost < ALL>(SELECT MAX(promo_cost) FROM promotions GROUP BY (promo_end_date- promo_begin_date)));
What would be the outcome of the above SQL statement?

A. It displays prod IDs in the promo with the lowest cost.
B. It displays prod IDs in the promos with the lowest cost in the same time interval.
C. It displays prod IDs in the promos with the highest cost in the same time interval.
D. It displays prod IDs in the promos which cost less than the highest cost in the same time interval.

Correct Answer:D

Question 9

View the Exhibit and examine the structure of the PRODUCT_INFORMATION and INVENTORIES tables.
1Z0-071 dumps exhibit
You have a requirement from the supplies department to give a list containing PRODUCT_ID, SUPPLIER_ID, and QUANTITY_ON_HAND for all the products wherein QUANTITY_ON_HAND is less than five.
Which two SQL statements can accomplish the task? (Choose two.)

A. SELECT i.product_id, i.quantity_on_hand, pi.supplier_idFROM product_information pi JOIN inventories iON (pi.product_id=i.product_id)WHERE quantity_on_hand < 5;
B. SELECT product_id, quantity_on_hand, supplier_idFROM product_informationNATURAL JOIN inventories AND quantity_on_hand < 5>SELECT i.product_id, i.quantity_on_hand, pi.supplier_idFROM product_information pi JOIN inventories iON (pi.product_id=i.product_id) AND quantity_on_hand < 5>SELECT i.product_id, i.quantity_on_hand, pi.supplier_idFROM product_information pi JOINinventories iON (pi.product_id=i.product_id)USING (product_id) AND quantity_on_hand < 5;

Correct Answer:AC

Question 10

Evaluate this ALTER TABLE statement: (Choose the best answer.) ALTER TABLE orders
SET UNUSED (order_date); Which statement is true?

A. After executing the ALTER TABLE command, a new column called ORDER_DATE can be added to the ORDERS table.
B. The ORDER_DATE column must be empty for the ALTER TABLE command to execute successfully.
C. ROLLBACK can be used to restore the ORDER_DATE column.
D. The DESCRIBE command would still display the ORDER_DATE column.

Correct Answer:A