Data Definition Language (DDL)

---

Data Definition Language (DDL) is a subset of SQL (Structured Query Language) used for defining and managing database structures, including tables, indexes, and constraints. Unlike Data Manipulation Language (DML), which deals with data manipulation (SELECT, INSERT, UPDATE, DELETE), DDL deals with the structure or schema of the data.

Common DDL Commands:

CREATE: Used to create database objects.

• CREATE DATABASE: Creates a new database.
• CREATE TABLE: Creates a new table in the database.
• CREATE INDEX: Creates an index on one or more columns of a table.

ALTER: Used to modify an existing database object.

• ALTER TABLE: Allows you to modify an existing table. It could be used to add, delete, or modify columns. You can also use it to add and drop various constraints on an existing table.

DROP: Used to delete an existing database object.

• DROP DATABASE: Deletes an existing database.
• DROP TABLE: Deletes an existing table.
• DROP INDEX: Deletes an index.

TRUNCATE: Removes all records from a table but retains the schema for future use.

Characteristics of DDL:

1. Schema Definition: DDL is primarily concerned with the structure or schema of the database.
2. No Implicit Commit: In some databases, executing a DDL command results in an implicit commit, meaning any transaction in progress is automatically committed once a DDL command is run.
3. Irreversible: Once a DDL command is executed, it cannot be rolled back using the standard transaction control statements. For example, once you drop a table, it’s gone, and the action can’t be undone unless you have a backup.

Examples:

1. Create a new table:

   CREATE TABLE employees (
       employee_id INT PRIMARY KEY,
       first_name VARCHAR(50),
       last_name VARCHAR(50),
       birth_date DATE
   );

2. Add a new column to an existing table:

   ALTER TABLE employees ADD email VARCHAR(100);

3. Drop a table:

   DROP TABLE employees;

Conclusion:

DDL provides the essential commands for setting up the structure of a database. It’s vital for database designers and administrators to understand and effectively use DDL to ensure that the database schema aligns with the requirements of the applications and users it serves. Proper use of DDL also ensures data integrity, performance optimization, and efficient storage.

---