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

Concept Flow - Dropping and altering views

Start

↓

Check if view exists

Yes↓

Drop or Alter view

↓

Confirm changes

↓

End

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

Execution Sample

SQL

DROP VIEW IF EXISTS employee_view;

CREATE OR REPLACE VIEW employee_view AS
SELECT id, name, department FROM employees;

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

Execution Table

Step	Action	Condition/Evaluation	Result/Output
1	Check if view 'employee_view' exists	View exists	Proceed to drop view
2	Execute DROP VIEW IF EXISTS employee_view;	Drop command runs	View 'employee_view' dropped
3	Execute CREATE OR REPLACE VIEW employee_view AS ...	Create or replace view	View 'employee_view' created with new definition
4	Query SELECT * FROM employee_view;	Select from view	Returns id, name, department columns from employees table
5	End	No more commands	Process complete

💡 All commands executed; view dropped and recreated successfully

Variable Tracker

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

Key Moments - 2 Insights

Why do we use DROP VIEW IF EXISTS instead of just DROP VIEW?

Using DROP VIEW IF EXISTS avoids errors if the view does not exist, as shown in step 1 and 2 of the execution_table.

What happens if we use CREATE OR REPLACE VIEW on a view that does not exist?

CREATE OR REPLACE VIEW creates the view if it does not exist, or replaces it if it does, as shown in step 3.

Visual Quiz - 3 Questions

Look at the execution_table, what is the state of 'employee_view' after step 2?

A View exists with old definition

B View created with new definition

C View dropped (does not exist)

D View altered but still exists

💡 Hint

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

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

A Step 2

B Step 3

C Step 1

D Step 4

💡 Hint

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

If we remove IF EXISTS from the DROP VIEW command and the view does not exist, what would happen?

A An error occurs stopping execution

B The view is created instead

C The command runs successfully anyway

D Nothing happens, silently ignored

💡 Hint

Consider the purpose of IF EXISTS in step 1 and 2 of execution_table

Concept Snapshot

DROP VIEW IF EXISTS view_name;
-- Safely drops a view if it exists

CREATE OR REPLACE VIEW view_name AS
SELECT ...;
-- Creates or updates a view definition

Use DROP VIEW IF EXISTS to avoid errors if view missing.
CREATE OR REPLACE VIEW updates or creates the view.

Full Transcript

This lesson shows how to drop and alter views in SQL. First, we check if the view exists. If it does, we drop it using DROP VIEW IF EXISTS to avoid errors if it doesn't exist. Then, we create or replace the view with a new definition using CREATE OR REPLACE VIEW. This ensures the view is updated or created fresh. The execution table traces these steps, showing the view's state changes. Key points include using IF EXISTS to prevent errors and understanding that CREATE OR REPLACE VIEW works whether the view exists or not.