DDL (CREATE, ALTER, DROP) in DBMS Theory - Step-by-Step Execution

Concept Flow - DDL (CREATE, ALTER, DROP)

Start

↓

Choose DDL Command

↓

CREATE

↓

Define

↓

New Object

↓

Execute Command

↓

Object Structure Updated

↓

End

The flow starts by choosing a DDL command: CREATE to make new objects, ALTER to change existing ones, or DROP to remove them, then executes and updates the database structure.

Execution Sample

DBMS Theory

CREATE TABLE Students (ID INT, Name VARCHAR(50));
ALTER TABLE Students ADD COLUMN Age INT;
DROP TABLE Students;

This sequence creates a table, adds a column, then deletes the table.

Analysis Table

Step	Command	Action	Result
1	CREATE TABLE Students (ID INT, Name VARCHAR(50));	Create new table named Students with columns ID and Name	Table Students created with 2 columns
2	ALTER TABLE Students ADD COLUMN Age INT;	Add new column Age to Students table	Students table now has columns ID, Name, Age
3	DROP TABLE Students;	Remove the Students table from database	Students table deleted
4	-	No more commands	Execution ends

💡 All commands executed; database structure updated accordingly.

State Tracker

Database Object	Start	After Step 1	After Step 2	After Step 3
Students Table	Does not exist	Exists with columns: ID, Name	Exists with columns: ID, Name, Age	Does not exist

Key Insights - 3 Insights

Why does the Students table not exist after the DROP command?

Because the DROP command removes the entire table from the database, as shown in execution_table step 3 where the table is deleted.

Can ALTER add a column to a table that does not exist?

No, ALTER only works on existing tables. In the execution_table, step 2 shows ALTER adding a column after the table was created in step 1.

Does CREATE overwrite an existing table?

No, CREATE fails if the table already exists unless specified otherwise. Here, the table is created first, so no overwrite occurs.

Visual Quiz - 3 Questions

Look at the execution_table at step 2, what change happens to the Students table?

A The table is renamed

B A new column Age is added

C The table is deleted

D A column is removed

💡 Hint

Check the 'Action' and 'Result' columns in execution_table row 2.

At which step does the Students table stop existing?

A Step 1

B Step 2

C Step 3

D Step 4

💡 Hint

Look at the 'Result' column in execution_table step 3.

If the DROP command was omitted, what would be the final state of the Students table?

A Table would have columns ID, Name, and Age

B Table would have columns ID and Name only

C Table would not exist

D Table would be renamed

💡 Hint

Refer to variable_tracker after step 2 showing columns present.

Concept Snapshot

DDL commands change database structure:
- CREATE makes new objects (tables, etc.)
- ALTER changes existing objects (add/remove columns)
- DROP deletes objects
Use these to manage database schema safely.

Full Transcript

This visual execution shows how DDL commands CREATE, ALTER, and DROP work step-by-step. First, CREATE makes a new table called Students with two columns. Then ALTER adds a new column Age to that table. Finally, DROP removes the Students table entirely. The execution table tracks each command's action and result, while the variable tracker shows the table's state after each step. Key moments clarify that DROP deletes the table, ALTER only works on existing tables, and CREATE does not overwrite existing tables. The quiz tests understanding of these changes by referencing the execution steps and variable states.

Practice

1. What does the CREATE statement do in a database?

easy

A. It makes a new table to store data.

B. It deletes an existing table and its data.

C. It changes the data inside a table.

D. It backs up the database.

Solution

1. Step 1: Understand the purpose of CREATE

   The CREATE statement is used to make new database objects, such as tables, to store data.

2. Step 2: Compare with other statements

   DROP deletes tables, ALTER changes structure, but CREATE makes new tables.

3. Final Answer:

   It makes a new table to store data. -> Option A

4. Quick Check:

   CREATE = Make new table [OK]

Hint: CREATE always makes new tables, not deletes or changes [OK]

Common Mistakes:

• Confusing CREATE with DROP which deletes tables
• Thinking CREATE changes existing data
• Assuming CREATE backs up data

2. Which of the following is the correct syntax to add a new column named age of type INT to an existing table students?

easy

A. ALTER students ADD age INT;

B. ALTER TABLE students ADD COLUMN age INT;

C. DROP TABLE students ADD age INT;

D. CREATE TABLE students ADD age INT;

Solution

1. Step 1: Identify correct ALTER syntax

   The correct syntax to add a column is: ALTER TABLE table_name ADD COLUMN column_name datatype;

2. Step 2: Check each option

   ALTER TABLE students ADD COLUMN age INT; matches the correct syntax. Options B and C misuse CREATE and DROP. ALTER students ADD age INT; misses TABLE keyword.

3. Final Answer:

   ALTER TABLE students ADD COLUMN age INT; -> Option B

4. Quick Check:

   ALTER TABLE + ADD COLUMN = ALTER TABLE students ADD COLUMN age INT; [OK]

Hint: ALTER TABLE ... ADD COLUMN ... is the correct form [OK]

Common Mistakes:

• Omitting TABLE keyword after ALTER
• Using CREATE or DROP instead of ALTER
• Forgetting the COLUMN keyword (optional but clearer)

3. Consider the following SQL commands executed in order:
CREATE TABLE employees (id INT, name VARCHAR(50));
ALTER TABLE employees ADD COLUMN salary INT;
What will be the structure of the employees table after these commands?

medium

A. Table with columns: id, name, salary

B. Table with columns: id, salary

C. Table with columns: id, name

D. Table with columns: name, salary

Solution

1. Step 1: Analyze CREATE TABLE command

   The first command creates a table named employees with columns id and name.

2. Step 2: Analyze ALTER TABLE command

   The second command adds a new column salary to the employees table.

3. Final Answer:

   Table with columns: id, name, salary -> Option A

4. Quick Check:

   CREATE + ALTER ADD COLUMN = id, name, salary [OK]

Hint: ALTER ADD COLUMN adds new columns to existing tables [OK]

Common Mistakes:

• Ignoring the ALTER command effect
• Assuming DROP was used to remove columns
• Thinking ALTER replaces columns instead of adding

4. Identify the error in the following SQL statement:
ALTER TABLE employees DROP salary;

medium

A. Table name is incorrect

B. DROP cannot be used with ALTER TABLE

C. Missing COLUMN keyword after DROP

D. salary column does not exist

Solution

1. Step 1: Understand DROP syntax in ALTER TABLE

   To remove a column, syntax requires: ALTER TABLE table_name DROP COLUMN column_name;

2. Step 2: Check the given statement

   The statement misses the COLUMN keyword after DROP, causing syntax error.

3. Final Answer:

   Missing COLUMN keyword after DROP -> Option C

4. Quick Check:

   ALTER TABLE DROP COLUMN required [OK]

Hint: DROP COLUMN must be used, not just DROP [OK]

Common Mistakes:

• Omitting COLUMN keyword after DROP
• Confusing DROP TABLE with DROP COLUMN
• Assuming DROP alone removes columns

5. You want to permanently delete a table named orders and all its data from the database. Which SQL command should you use?

hard

A. ALTER TABLE orders DELETE;

B. ALTER TABLE orders DROP;

C. CREATE TABLE orders;

D. DROP TABLE orders;

Solution

1. Step 1: Understand the purpose of DROP TABLE

   DROP TABLE deletes the entire table and its data permanently from the database.

2. Step 2: Evaluate the options

   DROP TABLE orders; uses correct syntax. ALTER TABLE orders DROP; and D misuse ALTER. CREATE TABLE orders; creates a table instead of deleting.

3. Final Answer:

   DROP TABLE orders; -> Option D

4. Quick Check:

   DROP TABLE = delete table permanently [OK]

Hint: Use DROP TABLE to delete tables permanently [OK]

Common Mistakes:

• Using ALTER instead of DROP to delete tables
• Confusing CREATE with DROP
• Omitting TABLE keyword in DROP statement