3.3 Data Definition Language (DDL)

Data Definition Language (DDL) is a subset of SQL (Structured Query Language) used to define, manage, and modify the structure of database objects such as tables, indexes, and schemas. The key DDL commands are CREATE, ALTER, DROP, and TRUNCATE.

Key DDL Commands

Sample Examples of DDL Commands

1. CREATE Command

The CREATE command is used to create new tables, databases, indexes, or other objects.

Example: Creating a Table

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    hire_date DATE,
    department_id INT
);

    

Explanation: This command creates a new table named employees with columns employee_id, first_name, last_name, hire_date, and department_id. The employee_id column is the primary key, and first_name and last_name are mandatory fields (NOT NULL).

2. ALTER Command

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

Example: Adding a New Column

ALTER TABLE employees
ADD email VARCHAR(100);

    

Explanation: This command adds a new column named email to the employees table.

Example: Modifying a Column

ALTER TABLE employees
MODIFY COLUMN first_name VARCHAR(100);

    

Explanation: This command modifies the first_name column to allow up to 100 characters.

3. DROP Command

The DROP command is used to remove an existing database object like a table or database.

Example: Dropping a Table

DROP TABLE employees;

    

Example: Dropping a Database

DROP DATABASE company_db;

    

4. TRUNCATE Command

The TRUNCATE command is used to remove all records from a table, but it does not remove the table structure itself.

Example: Truncating a Table

TRUNCATE TABLE employees;

    

Differences Between TRUNCATE and DELETE

TRUNCATE: Removes all records from a table and resets any auto-increment counters. It is faster and uses fewer resources than DELETE.

DELETE: Removes records row by row and can be used with a WHERE clause to delete specific records. It is slower than TRUNCATE for large datasets.

Example: Combining DDL Operations

Creating a New Table and Adding Constraints

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50) NOT NULL,
    location VARCHAR(100),
    budget DECIMAL(15, 2) CHECK (budget >= 0)
);

    

Explanation: This example creates a departments table with columns department_id, department_name, location, and budget. The budget column has a CHECK constraint ensuring that the budget cannot be negative.

Summary

DDL commands are essential for defining and managing the structure of database objects. Key commands include CREATE, ALTER, DROP, and TRUNCATE. DDL operations are typically used by database administrators or developers to design and modify databases and their components.