1z0-071 Practice Questions

E.  

F.  

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.

In Oracle SQL, substitution variables (&1) can be used to dynamically replace a value in a SQL statement at runtime. To avoid being prompted for a value when executing a query that includes a substitution variable:

A.  

Use the DEFINE command before executing the query: By setting a value for a substitution variable using the DEFINE command, Oracle will use this value instead of prompting the user for input.

D.  

Execute the SET VERIFY OFF command before executing the query: The SET VERIFY OFF command suppresses the prompt for substitution variables by turning off the verification feature. This makes Oracle use the currently defined value for the variable.

References:

Oracle Database SQL Language Reference 12c, particularly the sections on substitution variables, the DEFINE command, and the SET command.

The behavior of data insertion into a table with a specific numeric format in Oracle Database 12c can be explained as follows:

A.  

INSERT INTO rate_list VALUES (-.9) inserts the value as -.9: This is correct. The value -.9 is within the precision defined by the table column (NUMBER(6,2)), where 6 is the total number of digits (including both sides of the decimal point), and 2 is the number of digits after the decimal point.

E.  

INSERT INTO rate_list VALUES (0.551) inserts the value as .55: Oracle rounds the number to the nearest value that fits the specified scale of 2. Thus, 0.551 rounds down to 0.55.

References:

Oracle Database SQL Language Reference 12c, particularly sections on data types and numeric precision.

For the SELECT statement from two tables without a JOIN clause:

Option B: You can add a WHERE clause with filtering criteria.

A WHERE clause can be added to a query to filter the results based on specified conditions.

Option E: It returns the same rows as SELECT * FROM bricks CROSS JOIN colors.

A comma-separated list of tables in a FROM clause is equivalent to a CROSS JOIN, resulting in a Cartesian product of the tables.

Options A, C, and D are incorrect because:

Option A: You cannot add an ON clause without changing the comma-separated FROM clause to a proper JOIN.

Option C: It returns the Cartesian product, not the sum, of the rows from BRICKS and COLORS.

Option D: A USING clause is not applicable without changing the syntax to a proper JOIN.

In Oracle 12c SQL, the DELETE statement is used to remove rows from a table based on a condition. Given the constraints and the information provided, let's evaluate the options:

A.  

DELETE FROM orders WHERE order_total<1000;: This statement is correctly formatted and will delete rows from the ORDERS table where ORDER_TOTAL is less than 1000. If there is a DELETE CASCADE constraint, corresponding rows in the ORDER_ITEMS table will also be deleted.

B.  

DELETE * FROM orders WHERE order_total<1000;: This syntax is incorrect. The asterisk (*) is not used in the DELETE statement.

C.  

DELETE orders WHERE order_total<1000;: This statement is also correctly formatted and is a shorthand version of the DELETE statement without the FROM clause.

D.  

DELETE FROM orders;: This statement will delete all rows from the ORDERS table, and with DELETE CASCADE, it will also delete all related rows in the ORDER_ITEMS table.

E.  

DELETE order_id FROM orders WHERE order_total<1000;: This syntax is incorrect because you cannot specify a column after the DELETE keyword.

References:

Oracle Database SQL Language Reference 12c Release 1 (12.1), DELETE Statement

C: True. In Oracle Database 12c, character sorts are case-sensitive by default, meaning that it differentiates between uppercase and lowercase letters when sorting data. This behavior aligns with the binary collation rules typically used, unless explicitly overridden by using different collation settings.

D: True. Column aliases can indeed be used in the ORDER BY clause in Oracle SQL. This allows for more readable and maintainable code, as you can define a column alias in the SELECT list and refer to that alias in the ORDER BY clause. This usage promotes clarity, especially when dealing with complex calculations or function applications in the select list.