Oracle PL 1z0-071 Release Date

A. False. Constraints are enforced during INSERT and UPDATE operations, and by the nature of their definition, they impact DELETE operations as well (in the case of referential constraints).

B. False. A column with a foreign key constraint can contain a NULL value unless it is also constrained to be NOT NULL.

C. False. Not all constraints can be defined at the column level. For example, some constraints such as FOREIGN KEY constraints are more commonly defined at the table level.

D. True. A constraint can be disabled regardless of whether the constrained column contains data. However, re-enabling the constraint requires that all data satisfy the constraint rules.

E. True. A column with a UNIQUE constraint can indeed contain a NULL value, as NULL is considered not equal to any value, including itself. This means that multiple rows with NULL values do not violate the UNIQUE constraint.

References:

• Oracle Documentation on Constraints: https://docs.oracle.com/database/121/SQLRF/clauses002.htm#SQLRF52271
• Oracle Documentation on Enabling and Disabling Constraints: https://docs.oracle.com/database/121/ADMIN/clustrct.htm#ADMIN13508

When you need to compare dates and calculate the difference in months between two dates, you use the MONTHS_BETWEEN function in Oracle SQL.

A. This statement is incorrect because MONTHS_BETWEEN takes the later date first, then the earlier date.

B. This statement is correct. The MONTHS_BETWEEN function returns the number of months between two dates. It takes the later date (in this case, SYSDATE) first and the earlier date (start_date) second. If the start_date is within the last 25 months, the result will be less than or equal to 25.

C. This statement is incorrect. The expression TO_NUMBER(start_date - SYSDATE) would not give the correct number of months between the dates.

D. This statement is incorrect. The ADD_MONTHS(start_date, 25) function adds 25 months to start_date, and comparing it to SYSDATE would show dates that are at least 25 months in the future.

When the NLS_DATE_FORMAT is set to DD Mon YYYY, the database expects the date string to be in the format of day, abbreviated month name, and full year.

B. The query SELECT DATE '2019-01-01' FROM DUAL; correctly returns the value 1 Jan 2019 because the ANSI date literal DATE 'YYYY-MM-DD' is independent of the NLS_DATE_FORMAT parameter.

Option A is incorrect because the TO_DATE function requires the format model to match the string literal, which it does not in this case.

Option C is incorrect because TO_CHAR is used to convert a date to a string, not a string to a date.

Option D is incorrect because without specifying that the string is a date, the result is just a string and not a date value.

Option E is incorrect because TO_DATE without a format model relies on the NLS_DATE_FORMAT to interpret the string, and YYYY-MM-DD does not match DD Mon YYYY.

In Oracle SQL, roles can be granted privileges and other roles, and these privileges can then be passed on to users.

• Statement A is correct: GRANT CREATE SEQUENCE TO manager, emp; successfully grants the CREATE SEQUENCE privilege to both the manager and emp roles.
• Statement E is correct: GRANT CREATE ANY SESSION, CREATE ANY TABLE TO manager; successfully grants two system privileges to the manager role.
• Statements B, C, and D are incorrect for the following reasons: