View limitations in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding View Update Restrictions

Which of the following is a common limitation when trying to update data through a MySQL view?

A You cannot update a view if it has no WHERE clause.

B You cannot update a view if it is created with the SELECT * syntax.

C You cannot update a view if it contains a GROUP BY clause.

D You cannot update a view if it references only one table.

💡 Hint

Think about how aggregation affects the ability to identify individual rows.

Views that use GROUP BY aggregate rows, so MySQL cannot determine which underlying rows to update.

❓ query_result

intermediate

Result of Selecting from a View with a Join

Given two tables employees and departments, and 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;

What will be the output of SELECT * FROM emp_dept WHERE dept_name = 'Sales';?

MySQL

CREATE TABLE employees (id INT, name VARCHAR(20), dept_id INT);
CREATE TABLE departments (id INT, name VARCHAR(20));
INSERT INTO employees VALUES (1, 'Alice', 10), (2, 'Bob', 20), (3, 'Carol', 10);
INSERT INTO departments VALUES (10, 'Sales'), (20, 'HR');
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;
SELECT * FROM emp_dept WHERE dept_name = 'Sales';

A [{id: 1, name: 'Alice', dept_name: 'Sales'}, {id: 3, name: 'Carol', dept_name: 'Sales'}]

B [{id: 2, name: 'Bob', dept_name: 'Sales'}]

C []

D [{id: 1, name: 'Alice', dept_name: 'HR'}, {id: 3, name: 'Carol', dept_name: 'HR'}]

💡 Hint

Check which employees belong to the Sales department by matching dept_id.

The view joins employees with departments. Employees with dept_id 10 belong to Sales, so Alice and Carol appear.

📝 Syntax

advanced

Identifying Invalid View Definition

Which of the following CREATE VIEW statements will cause a syntax error in MySQL?

A CREATE VIEW v AS SELECT id, name FROM users GROUP BY id;

B CREATE VIEW v AS SELECT id, name FROM users WHERE id > 10;

C CREATE VIEW v AS SELECT id, name FROM users WHERE name LIKE 'A%';

D CREATE VIEW v AS SELECT id, name FROM users ORDER BY name;

💡 Hint

MySQL views do not allow ORDER BY without LIMIT.

MySQL does not allow ORDER BY in view definitions unless combined with LIMIT.

❓ optimization

advanced

Performance Impact of Views with Subqueries

Consider a view defined with a subquery that calculates the average salary per department. Which of the following statements about performance is true?

A Views are virtual and subqueries inside them run every time the view is queried, potentially slowing performance.

B Views with subqueries are optimized by MySQL to run subqueries only once per query.

C Views always store data physically, so subqueries run only once.

D Views with subqueries automatically create indexes to speed up queries.

💡 Hint

Think about how views behave internally in MySQL.

MySQL views are virtual tables; subqueries inside them execute each time the view is queried, which can slow down performance.

🔧 Debug

expert

Diagnosing an Error When Updating a View

You have a view defined as:

CREATE VIEW v AS SELECT id, name, salary * 1.1 AS increased_salary FROM employees;

When you try to run UPDATE v SET increased_salary = 5000 WHERE id = 1;, you get an error. What is the cause?

A You cannot update a view without a WHERE clause.

B You cannot update a view that contains calculated columns like salary * 1.1.

C You must use the base table name in the UPDATE statement, not the view name.

D The view must include all columns from the base table to allow updates.

💡 Hint

Think about what parts of a view can be updated.

Views with calculated columns cannot be updated because MySQL cannot map the update back to the base table column.