Pass the Oracle PL 1z0-071 Questions and answers with ExamsMirror

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.

For Transaction Control Language (TCL) operations:

A. COMMIT erases all the transaction’s savepoints and releases its locks: When a COMMIT operation is executed, it finalizes all changes made during the transaction, removes any savepoints that have been set during the transaction, and releases any locks held on the database objects by the transaction.

B. COMMIT ends the transaction and makes all its changes permanent: A COMMIT command completes the transaction, making all data modifications permanent and visible to other sessions.

E. ROLLBACK without the TO SAVEPOINT clause undoes all the transaction's changes, releases its locks, and erases all its savepoints: The ROLLBACK command, when used without specifying a SAVEPOINT, undoes all changes made during the transaction, releases any locks held, and removes any savepoints that were established during the transaction.

Incorrect options:

C: ROLLBACK, without any specification, not only undoes all the transaction's changes but also releases all locks held by the transaction.

D: ROLLBACK to a SAVEPOINT only undoes changes up to the named savepoint and does not end the transaction; the transaction continues until a COMMIT or a full ROLLBACK is issued.

F: ROLLBACK without a TO SAVEPOINT clause also erases all savepoints created during the transaction.

Set operators allow you to combine multiple queries into a single query. The key points for each option are:

A. An expression in the first SELECT list does not need to have a column alias; however, the column alias given to an expression in the first SELECT list is used as the column name of the output.

B. This statement is true. When CHAR columns of different lengths are combined with a set operator, Oracle will pad the shorter values with spaces to match the length of the longest value. This behavior is consistent with the SQL standard and Oracle's treatment of the CHAR datatype.

C. Oracle Database performs implicit conversion between compatible data types where necessary when using set operators. For example, a NUMBER can be implicitly converted to a VARCHAR2 and vice versa if the context requires it.

D. This is true. When multiple set operators are used in a single query, the INTERSECT operator takes precedence over the UNION and UNION ALL operators. This order of operation can be overridden by using parentheses.

E. Not all set operators work with all data types. For instance, LOB and LONG RAW data types cannot be used with set operators.

References:

Oracle Database SQL Language Reference, 12c Release 1 (12.1): "Set Operators"

Oracle Database SQL Language Reference, 12c Release 1 (12.1): "Data Types"

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

Option B: a cardinality

Cardinality refers to the numerical relationships between two entities, indicating the number of instances of one entity that can or must be associated with each instance of another.

Option C: label

A label is often used to describe the role or purpose of the relationship in a way that clarifies the nature of the interaction between entities.

Option D: an optionality

Optionality indicates whether or not a relationship is mandatory or optional; in other words, it shows if an instance of an entity must be related to an instance of another entity or if it can exist without such a relationship.

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.

A: True. The DBTIMEZONE function returns the database's time zone offset from UTC (Coordinated Universal Time). In Oracle 12c, DBTIMEZONE can return the time zone of the database in terms of a region name or as a numeric offset from UTC. This is stated in the Oracle documentation for managing time zones.

B: True. TIMESTAMP WITH LOCAL TIME ZONE is a data type that adjusts the data stored in the database to the time zone of the session that is querying or inserting the data. When data is stored, Oracle converts it from the session time zone to UTC, and upon retrieval, it converts it back to the session time zone. This is a feature designed to allow the same data to be viewed in different time zones automatically, making it highly useful in global applications.

D: True. SESSIONTIMEZONE function returns the time zone offset of the current session from UTC. This is useful for understanding and managing data conversions in applications that are used across different time zones. The time zone can be displayed as an offset from UTC or as a named region depending on the environment settings.

For conditional INSERT ALL in Oracle Database 12c:

D. A single WHEN condition can be used for multiple INTO clauses. This is true. A single WHEN condition in a multi-table insert can direct the insertion of a single row source into multiple target tables.

E. Each WHEN condition is tested for each row returned by the subquery. True, each row from the subquery is evaluated against each WHEN condition to determine into which table(s) the row should be inserted.

Options A, B, and C are incorrect:

A is incorrect because a row can indeed be directed into multiple tables based on the conditions.

B is incorrect as INSERT ALL can include an ELSE clause to handle rows that do not meet any of the specified conditions.

C is incorrect because not every row from the subquery necessarily results in a row insertion; it depends on the conditions being met.

A. PRODUCT_ID can be assigned the PRIMARY KEY constraint.

In Oracle Database 12c, a PRIMARY KEY constraint is a combination of a NOT NULL constraint and a unique constraint. It ensures that the data contained in a column, or a group of columns, is unique among all the rows in the table and not null. Given the PRODUCT_ID is marked as NOT NULL, it is a candidate for being a primary key because we can assume that it is intended to uniquely identify each product in the table.

In Oracle SQL, aggregation functions such as AVG and MAX cannot be nested directly inside each other and must be used in conjunction with GROUP BY on the column(s) that are not included in the aggregate function. Also, the HAVING clause filters groups after aggregation is applied.

A. This query will not execute successfully because it improperly nests MAX inside AVG. Oracle SQL does not allow this type of nested aggregation without a subquery.

B. This query will not execute successfully for the same reason as A, it improperly nests MAX inside AVG.

C. Similar to A and B, this query improperly nests SUM inside MAX, which is not allowed without a subquery.

D. This query will execute successfully. It filters rows based on the HIRE_DATE using a correct date format (assuming '9' refers to '09' or '1999' due to the NLS_DATE_FORMAT being 'DD-MON-RR'), then groups the remaining rows by DEPT_ID and calculates the sum of SALARY for each department.

E. This query will not execute successfully because it improperly nests MAX inside AVG without a subquery, and it incorrectly attempts to GROUP BY SALARY, which is already being aggregated.

References:

Oracle Database SQL Language Reference, 12c Release 1 (12.1): "Aggregate Functions"

Oracle Database SQL Language Reference, 12c Release 1 (12.1): "GROUP BY Clause"

Oracle Database SQL Language Reference, 12c Release 1 (12.1): "HAVING Clause"

Queries that will execute successfully given the PRODUCT_STATUS table:

C. SELECT prod_id || q'('s not available)' "CURRENT AVAILABILITY" FROM product_status WHERE status = 'OUT OF STOCK';This query will execute successfully because it uses the q quote operator correctly to handle the inclusion of single quotes in the string.

D. SELECT prod_id || q'('s not available)' FROM product_status WHERE status = 'OUT OF STOCK';Similar to the previous query, it uses the q quote operator correctly and will execute without syntax errors.

Options A, B, E, and F are incorrect because of the incorrect or incomplete use of the quote operator:

A is incorrect because it has an additional single quote before the FROM clause.

B is incorrect because of a missing ending single quote after the q quote operator.

E and F are incorrect because they both use the q quote operator incorrectly; q’(’s not available)' is not a valid use of the quote operator, and q"'s not available" incorrectly uses double quotes instead of single quotes.

A. Incorrect. There is no such restriction in Oracle that a global temporary table cannot have a public synonym. B. Correct. Global temporary tables can have more than one index, just like permanent tables. C. Correct. Global temporary tables can be referenced in the defining query of a view. However, any data selected by the view will be session-specific if it comes from the global temporary table. D. Correct. DML operations on global temporary tables do not generate redo log entries for the data changes; however, undo information for these tables is stored in the undo tablespace and can generate redo entries. E. Incorrect. As stated in B, global temporary tables can have more than one index. F. Correct. Triggers can be created on global temporary tables.

These properties are documented in the Oracle Database SQL Language Reference and Oracle Database Concepts Guide.