1z0-071 Exam Dumps : Oracle Database 12c SQL

Multiple INSERT statements allow data insertion into one or more tables based on different conditions or datasets:

Option A: False. Multiple INSERT operations can be performed using direct values, subqueries, or even default values, not exclusively through subqueries.

Option B: True. They can indeed be performed on relational tables, which is the standard use case in most relational databases.

Option C: True. INSERT operations can be performed on updatable views, assuming the view is not complex (involving joins, GROUP BY clauses, etc.).

Option D: True. Oracle allows INSERT operations on remote tables via database links, enabling distributed database interactions.

Option E: False. Direct INSERT statements cannot be performed on external tables. External tables are typically used for read operations, with data loading handled through utilities like SQL*Loader or external data processing tools.

Option F: False. Each INSERT statement inserts data into one table. While a single SQL command block can contain multiple INSERT statements, each one is directed at a single table.

Regarding the usage and rules of outer joins in SQL, specifically Oracle SQL:

D. A condition representing an outer join cannot be linked to another condition using the OR logical operator: In SQL, when using the Oracle-specific (+) notation for outer joins, it is not permitted to combine this condition with another using the OR operator. The use of (+) imposes restrictions to ensure the join logic is correctly interpreted.

E. The outer join operator (+) is used next to the column of the table without the matching rows: The (+) symbol in Oracle's SQL syntax denotes the table that should include "null" where data does not exist to satisfy the join condition, effectively including rows that do not have a match in the joined table.

Incorrect options:

A: The (+) operator cannot be used on both sides of a condition within the same join; it can only appear on one side to define which side of the join is the outer part.

B: An outer join is used to retrieve all rows from one table and the matched rows from the other table; it does not solely retrieve rows that do not meet the join condition.

C: The IN operator can be used in conditions involving an outer join, although specific rules and behaviors need to be considered depending on the SQL version and implementation.

The TRUNCATE TABLE command in Oracle SQL is used to quickly delete all rows from a table:

Option A:

It retains the indexes defined on the table. TRUNCATE does not affect the structure of the table, including its indexes.

Option D:

It retains the integrity constraints defined on the table. TRUNCATE does not remove or disable integrity constraints, except for unenforced foreign keys.

Options B, C, E, and F are incorrect because:

Option B: TRUNCATE does not drop triggers; it only removes all rows.

Option C: Flashback Table cannot be used after a TRUNCATE because TRUNCATE is a DDL operation that does not generate undo data for flashback.

Option E: A ROLLBACK cannot be used after a TRUNCATE because TRUNCATE is a DDL command that implicitly commits.

Option F: TRUNCATE may deallocate the space used by the table, depending on the database version and specific options used with the TRUNCATE command.