Helping Hand Questions for 1z0-071

Regarding privileges in Oracle:

Option A: Dropping user FINANCE will automatically revoke SELECT on SCOTT.EMP from user FIN_CLERK.

Dropping a user will revoke all privileges that user has granted to others.

Option B: Revoking SELECT on SCOTT.EMP from user FINANCE will also revoke the privilege from user FIN_CLERK.

Since FINANCE granted SELECT on SCOTT.EMP to FIN_CLERK with grant option, revoking the privilege from FINANCE will also cascade and revoke it from FIN_CLERK.

Options C, D, and E are incorrect because:

Option C: User FINANCE can grant privileges it owns, but CREATE SESSION is a system privilege; unless FINANCE has the 'WITH ADMIN OPTION' for CREATE SESSION, it cannot grant this privilege.

Option D: User FIN_CLERK can only grant privileges to others if it has been given those privileges with the 'WITH GRANT OPTION' and it was not given in this case.

Option E: User FINANCE can grant privileges on SCOTT.EMP to other users only if it has those privileges with 'WITH GRANT OPTION'. If the privilege was just SELECT with GRANT OPTION, it cannot grant ALL privileges.

C: True. A SQL statement may include multiple single row subqueries in different parts of the statement, such as in the SELECT list, WHERE clause, or HAVING clause. Each subquery must independently satisfy the requirement of returning a single row to avoid runtime errors.

D: True. Single row subqueries can be used in the HAVING clause. This allows for filtering groups based on conditions evaluated against individual or aggregated values returned by the subquery. The subquery must return a single value to be valid in this context.

E: True. Single row subqueries are often placed on the right side of the comparison operator in a SQL condition. This positioning is typical because the left side often references a column or an expression related to the main query, while the subquery on the right side dynamically provides a value for comparison.

B. CREATE INDEX price_idx on products (price); E. ALTER TABLE products DROP UNUSED COLUMNS; F. DROP TABLE products.

Comprehensive and Detailed Explanation WITH all References:

When a table is in read-only mode, most types of modifications are prohibited. However, certain operations can still be performed.

A. Incorrect. You cannot drop a column from a table that is in read-only mode, as it is a modifying operation. B. Correct. You can create an index on a read-only table. Creating an index does not modify the actual rows within the table; it builds a separate structure used for faster access. C. Incorrect. The SET UNUSED statement marks one or more columns as unused so they can be dropped when the database is not busy. This operation is considered a modifying operation and therefore is not allowed on a read-only table. D. Incorrect. Truncate is a DDL operation that would delete all rows from a table. This operation is not allowed on a read-only table. E. Correct. The ALTER TABLE ... DROP UNUSED COLUMNS statement is used to drop columns that have been previously marked as unused using the SET UNUSED statement. This operation is allowed because it only affects previously marked unused columns, not actively used data. F. Correct. Dropping a table is allowed even if it's in read-only mode because it is a DDL operation that does not operate on the rows of the table but rather on the object itself.

The behavior of read-only tables and the operations that can be performed on them are detailed in the Oracle Database SQL Language Reference and Oracle Database Administrator's Guide.

In Oracle Database 12c SQL, regarding date/time functions and considering a session where NLS_DATE_FORMAT is set to DD-MON-YYYY SH24:MI:SS:

C. CURRENT_DATE returns the current date and time as per the session time zone. This is correct as CURRENT_DATE returns the current date and time in the time zone of the current SQL session, as set by the ALTER SESSION command.

D. SYSDATE and CURRENT_DATE return the current date and time set for the operating system of the database server. This is partially correct. SYSDATE returns the current date and time from the operating system of the database server. However, CURRENT_DATE returns the date and time set for the client's operating system environment, adjusted to the session time zone.

Options A, B, E, and F are incorrect based on Oracle's documentation:

A is incorrect because SYSDATE is independent of the NLS_DATE_FORMAT setting.

B is incorrect because CURRENT_TIMESTAMP includes time zone information, which can differ from CURRENT_DATE.

E is incorrect because CURRENT_TIMESTAMP differs from SYSDATE by including fractional seconds and time zone.

F is incorrect as SYSDATE can be queried in any SELECT statement, not just from DUAL.