DDL commands in SQL - Data Definition Language

SQL

DDL commands which means Data Definition Language commands are SQL statements that allow you to manage the structure of a database. These commands enable the creation, modification, and deletion of database objects such as tables and schemas. Let’s delve into the key DDL commands:

Different types of DDL commands

There are five types of DDL commands:

  1. CREATE Command
  2. DROP Command
  3. ALTER Command
  4. TRUNCATE Command
  5. RENAME Command

Here are the examples of these commands

1. CREATE Command

Creating a Database

The CREATE DATABASE command is used to create a new database. Here’s an example:

CREATE DATABASE example_database;

Creating a Table

The CREATE TABLE command is used to create a new table within a database. Below is an example:

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(255),
    age INT
);

Table: students

student_idnameage

2. DROP Command

Dropping a Database

The DROP DATABASE command is employed to delete an existing database. Here’s an example:

DROP DATABASE example_database;

Dropping a Table

The DROP TABLE command is used to delete an existing table. Below is an example:

DROP TABLE students;

3. ALTER Command

Altering a Table

The ALTER TABLE command allows you to modify the structure of an existing table. Below are examples:

-- Adding a new column
ALTER TABLE students
ADD COLUMN grade CHAR(1);

-- Modifying a column
ALTER TABLE students
MODIFY COLUMN age INT;

Original Table: students

student_idnameage

Table after Adding Column: students

student_idnameagegrade

Table after Modifying Column: students

student_idnamegrade

4. TRUNCATE Command

Truncating a Table

The TRUNCATE TABLE command is used to delete all rows from a table while retaining the structure for future use:

TRUNCATE TABLE students;

5. RENAME Command

Renaming a Table

The RENAME TABLE command helps you rename an existing table:

RENAME TABLE old_table TO new_table;