Exam 70-762 topic 1 question 152 discussion

Answer is DE

answer: CD

BC A. The view must not include the primary key of the table.: False B. The view may include GROUP BY or HAVING statements.: only columns being modified can't be affected by GROUP BY or HAVING clauses. C. The view may include a composite field.: only affected columns can't be composite D. Each view must reference columns from one table.: it can as long as the update/insert affects one table only. E. The view must not include subqueries.: no such restriction

Given answer is correct and is fully explained in the explanation of the answer. Correct Answer: BD B: The columns being modified must not be affected by GROUP BY, HAVING, or DISTINCT clauses. D: Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table. Incorrect Answers: A: Primary keys are allowed. C: The columns cannot be derived in any other way, such as through the following: A computation. The column cannot be computed from an expression that uses other columns. E: The restrictions apply to any subqueries in the FROM clause of the view, just as they apply to the view itself. References: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql?view=sql-server-2017

The question is indeed tricky and you really cannot answer this until you read the documentation carefully and then confirm it by writing some code. So, let's start from the documenation (https://docs.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql?view=sql-server-ver15#updatable-views) and then exclude the wrong options: A. The view must not include the primary key of the table. This is definitely wrong. Primary keys are not only allowed, but also desirable. If your PK is not generated automatically then how are you going to insert a new record without providing a new PK?

B and C is correct. I wrote code to test all the answers. A is incorrect because you can include the primary key. B is correct because you can include gropu by fields as long as you don't attempt to update it. C is correct because you can include a composite field as long as you don't attempt to update it. D is incorrect because the view can include fields from multiple tables as long as the fields you are updating are from a single table. E is incorrect because you can include subqueries as long as you're updating fields from a single table only. All of these points were tested in sample views.

okay, all MAY answers are correct. PK - wrong, D - this rule is for DML, not for the view :) E - subqueries should follow the the same rules, all of them here - https://docs.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql?view=sql-server-2017 updateable views section

Answer D and E The view is defined based on one and only one table. 2. The view must include the PRIMARY KEY of the table based upon which the view has been created. 3. The view should not have any field made out of aggregate functions. 4. The view must not have any DISTINCT clause in its definition. 5. The view must not have any GROUP BY or HAVING clause in its definition. 6. The view must not have any SUBQUERIES in its definitions. 7. If the view you want to update is based upon another view, the later should be updatable. 8. Any of the selected output fields (of the view) must not use constants, strings or value expressions

D is wrong because you do not have to reference just columns from one table in the view aslong as you do not update fields of more tables: ALTER view [dbo].[vwTest] as Select T.ID, discription, comment, discription + comment test, S.object_id from Test T left join sys.objects S On T.id = S.create_date GO Update vwTest set comment = 'bla bla blaa' Where id =2

C is coorrect because this works aslong as the update is not over the computed column ALTER view [dbo].[vwTest] as Select ID, discription, comment, discription + comment test from Test GO Update vwTest set comment = 'bla bla blaa' Where id =2

The columns being modified in the view must directly reference the underlying data in the table columns. The columns cannot be derived in any other way, such as through the following: An aggregate function: AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR, and VARP. A computation. The column cannot be computed from an expression that uses other columns. Columns that are formed by using the set operators UNION, UNION ALL, CROSSJOIN, EXCEPT, and INTERSECT amount to a computation and are also not updatable. The columns being modified are not affected by GROUP BY, HAVING, or DISTINCT clauses. TOP is not used anywhere in the select_statement of the view together with the WITH CHECK OPTION clause. The previous restrictions apply to any subqueries in the FROM clause of the view, just as they apply to the view itself. Generally, the Database Engine must be able to unambiguously trace modifications from the view definition to one base table. For more information, see Modify Data Through a View. https://docs.microsoft.com/en-us/sql/relational-databases/views/modify-data-through-a-view?view=sql-server-ver15

B and D The B and D choices are vague, but the explanation contains the real B and D: B: The columns being modified must not be affected by GROUP BY, HAVING, or DISTINCT clauses. D: Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table. Updatable Views You can modify the data of an underlying base table through a view, as long as the following conditions are true: Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.

Answer BD in correct . Read the updatable view section from below link. https://docs.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql?view=sql-server-ver15

For a view to be updatable, there must be a one-to-one relationship between the rows in the view and the rows in the underlying table. There are also certain other constructs that make a view nonupdatable. To be more specific, a view is not updatable if it contains any of the following: Aggregate functions or window functions (SUM(), MIN(), MAX(), COUNT(), and so forth) DISTINCT GROUP BY HAVING UNION or UNION ALL Subquery in the select list https://dev.mysql.com/doc/refman/8.0/en/view-updatability.html

CE is correct

The B answer conflicts with given answers' explanation