Dropping and altering views in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What happens after dropping a view?

Consider a MySQL database with a view named employee_view. You run the command DROP VIEW employee_view;. What will be the result of trying to select from employee_view immediately after?

MySQL

DROP VIEW employee_view;
SELECT * FROM employee_view;

A SyntaxError due to invalid DROP VIEW syntax

B Error: Table 'employee_view' doesn't exist

C The original data from the underlying table is returned

D An empty result set with zero rows

💡 Hint

Think about what happens to a view after it is dropped and if it still exists to be queried.

After dropping a view, it no longer exists in the database. Trying to select from it causes an error because MySQL treats views like tables for querying, and the view name is no longer valid.

❓ query_result

intermediate

Altering a view with CREATE OR REPLACE

You have a view sales_summary defined as SELECT region, SUM(amount) AS total FROM sales GROUP BY region. You run the following command to change it:
CREATE OR REPLACE VIEW sales_summary AS SELECT region, COUNT(*) AS count FROM sales GROUP BY region;
What will be the output of SELECT * FROM sales_summary; after this change?

MySQL

CREATE OR REPLACE VIEW sales_summary AS SELECT region, COUNT(*) AS count FROM sales GROUP BY region;
SELECT * FROM sales_summary;

A A list of regions with the count of sales records per region

B SyntaxError because CREATE OR REPLACE is not supported in MySQL

C A list of regions with the total sales amount per region

D An empty result set because the view was replaced with no data

💡 Hint

Consider what the new view definition selects and groups by.

The CREATE OR REPLACE VIEW command replaces the old view with the new definition. The new view returns the count of sales records per region, not the sum of amounts.

📝 Syntax

advanced

Identify the syntax error in dropping multiple views

Which of the following DROP VIEW statements is syntactically correct in MySQL to drop multiple views at once?

A DROP VIEW view1; view2; view3;

B DROP VIEW (view1, view2, view3);

C DROP VIEW view1, view2, view3;

D DROP VIEW view1; DROP VIEW view2; DROP VIEW view3;

💡 Hint

Check the syntax for dropping multiple views in one statement.

In MySQL, you can drop multiple views by listing them separated by commas after DROP VIEW. Parentheses are not used, and multiple DROP VIEW statements must be separate commands.

🔧 Debug

advanced

Why does altering a view fail with an error?

You try to alter a view customer_data by running:
ALTER VIEW customer_data AS SELECT id, name FROM customers;
But MySQL returns an error. What is the reason?

MySQL

ALTER VIEW customer_data AS SELECT id, name FROM customers;

A The view must be dropped before recreating it with a new definition

B ALTER VIEW requires the keyword 'RENAME TO' to change the view name

C The SELECT statement must include all columns from the original view

D MySQL does not support ALTER VIEW to change the SELECT statement

💡 Hint

Think about how MySQL handles changing view definitions.

MySQL does not support altering the SELECT statement of a view using ALTER VIEW. To change a view's definition, you must use CREATE OR REPLACE VIEW or drop and recreate the view.

🧠 Conceptual

expert

Effect of dropping a view referenced by another view

Suppose you have two views:
CREATE VIEW view_a AS SELECT * FROM table_x;
CREATE VIEW view_b AS SELECT * FROM view_a WHERE col1 > 10;
If you run DROP VIEW view_a;, what happens when you try to query view_b?

MySQL

DROP VIEW view_a;
SELECT * FROM view_b;

A An error occurs because view_b depends on the dropped view_a

B view_b returns an empty result set

C view_b returns data from table_x filtered by col1 > 10

D view_b is automatically dropped when view_a is dropped

💡 Hint

Consider dependencies between views and what happens when a referenced view is removed.

Since view_b depends on view_a, dropping view_a breaks view_b. Querying view_b after dropping view_a causes an error because its definition references a non-existent view.