DDL and DML Commands in DBMS
DDL and DML Commands in SQL – A Complete Guide for Beginners
Structured Query Language (SQL) is the backbone of modern database systems. It allows users to create databases, store information, and manage data efficiently. Among SQL commands, DDL (Data Definition Language) and DML (Data Manipulation Language) are two fundamental categories that every database learner must understand.
This blog explains DDL and DML commands in detail, with examples and real-world relevance.
What Are SQL Commands?
SQL commands are instructions used to interact with a database. They are broadly classified into:
DDL – Data Definition Language
DML – Data Manipulation Language
DCL – Data Control Language
TCL – Transaction Control Language
In this blog, we focus on DDL and DML, which are the most commonly used.
Data Definition Language (DDL)
DDL commands are used to define and manage the structure of database objects. These commands decide how data is stored, not the data itself. Database administrators mainly use DDL while designing the database.
Key Features of DDL
Defines database schema
Affects table structure
Changes are permanent
Automatically committed
Common DDL Commands
1. CREATE
Used to create databases, tables, views, and indexes.
CREATE TABLE Employee (
emp_id INT PRIMARY KEY,
name VARCHAR(50),
department VARCHAR(30),
salary NUMERIC(10,2)
);
This command creates a new table to store employee information.
2. ALTER
Used to modify an existing table structure.
ALTER TABLE Employee ADD email VARCHAR(50);
You can add, delete, or modify columns using ALTER.
3. DROP
Used to delete database objects permanently.
DROP TABLE Employee;
⚠️ Once dropped, both the table and its data are lost.
4. TRUNCATE
Deletes all records from a table but keeps its structure.
TRUNCATE TABLE Employee;
It is faster than DELETE and does not support rollback.
Real-Life Use of DDL
Creating tables for a new application
Modifying schema when business requirements change
Removing unused tables from databases
Data Manipulation Language (DML)
DML commands deal with the actual data stored in tables. These commands allow users to insert, update, delete, and retrieve records.
DML is frequently used by developers and end users to maintain data.
Key Features of DML
Works on table data
Supports transactions
Changes can be rolled back
Requires commit for permanent changes
Common DML Commands
1. INSERT
Adds new records into a table.
INSERT INTO Employee VALUES (101, 'Ravi', 'IT', 45000);
2. SELECT
Retrieves data from the database.
SELECT * FROM Employee;
This command displays all employee records.
3. UPDATE
Modifies existing records.
UPDATE Employee SET salary = 50000 WHERE emp_id = 101;
4. DELETE
Removes specific records from a table.
DELETE FROM Employee WHERE emp_id = 101;
Unlike TRUNCATE, DELETE supports rollback.
Real-Life Use of DML
Adding new users to a system
Updating employee salary details
Deleting inactive accounts
Fetching data for reports
Difference Between DDL and DML
| Feature | DDL | DML |
|---|---|---|
| Full Form | Data Definition Language | Data Manipulation Language |
| Purpose | Defines structure | Manages data |
| Affects | Schema | Records |
| Rollback | Not possible | Possible |
| Commit | Auto-commit | Manual |
Why DDL and DML Are Important?
DDL ensures proper database design
DML enables daily data operations
Together, they ensure data integrity, efficiency, and scalability
Essential for developers, DBAs, and data analysts
Conclusion
DDL and DML commands form the foundation of SQL and database management systems. DDL builds the structure, while DML brings the database to life by managing data. Understanding both is essential for working with any relational database system like MySQL, PostgreSQL, or Oracle.
Comments
Post a Comment