SQL DDL and DML Explained Simply with Examples

SQL DDL vs DML: Understanding the Difference with Examples

In SQL (Structured Query Language), Data Definition Language (DDL) and Data Manipulation Language (DML) serve distinct but essential roles in managing a database. This article explains their differences clearly with real-world examples.

1. What is Data Definition Language (DDL)?

Purpose: DDL is used to define and manage the structure of database objects like tables, schemas, indexes, and views.

Key DDL Commands:

CREATE: Creates new database objects
ALTER: Modifies existing database structures
DROP: Deletes existing objects from the database
TRUNCATE: Deletes all data from a table efficiently

DDL Examples:

CREATE TABLE Employees (
  EmployeeID INT PRIMARY KEY,
  Name VARCHAR(100),
  Department VARCHAR(50)
);

ALTER TABLE Employees ADD HireDate DATE;

DROP TABLE Employees;

2. What is Data Manipulation Language (DML)?

Purpose: DML is used to manage and manipulate the data within tables defined by DDL. It includes inserting, updating, querying, and deleting records.

Key DML Commands:

SELECT: Retrieves data from one or more tables
INSERT: Adds new data into tables
UPDATE: Modifies existing data
DELETE: Removes data from tables

DML Examples:

INSERT INTO Employees (EmployeeID, Name, Department, HireDate)
VALUES (101, 'Alice Smith', 'HR', '2023-05-01');

SELECT * FROM Employees WHERE Department = 'HR';

UPDATE Employees SET Department = 'Finance' WHERE EmployeeID = 101;

DELETE FROM Employees WHERE EmployeeID = 101;

3. Summary of Differences

Feature	DDL	DML
Function	Defines database structure	Manipulates data within the structure
Examples	CREATE, ALTER, DROP	SELECT, INSERT, UPDATE, DELETE
Affects	Schema-level objects	Table data (records)
Transaction Log	Auto-committed, usually not reversible	Can be rolled back within a transaction

Conclusion

Understanding the distinction between DDL and DML is essential for anyone working with databases. DDL structures your data storage, while DML allows you to interact with and modify the data. Mastering both provides complete control over database development and management.

Trending Flash News