DDL (Data Definition Language)

DDL (Data Definition Language) consists of SQL commands used to define, modify, and delete database structures like tables, schemas, and indexes. These commands affect the database schema but do not manipulate the actual data.

The primary DDL commands in SQL are:

1. CREATE
2. ALTER
3. DROP
4. TRUNCATE
5. RENAME

---

1. CREATE Command

The CREATE command is used to create a new database object, such as a table, view, or index.

Syntax for Creating a Table:

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

Example:

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(50),
    Age INT,
    Course VARCHAR(50)
);

Here, we create a table named Students with four columns:

• StudentID: Integer, primary key (unique and not null).
• Name: Stores student names (up to 50 characters).
• Age: Stores student age (integer).
• Course: Stores the course name (up to 50 characters).

---

2. ALTER Command

The ALTER command is used to modify an existing table structure, such as adding, modifying, or deleting columns.

Syntax to Add a Column:

ALTER TABLE table_name 
ADD column_name datatype constraints;

Example:

ALTER TABLE Students 
ADD Email VARCHAR(100);

This adds a new column Email to the Students table.

Syntax to Modify a Column:

ALTER TABLE table_name 
MODIFY column_name new_datatype;

Example:

ALTER TABLE Students 
MODIFY Age SMALLINT;

This changes the data type of the Age column from INT to SMALLINT.

Syntax to Drop a Column:

ALTER TABLE table_name 
DROP COLUMN column_name;

Example:

ALTER TABLE Students 
DROP COLUMN Email;

This removes the Email column from the Students table.

---

3. DROP Command

The DROP command is used to permanently delete a database object, such as a table, database, or index.

Syntax:

DROP TABLE table_name;

Example:

DROP TABLE Students;

This completely deletes the Students table along with all its data.

Syntax to Drop a Database:

DROP DATABASE database_name;

Example:

DROP DATABASE CollegeDB;

This deletes the CollegeDB database permanently.

⚠ Warning: The DROP command permanently removes the object and cannot be undone.

---

4. TRUNCATE Command

The TRUNCATE command is used to remove all records from a table but keeps the table structure intact. It is faster than DELETE because it does not log individual row deletions.

Syntax:

TRUNCATE TABLE table_name;

Example:

TRUNCATE TABLE Students;

This removes all records from the Students table but retains the table structure.

Difference between DELETE and TRUNCATE:

• DELETE removes specific records and can be rolled back using ROLLBACK.
• TRUNCATE removes all records and cannot be rolled back.

---

5. RENAME Command

The RENAME command is used to change the name of an existing table or other database objects.

Syntax:

RENAME TABLE old_table_name TO new_table_name;

Example:

RENAME TABLE Students TO StudentDetails;

This renames the Students table to StudentDetails.

---

Summary of DDL Commands:

Command	Purpose
CREATE	Creates a new table, database, or other objects.
ALTER	Modifies an existing table (add, modify, or delete columns).
DROP	Deletes a table, database, or object permanently.
TRUNCATE	Removes all records from a table but keeps the structure.
RENAME	Renames an existing table.

These commands are essential for database design and schema management. Understanding them helps in creating and modifying databases efficiently. 🚀