Online 1z0-071 Questions Video

The WHERE clause in SQL filters the rows returned by the SELECT statement. The result of logical operators and conditions can change significantly depending on the use of parentheses.

Options A and E both use parentheses to ensure that borrowed_date = SYSDATE is evaluated with transaction_type ='RM' as one group and the member_id conditions as another group. The parentheses ensure that both conditions within each set of parentheses must be true for the rows to be included.

Option B is incorrect because it does not use parentheses to enforce the grouping of conditions, leading to potentially different results due to the way logical OR works.

Option C is incorrect because it has a syntax error; it is missing a parenthesis.

Option D is incorrect because it will return rows where either borrowed_date = SYSDATE AND transaction_type ='RM' is true or member_id IN ('A101','A102') is true, which is a broader condition than what's specified in options A and E.

Regarding INTERVAL data types in Oracle Database 12c:

• A. INTERVAL YEAR TO MONTH columns only support monthly intervals within a range of years. This is true. The INTERVAL YEAR TO MONTH data type stores a period of time using years and months.
• E. INTERVAL DAY TO SECOND columns support fractions of seconds. This is true. The INTERVAL DAY TO SECOND data type can store days, hours, minutes, seconds, and fractional seconds.

Options B, C, D, and F are incorrect:

• B is incorrect because data types between INTERVAL DAY TO SECOND and INTERVAL YEAR TO MONTH are not compatible.
• C is incorrect as it incorrectly limits INTERVAL YEAR TO MONTH to a single year.
• D is incorrect; the YEAR field can be negative to represent a past interval.
• F is incorrect as INTERVAL YEAR TO MONTH supports intervals that can span multiple years, not just annual increments.

C. True, the data dictionary is constantly updated to reflect changes to the metadata of the database objects, permissions, and structures, among other things.E. True, the SYS user owns all base tables in the data dictionary. These base tables underlie all data dictionary views that are accessible by the users.

A, B, and D are not correct because: A. Base tables do not necessarily have the prefix DBA_; instead, DBA_ is a prefix for administrative views that are accessible to users with DBA privileges. B. The data dictionary records metadata about the actions, not the actions themselves. D. Not all users have access to all information in the data dictionary. Access is controlled by privileges.

References:

• Oracle documentation on data dictionary and dynamic performance views: Oracle Database Reference
• Understanding the Oracle data dictionary: Oracle Database Concepts

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.