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.

Practice

1. Which of the following is true about MySQL views?

easy

A. Views always allow you to update data directly.

B. Views do not store data themselves; they show data from base tables.

C. Views can have indexes to speed up queries.

D. Views automatically create backup copies of data.

Solution

1. Step 1: Understand what a view is and check each option

   A view is a virtual table that shows data from one or more base tables but does not store data itself. Views do not store data themselves; they show data from base tables. matches the definition. Options A, C, and D are incorrect because views do not always allow updates, cannot have indexes, and do not store backup data.

2. Final Answer:

   Views do not store data themselves; they show data from base tables. -> Option B

3. Quick Check:

   Views are virtual tables = B [OK]

Hint: Remember: Views show data, they don't store it [OK]

Common Mistakes:

• Thinking views store data like tables
• Assuming views always allow updates
• Believing views can have indexes

2. Which of the following is the correct syntax to create a simple view in MySQL?

easy

A. CREATE VIEW AS SELECT * FROM table_name;

B. CREATE TABLE view_name AS SELECT * FROM table_name;

C. CREATE VIEW view_name FROM table_name;

D. CREATE VIEW view_name AS SELECT * FROM table_name;

Solution

1. Step 1: Recall the syntax for creating a view and compare options

   The correct syntax is: CREATE VIEW view_name AS SELECT ... CREATE VIEW view_name AS SELECT * FROM table_name; matches the correct syntax. CREATE TABLE view_name AS SELECT * FROM table_name; creates a table, not a view. CREATE VIEW view_name FROM table_name; has incorrect syntax.

2. Final Answer:

   CREATE VIEW view_name AS SELECT * FROM table_name; -> Option D

3. Quick Check:

   CREATE VIEW ... AS SELECT ... = D [OK]

Hint: Use 'CREATE VIEW name AS SELECT...' for views [OK]

Common Mistakes:

• Using CREATE TABLE instead of CREATE VIEW
• Omitting AS keyword
• Incorrect order of clauses

3. Given the view definition:
CREATE VIEW v_emp AS SELECT id, name FROM employees;
Which of the following queries will return the correct result?

medium

A. INSERT INTO v_emp (id, name) VALUES (6, 'Anna');

B. UPDATE v_emp SET name = 'John' WHERE id = 5;

C. SELECT * FROM v_emp WHERE id = 5;

D. DELETE FROM v_emp WHERE id = 5;

Solution

1. Step 1: Understand the view and its limitations, then analyze each query

   The view selects id and name from employees. Simple views on one table allow SELECT but may restrict UPDATE, INSERT, DELETE. SELECT * FROM v_emp WHERE id = 5; is a SELECT query and will work correctly. Options B, C, and D try to modify data through the view, which is often not allowed or restricted.

2. Final Answer:

   SELECT * FROM v_emp WHERE id = 5; -> Option C

3. Quick Check:

   Simple views allow SELECT only = A [OK]

Hint: Views usually allow SELECT, not data changes [OK]

Common Mistakes:

• Trying to update or insert through views without triggers
• Assuming all views are updatable
• Ignoring view restrictions on DELETE

4. You created a view with this statement:
CREATE VIEW v_sales AS SELECT order_id, customer_id, total FROM orders WHERE total > 100;
When you try to update the view with:
UPDATE v_sales SET total = 90 WHERE order_id = 10;
You get an error. What is the likely cause?

medium

A. The view contains a WHERE clause filtering rows, making it non-updatable.

B. You cannot update any view in MySQL.

C. The view does not include the primary key column.

D. The UPDATE syntax is incorrect for views.

Solution

1. Step 1: Identify view update restrictions and check the error cause

   Views with WHERE clauses or filtering conditions are often non-updatable because the database cannot guarantee consistent updates. The view contains a WHERE clause filtering rows, making it non-updatable. explains the error correctly. You cannot update any view in MySQL. is false because some views are updatable. The view does not include the primary key column. is incorrect because order_id is included. The UPDATE syntax is incorrect for views. is wrong as syntax is valid.

2. Final Answer:

   The view contains a WHERE clause filtering rows, making it non-updatable. -> Option A

3. Quick Check:

   WHERE clause in view blocks updates = A [OK]

Hint: Views with filters often block updates [OK]

Common Mistakes:

• Assuming all views are updatable
• Blaming syntax instead of view definition
• Ignoring WHERE clause effect on updates

5. You want to create a view that allows updating the salary column of employees but also shows the department name from a joined table. Which approach respects MySQL view limitations?

hard

A. Create a simple view on employees only, then update salary directly on employees table.

B. Create a view joining employees and departments, then update salary through the view.

C. Create a view with a WHERE clause filtering departments, then update salary through it.

D. Create a view with GROUP BY on departments and update salary through the view.

Solution

1. Step 1: Understand update restrictions on views with joins and choose the approach that allows updates

   Views that join multiple tables or use GROUP BY are generally non-updatable in MySQL. Create a simple view on employees only, then update salary directly on employees table. creates a simple view on one table, allowing updates on salary by updating the base table directly. Options A, B, and D involve joins, filters, or grouping, which block updates.

2. Final Answer:

   Create a simple view on employees only, then update salary directly on employees table. -> Option A

3. Quick Check:

   Simple single-table views allow updates = C [OK]

Hint: Update only through simple single-table views [OK]

Common Mistakes:

• Trying to update through joined or grouped views
• Assuming filters don't block updates
• Ignoring base table updates