Oracle PL 1z0-071 Dumps PDF

When creating a new table with a query, the statements that are true are:

• A. SALES1 is created with 1 row.This is incorrect actually; the given query with WHERE 1=1 will retrieve all rows from the sales table, not just one row. Therefore, SALES1 will be created with the same number of rows as in the SALES table, assuming there are no WHERE clause conditions limiting the rows.
• D. SALES1 has NOT NULL constraints on any selected columns which had those constraints in the SALES table.This is true. When a table is created using a CREATE TABLE AS SELECT statement, the NOT NULL constraints on the columns in the selected columns are preserved in the new table.

Options B and C are incorrect:

• B is incorrect because the primary key and unique constraints are not carried over to the new table when using the CREATE TABLE AS SELECT syntax.
• C is incorrect in the context of this scenario. However, if interpreted as a standalone statement (ignoring A), C would be the correct description of the outcome since WHERE 1=1 will not filter out any rows.

Indexes are structures that can improve the retrieval time of data from a database table and have certain characteristics:

• A. A SELECT statement can access one or more indices without accessing any tables: If the index contains all the columns needed for the query, Oracle can retrieve the data from the index alone without accessing the table. This is known as an index-only scan.
• D. A UNIQUE index can be altered to be non-unique: It is possible to alter a unique index to become non-unique by using the ALTER INDEX command.
• F. An update to a table can result in updates to any or all of the table's indexes: If the updated column is part of one or more indexes, those indexes will need to be updated to reflect the change. If the updated column is not part of an index, then no index update is required.

References:

• Oracle Database SQL Language Reference 12c, especially sections on index management and the behavior of DML operations with respect to indexes.

For the *TABLES views in Oracle:

• Option C: USER_TABLES displays all tables owned by the current user.
• Option F: All users can query DBA_TABLES successfully.

Options A, B, D, and E are incorrect:

• Option A and Option E are incorrect because ALL_TABLES and USER_TABLES show tables accessible to or owned by the current user, respectively, without requiring individual SELECT privileges.
• Option B is incorrect because DBA_TABLES requires users to have the SELECT ANY TABLE privilege or equivalent, not SELECT privileges on each table.
• Option D is incorrect because ALL_TABLES displays all tables that the current user has access to, not just those owned by them.

Substitution variables in Oracle are used to replace a value dynamically during the execution of SQL statements. The behavior of these variables is well-documented:

• C. A substitution variable prefixed with & always prompts only once for a value in a session: This is true. In a session, when you use a single ampersand (&), SQL*Plus or SQL Developer will prompt for the value the first time the variable is encountered. The value for this variable will then be reused for the remainder of the session unless it is redefined.
• D. A substitution variable can be used with any clause in a SELECT statement: Substitution variables can be placed in any part of a SQL statement, including the SELECT, WHERE, GROUP BY, ORDER BY, etc. They are not limited to any specific clause.

References:

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