Data Definition Language (DDL) in DBMS: Structuring Your Database

Data Definition Language (DDL) in DBMS

Data Definition Language (DDL) in DBMS is a component of SQL (Structured Query Language). DDL in DBMS is utilized in managing database systems (DBMS) to define and oversee all the elements within a database. DDL commands are employed to create, alter, and remove database configurations without affecting the data. Through the application of DDL commands, database frameworks can be Upheld, guaranteeing the arrangement and format of information stored in the database.

In this DBMS Tutorial, we will learn deep down what is the Data Definition Language(DDL), what are DDL commands, the types of DDL commands, create commands, alter commands, update commands, truncate commands, comment commands, and many more.

What is Data Definition Language in DBMS?

In an easy way, Data Definition Language (DDL) is a bunch of SQL commands used to explain and customize the structure of a database management system, such as tables, indexes, and schemas. It provides several commands to the user by which you can operate the database, such as creating commands, altering commands, and updating commands. Let us learn about Data Definition Language Commands first.

What are Data Definition Language(DDL) Commands in DBMS?

The structure of database objects is defined and managed using DDL (Data Definition Language) commands. This is a succinct explanation:

• CREATE: Generates new indexes and tables in a database.
• ALTER: Changes an object's existing structure.
• DROP: Permanently deletes database objects.
• TRUNCATE: A table's structure is preserved when all of its rows are removed.
• RENAME: Changes the name of tables or columns in a database.

You have four types of DDL commands that are:

1. Create Command
2. Alter COmmand
3. Drop Command
4. Truncate Command
5. Comment Command
6. Rename Command

If you are using the DDL commands, you should remember that DDL commands don't change or experiment with the data that is stored in the database.

1. CREATE Command

You can use the Create Command in the SQL to create the table, create the index, and create the view. These SQL commands result in the newly formatted table.

Create Command Syntax

You can use this syntax to make a table in the database:

CREATE TABLE table_name
(
column_1 datatype,
column_2 datatype,
column_3 datatype,
....
);

Example

By following the above syntax, we are creating a table of Students.

CREATE TABLE Students(
    stud_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    admission_date DATE
);

After running the above command, it generates the table of Students:

Students Table:

2. ALTER Command

The following database objects can be reorganized by adding, removing, or changing table columns using the Alter command.

Alter Command Syntax

You can use Alter Command syntax to alter a table in the database:

ALTER TABLE table_name
ADD column_name datatype;

ALTER TABLE students ADD COLUMN email VARCHAR(100);

You can use the DROP command to permanently delete existing database objects.

DROP Command Syntax

You can use the drop Command syntax to delete an object in the database:

DROP TABLE table_name;

Example

Here, you can delete the above Student table by using the above Drop Commands.

DROP TABLE students;

TRUNCATE Command Syntax

You can use the TRUNCATE Command syntax to delete all the rows in the database:

TRUNCATE TABLE table_name;

TRUNCATE TABLE Students;

Rename <OLD_TABLENAME> to <NEW_TABLENAME>; 

RENAME Students to Student_details;

We can use the Data Definition Language for creating different types of database objects:

1. Creating Databases: Creating a new database involves defining the database name and establishing its initial structure.

CREATE DATABASE company_db;

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100)
);

CREATE INDEX idx_employee_name ON employees (last_name);

We can use the Data Definition Language to modify the database.

1. Objects Altering Tables: Altering tables allows for adding, dropping, or modifying columns and constraints.

ALTER TABLE employees MODIFY COLUMN email VARCHAR(255);

ALTER TABLE employees RENAME TO staff;

We can also use the DDL to delete the database.

1. Dropping Tables and Databases: Dropping tables and databases removes the objects and their data permanently.

DROP DATABASE company_db;
DROP TABLE departments;

TRUNCATE TABLE departments;

CREATE TABLE Employees (
    employee_id INT NOT NULL,
    employee_name VARCHAR(100) NOT NULL
);

CREATE TABLE Employees (
    employee_id INT UNIQUE,
    employee_email VARCHAR(100) UNIQUE
);

CREATE TABLE Employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL
);

CREATE TABLE Departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100)
);

CREATE TABLE Employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES Departments(department_id)
);

CREATE TABLE Employees (
    employee_id INT,
    employee_age INT CHECK (employee_age >= 18)
);

CREATE TABLE Employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    employee_status VARCHAR(20) DEFAULT 'Active'
);

CREATE TABLE Employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    employee_name VARCHAR(100)
);