1z0-071 Questions Bank

Performance implications related to non-equijoin SQL statements in Oracle Database are often a topic of optimization:

• C. The join syntax used makes no difference to performance: In Oracle Database, the performance of a query involving joins is typically more dependent on factors like the underlying data distribution, indexes, optimizer statistics, and system configuration rather than the syntax (ANSI vs Oracle traditional syntax). The optimizer in Oracle is sophisticated enough to interpret different syntactical expressions of joins and optimize them accordingly.

References:

• Oracle Database Performance Tuning Guide 12c, which discusses the impact of different join syntaxes and how Oracle's optimizer handles them.

A. Correct. Foreign key constraints can be nullable. This allows the possibility of a row not having a link to another table. B. Incorrect. Unique key constraints do not allow multiple rows to have null values unless the constraint is defined on multiple columns. C. Incorrect. Primary key columns must be NOT NULL and unique across the table. D. Incorrect. Primary or unique key values do not refer to foreign keys; it's the foreign keys that refer to primary or unique keys. E. Correct. Foreign key constraints enforce referential integrity by requiring that each foreign key value matches a primary or unique key value in the related table.

These are standard rules in relational databases, which can be confirmed in the Oracle Database Concepts Guide.

• A: True, the name of a column can be changed in Oracle using the ALTER TABLE ... RENAME COLUMN command.
• B: False, you cannot change a column's data type from NUMBER to VARCHAR2 if the table contains data, unless the change does not result in data loss or inconsistency.
• C: True, it is possible to change a DATE data type column to TIMESTAMP because TIMESTAMP is an extension of DATE that includes fractional seconds. This operation is allowed if there is no data loss.
• D: False, any column that is not part of a primary key or does not have a non-deferrable constraint can generally be dropped unless it contains data that does not allow for such a change.
• E: False, the DEFAULT clause can be added to a column provided there is no data that contradicts the default value or it doesn't violate any constraints.

These statements are verified against the Oracle Database 12c SQL documentation, specifically the sections on data types, the ALTER TABLE command, and the use of literals in SQL expressions.

The SQL LIKE operator is used in a WHERE clause to search for a specified pattern in a column. Here are the evaluations of the options:

• A: The pattern ' % a % ’ will match any customer names that contain the letter 'a' anywhere in the name, not necessarily those starting with 'Ma'.
• B: This is correct as 'Ma%' will match any customer names that start with 'Ma'.
• C: In SQL, the wildcard character is '%' not '*', therefore, the pattern 'Ma' is incorrect.
• D: This is the correct syntax. 'UPPER (customer_ name ) LIKE 'MA%'' will match customer names starting with 'Ma' in a case-insensitive manner.
• E: Again, '*' is not a valid wildcard character in SQL.
• F: The character '&' is not a wildcard in SQL.
• G: The operator 'KIKE' is a typo, and '.Ma' is not valid SQL pattern syntax.