Dropping and altering views in MySQL - Step-by-Step Execution

Concept Flow - Dropping and altering views

Start

↓

Check if view exists

Yes↓

Drop or Alter view

↓

View updated or removed

↓

End

This flow shows checking for a view, then dropping or altering it, and finally confirming the change.

Execution Sample

MySQL

DROP VIEW IF EXISTS my_view;
CREATE OR REPLACE VIEW my_view AS
SELECT id, name FROM users WHERE active = 1;

This code drops a view if it exists, then creates or replaces it with a new definition.

Execution Table

Step	Action	Check/Command	Result
1	Check if view 'my_view' exists	SHOW FULL TABLES LIKE 'my_view';	View exists
2	Drop view	DROP VIEW IF EXISTS my_view;	View 'my_view' dropped
3	Create or replace view	CREATE OR REPLACE VIEW my_view AS SELECT id, name FROM users WHERE active = 1;	View 'my_view' created with new definition
4	Verify view	SELECT * FROM my_view;	Returns active users' id and name
5	End	-	Process complete

💡 Process ends after view is dropped and recreated or altered successfully

Variable Tracker

Variable	Start	After Step 2	After Step 3	Final
my_view	Exists	Dropped (does not exist)	Created with new definition	Exists with updated definition

Key Moments - 3 Insights

Why do we use 'DROP VIEW IF EXISTS' before creating or altering a view?

Using 'DROP VIEW IF EXISTS' ensures the view is removed if it exists, preventing errors when creating or replacing it, as shown in execution_table step 2.

What happens if we use 'CREATE OR REPLACE VIEW' without dropping the view first?

'CREATE OR REPLACE VIEW' updates the view if it exists or creates it if not, so dropping first is optional but can be used to clear dependencies, as seen in step 3.

How can we verify that the view was altered correctly?

By running a SELECT query on the view after creation (step 4), we confirm it returns the expected data.

Visual Quiz - 3 Questions

Look at the execution table, what is the state of 'my_view' after step 2?

A The view does not exist

B The view exists with old definition

C The view exists with new definition

D The view is locked

💡 Hint

Check the 'Result' column in step 2 of the execution_table

At which step does the view get created with the new definition?

A Step 1

B Step 2

C Step 3

D Step 4

💡 Hint

Look at the 'Action' and 'Result' columns in the execution_table

If the view did not exist initially, which step would be skipped or have a different result?

A Step 3 would not run

B Step 1 would say 'View does not exist'

C Step 2 would fail

D Step 4 would return an error

💡 Hint

Refer to the 'Check if view exists' action in step 1 of the execution_table

Concept Snapshot

DROP VIEW IF EXISTS view_name;  -- removes view if it exists
CREATE OR REPLACE VIEW view_name AS SELECT ...;  -- creates or updates view
Use DROP to remove views safely.
Use CREATE OR REPLACE to alter views without dropping.
Verify changes by selecting from the view.

Full Transcript

This lesson shows how to drop and alter views in MySQL. First, we check if the view exists. If it does, we drop it using DROP VIEW IF EXISTS to avoid errors. Then, we create or replace the view with a new definition using CREATE OR REPLACE VIEW. Finally, we verify the view by selecting data from it. This process ensures views are updated safely and correctly.

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