Oracle Updated 1z0-071 Exam Questions and Answers by lily-mae

A. Incorrect. An uncommitted transaction is not automatically committed if the user exits SQL*Plus. It is rolled back unless otherwise specified. B. Correct. A DML statement such as INSERT, UPDATE, or DELETE will implicitly start a new transaction if there is no current transaction running in the session. C. Incorrect. A user cannot see uncommitted updates made by another session; a session can only see its own uncommitted changes. D. Incorrect. A Data Definition Language (DDL) statement automatically commits any outstanding transactions in the session, not just the changes to the data dictionary. E. Correct. A session can always see its own uncommitted updates. This is because Oracle uses a multiversion consistency model, allowing each user to see a consistent view of the data including their own changes. F. Incorrect but close. If a session has an uncommitted transaction, then a DDL statement does issue a COMMIT before executing and starting a new implicit transaction, not just for the data dictionary updates but for all pending changes in the session.

This information can be verified in the Oracle Database SQL Language Reference and Oracle Database Concepts documentation, which discuss transaction management and the behavior of DML and DDL statements within transactions.

For the provided UNION ALL query:

Option C: It will return rows from both SELECT statements including duplicate rows.

UNION ALL is used to combine the results of two SELECT statements and does not eliminate duplicates.

Options A, B, and D are incorrect because:

Option A: UNION ALL does not eliminate duplicate rows, unlike UNION.

Option B: This would be true for INTERSECT, not UNION ALL.

Option D: This would be true for EXCEPT or MINUS, not UNION ALL.

To calculate the length of employment, you would compare the current date to the HIRE_DATE and calculate the difference in years.

A. This is the correct answer. The expression calculates the number of days between the current date (SYSDATE) and the HIRE_DATE, then divides by 365 to convert it to years, and checks if it is greater than 5.

B. SYSTIMESTAMP includes a time component including fractional seconds and time zone information, making this comparison incorrect.

C. There are typos in this option (CUARENT_DATE should be CURRENT_DATE and hire_data should be hire_date). Even with the correct function and column names, CURRENT_DATE returns the current date in the session time zone, not in years.

D. There are typos in this option (SYSNAYW should be SYSDATE). Additionally, dividing by 12 incorrectly assumes that a month is equivalent to a year.

E. This is a repetition of option D with the same issues.

F. There are typos in this option (CUNACV_DATE should be CURRENT_DATE and hire_data should be hire_date). Additionally, dividing by 12 incorrectly assumes that a month is equivalent to a year.

References:

Oracle Database SQL Language Reference, 12c Release 1 (12.1): "Date Functions"

In Oracle SQL, when using the set operators like MINUS, the number of columns and their data types in the SELECT statements must match in sequence.

A. This query will not execute successfully because the SELECT * FROM bricks_stage will return all columns from the BRICKS_STAGE table, which are WEIGHT, SHAPE, and COLOR, but the first SELECT statement specifies only SHAPE and COLOR. The order and number of columns must match.

B. This query will not execute successfully. The SELECT statements have a different number of columns, and the data types of the columns in the same positions do not match between the two queries. The first column in the first SELECT is SHAPE (VARCHAR2), and in the second SELECT, it is WEIGHT (NUMBER).

C. This query will execute successfully. The SELECT * from both tables will ensure that the number of columns and their data types are the same, as SELECT * selects all columns from the table. As long as the two tables have the same column order and data types for those columns, the query will execute.

D. This query will not execute successfully. Even though the columns are of the same data types, their order in the SELECT statements must match for the set operator to work. The order of SHAPE and COLOR is switched between the two queries.

E. This query will not execute successfully. The number of columns in the SELECT statements is the same, but their data types do not match between the two queries. BRICK_ID (NUMBER) in the first query does not match WEIGHT (NUMBER) in the second, and SHAPE (VARCHAR2) does not match COLOR (VARCHAR2).

References:

Oracle Database SQL Language Reference, 12c Release 1 (12.1): "Combining Queries with Set Operators"

Oracle Database SQL Language Reference, 12c Release 1 (12.1): "MINUS"