5.2 DDL — CREATE, DROP, TRUNCATE, ALTER

Level: 5 | Version: v1.1 | Author: Meptrasoft

Overview

DDL (Data Definition Language) contains SQL commands used to create, change, and remove database structures such as tables.

The four commands covered here are:

Command	Main Purpose
CREATE	Create a new database object
ALTER	Change an existing structure
TRUNCATE	Remove all rows but keep the table
DROP	Remove the database object

DDL mainly works with database structure rather than individual row values.

CREATE

CREATE is used to create a new database object.

For example:

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50),
    department VARCHAR(50),
    salary NUMERIC(10, 2)
);

This creates the employees table and defines its columns.

CREATE → Create structure

ALTER

ALTER is used to change the structure of an existing database object.

Add a Column

ALTER TABLE employees
ADD COLUMN join_date DATE;

Now the table has an additional join_date column.

Change a Column Type

For example, in PostgreSQL:

ALTER TABLE employees
ALTER COLUMN emp_name TYPE VARCHAR(100);

Note: ALTER syntax can differ between database systems.

ALTER → Change structure

TRUNCATE

TRUNCATE removes all rows from a table while keeping the table itself.

TRUNCATE TABLE employees;

Before:

emp_id	emp_name
1	Ravi
2	Anjali

After:

emp_id	emp_name
(no rows)

The table still exists and can be used again.

TRUNCATE → Remove all rows, keep the table

DROP

DROP removes the database object itself.

DROP TABLE employees;

After DROP, the table no longer exists.

DROP → Remove the structure

CREATE vs ALTER vs TRUNCATE vs DROP

Command	Table Exists After Command?	Rows Remain?	Structure Remains?
`CREATE`	Yes	Depends	Creates structure
`ALTER`	Yes	Yes	Modified
`TRUNCATE`	Yes	No	Yes
`DROP`	No	No	No

Easy Memory Trick

CREATE   → CREATE
ALTER    → CHANGE
TRUNCATE → EMPTY
DROP     → REMOVE

Practice Table Setup

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50),
    department VARCHAR(50),
    salary NUMERIC(10, 2)
);

INSERT INTO employees
(emp_id, emp_name, department, salary)
VALUES
(1, 'Ravi Kumar', 'IT', 45000.00),
(2, 'Anjali Mehta', 'HR', 38000.00),
(3, 'Suresh Rao', 'Finance', 42000.00),
(4, 'Priya Nair', 'IT', 50000.00);

Common Beginner Mistakes

✗ Mistake	✓ Correct Understanding
`DELETE` and `DROP` are the same	`DELETE` removes rows; `DROP` removes the object
`TRUNCATE` removes the table	It removes all rows but keeps the table structure
`ALTER` is used to change row values	`ALTER` changes structure
`CREATE` is used only for tables	It can create different database objects, depending on the DBMS
`DROP` can always be undone with `ROLLBACK`	Behavior depends on the database system; don't assume it can be reversed
All DDL syntax is identical across databases	Some syntax differs between products

Placement Quick Points

CREATE
→ CREATE DATABASE OBJECT

ALTER
→ CHANGE DATABASE STRUCTURE

TRUNCATE
→ REMOVE ALL ROWS
→ KEEP TABLE

DROP
→ REMOVE DATABASE OBJECT

DDL stands for Data Definition Language.
DDL is mainly used to manage database structure.
CREATE creates an object.
ALTER modifies an existing object's structure.
TRUNCATE removes all rows but keeps the table.
DROP removes the table or other database object.
Some DDL behavior varies between database products.

Interview Questions

What is DDL?

DDL is a group of SQL commands used to create and modify database structures.

What is the difference between DROP and TRUNCATE?

DROP removes the table itself, while TRUNCATE removes all rows but keeps the table structure.

What is the difference between DELETE and TRUNCATE?

DELETE removes rows and can use a WHERE condition. TRUNCATE removes all rows from the table and does not use a WHERE condition.

What is ALTER used for?

ALTER is used to modify the structure of an existing database object.

Give examples of DDL commands.

CREATE
ALTER
DROP
TRUNCATE

Does TRUNCATE remove the table?

No. The table structure remains; its rows are removed.

Does DROP remove the data?

Yes. When a table is dropped, the table structure and its stored rows are removed.

Practice & Hands-On Exercises

Using the employees table:

Add a join_date column using ALTER:

ALTER TABLE employees ADD COLUMN join_date DATE;

Display the table structure:

SELECT * FROM employees LIMIT 1;

Remove all rows using TRUNCATE:

TRUNCATE TABLE employees;

Create the table again using CREATE.
Finally, remove the table using DROP:

DROP TABLE employees;

Explain the difference between TRUNCATE and DROP.
Explain the difference between ALTER and UPDATE.

💡 Tip: Test your queries using the in-browser interactive runner above.

Key Takeaway

CREATE   → CREATE STRUCTURE
ALTER    → CHANGE STRUCTURE
TRUNCATE → REMOVE ALL ROWS
DROP     → REMOVE STRUCTURE

DDL commands manage database structure, with CREATE creating objects, ALTER modifying them, TRUNCATE emptying tables, and DROP removing objects.

End of DDL — CREATE, DROP, TRUNCATE, ALTER