PDF 1z0-071 Study Guide

In SQL, when you want to count occurrences of null values using the COUNT function, you must remember that COUNT ignores null values. So, if you want to count rows with null list_price, you have to replace nulls with some value that can be counted. This is what the NVL function does. It replaces a null value with a specified value, in this case, 0.

A, C, and D options attempt to count list_price directly where it is null, but this will always result in a count of zero because COUNT does not count nulls. Option B correctly uses the NVL function to convert null list_price values to 0, which can then be counted. The WHERE list_price IS NULL clause ensures that only rows with null list_price are considered.

The SQL documentation confirms that COUNT does not include nulls in its count and NVL is used to substitute a value for nulls in an expression. So option B will give us the correct count of rows with a null list_price.

For the EMPLOYEES table and given that NLS_DATE_FORMAT is set to DD-MON-YY, the query that requires explicit data type conversion is:

D. SELECT join_date FROM employees where join_date > '10-02-2018'; This query compares the JOIN_DATE column of type DATE with a string '10-02-2018'. The string must be explicitly converted to a DATE using the TO_DATE function with a format mask that matches the NLS_DATE_FORMAT or the string literal.

Options A, B, C, and E do not necessarily require explicit data type conversion:

A does not require conversion because adding a number to a number column is a valid operation.

B is syntactically incorrect; the correct function to extract a substring is SUBSTR(join_date, 1, 2) and that would require an explicit conversion since JOIN_DATE is a DATE and SUBSTR expects a string, but it seems to be a typo.

C uses an invalid concatenation operation with the pipe symbols, which could be a typo or misunderstanding, but would not typically require a data type conversion.

E requires implicit data type conversion because adding a number to a date is valid in Oracle, which adds the number as days to the date.

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.

In the context of the MERGE statement, certain syntax rules and logical sequences must be observed:

Option B:

Correct use of the MERGE statement includes specifying the source and target correctly, and handling cases for matched and unmatched rows. This option properly structures the MERGE command with correct conditions for both matching (on prod_id) and actions (insert for unmatched, and delete under certain conditions for matched rows).

Option D:

While this option has a typographical error with "WHEN NOT NATCHED", assuming it's intended as "WHEN NOT MATCHED", it correctly describes the action for inserting into new_prices when no match is found in the target table based on the condition specified (product cost less than 200).

Options A and C contain syntax errors and logical contradictions (such as attempting to both update and delete in the same matched clause without proper separation or conditions), which are not allowed in a single WHEN MATCHED clause in Oracle SQL.