Updatable views and limitations in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is an updatable view in SQL?

An updatable view is a virtual table that allows you to insert, update, or delete rows through it, and these changes affect the underlying base tables.

beginner

Name one common limitation of updatable views.

Views that use joins, aggregates, or groupings often cannot be updated because the database cannot clearly map changes back to the original tables.

intermediate

Can you update a view that contains a GROUP BY clause?

No, views with GROUP BY clauses are generally not updatable because the data is aggregated and does not directly map to individual rows in the base tables.

intermediate

What happens if you try to update a view that includes a join of multiple tables?

Most databases do not allow updates on views with joins because it is unclear which base table should be updated, leading to ambiguity.

advanced

How can you make a non-updatable view updatable?

You can create INSTEAD OF triggers on the view to define custom actions for insert, update, or delete operations, making the view behave as updatable.

Which of the following views is usually updatable?

A A view with a GROUP BY clause

B A view with DISTINCT keyword

C A view joining multiple tables

D A view selecting columns from a single table without aggregates

Views selecting from a single table without aggregates are typically updatable because changes map directly to the base table.

Why are views with joins often not updatable?

A Because the database cannot decide which base table to update

B Because they contain too many columns

C Because they use indexes

D Because they are read-only by default

Joins create ambiguity about which underlying table should be updated, so most databases disallow updates on such views.

What SQL feature can make a non-updatable view behave like an updatable one?

A DISTINCT keyword

B GROUP BY clause

C INSTEAD OF triggers

D Indexes

INSTEAD OF triggers let you define custom update, insert, or delete actions on views, enabling updates even if the view is complex.

Can you update a view that uses aggregate functions like SUM or COUNT?

A Yes, always

B No, because aggregates do not map to individual rows

C Yes, but only in MySQL

D Only if the view has a primary key

Aggregate functions summarize data, so updates cannot be mapped back to individual rows in the base tables.

Which of these is NOT a limitation for updatable views?

A Selecting from a single table

B Using DISTINCT

C Using GROUP BY

D Using joins

Selecting from a single table without complex clauses usually allows the view to be updatable.

Explain what makes a view updatable and list common limitations that prevent a view from being updatable.

Think about how the database knows where to apply changes.

Describe how INSTEAD OF triggers can help with updating views that are normally not updatable.

Triggers act like middlemen for view updates.