Data Definition Language (DDL) Explained

Key Concepts

• CREATE
• ALTER
• DROP

CREATE

The CREATE statement is used to create new databases, tables, or other database objects. It defines the structure of the object, including its name and attributes. For example, to create a new table named "Employees" with columns for "EmployeeID," "Name," and "Department," you would use the following SQL command:

        CREATE TABLE Employees (
            EmployeeID INT PRIMARY KEY,
            Name VARCHAR(100),
            Department VARCHAR(50)
        );
    

ALTER

The ALTER statement is used to modify the structure of an existing database object. This can include adding, deleting, or modifying columns in a table. For instance, to add a new column "Email" to the "Employees" table, you would use:

        ALTER TABLE Employees
        ADD Email VARCHAR(100);
    

To modify the data type of an existing column, such as changing the "Department" column to allow longer names, you would use:

        ALTER TABLE Employees
        MODIFY Department VARCHAR(100);
    

DROP

The DROP statement is used to delete an existing database object, such as a table or a database. This action is irreversible and permanently removes the object and its data. For example, to drop the "Employees" table, you would use:

        DROP TABLE Employees;
    

Examples and Analogies

Consider a library system: the CREATE statement is like setting up a new shelf to store books, defining where each book will go. The ALTER statement is like adding more shelves or changing the size of the shelves to accommodate more books. The DROP statement is like removing a shelf entirely, which means all the books on that shelf are also removed.

In a student records system, the CREATE statement would be used to set up a new table for student information. The ALTER statement could be used to add a new column for student email addresses or to change the data type of an existing column. The DROP statement would be used to delete the entire table if it is no longer needed.

Understanding these DDL commands is essential for managing the structure of a database. By mastering CREATE, ALTER, and DROP, a Database Specialist can effectively design and maintain database schemas that meet the evolving needs of an application.