Updatable views in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of updating a simple updatable view

Given a table Employees with columns id, name, and salary, and a view HighEarners defined as SELECT id, name, salary FROM Employees WHERE salary > 50000, what will be the result of this update?

UPDATE HighEarners SET salary = salary + 5000 WHERE id = 3;

Assume employee with id = 3 has a salary of 60000 before the update.

MySQL

CREATE TABLE Employees (id INT PRIMARY KEY, name VARCHAR(50), salary INT);
INSERT INTO Employees VALUES (1, 'Alice', 45000), (2, 'Bob', 52000), (3, 'Charlie', 60000);
CREATE VIEW HighEarners AS SELECT id, name, salary FROM Employees WHERE salary > 50000;

A The salary of employee with id 3 remains 60000; no change occurs.

B The update fails because views cannot be updated.

C The update changes salary in HighEarners view only, Employees table remains unchanged.

D The salary of employee with id 3 becomes 65000 in Employees and HighEarners view.

💡 Hint

Think about whether the view is updatable and if changes reflect in the base table.

Since the view selects directly from Employees without aggregation or joins, it is updatable. Updating the view updates the underlying Employees table. So, employee 3's salary increases by 5000.

🧠 Conceptual

intermediate

Which views are not updatable?

Which of the following types of views in MySQL are generally not updatable?

A Views that include only columns from one table with no WHERE clause.

B Views that select directly from a single table without joins.

C Views that use aggregate functions like SUM or COUNT.

D Views that select all columns from a single table.

💡 Hint

Think about what happens when a view summarizes data.

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

📝 Syntax

advanced

Identify the syntax error in creating an updatable view

Which option contains a syntax error when creating an updatable view in MySQL?

MySQL

CREATE VIEW ActiveUsers AS SELECT id, username FROM Users WHERE status = 'active';

A CREATE VIEW ActiveUsers AS SELECT id, username FROM Users WHERE status = 'active' WITH CHECK OPTION;

B CREATE VIEW ActiveUsers AS SELECT id, username FROM Users WHERE status = 'active' WITH UPDATE OPTION;

C CREATE VIEW ActiveUsers AS SELECT id, username FROM Users WHERE status = 'active' WITH READ ONLY;

D CREATE VIEW ActiveUsers AS SELECT id, username FROM Users WHERE status = 'active';

💡 Hint

Check which clauses are valid in MySQL view definitions.

MySQL supports WITH CHECK OPTION but does not support WITH UPDATE OPTION. Using WITH UPDATE OPTION causes a syntax error.

❓ optimization

advanced

Optimizing updates through views with joins

Consider a view defined as:
CREATE VIEW EmpDept AS SELECT e.id, e.name, d.dept_name FROM Employees e JOIN Departments d ON e.dept_id = d.id;

Which statement about updating the EmpDept view is true?

A You can update the employee's name through the view, but not the department name.

B You cannot update any columns through this view because it uses a join.

C You can update both employee name and department name through the view.

D You can update the department name but not the employee name through the view.

💡 Hint

Think about which table columns the view maps to and MySQL's rules for updatable views with joins.

MySQL allows updates through views with joins only if the update affects columns from one table and the join keys are preserved. Updating employee name is allowed; updating department name is not.

🔧 Debug

expert

Why does this update on a view fail?

Given the view:
CREATE VIEW RecentOrders AS SELECT order_id, customer_id, order_date FROM Orders WHERE order_date >= '2024-01-01' WITH CHECK OPTION;

Attempting to run:
UPDATE RecentOrders SET order_date = '2023-12-31' WHERE order_id = 101;

causes an error. What is the most likely reason?

A The update violates the view's WHERE condition, so MySQL rejects it.

B Views with WHERE clauses are never updatable in MySQL.

C The order_date column is read-only in all views.

D The order_id column is not a primary key, so update fails.

💡 Hint

Consider what happens if the update changes a row so it no longer meets the view's filter.

MySQL rejects updates that would cause rows to no longer satisfy the view's WHERE clause when the view is created with WITH CHECK OPTION.