Download Full Version 1z0-071 Oracle Exam

Understanding Oracle's data dictionary views is crucial for database management:

Option C: USER_TABLES displays all tables owned by the current user.

USER_TABLES is a view that shows all tables owned by the user executing the query.

Option F: All users can query DBA_TABLES successfully.

While all users can attempt to query DBA_TABLES, only users with the appropriate privileges can retrieve information from it. This option is tricky; "querying" is possible for all, but "retrieving data" depends on privileges.

Options A, B, D, and E are not entirely correct:

Option A and Option E are incorrect because USER_TABLES shows tables regardless of SELECT privileges.

Option B is incorrect; one does not need SELECT privileges on each table to view them in DBA_TABLES, but they need appropriate system privileges to access DBA_TABLES.

Option D is incorrect as ALL_TABLES displays tables accessible to the current user, not just owned by them.

The requirements specify that we need to select book titles based on a price condition and a date condition, and then sort the results by the date of purchase.

A. This query will not execute successfully because there is a syntax error in the price condition. It should be price < 500 OR price > 1000.

B. This query is incorrect. The requirement specifies that the price must be less than 500 or more than 1000, not in a list of values.

C. This query is correct. It selects books where the price is not between 500 and 1000 (thus, less than 500 or more than 1000) and the purchase date is before January 17, 2007, ordered by purchase date in descending order.

D. This query is incorrect because it selects books with prices between 500 and 1000, which is not what the requirement specifies.

In Oracle SQL, substitution variables (&1) can be used to dynamically replace a value in a SQL statement at runtime. To avoid being prompted for a value when executing a query that includes a substitution variable:

A. Use the DEFINE command before executing the query: By setting a value for a substitution variable using the DEFINE command, Oracle will use this value instead of prompting the user for input.

D. Execute the SET VERIFY OFF command before executing the query: The SET VERIFY OFF command suppresses the prompt for substitution variables by turning off the verification feature. This makes Oracle use the currently defined value for the variable.

References:

Oracle Database SQL Language Reference 12c, particularly the sections on substitution variables, the DEFINE command, and the SET command.

Assuming the statement involves creating a new table SALES1 from the existing SALES table:

B. SALES1 created with 55,000 rows: If the statement involved creating SALES1 by selecting all rows from SALES (such as with a CREATE TABLE AS SELECT operation), then all 55,000 rows would be copied if no WHERE clause limited the selection.

E. SALES1 has NOT NULL constraints on any selected columns which had those constraints in the SALES table: When creating a table using the CREATE TABLE AS SELECT syntax, any NOT NULL constraints on columns in the original table will also apply to the new table.

Incorrect options:

A: PRIMARY KEY and UNIQUE constraints are not automatically copied with a CREATE TABLE AS SELECT; they must be explicitly redefined.

C: The table is created with rows if the original SELECT statement (assumed here) selected rows.

D: The statement about creating a table with 1 row is incorrect based on the assumed full selection of the original table's rows.