DDL in SQL: A Guide to CREATE, DROP, and ALTER Commands

If you’re learning SQL, you’ve likely come across terms like DDL and DML. While DML (Data Manipulation Language) handles the data inside tables, DDL (Data Definition Language) is all about the structure of your database.

In simple terms, DDL commands help you create, modify, or delete the structure of tables, databases, and other schema objects.

In this guide, we’ll focus on the three primary DDL commands in SQL:

• CREATE
• DROP
• ALTER

Each of these commands plays a vital role in managing the blueprint of your database. Let’s explore them one by one, with examples and explanations for beginners.

What is DDL (Data Definition Language)?

DDL stands for Data Definition Language. It’s a subset of SQL commands that define and control the structure of your database objects such as:

• Tables
• Views
• Indexes
• Schemas

Unlike DML, which is used for interacting with the data (like inserting or updating), DDL changes how your database is designed.

Here’s what DDL can do:

• Create new tables and structures
• Modify the structure of existing tables
• Delete unwanted database elements

1. CREATE – Building Tables and Other Objects

The CREATE command is used to create new objects in a database—primarily tables, but also databases, views, indexes, and more.

Syntax for Creating a Table

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    ...
);

Example: Creating a Table

CREATE TABLE Students (
    student_id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    email VARCHAR(100) UNIQUE
);

This SQL statement creates a table called Students with four columns:

• student_id: a unique integer that acts as the primary key
• name: a variable-length string up to 50 characters
• age: an integer value
• email: a unique string for student emails

Other CREATE Examples

• Create a new database:

  CREATE DATABASE School;

• Create an index:

  CREATE INDEX idx_name ON Students(name);

2. DROP – Removing Tables or Databases

The DROP command is used to permanently delete a database object. Be very careful with DROP, as it removes the structure and all the data it contains. There’s no undo!

Syntax for Dropping a Table

DROP TABLE table_name;

Example: Dropping a Table

DROP TABLE Students;

This command completely removes the Students table, along with all its records and structure.

Other DROP Examples

• Drop an entire database:

  DROP DATABASE School;

• Drop an index:

  DROP INDEX idx_name;

⚠️ Important Warning

Always double-check before using DROP. Once a table or database is dropped, all information is lost unless you have a backup.

3. ALTER – Modifying Existing Table Structures

The ALTER command allows you to change the structure of an existing table. You can:

• Add new columns
• Modify data types
• Rename columns
• Remove columns
• Add constraints (like UNIQUE or NOT NULL)

Syntax for Adding a Column

ALTER TABLE table_name
ADD column_name datatype;

Example: Add a Column

ALTER TABLE Students
ADD gender VARCHAR(10);

Now the Students table has an additional column called gender.

Syntax for Modifying a Column

The exact syntax for modifying columns can vary slightly between SQL databases (MySQL, PostgreSQL, etc.), but here’s a general format:

ALTER TABLE Students
MODIFY age TINYINT;

Or in some systems:

ALTER TABLE Students
ALTER COLUMN age TYPE TINYINT;

This changes the age column’s datatype to TINYINT.

Syntax for Dropping a Column

ALTER TABLE Students
DROP COLUMN gender;

This removes the gender column from the table.

Renaming a Column (Example)

Again, syntax can vary:

PostgreSQL:

ALTER TABLE Students
RENAME COLUMN name TO full_name;

MySQL:

ALTER TABLE Students
CHANGE name full_name VARCHAR(50);

Summary Table of DDL Commands

DDL vs DML: Key Differences

Why DDL Matters

Understanding DDL is crucial for:

• Designing effective database structures
• Updating tables to match application needs
• Safeguarding data integrity
• Working with large-scale systems where schema evolves

If you’re a developer, data analyst, or even a student starting with SQL, mastering DDL gives you full control over your database design.

Best Practices with DDL

1. ✅ Always backup your data before using DROP or ALTER.
2. 🧪 Test your commands in a development environment first.
3. 📄 Document schema changes for version control and audits.
4. 💬 Communicate changes with your team before altering live databases.

Conclusion

The DDL commands—CREATE, DROP, and ALTER—are fundamental tools in SQL that help define, structure, and modify your database. While they may seem technical at first, they are surprisingly straightforward once you start practicing.

Whether you’re creating a new table from scratch or adjusting one to fit your evolving needs, these commands give you the control and flexibility to build well-structured, efficient databases.

So go ahead—try creating your own tables, make some structural tweaks, and don’t forget to handle DROP with care. Happy querying!