Updatable views and limitations in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of updating a simple updatable view

Consider a table Employees with columns id, name, and salary. A view EmpView is created as SELECT id, name, salary FROM Employees. What will be the result of this query after executing UPDATE EmpView SET salary = salary + 1000 WHERE id = 2;?

Assume the original Employees table data is:

id | name  | salary
---+-------+--------
1  | Alice | 5000
2  | Bob   | 6000
3  | Carol | 5500

What will be the salary of Bob after the update?

SQL

CREATE TABLE Employees (id INT PRIMARY KEY, name VARCHAR(50), salary INT);
INSERT INTO Employees VALUES (1, 'Alice', 5000), (2, 'Bob', 6000), (3, 'Carol', 5500);
CREATE VIEW EmpView AS SELECT id, name, salary FROM Employees;
UPDATE EmpView SET salary = salary + 1000 WHERE id = 2;
SELECT salary FROM Employees WHERE id = 2;

A 7000

B 6000

C 6500

D Error: View is not updatable

💡 Hint

Simple views that directly select from one table without aggregation are usually updatable.

The view EmpView is a simple select from Employees. Updating the view updates the underlying table. Bob's salary increases from 6000 to 7000.

🧠 Conceptual

intermediate

Limitation on updating views with joins

Which of the following statements correctly describes a limitation when trying to update a view that is created by joining two tables?

A You can only update columns from one of the base tables if the DBMS supports it, but not both simultaneously.

B You cannot update any columns in a view created by joining tables.

C You can update columns from both tables freely if the join is an inner join.

D You can update any column from either table through the view without restrictions.

💡 Hint

Think about how the database knows which table to update when multiple tables are joined.

Views created by joining tables are generally not fully updatable. Some DBMS allow updates on columns from one base table only, not both at the same time.

📝 Syntax

advanced

Which view definition is NOT updatable?

Given the following view definitions, which one is NOT updatable according to standard SQL rules?

A CREATE VIEW V1 AS SELECT id, name FROM Employees WHERE salary > 5000;

B CREATE VIEW V2 AS SELECT id, name, salary * 1.1 AS increased_salary FROM Employees;

C CREATE VIEW V3 AS SELECT id, name FROM Employees;

D CREATE VIEW V4 AS SELECT id, name FROM Employees WHERE name LIKE 'A%';

💡 Hint

Consider if the view contains computed columns or expressions.

View V2 contains a computed column salary * 1.1. Views with computed columns are not updatable because the database cannot map updates back to the base table column.

🔧 Debug

advanced

Why does this update on a view fail?

Consider the view and update statement below:

CREATE VIEW DeptEmp AS
SELECT e.id, e.name, d.department_name
FROM Employees e JOIN Departments d ON e.department_id = d.id;

UPDATE DeptEmp SET department_name = 'Sales' WHERE id = 3;

The update fails with an error. Why?

A Because the update statement syntax is incorrect.

B Because the view does not include the primary key of Employees.

C Because the view includes a join, the DBMS cannot determine which base table to update for the department_name column.

D Because the department_name column is not updatable due to a missing WHERE clause.

💡 Hint

Think about how updates work on joined views.

Views with joins are generally not updatable for columns from the joined table unless INSTEAD OF triggers or rules are defined. The DBMS cannot decide how to update the department_name column in Departments table through this view.

🧠 Conceptual

expert

Which condition prevents a view from being updatable?

Which of the following conditions will prevent a view from being updatable in most SQL database systems?

A The view is created with a single table and no computed columns.

B The view selects all columns from a single base table without any filters.

C The view is created with a simple WHERE clause filtering rows.

D The view contains aggregate functions like SUM or COUNT.

💡 Hint

Think about what aggregate functions do to rows.

Views with aggregate functions summarize data and do not represent individual rows in the base table, so they cannot be updated directly.

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