Oracle 1Z0-071 Quiz 64 Topic 8 Questions 316-320

Question: 1

View the Exhibit and examine the structure of the PORDUCT_INFORMATION table. (Choose the best answer.)

q1_1Z0-071

PRODUCT_ID column is the primary key.

You create an index using this command:

SQL > CREATE INDEX upper_name_idx

ON product_information(UPPER(product_name));

No other indexes exist on the PRODUCT_INFORMATION table.

Which query would use the UPPER_NAME_IDX index?

ASELECT product_id, UPPER(product_name)FROM product_informationWHERE UPPER(product_name) = 'LASERPRO' OR list_price > 1000;

BSELECT UPPER(product_name)FROM product_information;

CSELECT UPPER(product_name)FROM product_informationWHERE product_id = 2254;

DSELECT product_idFROM product_informationWHERE UPPER(product_name) IN ('LASERPRO', 'CABLE');

Show Answer

Question: 2

Examine the types and examples of relationship that follows: (Choose the best answer.)

1 One-to-one a) teacher to Student

2 One-to-many b) Employees to Manager

3 Many-to-one c) Person to SSN

4 Many-to-many d) Customers to Products

Which option indicates correctly matched relationships?

A1-d, 2-b, 3-a, and 4-c

B1-c, 2-d, 3-a, and 4-b

C1-a, 2-b, 3-c, and 4-d

D1-c, 2-a, 3-b, and 4-d

Show Answer

Question: 3

When does a transaction complete? (Choose all that apply.)

AWhen a PL/SQL anonymous block is executed

BWhen a DELETE statement is executed

CWhen a data definition language statement is executed

DWhen a TRUNCATE statement is executed after the pending transaction

EWhen a ROLLBACK command is executed

Show Answer

Question: 4

Which three statements are true reading subquenes?

AA Main query can have many subqueries.

BA subquery can have more than one main query.

CThe subquery and main query must retrieve date from the same table.

DThe subquery and main query can retrieve data from different tables.

EOnly one column or expression can be compared between the subquery and main query.

FMultiple columns or expressions can be compared between the subquery and main query.

Show Answer

Question: 5

View the Exhibit and examine PRODUCTS and ORDER_ITEMS tables.

q5_1Z0-071

You executed the following query to display PRODUCT_NAME and the number of times the product has been ordered:

SQL>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?

AThe statement would execute successfully to produce the required output.

BThe statement would not execute because inline views and outer joins cannot be used together.

CThe statement would not execute because the ITEM_CNT alias cannot be displayed in the outer query.

DThe statement would not execute because the GROUP BY clause cannot be used in the inline.

Show Answer

Oracle 1Z0-071 Quiz:64 Topic:8 Questions:316-320