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.

Practice

1. What does the DROP VIEW statement do in MySQL?

easy

A. Deletes an existing view from the database

B. Creates a new view in the database

C. Updates the data inside a view without deleting it

D. Renames an existing view

Solution

1. Step 1: Understand the purpose of DROP VIEW

   The DROP VIEW command is used to remove a view completely from the database.

2. Step 2: Differentiate from other commands

   Creating or updating views uses CREATE VIEW or ALTER VIEW, not DROP VIEW.

3. Final Answer:

   Deletes an existing view from the database -> Option A

4. Quick Check:

   DROP VIEW = delete view [OK]

Hint: DROP VIEW removes the view completely from the database [OK]

Common Mistakes:

• Confusing DROP VIEW with ALTER VIEW
• Thinking DROP VIEW updates data inside the view
• Assuming DROP VIEW renames the view

2. Which of the following is the correct syntax to drop a view named employee_view?

easy

A. DELETE VIEW employee_view;

B. DROP employee_view;

C. DROP VIEW employee_view;

D. REMOVE VIEW employee_view;

Solution

1. Step 1: Recall the correct DROP VIEW syntax

   The correct syntax to delete a view is DROP VIEW view_name;.

2. Step 2: Check each option

   DROP VIEW employee_view; matches the correct syntax exactly. Others use invalid keywords like DELETE or REMOVE.

3. Final Answer:

   DROP VIEW employee_view; -> Option C

4. Quick Check:

   DROP VIEW + view name = correct syntax [OK]

Hint: Use DROP VIEW followed by the view name to delete it [OK]

Common Mistakes:

• Omitting the VIEW keyword
• Using DELETE or REMOVE instead of DROP
• Adding extra semicolons inside the command

3. Given the view definition:

CREATE VIEW active_users AS SELECT id, name FROM users WHERE status = 'active';

What will be the result after running:

ALTER VIEW active_users AS SELECT id, name FROM users WHERE status = 'inactive';

and then querying SELECT * FROM active_users;?

medium

A. It will show users with status 'active'

B. It will cause a syntax error

C. It will show all users regardless of status

D. It will show users with status 'inactive'

Solution

1. Step 1: Understand ALTER VIEW effect

   The ALTER VIEW statement changes the view's query to the new SELECT statement.

2. Step 2: Analyze the new query condition

   The new view selects users where status = 'inactive', so querying the view returns inactive users.

3. Final Answer:

   It will show users with status 'inactive' -> Option D

4. Quick Check:

   ALTER VIEW changes query = shows inactive users [OK]

Hint: ALTER VIEW changes the view's query to the new SELECT statement [OK]

Common Mistakes:

• Assuming ALTER VIEW appends to the old query
• Expecting a syntax error from ALTER VIEW
• Thinking the view still shows old data after alteration

4. You try to run the command:

ALTER VIEW sales_summary AS SELECT product_id, SUM(quantity) FROM sales GROUP BY product_id;

but get an error. What is the most likely cause?

medium

A. You cannot use GROUP BY in a view

B. The view sales_summary does not exist

C. ALTER VIEW requires DROP VIEW first

D. The view name is invalid

Solution

1. Step 1: Check prerequisites for ALTER VIEW

   ALTER VIEW requires the specified view to already exist in the database.

2. Step 2: Rule out other causes

   GROUP BY is allowed in views, no need to DROP first, and sales_summary is valid.

3. Final Answer:

   The view sales_summary does not exist -> Option B

4. Quick Check:

   ALTER VIEW requires existing view [OK]

Hint: Ensure the view exists before ALTER VIEW [OK]

Common Mistakes:

• Thinking GROUP BY is not allowed in views
• Trying to ALTER VIEW without dropping it first
• Assuming view names cause this error

5. You have a view customer_orders that shows customer names and their order counts. You want to update it to also show the total order amount. Which sequence of commands correctly updates the view without losing it?

hard

A. ALTER VIEW customer_orders AS SELECT name, COUNT(*), SUM(amount) FROM orders GROUP BY name;

B. DROP VIEW customer_orders; CREATE VIEW customer_orders AS SELECT name, COUNT(*), SUM(amount) FROM orders GROUP BY name;

C. UPDATE VIEW customer_orders SET total_amount = SUM(amount);

D. MODIFY VIEW customer_orders AS SELECT name, COUNT(*), SUM(amount) FROM orders GROUP BY name;

Solution

1. Step 1: Identify how to update a view

   Use ALTER VIEW to change the view's query without dropping it.

2. Step 2: Check the options

   ALTER VIEW customer_orders AS SELECT name, COUNT(*), SUM(amount) FROM orders GROUP BY name; uses ALTER VIEW with the new SELECT statement including SUM(amount). DROP VIEW customer_orders; CREATE VIEW customer_orders AS SELECT name, COUNT(*), SUM(amount) FROM orders GROUP BY name; drops and recreates, which loses the view temporarily. The other options use invalid commands.

3. Final Answer:

   ALTER VIEW customer_orders AS SELECT name, COUNT(*), SUM(amount) FROM orders GROUP BY name; -> Option A

4. Quick Check:

   ALTER VIEW updates view query safely [OK]

Hint: Use ALTER VIEW to update view queries without dropping [OK]

Common Mistakes:

• Dropping view unnecessarily before recreating
• Using UPDATE or MODIFY which are invalid for views
• Not including GROUP BY when using aggregation