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.