DDL and DML - Definition

Databases are essential for storing and managing data in modern applications. But behind every
well-organized database is a system that defines its structure and handles its data, this is where
DDL and DML come in. Some industry analysts prefer the term Database Management System
(DBMS) over "database" because, technically, a database is just the data store. While a DBMS in
fact combines the data store with the computational logic that defines, manages, and manipulates
that data.

Most DBMSs operate using two core components: Data Definition Language (DDL) and Data
Manipulation Language (DML). Together, DDL and DML handle the computation within a DBMS,
while the database itself stores the data. In a Relational Database Management System (RDBMS),
these operations are carried out using SQL.

DDL commands such as CREATE, ALTER, and DROP define and modify database structures, while
DML commands like SELECT, INSERT, UPDATE, and DELETE manage the data within those
structures.

Understanding the role of DDL and DML in database operations is essential for working with both
transactional and analytical database systems. Read on to explore their roles and applications.

What Is DDL (Data Definition Language)?

Data Definition Language (DDL) consists of SQL commands that establish a database's framework.
It is responsible for setting up structures such as tables and schemas, modifying their design, and
removing them when they are no longer needed.

When setting up a database, DDL commands define tables, indexes, and relationships. These
commands modify structures and remove database objects. Common examples include:

 CREATE: Defines new database objects like tables, indexes, or entire databases.
‍

o CREATE DATABASE my database: Creates a new database.

o CREATE INDEX idx_email ON users(email): Generates an index for faster lookups.

 ALTER: Modifies existing database structures without affecting stored data.

o ALTER TABLE users ADD COLUMN phone VARCHAR(20): Adds a new column to
the users table.

o ALTER TABLE users MODIFY COLUMN name VARCHAR(100): Changes a

 DROP: Deletes database objects permanently.

o DROP TABLE users: Removes the users table and its data.

o DROP INDEX idx_email ON users: Deletes an index. (SQL Server syntax: DROP
INDEX users.idx_email;)

 TRUNCATE: Clears all records from a table without removing its structure.
‍

o TRUNCATE TABLE users: Empties the users table while keeping its schema intact.

What Is DML (Data Manipulation Language)?

Data Manipulation Language (DML) is a component of SQL used to insert, retrieve, modify, and
delete records within a database structure. Unlike DDL, which defines database objects
like tables and indexes, DML manages data within those structures.

The most frequent examples of commands include:

on the other hand, is concerned with managing the actual data stored within the database structures
defined by DDL. It's the language used to insert, retrieve, modify, and delete data records within the
tables. DML commands operate on the data content of the database.

Common DML commands include:

* SELECT: Used to retrieve data from one or more tables.

* INSERT: Used to add new rows of data into a table.

* UPDATE: Used to modify existing data within a table.

* DELETE: Used to remove rows of data from a table.

Similarities:

Despite their different functions, DDL and DML share some key similarities:

* Subsets of SQL: Both DDL and DML are subsets of the Structured Query Language (SQL), the
standard language for managing relational databases.

* Database Interaction: Both are used to interact with a database system, albeit at different levels of
abstraction.

* Essential for Database Management: Both are crucial for the effective creation, maintenance, and
use of a database. DDL provides the necessary structure, while DML allows for the population and
management of the data within that structure.

In essence, DDL provides the foundation and framework of a database, while DML enables the
dynamic handling and utilization of the information stored within that framework. Both are
indispensable tools for anyone working with databases.

differences:

* Purpose:
 * DDL (Data Definition Language): Used to define and manage the structure of the database. Think
of it as setting up the blueprint and framework.

* DML (Data Manipulation Language): Used to manipulate the data within the database structures.
Think of it as working with the actual content.

* What they operate on:

* DDL: Operates on database objects like tables, indexes, schemas, etc.

* DML: Operates on the data records (rows) within those objects.

* Common Commands:

* DDL: CREATE, ALTER, DROP, TRUNCATE, RENAME. These commands build or modify the
containers for data.

* DML: SELECT, INSERT, UPDATE, DELETE. These commands work with the data inside the
containers.

* Transaction Control:

* DDL: Changes are typically permanent and auto-committed.

* DML: Changes can often be rolled back using transaction commands (COMMIT, ROLLBACK).

In short: DDL is for building and changing the database structure, while DML is for adding, changing,
deleting, and retrieving the data inside that structure.