Oracle Updated 1z0-071 Exam Questions and Answers by kyan

For the sorting of output in compound queries (INTERSECT, UNION, etc.):

D. The first column in the first SELECT of the compound query: By default, Oracle does not automatically sort the results of SET operations unless an ORDER BY clause is explicitly stated. However, if an ORDER BY is implied or specified without explicit columns, the default sorting would logically involve the first column specified in the first SELECT statement of the compound query.

For situations where the transaction would complete by committing the updates:

A. When the session logs out successfully: When a user session logs out, Oracle automatically commits any outstanding transactions.

C. When a CREATE INDEX statement is executed successfully in the same session: Most DDL statements, including CREATE INDEX, cause an implicit commit before and after they are executed.

F. When a DBA issues a successful SHUTDOWN TRANSACTIONAL statement: This type of shutdown ensures that active transactions are either committed or rolled back. If a COMMIT is then issued explicitly, it would be redundant but emphasizes the transaction completion.

Incorrect options:

B: SHUTDOWN IMMEDIATE will roll back transactions, not commit them.

D: A COMMIT in one session cannot affect the transaction state of another session; each session is isolated in terms of transaction management.

E: If a CREATE TABLE AS SELECT statement executes unsuccessfully, no implicit commit is performed; the statement failure means the transaction state remains unchanged.

In Oracle SQL, date arithmetic can be performed directly, subtracting one date from another to get the number of days between them:

Option A:

SELECT (SYSDATE - DATE '2019-01-01') / 1 FROM DUAL; This query successfully calculates the difference between the current date and a specific date.

Option D:

SELECT SYSDATE - DATE '2019-01-01' - 1 FROM DUAL; This query subtracts a specific date from the current date and then subtracts 1 more day, which will execute successfully.

Option F:

SELECT SYSDATE - 1 - DATE '2019-01-01' FROM DUAL; Similar to D, this will successfully compute the date difference minus one day.

Options B, C, and E are incorrect because:

Option B and Option C: You cannot divide by a date or divide a date by a number.

Option E: You cannot subtract a date from a number.

A. True. The GROUP BY clause needs to include all non-aggregated columns from the SELECT list to provide the correct grouping for the output. The output shown in the image indicates that the data is grouped by channel_type, month, and country_code.

B, C, and D are incorrect because:

B includes a ROLLUP which would introduce subtotals that are not reflected in the output shown.

C specifies a CUBE, which would produce all possible combinations of groupings including the grand total, which is not shown in the output.

D specifies a ROLLUP on country_code only, which would not correctly group by channel_type and month.