Oracle 1z0-071 Online Access

The data dictionary is a read-only set of tables that provides information about the database. Certain operations that modify the database structure will cause changes to the data dictionary:

• Option B: GRANT UPDATE ON scott.emp TO fin_manager;
• Option D: TRUNCATE TABLE emp;

Options A, C, and E do not cause changes to the data dictionary:

• Option A is incorrect because the DELETE command modifies data, not the data dictionary structure.
• Option C is incorrect because altering a session parameter does not change the data dictionary; it is a temporary change for the session.
• Option E is incorrect because a SELECT query does not change the database, it just retrieves information.

Regarding single-row subqueries:

• A. THEY CAN BE USED in the where clause: Single-row subqueries can be used in the WHERE clause to compare a column's value against the result of the subquery.
• B. A SQL STATEMENT MAY HAVE MULTIPLE SINGLE ROW SUBQUERY BLOCKS: It is possible to use more than one single-row subquery within a SQL statement, each potentially in different parts of the statement (e.g., SELECT clause, WHERE clause).
• E. THEY CAN BE USED IN THE HAVING CLAUSE: Similar to the WHERE clause, single-row subqueries can also be used in the HAVING clause to filter groups based on a condition evaluated against a subquery result.

Incorrect options are:

• C and D: There is no such limitation on the placement of single-row subqueries relative to the comparison operator; they can be used on either side.
• F: Single-row subqueries must return exactly one row to avoid errors, but they do not necessarily have to return a row; if no row is returned, the query may still execute, potentially returning no rows in the outer query.

Regarding creating tables in an Oracle database:

• B. The same table name can be used for tables in different schemas: In Oracle, a schema is essentially a namespace within the database; thus, the same table name can exist in different schemas without conflict, as each schema is distinct.
• C. A system privilege is required: To create tables, a user must have the necessary system privileges, typically granted explicitly or through roles such as CREATE TABLE or administrative privileges depending on the environment setup.

Incorrect options for all three repeated questions:

• A: Oracle SQL does not allow specifying the maximum number of rows directly in a CREATE TABLE statement; this is controlled by storage allocation and database design rather than table creation syntax.
• D: Creating an external table does not create the physical file. It merely creates a table structure that allows access to data stored in an external file specified in the directory; the file itself must already exist or be managed outside of Oracle.
• E: A primary key constraint is not mandatory for creating tables. While it is a common practice to define a primary key to enforce entity integrity, it is not required by the Oracle SQL syntax for table creation.

These answers and explanations are aligned with Oracle Database 12c SQL documentation and standard practices.

To format a date in Oracle SQL, TO_CHAR function is used to convert date types to string with a specified format:

• Option A: SELECT TO_CHAR(SYSDATE, 'FMDAY, DD MONTH, YYYY') FROM DUAL;

Options B, C, and D do not achieve the desired output:

• Option B is incorrect because TO_DATE converts a string to a date, not format a date.
• Option C and D have incorrect format strings that do not match the required output format.