Complete 1z0-071 Oracle Materials

The REPLACE function in Oracle SQL is used to replace occurrences of a specified substring with another substring. In this query, the inner REPLACE function call REPLACE(cust_last_name, 'son', '') removes the substring 'son' from cust_last_name. The outer REPLACE function call then replaces the substring 'An' with 'O'. For the given data, 'Anderson' would first be transformed to 'Ander' by the inner REPLACE, and then 'Ander' would be transformed to 'Oder' by the outer REPLACE. Similarly, 'Ausson' would first change to 'Aus' by the inner REPLACE, which is unaffected by the outer REPLACE.

Reference can be found in the Oracle Database SQL Language Reference documentation, which details the functionality of string functions, including REPLACE.

After creation and initial inserts, the pcode for 'pen' is updated to 10, and then committed.

The 'pencil' row is deleted and not yet committed.

A savepoint b is set after the deletion of the 'pencil' row.

The 'fountain pen' pcode is updated to 30, followed by setting savepoint c.

The 'pen' row (now with pcode 10) is deleted.

A rollback to savepoint b reverts the deletion of 'pen' and the update to 'fountain pen', but not the deletion of 'pencil', which was committed earlier due to the scope of the savepoint.

Therefore, after the final commit:

A: The code for 'pen' is 10, since the update was committed and the subsequent delete was rolled back.

C: There is no row containing 'pencil' because its deletion was committed.

F: There is a row containing 'pen' because the deletion was rolled back to savepoint b which was set after the deletion of 'pencil'.

Unused columns are a feature in Oracle that allows a column to be marked as unused without immediately dropping it from the table structure. The key points for each option are:

A. The DESCRIBE command does not display unused columns. These columns are also not visible in user_tab_cols but are visible in user_unused_col_tabs.

B. Primary key columns can be set to unused, but you must drop the primary key constraint before doing so.

C. Once a column is marked as unused, it is not possible to query it, and Oracle will not allow DML operations on that column.

D. This is true. Once a column is set to unused, you can add a new column with the same name because Oracle essentially considers the unused column as removed from the table.

E. Just like primary key columns, a foreign key column can be marked as unused, but you must drop the foreign key constraint first.

F. This is true. Unused columns retain their data until the column is dropped using the DROP UNUSED COLUMNS command.

References:

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

Oracle Database Administrator’s Guide, 12c Release 1 (12.1)

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.