Commands of DDL Statements with Example

Data Definition Language (DDL) is the structural cornerstone of SQL—it’s how you define, shape, and maintain the architecture of your database. Just like an architect uses blueprints to design buildings, Data Defination commands allow you to define the schema of the application database with precision. In this article we will discuss the commands with DDL example.

Overview of Data Definition Language (Commands of DDL)

Data Definition Language (DDL) is a fundamental subset of Structured Query Language (SQL) used to define and manage the structure of databases and their objects. Think of DDL as the architectural blueprint of a database—it allows developers and database administrators to create, modify, and remove the structural components of a database without manipulating the actual data inside.

Key Purposes of DDL:

• Creating Database Objects: DDL enables the creation of essential database objects, such as:
  • Tables: Organize data in rows and columns.
  • Indexes: Speed up data retrieval.
  • Views: Virtual tables generated from SQL queries.
  • Schemas: Logical containers for grouping related objects.
  • Users and Roles: Define access control and permissions.
• Modifying Database Objects: DDL allows structural changes such as:
  • Adding or removing columns.
  • Altering data types and constraints.
  • Renaming objects to reflect updated business logic.
• Removing Database Objects: Obsolete tables, views, or schemas can be dropped to streamline the schema.
• Defining Data Types: Ensures that each column accepts only valid and appropriate data (e.g., VARCHAR, DATE, DECIMAL).
• Setting Constraints: Enforces business rules and data integrity through constraints like PRIMARY KEY, FOREIGN KEY, NOT NULL, and CHECK.

DDL vs. Other SQL Subsets:

In summary, DDL defines what data structures exist and how they’re organized, while DML and DCL manage the data and access to those structures.

1. DDL Statement CREATE Commands

The CREATE command is used to build new database objects such as tables, indexes, or views. When creating a table, you define its name, columns, and data types, along with optional constraints.

Syntax of DDL Statement CREATE

CREATE TABLE table_name (column_name1 datatype constraints,    
                         column_name2 datatype constraints,
                         ...    
                         table_constraints
                         );

CREATE TABLE table_name (column_name1 datatype constraints,    
                         column_name2 datatype constraints,
                         ...    
                         table_constraints
                         );

Explanation of Key Components:

• CREATE TABLE table_name: Initiates the creation of a new table.
• column_name datatype: Defines each column’s name and the kind of data it will store.
• constraints: Enforce rules on column values (NOT NULL, UNIQUE etc.).
• table_constraints: Rules applied to multiple columns (e.g., composite primary keys).

Example: Online Bookstore

CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    Title VARCHAR(255) NOT NULL,
    Author VARCHAR(100) NOT NULL,
    ISBN VARCHAR(20) UNIQUE NOT NULL,
    PublicationYear INT,
    Genre VARCHAR(50),
    Price DECIMAL(5, 2) NOT NULL CHECK (Price > 0),
    DateAdded TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    Title VARCHAR(255) NOT NULL,
    Author VARCHAR(100) NOT NULL,
    ISBN VARCHAR(20) UNIQUE NOT NULL,
    PublicationYear INT,
    Genre VARCHAR(50),
    Price DECIMAL(5, 2) NOT NULL CHECK (Price > 0),
    DateAdded TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2. DDL Statement ALTER Commands

The ALTER command allows you to change the schema of an existing table, including adding, modifying, or deleting columns and constraints.

3. DDL Statement TRUNCATE: The Quick Eraser

DDL command TRUNCATE is used to delete all rows in a table without affecting its structure. It is faster than DELETE and uses minimal logging.

Syntax of DDL Statement TRUNCATE:

TRUNCATE TABLE table_name;

TRUNCATE TABLE table_name;

Example of DDL Statement TRUNCATE:

TRUNCATE TABLE Books;

TRUNCATE TABLE Books;

Key Differences – TRUNCATE VS DELETE

4. Commands of DDL Statement DROP

The DROP command permanently removes a database object, including its data and structure.

Syntax of DDL Statement DROP

DROP TABLE table_name;

DROP TABLE table_name;

Example of DDL Statement DROP

DROP TABLE BookDetails;

DROP TABLE BookDetails;

Note: Use with caution— DROP operations are irreversible without a backup.

5. DDL Statement RENAME Commands

RENAME changes the name of an existing table, allowing for better naming conventions or evolving business needs.

Syntax of DDL Statement RENAME

RENAME TABLE old_table_name TO new_table_name;

RENAME TABLE old_table_name TO new_table_name;

Example of DDL Statement RENAME Commands

RENAME TABLE CustomerInfo TO Customers;

RENAME TABLE CustomerInfo TO Customers;

6. Commands of DDL GRANT: Assigning Permissions

GRANT is technically categorized under Data Control Language (DCL) in strict SQL standards, but in Oracle it’s often discussed alongside DDL because it defines access to structural objects.

Purpose of DCL Command GRANT

Allows a user or role to perform certain operations on database objects.

Syntax of DCL Command GRANT

GRANT privilege [, privilege...]
ON object_name
TO user_or_role;

Example of DCL Command GRANT

GRANT SELECT, INSERT, UPDATE 
ON Books
TO DEEN;

GRANT SELECT, INSERT, UPDATE 
ON Books
TO DEEN;

This allows the DEEN to read and modify data in the employees table.

7. Commands of DDL: REVOKE (Withdrawing Permissions)

REMOVE removes previously granted privileges to a user/schema.

Syntax of SQL Statement REMOVE:

REVOKE privilege [, privilege...]
ON object_name
FROM user_or_role;

Example of REVOKE:

REVOKE UPDATE 
ON Books
FROM DEEN;

REVOKE UPDATE 
ON Books
FROM DEEN;

This revokes the ability of DEEN to update rows in Books.

8. DDL Commands SYNONYM: Creating Aliases

In Oracle, a SYNONYM is a database object that serves as an alias for another object. This is especially useful to simplify SQL statements or hide the owner/schema name.

Purpose of SQL Command SYNONYM

• Create short, meaningful names for objects.
• Mask schema changes from end users.

Syntax of SQL Command SYNONYM

CREATE [PUBLIC] SYNONYM synonym_name
FOR schema.object_name;

Example of SQL Command SYNONYM

CREATE SYNONYM MyBooks FOR Library.Books;

CREATE SYNONYM MyBooks FOR Library.Books;

Now you can reference MyBooks in queries instead of Library.Books.

To Drop the synonym you need to use below command.

DROP SYNONYM MyBooks;

DROP SYNONYM MyBooks;

For public synonyms:

DROP PUBLIC SYNONYM MyBooks;

DROP PUBLIC SYNONYM MyBooks;

🔗 Optional: COMMENT and INDEX

While often discussed in DDL contexts:

• COMMENT allows adding documentation to objects.
• CREATE INDEX and DROP INDEX help optimize queries.

Examples:

COMMENT ON TABLE Books IS 'Stores the books information';

COMMENT ON TABLE Books IS 'Stores the books information';

CREATE INDEX idx_books_title ON Books (Title);

📌 Next Steps

Important Considerations of DDL Commands

• Implicit Commit: Many systems auto-commit DDL changes, making them permanent.
• Permissions: DDL actions often require elevated privileges.
• Application Impact: Changes to structure may affect code and users—always assess dependencies.
• Backups: Always back up before major structural changes, especially when using DROP.

Conclusion

Understanding and effectively using DDL commands is essential for shaping and evolving a database to match changing business needs. Whether you’re building new tables, modifying existing ones, or cleaning up outdated objects, DDL forms the foundation of your data architecture. Use these powerful commands wisely to ensure a robust and maintainable database system.