Free Oracle 1z0-071 Practice Exam with Questions & Answers | Set: 8

After creation and initial inserts, the pcode for 'pen' is updated to 10, and then committed.

The 'pencil' row is deleted and not yet committed.

A savepoint b is set after the deletion of the 'pencil' row.

The 'fountain pen' pcode is updated to 30, followed by setting savepoint c.

The 'pen' row (now with pcode 10) is deleted.

A rollback to savepoint b reverts the deletion of 'pen' and the update to 'fountain pen', but not the deletion of 'pencil', which was committed earlier due to the scope of the savepoint.

Therefore, after the final commit:

A: The code for 'pen' is 10, since the update was committed and the subsequent delete was rolled back.

C: There is no row containing 'pencil' because its deletion was committed.

F: There is a row containing 'pen' because the deletion was rolled back to savepoint b which was set after the deletion of 'pencil'.

A. True. The GROUP BY clause needs to include all non-aggregated columns from the SELECT list to provide the correct grouping for the output. The output shown in the image indicates that the data is grouped by channel_type, month, and country_code.

B, C, and D are incorrect because:

B includes a ROLLUP which would introduce subtotals that are not reflected in the output shown.

C specifies a CUBE, which would produce all possible combinations of groupings including the grand total, which is not shown in the output.

D specifies a ROLLUP on country_code only, which would not correctly group by channel_type and month.

In Oracle Database, when a transaction is not committed, the changes it makes are not visible to other sessions. This is due to Oracle's read consistency model, which means that Andrew will not be able to see the changes you have made until they are committed.

Option A is correct because, in Oracle, another session can modify rows that have not been locked by an uncommitted transaction.

Option C is incorrect because, as per Oracle's read consistency, the changes made by an uncommitted transaction are not visible to other users.

Option D is incorrect because Andrew can perform INSERT, UPDATE, or DELETE operations on the rows that you have not modified.

Option E is incorrect because, while Andrew will be able to SELECT from the table, he will still be able to modify rows that are not locked by your uncommitted update.

References:

Oracle Documentation on Transactions: Transactions

Oracle Documentation on Read Consistency: Read Consistency

To prevent the prompting for a substitution variable value when executing a query:

B. Execute the SET VERIFY OFF command before executing the query: This command turns off the substitution variable prompting, which means that SQL*Plus will not prompt for a value and will use whatever value is currently set for that variable.

F. Use the DEFINE command before executing the query: By using the DEFINE command to set a value for the variable before the query execution, you ensure that SQL*Plus uses this predefined value instead of prompting for it.

References:

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

The true statements about built-in data types in Oracle are:

B: A BFILE is a data type in Oracle that allows for a read-only link to binary files stored outside the database in the operating system. This is correct as per Oracle's documentation.

D: The default length for a CHAR column, when not specified, is one character. This is according to the Oracle SQL standard.

F: A BLOB is used for storing binary data within the Oracle database, allowing for storage of large amounts of unstructured binary data.

The incorrect options are:

A: A VARCHAR2 column does not blank-pad values; it is CHAR that may blank-pad to the fixed length.

C: A CHAR column requires a length specification, although if omitted, the default is one character.

E: A VARCHAR2 column requires a length specification; without it, the statement will fail.

References:

Oracle Documentation on Data Types: Data Types

Oracle Documentation on LOBs: LOBs

Oracle Documentation on Character Data Types: Character Data Types

For question 134, the correct options are B and F based on the capabilities and design of SQL:

B. It best supports relational databases: SQL is fundamentally designed to manage and query data in relational databases. It is the standard language used for managing relational database management systems (RDBMS) and for performing all types of data operations within them.

F. It provides independence for logical data structures being manipulated from the underlying physical data storage: SQL allows users to interact with the data at a logical level without needing to know how the data is physically stored. This is known as logical data independence, which is a key feature of SQL in managing databases.

Other options are incorrect because:

A: SQL itself doesn't guarantee ACID properties; these are provided by the database management system's transaction control mechanisms.

C, D, E: These statements are incorrect as SQL does not inherently support object-oriented concepts like encapsulation and polymorphism, is not limited to hierarchical data storage, and is not the only language used for both relational and object-oriented databases.

In Oracle SQL, roles can be granted privileges and other roles, and these privileges can then be passed on to users.

Statement A is correct: GRANT CREATE SEQUENCE TO manager, emp; successfully grants the CREATE SEQUENCE privilege to both the manager and emp roles.

Statement E is correct: GRANT CREATE ANY SESSION, CREATE ANY TABLE TO manager; successfully grants two system privileges to the manager role.

Statements B, C, and D are incorrect for the following reasons:

B is incorrect because the correct syntax to grant object privileges includes separating privileges with commas, not spaces, and the object name should be in singular form (hr.employees).

C is incorrect because you cannot grant a role and a privilege in the same statement.

D is incorrect because the correct syntax for granting object privileges does not include the privilege CREATE TABLE before specifying the object (hr.employees).

In the case of SQL commands TRUNCATE and DELETE:

A. the result of a delete can be undone by issuing a rollback: DELETE is a DML operation that affects rows individually and can be rolled back if it is performed within a transaction.

B. delete can use a where clause to determine which row(s) should be removed: DELETE operation allows the use of a WHERE clause to specify which rows should be deleted based on certain conditions.

Incorrect options are:

C: TRUNCATE does not support the use of a WHERE clause. It is designed to remove all rows from a table swiftly and cannot be conditional.

D: TRUNCATE does not leave indexes in an unusable state; it simply removes all rows.

E: TRUNCATE is a DDL command and its operation typically cannot be rolled back in many SQL database systems, including Oracle.

B: True. This MERGE statement should execute without errors. It uses a conditionally filtered selection from the products table as a source to update or delete rows in the new_prices table based on whether the prod_id matches and the cost is greater than 150. The delete operation within a MERGE statement is allowed in Oracle when a WHEN MATCHED clause is specified.

The MERGE statement is correctly structured with a USING clause that includes a subquery with a valid WHERE condition, an ON condition that specifies how to match rows between the source and the target, and a WHEN MATCHED THEN clause that specifies the update and delete operations based on the cost condition.

References:Oracle SQL documentation specifies that within a MERGE statement, you can specify a WHEN MATCHED clause to update and/or delete rows in the target table based on the condition specified after the DELETE keyword.