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

Regarding the properties and capabilities of virtual columns in Oracle Database:

• C. They can be indexed: In Oracle, virtual columns can indeed be indexed. This allows for performance optimization on queries that utilize these columns, despite the fact that their values are derived and not stored physically.
• D. They cannot have a data type explicitly specified: The data type of a virtual column is derived from the expression used to define it. Therefore, it is not specified explicitly but is inferred from the expression itself.

Incorrect options:

• A: Virtual columns cannot be referenced in the WHERE clause of UPDATE or DELETE statements because they are not stored and cannot be individually modified.
• B: Virtual columns cannot be referenced in the SET clause of an UPDATE statement since they are derived from other column values and cannot be updated directly.
• E: Virtual columns cannot reference other virtual columns in their expressions due to the potential for recursive and complex dependencies.

A. Incorrect. DUAL consists of a single row and column. B. Incorrect. DUAL is a special one-row, one-column table present by default in all Oracle database installations and can be accessed by any user with SELECT privilege. C. Correct. Any user with the SELECT privilege can select from the DUAL table. It is a dummy table used primarily for selecting a constant, pseudocolumn, or expression not involving any tables. D. Incorrect. DUAL has only one row and one column. E. Correct. The DUAL table has a single VARCHAR2 column called DUMMY, which has a value of 'X'. F. Incorrect. DUAL can be used for a variety of purposes, such as selecting system constants, user environment variables, expressions, and sequences.

This information is detailed in the Oracle Database SQL Language Reference, which describes the properties and uses of the DUAL table.

In data modeling and database design, the direction of a relationship typically includes:

• Option B: a cardinality
• Option C: label
• Option D: an optionality

Options A, E, and F are not part of the direction of a relationship:

• Option A: An attribute is a property or characteristic of an entity, not the direction of a relationship.
• Option E: A unique identifier uniquely identifies each instance of an entity, not a relationship's direction.
• Option F: An entity is a thing with distinct and independent existence in a database, usually mapped to a table, not a part of the direction of a relationship.

The ORDER BY clause is used in a SELECT statement to sort the returned rows by one or more columns.

A. This clause would attempt to order by ename as if it were a numeric column, which is not correct since ename is not numeric.

B. This is the correct answer. The NVL function replaces nulls with the specified value, in this case, 0. This clause sorts by comm, with nulls considered as 0 and appearing first, followed by the actual values, and then sorts by ename.

C. This clause is incorrect because it would place null values at the end, but in the output, rows with null comm appear at the beginning.

D. This clause would sort the comm in descending order with the nulls at the end, which is not consistent with the output shown.

References:

• Oracle Database SQL Language Reference, 12c Release 1 (12.1): "ORDER BY Clause"
• Oracle Database SQL Language Reference, 12c Release 1 (12.1): "NVL Function"

Please note that the correct formatting of SQL statements and clauses is crucial for the successful execution of queries.