View limitations in MySQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a view in MySQL?

A view is a virtual table that shows data from one or more tables. It does not store data itself but displays data based on a query.

beginner

Can you perform INSERT, UPDATE, or DELETE on any MySQL view?

No, you can only perform these operations on views if they are updatable. Some views are not updatable due to their complexity or use of certain SQL features.

intermediate

Why can't views with GROUP BY clauses be updated directly?

Because GROUP BY summarizes data, it is unclear how to update individual rows in the original tables from the grouped results.

intermediate

Are views that use UNION or DISTINCT updatable in MySQL?

No, views that use UNION or DISTINCT are not updatable because they combine or filter rows in ways that prevent direct row mapping to base tables.

beginner

What happens if you try to create an index on a MySQL view?

MySQL does not allow creating indexes on views because views do not store data physically.

Which of the following is true about MySQL views?

A Views store data physically in the database.

B Views can always be updated regardless of their query.

C Views can have indexes created on them.

D Views are virtual tables based on queries.

Views are virtual tables that show data based on queries but do not store data themselves.

Why are views with GROUP BY clauses not updatable?

A Because GROUP BY changes the structure of the base tables.

B Because GROUP BY summarizes data making row updates ambiguous.

C Because GROUP BY requires indexes on views.

D Because GROUP BY is not supported in views.

GROUP BY summarizes data, so it is unclear how to update individual rows in the original tables.

Which SQL feature in a view prevents it from being updatable?

A Simple SELECT from one table

B Use of ORDER BY without LIMIT

C Use of UNION or DISTINCT

D Use of WHERE clause

UNION or DISTINCT combine or filter rows in ways that prevent direct updates to the base tables.

Can you create an index on a MySQL view?

A No, because views do not store data.

B Only if the view is updatable.

C Yes, always.

D Only on views with a primary key.

Views do not store data physically, so indexes cannot be created on them.

Which operation is NOT always allowed on a MySQL view?

A INSERT

B SELECT

C JOIN

D WHERE filtering

INSERT is only allowed on updatable views; some views cannot be inserted into.

Explain why some MySQL views are not updatable and give examples of SQL features that cause this limitation.

Think about how the view's query affects the ability to map changes back to base tables.

Describe what a view is in MySQL and list at least two limitations related to modifying data through views.

Focus on the nature of views and their interaction with data modification.