Oracle 1Z0-071 Quiz 24 Topic 3 Questions 116-120

Question: 1

View the Exhibit and examine the details of the ORDER_ITEMS table.

q1_1Z0-071

Evaluate the following SQL statements:

Statement 1:

SELECT MAX(unit_price*quantity) ''Maximum Order''

FROM order_items;

Statement 2:

SELECT MAX(unit_price*quantity) ''Maximum Order''

FROM order_items

GROUP BY order_id;

Which statements are true regarding the output of these SQL statements? (Choose all that apply.)

AStatement 2 would return multiple rows of output.

BBoth statements would ignore NULL values for the UNIT_PRICE and QUANTITY columns.

CStatement 1 would not return give the same output.

DBoth the statements would give the same output.

EStatement 1 would return only one row of output.

Show Answer

Question: 2

Which two statements are true regarding subqueries? (Choose two.)

AOnly two subqueries can be placed at one level.

BA subquery in the WHERE clause of a SELECT statement can be nested up to three levels only.

CA subquery can be used to access data from one or more tables or views.

DThe columns in a subquery must always be qualified with the name or alias of the table used.

EIf the subquery returns 0 rows, then the value returned by the subquery expression is NULL.

Show Answer

Question: 3

Examine the description of the EMP_DETAILS table given below:

q3_1Z0-071

Which two statements are true regarding SQL statements that can be executed on the EMP_DETAIL TABLE?

AAn EMP_IMAGE column cannot be included in the ORDER BY clause.

BYou can alter the table to include the NOT NULL constraint on the EMP_IMAGE column.

CYou cannot add a new column to the table with LONG as the data type.

DAn EMP_IMAGE column can be included in the GROUP BY clause.

Show Answer

Question: 4

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

q4_1Z0-071

You need to remove from the ORDER_ITEMS table those rows that have an order status of 0 or 1 in the ORDERS table.

Which two DELETE statements are valid (Choose two.)

ADELETE *FROM order_itemsWHERE order_id IN (SELECT order_id)FROM ordersWHERE order_status IN (0,1));

BDELETEFROM (SELECT * FROM order_items I,orders oWHERE i.order_id = o.order_id AND order_status IN (0,1));

CDELETE FROM order_items iWHERE order_id = (SELECT order_id FROM orders oWHERE i.order_id = o.order_id AND order_status IN (0,1));

DDELETEFROM order_itemsWHERE order_id IN (SELECT order_idFROM ordersWHERE orders_status in (0,1));

Show Answer

Question: 5

The PRODUCTS table has the following structure.

q5_1Z0-071

Evaluate the following two SQL statements:

SQL>SELECT prod_id, NVL2 (prod_expiry_date, prod_expiry_date + 15, ' ') FROM products;

SQL>SELECT prod_id, NVL (prod_expiry_date, prod_expiry_date + 15) FROM products;

Which statement is true regarding the outcome?

ABoth the statements execute and give different results

BOnly the second SQL statement executes successfully

CBoth the statements execute and give the same result

DOnly the first SQL statement executes successfully

Show Answer

Oracle 1Z0-071 Quiz:24 Topic:3 Questions:116-120