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

C. True. If you have a table named 123, which is not a standard naming convention because it begins with a digit, you can reference it in SQL by using double quotation marks. The correct syntax to reference a table with a name that does not follow standard naming conventions is to enclose the name in double quotation marks.

A, B, and D are incorrect because they do not use the proper quoting mechanism for a table named with non-standard characters. In Oracle, double quotation marks are used for case-sensitive table names or those that do not follow the usual naming rules.

B: True. Multiple table insert statements, specifically the conditional INSERT ALL, can transform a single row from the source table into multiple rows in one or more target tables depending on the conditions specified in the WHEN clauses.

E: True. The unconditional INSERT ALL statement will insert rows into multiple target tables without any conditions. However, it does not require the same number of columns in both the source and target tables. The INSERT ALL syntax allows you to specify different columns for each target table into which rows will be inserted.

Multiple table insert operations allow for complex insert scenarios, where based on the data, one can insert into different tables or multiple times into the same table based on different conditions.

References:Oracle SQL reference details the use of INSERT ALL and INSERT FIRST clauses for inserting into multiple tables based on conditions specified.

A look at the queries and how Oracle Database handles data type conversion:

• A: This statement takes a substring of the JOIN_DATE column. Since JOIN_DATE is a DATE type and SUBSTR function expects a string, implicit conversion will occur from DATE to string based on the NLS_DATE_FORMAT setting. However, this will not require explicit conversion in the query itself.
• B: This statement tries to add '20' to JOIN_DATE. Oracle Database does not support adding a string to a DATE implicitly. An explicit conversion of '20' to a number is required, or using the INTERVAL keyword to add days or years as appropriate.
• C: This statement concatenates the date with an empty string and the salary. The database will implicitly convert the JOIN_DATE from a DATE to a string using the NLS_DATE_FORMAT setting and then perform the concatenation.
• D: This query attempts to compare a DATE column with a string literal '*10-02-2018'. An explicit conversion using TO_DATE is needed to convert the string to a DATE data type with the appropriate date format mask. In this case, because the string does not follow the NLS_DATE_FORMAT 'DD-MON-YY', it will not be implicitly converted correctly.
• E: This statement adds '120.50' to SALARY. Oracle Database will implicitly convert the string '120.50' to a number before performing the arithmetic addition, as SALARY is a NUMBER data type.

The data dictionary is a read-only set of tables that provides information about the database. Certain operations that modify the database structure will cause changes to the data dictionary:

• Option B: GRANT UPDATE ON scott.emp TO fin_manager;
• Option D: TRUNCATE TABLE emp;

Options A, C, and E do not cause changes to the data dictionary:

• Option A is incorrect because the DELETE command modifies data, not the data dictionary structure.
• Option C is incorrect because altering a session parameter does not change the data dictionary; it is a temporary change for the session.
• Option E is incorrect because a SELECT query does not change the database, it just retrieves information.