Oracle Database Sql Certified Associate · Free Practice Question Medium
Question 34
View and examine the following available responses.
Which statements are true about performing Data Manipulation Language (DML) operations on a view in Oracle? (Select three.)
-
A
DML operations are not permitted on views that include group functions, a GROUP BY clause, the DISTINCT keyword, or the ROWNUM pseudocolumn.
-
B
Views cannot be used to query rows from an underlying table if the table has a PRIMARY KEY and the PRIMARY KEY columns are not referenced in the defining query of the view.
-
C
The WITH CHECK clause has no effect when deleting rows from the underlying table through the view.
-
D
Insert statements can always be done on a table through a view.
-
E
Views cannot be used to add rows to an underlying table if the table has columns with NOT NULL constraints lacking default values which are not referenced in the defining query of the view.
Reveal correct answers
Correct answers: A, C, E
Explanation
The correct statements about performing DML (Data Manipulation Language) operations on views in Oracle are:
1. Views cannot be used to add rows to an underlying table if the table has columns with NOT NULL constraints lacking default values which are not referenced in the defining query of the view.
True. If the view does not include all columns of the underlying table that are defined as NOT NULL and have no default values, an INSERT operation will fail because the database does not know what values to insert into those NOT NULL columns.
2. DML operations are not permitted on views that include group functions, a GROUP BY clause, the DISTINCT keyword, or the ROWNUM pseudocolumn.
True. Views that include group functions (like
SUM,AVG), GROUP BY, DISTINCT, or ROWNUM are not inherently updatable, and Oracle disallows DML operations (INSERT, UPDATE, DELETE) on them unless an INSTEAD OF trigger is used.
3. The WITH CHECK clause has no effect when deleting rows from the underlying table through the view.
The
WITH CHECK OPTIONonly applies toINSERTandUPDATEoperations to ensure any data added or modified through the view conforms to the view’s definingWHEREclause.It does not apply to
DELETEoperations.This means you can delete rows through the view, even if the rows do not satisfy the view’s
WHEREclause.In that sense, the
WITH CHECK OPTIONhas no effect onDELETE.
False Statements:
Insert statements can always be done on a table through a view.
False. INSERTs through a view are only allowed if certain conditions are met: the view must not include joins, group functions, etc., and all required columns (e.g., NOT NULL without default) must be present in the view.
Views cannot be used to query rows from an underlying table if the table has a PRIMARY KEY and the PRIMARY KEY columns are not referenced in the defining query of the view.
False. You can query data through a view regardless of whether the PRIMARY KEY columns are included. However, excluding PRIMARY KEY columns might affect updatability or uniqueness, not the ability to query.
The following list summarizes the guidelines regulating DML operations on complex views:
DML operations that violate a constraint aren’t permitted.
A value can’t be added to a column containing an arithmetic expression.
DML operations aren’t permitted on non-key-preserved tables.
DML operations aren’t permitted on views that include group functions, a GROUP BY clause, the DISTINCT keyword, or the ROWNUM pseudocolumn.
A. DML operations such as INSERT, UPDATE, and DELETE are not allowed on views that contain group functions, a GROUP BY clause, the DISTINCT keyword, or the ROWNUM pseudocolumn. This restriction is in place to maintain the integrity of the data and prevent ambiguous results.
B. This statement is incorrect because views can be used to query rows from an underlying table even if the table has a PRIMARY KEY. The defining query of the view does not necessarily need to reference the PRIMARY KEY columns as long as the view's query returns valid results.
C. The WITH CHECK OPTION clause is used to ensure that any data modification made through a view complies with the view's defining query. However, this clause has no effect when deleting rows from the underlying table through the view, as it only applies to INSERT and UPDATE operations.
D. While INSERT statements can generally be performed on a table through a view, there are certain restrictions such as the presence of NOT NULL columns without default values that must be considered. It is not always guaranteed that insert statements can be executed on a table through a view.
E. This statement is true because when adding rows to an underlying table through a view, Oracle requires that all columns that do not have default values and are marked as NOT NULL in the table must be included in the defining query of the view to ensure data integrity.
Discussion
Think the marked answer is wrong, or have a better explanation? Share it below — comments appear after review.
