Full Access Oracle 1z0-071 Tutorials

The TRUNCATE TABLE command in Oracle SQL is used to quickly delete all rows from a table:

Option A:

It retains the indexes defined on the table. TRUNCATE does not affect the structure of the table, including its indexes.

Option D:

It retains the integrity constraints defined on the table. TRUNCATE does not remove or disable integrity constraints, except for unenforced foreign keys.

Options B, C, E, and F are incorrect because:

Option B: TRUNCATE does not drop triggers; it only removes all rows.

Option C: Flashback Table cannot be used after a TRUNCATE because TRUNCATE is a DDL operation that does not generate undo data for flashback.

Option E: A ROLLBACK cannot be used after a TRUNCATE because TRUNCATE is a DDL command that implicitly commits.

Option F: TRUNCATE may deallocate the space used by the table, depending on the database version and specific options used with the TRUNCATE command.

Synonyms in Oracle are aliases for database objects that can simplify SQL statements for database users.

A. The term "GLOBAL" is not used in the creation of synonyms in Oracle.

B. The statement without the keyword PUBLIC will create a private synonym that is only accessible to the user creating the synonym, not all users.

C. The correct syntax does not include PUBLIC as a prefix to the synonym name itself, making this option incorrect.

D. You cannot specify the SYS schema for creating synonyms, as it is reserved for system objects.

E. This is the correct syntax to create a public synonym, which makes the underlying object accessible to all users.

References:

Oracle Database SQL Language Reference, 12c Release 1 (12.1): "CREATE SYNONYM"

C: True. To display the number of products whose PROD_LIST_PRICE is more than the average PROD_LIST_PRICE, you would need to use a subquery to first calculate the average PROD_LIST_PRICE and then use that result to compare each product’s list price to the average.

E: True. Displaying products whose PROD_MIN_PRICE is more than the average PROD_LIST_PRICE of all products and whose status is orderable would require a subquery. The subquery would be used to determine the average PROD_LIST_PRICE, and then this average would be used in the outer query to filter the products accordingly.

Subqueries are necessary when the computation of a value relies on an aggregate or a result that must be obtained separately from the main query, and cannot be derived in a single level of query execution.

References:Oracle's SQL documentation provides guidelines for using subqueries in scenarios where an inner query's result is needed to complete the processing of an outer query.

The WITH GRANT OPTION clause in Oracle SQL allows the grantee to grant the privilege they have received to another user or role.

E. It cannot be used to pass on privileges to PUBLIC by the grantee: The WITH GRANT OPTION does not allow the grantee to pass on privileges to PUBLIC. Only the object's owner or a user with the GRANT ANY OBJECT PRIVILEGE system privilege can grant privileges to PUBLIC.

F. It can be used to pass on privileges to other users by the grantee: This is true. When a user receives privileges with the WITH GRANT OPTION, they can grant that privilege to another user or role.

References:

Oracle Database SQL Language Reference 12c, specifically sections on user privileges and the WITH GRANT OPTION.