1z0-071 Dumps with Practice Exam Questions Answers

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:
• 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 statement is correct. It uses the TO_DATE function with a proper date string, format mask, and NLS_DATE_LANGUAGE setting.
• B: This statement will not execute successfully because the syntax of the TO_CHAR function is incorrect. The date string should be a DATE data type when used with TO_CHAR, and the format mask and NLS parameter are incorrectly specified.
• C: This statement will not execute successfully because it is redundant to use TO_CHAR and then immediately convert it back to a date with TO_DATE without specifying a proper format mask.
• D: This statement is correct. It converts a string to a DATE using TO_DATE and then back to a string with TO_CHAR, without specifying a format which defaults to the session’s NLS_DATE_FORMAT.
• E: This statement will not execute successfully because TO_CHAR is used incorrectly; the first argument must be of DATE data type when you're using a date format mask.

References for the TO_DATE and TO_CHAR functions and their proper usage can be found in the Oracle Database SQL Language Reference 12c documentation.

The statement is trying to create a table with columns of different data types and constraints. Here's what needs to be corrected:

• C: In Oracle, the LONG data type is used for character data of variable length up to 2 Gigabytes, but it is deprecated, and you should use CLOB or VARCHAR2 instead. Furthermore, a table cannot have more than one LONG column.
• D: The NOT NULL constraint should be specified at the column level, not at the table level. The correct syntax for creating a table with a NOT NULL constraint is to define it inline with the column definition, like this:

ename VARCHAR2(15) CONSTRAINT ename_nn NOT NULL,

The other options are incorrect:

• A: The foreign key constraint syntax is correct; the word CONSTRAINT is followed by the constraint name and then the REFERENCES clause.
• B: The foreign key constraint can be defined at the column level.
• E: While it's a good practice to name constraints, it is not mandatory for the primary key constraint to have a name; Oracle will generate one if it's not provided.

References:

• Oracle Documentation on CREATE TABLE: SQL Language Reference - CREATE TABLE
• Oracle Documentation on Data Types: SQL Language Reference - Data Types

Scalar subquery expressions are used in Oracle SQL to return a single value from a subquery.

• Option C: They can be nested.
• Option D: A scalar subquery expression that returns zero rows evaluates to null.
• Option F: They can be used as default values for columns in a create table statement.

Options A, B, and E are incorrect based on Oracle SQL standards and functionalities.