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.

Practice

1. Which of the following best describes an updatable view in SQL?

easy

A. A view created using multiple tables joined together.

B. A view that only shows data but does not allow any changes.

C. A view that contains aggregate functions like SUM or COUNT.

D. A view that allows you to insert, update, or delete rows through it.

Solution

1. Step 1: Understand what an updatable view is

   An updatable view lets you change data (insert, update, delete) through the view as if it were a table.

2. Step 2: Compare options to definition

   Options B, C, and D describe views that are either read-only or complex and usually not updatable.

3. Final Answer:

   A view that allows you to insert, update, or delete rows through it. -> Option D

4. Quick Check:

   Updatable view = allows data changes [OK]

Hint: Updatable views let you change data through them [OK]

Common Mistakes:

• Thinking all views allow data changes
• Confusing aggregate views as updatable
• Assuming joins always allow updates

2. Which SQL syntax correctly creates a simple updatable view on a single table employees showing id and name?

easy

A. CREATE VIEW emp_view AS SELECT id, name FROM employees GROUP BY id;

B. CREATE VIEW emp_view AS SELECT id, name, COUNT(*) FROM employees;

C. CREATE VIEW emp_view AS SELECT id, name FROM employees;

D. CREATE VIEW emp_view AS SELECT id, name FROM employees JOIN departments ON employees.dept_id = departments.id;

Solution

1. Step 1: Identify simple view syntax

   CREATE VIEW emp_view AS SELECT id, name FROM employees; selects columns directly from one table without grouping or joins, which is valid for an updatable view.

2. Step 2: Check other options for complexity

   Options A and C use GROUP BY or aggregate functions, making views non-updatable. CREATE VIEW emp_view AS SELECT id, name FROM employees JOIN departments ON employees.dept_id = departments.id; uses JOIN, also usually non-updatable.

3. Final Answer:

   CREATE VIEW emp_view AS SELECT id, name FROM employees; -> Option C

4. Quick Check:

   Simple select from one table = updatable view syntax [OK]

Hint: Simple SELECT without joins or aggregates creates updatable views [OK]

Common Mistakes:

• Adding GROUP BY or aggregates in view
• Using JOINs in view definition
• Forgetting to select from only one table

3. Given the view definition:

CREATE VIEW dept_salary AS SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id;

Which of the following statements is true when trying to update avg_salary through this view?

medium

A. You can update avg_salary directly and it will change salaries in employees.

B. You cannot update avg_salary because the view uses aggregation.

C. Updating avg_salary will update all salaries in the department equally.

D. The view allows updates only if you use INSTEAD OF triggers.

Solution

1. Step 1: Analyze the view's use of aggregation

   The view uses AVG() and GROUP BY, which creates a summary, not individual rows.

2. Step 2: Understand update limitations on aggregated views

   Aggregated columns like avg_salary cannot be updated directly because they do not map to single rows.

3. Final Answer:

   You cannot update avg_salary because the view uses aggregation. -> Option B

4. Quick Check:

   Aggregated views are not updatable by default [OK]

Hint: Aggregated views block direct updates [OK]

Common Mistakes:

• Trying to update aggregate columns
• Assuming updates affect underlying rows automatically
• Ignoring need for triggers on complex views

4. You have a view defined as:

CREATE VIEW emp_dept AS SELECT e.id, e.name, d.name AS dept_name FROM employees e JOIN departments d ON e.dept_id = d.id;

Attempting to update dept_name through this view causes an error. What is the main reason?

medium

A. Views with JOINs are not updatable by default.

B. You cannot update columns with aliases in views.

C. The view lacks a WHERE clause to filter rows.

D. The view must include all columns from both tables to be updatable.

Solution

1. Step 1: Identify the view's structure

   The view uses a JOIN between employees and departments tables.

2. Step 2: Recall update limitations on joined views

   Views involving JOINs are generally not updatable because the database cannot determine which table to update.

3. Final Answer:

   Views with JOINs are not updatable by default. -> Option A

4. Quick Check:

   JOIN views block updates unless special rules apply [OK]

Hint: JOINs in views block updates by default [OK]

Common Mistakes:

• Thinking alias columns block updates
• Believing WHERE clause affects updatability
• Assuming all columns must be selected

5. You want to create an updatable view that allows changes to employee names and salaries but also shows department names. Which approach is best?

hard

A. Create a view joining employees and departments, then use INSTEAD OF triggers to handle updates.

B. Create a simple view on employees only and join departments in queries outside the view.

C. Create a view with GROUP BY department to summarize salaries and allow updates.

D. Create a view with aggregate functions and update the aggregates directly.

Solution

1. Step 1: Understand the requirement

   You want to update employee data but also show department names, which requires joining tables.

2. Step 2: Recognize limitations of joined views

   Joined views are not updatable by default, but INSTEAD OF triggers can enable updates by defining custom behavior.

3. Step 3: Evaluate other options

   Create a simple view on employees only and join departments in queries outside the view. avoids joins in the view but does not show department names in the view itself. Options C and D use aggregates, which block updates.

4. Final Answer:

   Create a view joining employees and departments, then use INSTEAD OF triggers to handle updates. -> Option A

5. Quick Check:

   Use triggers to update joined views [OK]

Hint: Use INSTEAD OF triggers to update joined views [OK]

Common Mistakes:

• Trying to update aggregates directly
• Ignoring triggers for complex views
• Using simple views that lack needed columns