Oracle Updated 1z0-071 Exam Questions and Answers by marlie

For question 138 regarding Oracle views, the correct answers are B, E, and G:

B. The WITH CHECK OPTION clause prevents certain rows from being updated or inserted: This clause ensures that all data modifications performed through the view result in data that conforms to the view's defining query.

E. Deleting one or more rows using a view whose defining query contains a GROUP BY clause will cause an error: Views that involve aggregate functions, GROUP BY clauses, or DISTINCT cannot be directly modified with DML operations such as DELETE, because the underlying data manipulation would be ambiguous.

G. Inserting one or more rows using a view whose defining query contains a GROUP BY clause will cause an error: Similarly to DELETE, INSERT operations into a view that uses a GROUP BY clause are generally not allowed because it's unclear how the new rows should be aggregated into the existing groups.

Other options are incorrect because:

A: The WITH CHECK OPTION clause does not prevent rows from being displayed; it restricts DML operations.

C: Views can be created with non-existent underlying tables or views using the FORCE option in Oracle.

D: Not all views are updatable, especially those involving joins, group functions, or distinct aggregation.

F: Updating a view does not impact the granted privileges unless the definition of the view changes.

In Oracle Database 12c, it is important to understand the behavior and properties of constraints.

A. This statement is false. A FOREIGN KEY column in a child table does not need to have the same name as the referenced PRIMARY KEY column in the parent table. What is required is that the data type is the same and that the values in the FOREIGN KEY column correspond to values in the PRIMARY KEY column of the parent table.

B. This statement is false. A table cannot have multiple PRIMARY KEY constraints. By definition, a PRIMARY KEY is a unique identifier for a row in a table, and there can only be one such identifier.

C. This statement is false for the same reasons as B; a table can have only one PRIMARY KEY. However, it can have multiple FOREIGN KEY constraints that reference PRIMARY KEYS in other tables.

D. This is true. PRIMARY KEY and FOREIGN KEY constraints can indeed be specified at the column level with the column definition or at the table level with the ALTER TABLE statement.

E. This is true. A table can have only one PRIMARY KEY constraint because it defines a unique row identifier. However, it can have multiple FOREIGN KEY constraints referencing keys in other tables, allowing for complex relational structures.

F. This statement is false. NOT NULL constraints are always defined at the column level, as they apply to individual columns. They cannot be specified at the table level.

References:

Oracle Documentation on Constraints: https://docs.oracle.com/database/121/SQLRF/clauses002.htm#SQLRF52271

Oracle Documentation on NOT NULL Constraints: https://docs.oracle.com/database/121/SQLRF/clauses002.htm#SQLRF52162

To calculate the date from a given base date with intervals, Oracle allows you to add or subtract intervals from dates. Since the NLS_DATE_FORMAT is set to DD-MM-YYYY, the output is expected to be in that format.

Option B seems to calculate a date that is 3 months and 7 days ahead of October 29, 2019, and then subtracts 360 seconds (which is 6 minutes), resulting in a time that is still within the same day.

Here's how the calculation in option B would work out:

Start date: 29-10-2019

Add 3 months: 29-01-2020

Add 7 days: 05-02-2020

Subtract 360 seconds: Since it's only a few minutes, the date remains 05-02-2020.

However, this does not match the provided result of 02-JAN-2020. We would need to consider the exact amount of time being subtracted or added to find the correct answer.

But upon reviewing the options, they all have various syntax errors such as a missing TO_CHAR function, incorrect quotes, and date formats not matching the session's NLS_DATE_FORMAT. Therefore, we would need to correct these issues to find the right answer.