Data Definition Language (DDL) serves as the foundation for managing the structural framework of a relational database. These SQL commands are responsible for defining, modifying, and removing schema objects such as databases, tables, indexes, and views.

The CREATE Command

The CREATE keyword facilitates the instantiation of new database objects. It is essential for initializing the environment and establishing data storage containers.

To establish a new database schema:

CREATE DATABASE IF NOT EXISTS project_inventory
    DEFAULT CHARACTER SET utf8mb4
    DEFAULT COLLATE utf8mb4_unicode_ci;

When defining a table structure, the syntax allows for specifying columns, data types, and constraints:

CREATE TABLE IF NOT EXISTS user_accounts (
    account_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email_address VARCHAR(100),
    account_status ENUM('active', 'suspended', 'pending') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Additional objects created via this command include views, stored procedures, functions, triggers, and indexes.

The DROP Command

The DROP statement is used to permanently remove database objects and the data they contain. It is a destructive operation and should be used with caution.

To delete an entire database schema:

DROP DATABASE IF EXISTS legacy_archive;

To remove specific tables:

DROP TEMPORARY TABLE IF EXISTS temp_session_data;

The ALTER Command

Structural modifications to existing databases or tables are handled by the ALTER statement. This command supports changing definitions, adding columns, or modifying constraints.

Modifying database properties, such as character sets:

ALTER DATABASE project_inventory
    CHARACTER SET = latin1
    COLLATE = latin1_swedish_ci;

Modifying a table structure, such as adding a new column or changing a primary key, requires specific sequencing to avoid dependency errors. For example, when modifying a primary key on a table with an auto-incrementing column, the auto-increment attribute must typically be removed first.

ALTER TABLE user_accounts
    ADD COLUMN last_login DATETIME,
    MODIFY COLUMN username VARCHAR(75);

SQL Command Categories

SQL operations are generally classified into four distinct groups based on their function:

• DDL (Data Definition Language): Defines database structures (CREATE, ALTER, DROP).
• DML (Data Manipulation Language): Manages data within existing structures (INSERT, UPDATE, DELETE).
• DCL (Data Control Language): Governs access rights and permissions (GRANT, REVOKE).
• DQL (Data Query Language): Retrieves data from the database (SELECT).