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

In the context of SQL statements using SET operations like UNION in Oracle Database 12c:

A. The data type of each column returned by the second query must be implicitly convertible to the data type of the corresponding column returned by the first query. This is correct. Oracle allows the union of columns as long as their data types are implicitly convertible, not necessarily identical.

C. The number, but not names, of columns must be identical for all SELECT statements in the query. This is correct. For a UNION operation to be valid, all SELECT statements involved must have the same number of columns, although their names and exact data types need not match.

Options B, D, and E are incorrect:

B is incorrect because exact data type matches are not required, only that they be implicitly convertible.

D is also incorrect for the same reason as B; it's enough that the types are compatible, not identical.

E is incorrect as the names

A: 'CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1 NOCACHE;' is correct for ensuring unique values without gaps as much as possible by not caching sequence numbers, which might otherwise be lost in a system crash.

B: CYCLE allows the sequence to restart when its max/min value is reached, which does not help with requirement 3 (duplicates are never permitted). Therefore, B is incorrect.

C: This lacks the necessary attributes like START WITH and INCREMENT BY which are crucial to defining a sequence. Thus, statement C is incorrect.

D: 'CREATE SEQUENCE emp_seq START WITH 1 CACHE;' might introduce gaps due to the caching of sequence numbers. This statement is somewhat contrary to requirement 5.

E: 'CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1 CACHE;' will provide continuous unique values, but may include gaps when the cache is lost due to a restart, yet it is more efficient and still generally aligns with the requirements. Hence, statement E is considered correct.

F: This lacks detail and is too ambiguous, lacking the necessary parameters. Therefore, F is incorrect.

A: True, the name of a column can be changed in Oracle using the ALTER TABLE ... RENAME COLUMN command.

B: False, you cannot change a column's data type from NUMBER to VARCHAR2 if the table contains data, unless the change does not result in data loss or inconsistency.

C: True, it is possible to change a DATE data type column to TIMESTAMP because TIMESTAMP is an extension of DATE that includes fractional seconds. This operation is allowed if there is no data loss.

D: False, any column that is not part of a primary key or does not have a non-deferrable constraint can generally be dropped unless it contains data that does not allow for such a change.

E: False, the DEFAULT clause can be added to a column provided there is no data that contradicts the default value or it doesn't violate any constraints.

These statements are verified against the Oracle Database 12c SQL documentation, specifically the sections on data types, the ALTER TABLE command, and the use of literals in SQL expressions.

Global temporary tables in Oracle Database 12c have unique characteristics, primarily around their visibility and lifespan which is session-specific:

B. A TRUNCATE command issued in a session causes all rows in a GLOBAL TEMPORARY TABLE for the issuing session to be deleted: This is accurate as TRUNCATE in the context of a global temporary table only affects the rows inserted during the session that issues the command. The effect is isolated to the session.

D. A GLOBAL TEMPORARY TABLE's definition is available to multiple sessions: The definition (i.e., the structure of the table such as column names, data types, etc.) of a global temporary table is persistent and visible across sessions. However, the data within is session-specific.

E. Any GLOBAL TEMPORARY TABLE rows existing at session termination will be deleted: True, as the data in a global temporary table is designed to be temporary for the duration of a session. When the session ends, the data is automatically deleted.

References:

Oracle Database Concepts and SQL Language Reference 12c, especially sections on temporary tables.

Substitution variables in Oracle are used to replace a value dynamically during the execution of SQL statements. The behavior of these variables is well-documented:

C. A substitution variable prefixed with & always prompts only once for a value in a session: This is true. In a session, when you use a single ampersand (&), SQL*Plus or SQL Developer will prompt for the value the first time the variable is encountered. The value for this variable will then be reused for the remainder of the session unless it is redefined.

D. A substitution variable can be used with any clause in a SELECT statement: Substitution variables can be placed in any part of a SQL statement, including the SELECT, WHERE, GROUP BY, ORDER BY, etc. They are not limited to any specific clause.

References:

Oracle SQL*Plus User's Guide and Reference, which discusses substitution variables.

To include employees with no manager in the query results, a LEFT OUTER JOIN should be used. This type of join returns all records from the left table (employees), and the matched records from the right table (managers). The result is NULL from the right side if there is no match.

Here's the modified query:

SELECT e.last_name AS emp, m.last_name AS mgr FROM employees e LEFT OUTER JOIN managers m ON e.manager_id = m.employee_id ORDER BY emp;

This ensures that even if an employee does not have a manager (i.e., e.manager_id is NULL or there is no corresponding m.employee_id), that employee will still be included in the results.

Regarding Oracle join and ANSI join syntax:

B. The Oracle join syntax supports the creation of a Cartesian product of two tables. This is true. In Oracle, if you list tables in the FROM clause without a join condition, it creates a Cartesian product.

C. The SQL:1999 compliant ANSI join syntax supports natural joins. This is true. ANSI syntax supports natural joins, which join tables based on columns with the same names in the joined tables.

F. The SQL:1999 compliant ANSI join syntax supports the creation of a Cartesian product of two tables. This is true. The ANSI standard allows for Cartesian products when tables are listed in the FROM clause without a join condition.

Options A, D, E, and G are incorrect:

A is incorrect because the Oracle join syntax supports all types of joins, including right outer joins.

D is incorrect because Oracle's proprietary join syntax does not use the term "natural join."

E is incorrect because there is no inherent performance difference between Oracle join syntax and ANSI join syntax; performance depends on how the query is written and how the database optimizer handles it.

G is incorrect for the same reason as E.

Global temporary tables in Oracle Database are designed to hold temporary data for the duration of a session or a transaction.

Given that the global temporary table is created and then populated twice from the emp table without any mention of data deletion, one would initially think the count would be 28, since 14 rows are inserted twice. However, if the temporary table was created with the default ON COMMIT DELETE ROWS option, the rows are deleted at the end of the transaction, which is signified by the COMMIT operation.

Thus, after the first COMMIT, the temporary table would be empty. After the second INSERT, another 14 rows would be added. So, the last query would retrieve 14 rows unless a DELETE operation was committed.