DROP TABLE and ALTER TABLE in SQL - Step-by-Step Execution

Concept Flow - DROP TABLE and ALTER TABLE

Start

↓

Check Command Type

↓

DROP

↓

Check if Table Exists

↓

Execute DROP TABLE

↓

Table Removed

↓

End

The flow starts by checking if the command is DROP or ALTER. For DROP, it checks if the table exists and removes it. For ALTER, it checks the table and applies changes like adding or dropping columns.

Execution Sample

SQL

DROP TABLE employees;
ALTER TABLE employees ADD COLUMN age INT;
ALTER TABLE employees DROP COLUMN age;

This code first deletes the 'employees' table, then adds an 'age' column, and finally removes the 'age' column from the 'employees' table.

Execution Table

Step	Command	Check Table Exists	Action	Result
1	DROP TABLE employees;	Yes	Remove table 'employees'	Table 'employees' dropped
2	ALTER TABLE employees ADD COLUMN age INT;	No	Error	Error: Table 'employees' does not exist
3	CREATE TABLE employees (id INT, name VARCHAR(50));	N/A	Create table	Table 'employees' created
4	ALTER TABLE employees ADD COLUMN age INT;	Yes	Add column 'age'	Column 'age' added
5	ALTER TABLE employees DROP COLUMN age;	Yes	Remove column 'age'	Column 'age' dropped
6	DROP TABLE employees;	Yes	Remove table 'employees'	Table 'employees' dropped
7	DROP TABLE employees;	No	Error	Error: Table 'employees' does not exist

💡 Execution stops when commands are completed or error occurs due to missing table.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	After Step 5	After Step 6	After Step 7
Table 'employees'	Exists	Dropped	Dropped (error)	Created	Exists with new column 'age'	Exists without column 'age'	Dropped	Does not exist (error)

Key Moments - 3 Insights

Why does ALTER TABLE fail right after DROP TABLE?

Because after DROP TABLE, the table no longer exists, so ALTER TABLE cannot find it (see execution_table step 2).

What happens if you try to DROP a table that does not exist?

You get an error saying the table does not exist (see execution_table step 7).

Can you add and then remove a column in ALTER TABLE?

Yes, you can add a column and later remove it as shown in steps 4 and 5.

Visual Quiz - 3 Questions

Look at the execution table, what is the result of step 2?

A Table 'employees' dropped

B Column 'age' added

C Error: Table 'employees' does not exist

D Table created

💡 Hint

Check the 'Result' column in execution_table row for step 2.

At which step does the 'employees' table get created?

A Step 1

B Step 3

C Step 4

D Step 6

💡 Hint

Look at the 'Action' column for the step where the table is created.

If you try to DROP a table twice, what happens at the second DROP?

A Error: Table does not exist

B Column is dropped instead

C Table is dropped again successfully

D Table is recreated

💡 Hint

See execution_table step 7 for dropping a non-existent table.

Concept Snapshot

DROP TABLE removes an entire table and its data.
ALTER TABLE changes table structure (add/drop columns).
DROP fails if table doesn't exist.
ALTER fails if table doesn't exist.
Always check table existence before ALTER or DROP.

Full Transcript

This visual execution trace shows how DROP TABLE and ALTER TABLE commands work step-by-step. First, DROP TABLE checks if the table exists and removes it if yes. ALTER TABLE checks for the table and applies changes like adding or dropping columns. If the table does not exist, both commands produce errors. The trace includes creating the table to demonstrate ALTER TABLE success. Key moments highlight common confusions like why ALTER fails after DROP and what happens when dropping a non-existent table. The quiz questions reinforce understanding by referencing specific steps and results.