Data Definition Language (DDL)

Post Views: 0

CREATE TABLE Student (
    ID INT,
    Name VARCHAR(50)
);

CREATE TABLE Student (

Name VARCHAR(50)

CREATE TABLE Test (id INT);
ROLLBACK;

1 2	CREATE TABLE Test (id INT); ROLLBACK;

CREATE TABLE Employee (...);
CREATE VIEW Emp_View AS SELECT * FROM Employee;

1 2	CREATE TABLE Employee (...); CREATE VIEW Emp_View AS SELECT * FROM Employee;

CREATE TABLE Student (
    Student_ID INT,
    Name VARCHAR(100),
    Marks INT
);

CREATE TABLE Student (

Student_ID INT,

Name VARCHAR(100),

CREATE TABLE Employee (
    Emp_ID INT PRIMARY KEY,
    Emp_Name VARCHAR(50) NOT NULL,
    Salary INT
);

CREATE TABLE Employee (

Emp_ID INT PRIMARY KEY,

Emp_Name VARCHAR(50) NOT NULL,

Example – Add Column
ALTER TABLE Student
ADD Address VARCHAR(200);
Example – Modify Column
ALTER TABLE Student
MODIFY Name VARCHAR(150);
Example – Rename Column
ALTER TABLE Student
RENAME Marks TO Score;
Example – Drop Column
ALTER TABLE Student
DROP Score;
ALTER modifies structure without deleting the table.

Example – Add Column

ALTER TABLE Student

ADD Address VARCHAR(200);

Example – Modify Column

ALTER TABLE Student

MODIFY Name VARCHAR(150);

Example – Rename Column

ALTER TABLE Student

RENAME Marks TO Score;

Example – Drop Column

ALTER TABLE Student

ALTER modifies structure without deleting the table.

DROP TABLE Test;
ROLLBACK;

1 2	DROP TABLE Test; ROLLBACK;

CREATE TABLE Users (
    User_ID INT PRIMARY KEY,
    Email VARCHAR(100) UNIQUE,
    Age INT CHECK (Age > 18)
);

CREATE TABLE Users (

User_ID INT PRIMARY KEY,

Email VARCHAR(100) UNIQUE,

Age INT CHECK (Age > 18)

CREATE INDEX idx_emp ON Employee(Emp_ID);

1	CREATE INDEX idx_emp ON Employee(Emp_ID);

DROP TABLE Employee;

1	DROP TABLE Employee;

Language	Purpose
DDL	Defines structure
DML	Modifies data
DQL	Retrieves data
DCL	Controls access
TCL	Manages transactions

Kishor Kumar

Share this post

Tags

Data Definition Language (DDL)

Introduction

DDL is responsible for defining the structure of the database, including tables, columns, data types, constraints, and schema-level objects. Before data can be stored or manipulated, the database structure must be properly defined. This structural definition is achieved using DDL commands.

In real-world database systems, DDL operations are typically performed by database designers, database administrators, and backend developers. A well-designed database structure improves performance, maintains data integrity, and ensures scalability of applications.

Meaning of Data Definition Language

DDL stands for Data Definition Language. It is a subset of SQL used to create, modify, and delete database objects.

DDL commands do not deal with the actual data stored inside tables. Instead, they focus on:

● How data is organized

● How tables are structured

● How relationships are formed

● How database schemas are maintained

In simple terms, DDL defines the “skeleton” or “framework” of the database.

Example: If a database does not have tables, data cannot be stored. DDL helps in creating such tables before inserting data.

For example:

This command creates only the table structure, not the data.

Characteristics of DDL

The major characteristics of DDL are:

1. Structure-Oriented: DDL deals only with the database structure, not with data manipulation.

2. Auto-Commit Nature: All DDL commands are automatically committed. Once executed, changes become permanent.

3. Schema-Level Operations; DDL works at the schema level rather than record level.

4. Foundation for Other SQL Commands: Without DDL, DML and DQL operations cannot be performed.

5. Used During Database Design Phase: DDL is primarily used during initial database development.

Example of Auto-Commit

Even after ROLLBACK, the table still exists because DDL commands are auto-commit.

Importance of DDL in Database Systems

DDL is extremely important in relational databases for the following reasons:

Establishes the logical structure of the database

● Maintains uniform data organization

● Supports normalization techniques

● Ensures consistency across applications

● Enables enforcement of business rules

● Simplifies maintenance and upgrades

A poorly designed database structure can lead to redundancy, performance issues, and data inconsistency. Hence, DDL plays a crucial role in long-term database reliability.

Example: In a college database:

● Student table

● Course table

● Faculty table

All these tables must be created using DDL before data entry begins.

Database Objects Managed by DDL

DDL commands can be used to manage several database objects:

● Tables

● Views

● Indexes

● Sequences

● Synonyms

● Schemas

● Constraints

Each object serves a specific purpose in database architecture.

Example: DDL can create multiple objects:

Both table and view are database objects created using DDL.

Types of DDL Commands

Data Definition Language consists of five primary commands:

1. CREATE

2. ALTER

3. DROP

4. TRUNCATE

5. RENAME

Each command performs a unique structural operation.

CREATE Command – Conceptual Explanation

The CREATE command is used to create new database objects. It defines the structure of tables by specifying:

● Column names

● Data types

● Column sizes

● Constraints

CREATE is typically the first command used while designing a database.

When a table is created, memory space is allocated and metadata information is stored in the data dictionary

Example – Creating a Table

This creates a student table with three columns.

Example – Creating a Table with Constraints

This ensures uniqueness and prevents null values.

Table Design Considerations

Before creating a table, the following factors must be considered:

● Identification of entities

● Selection of appropriate data types

● Avoiding redundant attributes

● Proper naming conventions

● Determining primary keys

Example: If a database does not have tables, data cannot be stored.
DDL helps in creating such tables before inserting data.

Example; If marks are always numeric, using VARCHAR is incorrect.
Correct design:

Example: If a column datatype is changed incorrectly, existing data may be lost.
Hence ALTER should always be tested first.