Data Definition Language (DDL) in Oracle SQL

Data Definition Language (DDL) is a subset of SQL used to define and manage the structure of database objects. It includes commands to create, alter, and drop database objects such as tables, indexes, and views. Understanding these commands is essential for designing and maintaining a well-structured database.

1. CREATE

The CREATE statement is used to create new database objects such as tables, indexes, and views. It defines the structure of the object, including column names, data types, and constraints.

Example:

Creating a table named 'Employees' with columns for EmployeeID, FirstName, LastName, and HireDate:


        CREATE TABLE Employees (
            EmployeeID NUMBER PRIMARY KEY,
            FirstName VARCHAR2(50),
            LastName VARCHAR2(50),
            HireDate DATE
        );

2. ALTER

The ALTER statement is used to modify the structure of existing database objects. It can be used to add, modify, or delete columns, change data types, and add or drop constraints.

Example:

Adding a new column 'Salary' to the 'Employees' table:


        ALTER TABLE Employees ADD Salary NUMBER;

3. DROP

The DROP statement is used to remove database objects such as tables, indexes, and views. This operation is irreversible and permanently deletes the object and its data.

Example:

Dropping the 'Employees' table:


        DROP TABLE Employees;

4. TRUNCATE

The TRUNCATE statement is used to remove all rows from a table, effectively emptying the table. Unlike the DELETE statement, TRUNCATE does not generate individual row delete operations and cannot be rolled back.

Example:

Truncating the 'Employees' table to remove all rows:


        TRUNCATE TABLE Employees;

5. RENAME

The RENAME statement is used to change the name of an existing database object. This can be useful for renaming tables, columns, or other objects to improve clarity or consistency.

Example:

Renaming the 'Employees' table to 'Staff':


        RENAME Employees TO Staff;

6. COMMENT

The COMMENT statement is used to add descriptive comments to database objects such as tables, columns, and views. These comments can be useful for documenting the purpose and structure of the objects.

Example:

Adding a comment to the 'Employees' table:


        COMMENT ON TABLE Employees IS 'Stores employee information';

Understanding and mastering these DDL commands is essential for effectively managing the structure and documentation of your Oracle database. By using CREATE to define new objects, ALTER to modify existing objects, DROP to remove objects, TRUNCATE to empty tables, RENAME to change object names, and COMMENT to document objects, you can ensure your database remains well-organized and easy to maintain.