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

B: True. The REFERENCES privilege can be granted on specific columns within a table. This is necessary when a user needs to define foreign key constraints that reference those particular columns.

C: True. The UPDATE privilege can be granted on specific columns, allowing users to update only designated columns within a table. This is useful for restricting write access to sensitive or critical data within a table.

F: True. The INSERT privilege can also be granted on specific columns, meaning a user can be permitted to insert data into only certain columns of a table. This helps in maintaining data integrity and controlling access to data fields based on user roles.

In Oracle Database 12c, the INTERSECT operator is used in compound queries to return only the rows that are common to both SELECT statements involved in the query. Here's a breakdown of why the correct answer is C:

• A: Incorrect. The order of the tables in an INTERSECT operation does not affect the output because INTERSECT returns only the common elements between the two queries. The operation is symmetric.
• B: Incorrect. While the column names themselves can differ between the two SELECT statements, the data types and the number of columns must match for INTERSECT to function correctly.
• C: Correct. INTERSECT returns the intersection of the results of the two queries, which includes only rows that are found in both result sets.
• D: Incorrect. The number of columns and their data types in each SELECT statement of the INTERSECT must be the same to avoid errors and to correctly perform the comparison.
• E: Incorrect. INTERSECT does not ignore NULLs; rows with NULL values in corresponding columns must exactly match (both sides having NULLs in the same column) to be included in the output.

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

• Option A:
• Option D:

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.

• A: This is true. Using PURGE in the DROP TABLE command will permanently remove the table and its dependent objects so that it cannot be recovered, and the action cannot be rolled back.
• B: This statement is true. Dropping the base table will result in the removal of all views that are based on the DEPARTMENTS table.
• C: This statement is false. The PURGE option removes the table and does not just delete rows.
• D: This statement is true. The DROP TABLE command will remove the DEPARTMENTS table from the database.
• E: This statement is true. When a table is dropped, all synonyms for that table are also removed.
• F: This statement is true. Dropping a table will automatically drop all indexes associated with the table.