Oracle 1Z0-071 Quiz 41 Topic 1 Questions 201-205

Question: 1

Examine the structure of the ORDERS table:

q1_1Z0-071

You want to find the total value of all the orders for each year and issue this command:

SQL> SELECT TO_CHAR(order_date,'rr'), SUM(order_total) FROM orders

GROUP BY TO_CHAR(order_date, 'yyyy');

Which statement is true regarding the result? (Choose the best answer.)

AIt executes successfully but does not give the correct output.

BIt executes successfully and gives the correct output.

CIt returns an error because the TO_CHAR function is not valid.

DIt return an error because the datatype conversion in the SELECT list does not match the data type conversion in the GROUP BY clause.

Show Answer

Question: 2

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

q2_1Z0-071

You must select the ORDER_ID of the order that has the highest total value among all the orders in the ORDER_ITEMS table.

Which query would produce the desired result?

ASELECT order_idFROM order_itemsGROUP BY order_idHAVING SUM(unit_price*quantity) = (SELECT MAX(SUM(unit_price*quantity))FROM order_items GROUP BY order_id);

BSELECT order_idFROM order_itemsWHERE(unit_price*quantity) = (SELECT MAX(unit_price*quantity)FROM order_items)GROUP BY order_id;

CSELECT order_idFROM order_itemsWHERE(unit_price*quantity) = MAX(unit_price*quantity)GROUP BY order_id;

DSELECT order_idFROM order_itemsWHERE (unit_price*quantity) = (SELECT MAX(unit_price*quantity)FROM order_itemsGROUP BY order_id)

Show Answer

Question: 3

View the Exhibit and examine the structure of the EMP table which is not partitioned and not an index-organized table. (Choose two.)

q3_1Z0-071

Evaluate this SQL statement:

ALTER TABLE emp

DROP COLUMN first_name;

Which two statements are true?

AThe FIRST_NAME column can be dropped even if it is part of a composite PRIMARY KEY provided the CASCADE option is added to the SQL statement.

BThe FIRST_NAME column would be dropped provided at least one column remains in the table.

CThe FIRST_NAME column would be dropped provided it does not contain any data.

DThe drop of the FIRST_NAME column can be rolled back provided the SET UNUSED option is added to the SQL statement.

Show Answer

Question: 4

View the exhibit and examine the structure and data in the INVOICE table.

q4_1Z0-071

Which two SQL statements would execute successfully? (Choose two.)

ASELECT MAX(AVG(SYSDATE -inv_date))FROM invoice;

BSELECT AVG(inv_date)FROM invoice;

CSELECT MAX(inv_date),MIN(cust_id)FROM invoice;

DSELECT AVG( inv_date - SYSDATE), AVG(inv_amt)FROM invoice;

Show Answer

Question: 5

Which two statements best describe the benefits of using the WITH clause? (Choose two.)

AIt can improve the performance of a large query by storing the result of a query block having the WITH clause in the session's temporary tablespace.

BIt enables sessions to reuse the same query block in a SELECT statement, if it occurs more than once in a complex query.

CIt enables sessions to store a query block permanently in memory and use it to create complex queries.

DIt enables sessions to store the results of a query permanently.

Show Answer

Oracle 1Z0-071 Quiz:41 Topic:1 Questions:201-205