Data Manipulation Language (DML) in SQL

Post Views: 0

INSERT INTO student VALUES (101, 'Rahul', 90);
ROLLBACK;

1 2	INSERT INTO student VALUES (101, 'Rahul', 90); ROLLBACK;

High-Level DML	Low-Level DML
Non-procedural	Procedural
Set-at-a-time	Record-at-a-time
Easy to learn	Complex
SQL statements	PL/SQL, SQL PL
Focus on output	Focus on logic

Syntax
SELECT column_name
FROM table_name
WHERE condition;

Examples
Retrieve all records:
SELECT * FROM students;
Retrieve specific columns:
SELECT name, birthday FROM friends;
Apply condition:
SELECT * FROM students WHERE due_fees <= 20000;
SELECT is the most frequently used SQL command in analytics and reporting.

SELECT column_name

FROM table_name

WHERE condition;

Retrieve all records:

SELECT * FROM students;

Retrieve specific columns:

SELECT name, birthday FROM friends;

Apply condition:

SELECT * FROM students WHERE due_fees <= 20000;

SELECT is the most frequently used SQL command in analytics and reporting.

INSERT INTO Student (sno, sname, sphno)
VALUES (1, 'BHARATH', 9876543210);

1 2	INSERT INTO Student (sno, sname, sphno) VALUES (1, 'BHARATH', 9876543210);

INSERT INTO Student
VALUES (2, 'KIRAN', 9876501234);

1 2	INSERT INTO Student VALUES (2, 'KIRAN', 9876501234);

INSERT INTO Student (sno, sname, sphno)
VALUES (&sno, '&sname', &sphno);

1 2	INSERT INTO Student (sno, sname, sphno) VALUES (&sno, '&sname', &sphno);

Syntax
UPDATE table_name
SET column_name = value
WHERE condition;


Examples
UPDATE students
SET due_fees = 20000
WHERE stu_name = 'Mini';
⚠️ Without WHERE clause, all rows will be updated.

UPDATE table_name

SET column_name = value

WHERE condition;

UPDATE students

SET due_fees = 20000

WHERE stu_name = 'Mini';

⚠️ Without WHERE clause, all rows will be updated.

Syntax
DELETE FROM table_name
WHERE condition;


Examples
DELETE FROM students WHERE stu_id = '001';
⚠️ Omitting WHERE deletes all rows.

DELETE FROM table_name

WHERE condition;

DELETE FROM students WHERE stu_id = '001';

⚠️ Omitting WHERE deletes all rows.

Bharath Kumar

Share this post

Tags

Data Manipulation Language (DML) in SQL

Introduction

What is Data Manipulation Language (DML)?

Data Manipulation Language (DML) is a subset of SQL that allows users to retrieve, insert, update, and delete data stored in relational database tables or views.

DML commands directly operate on the data and enable users to perform day-to-day database operations. If DDL defines what the database looks like, DML defines what happens to the data inside it.

In simple words:

DML is used to manipulate data after the database structure has been created.

Every time an application displays records, updates user information, processes transactions, or removes outdated data, DML commands are executed in the background.

Purpose and Significance of DML

The primary purpose of DML is to make databases dynamic and functional. Without DML, databases would only contain empty tables with no meaningful data.

DML is used to:

● Add new records to tables

● Modify existing records

● Remove unwanted or outdated records

● Retrieve meaningful information for analysis

DML plays a vital role in:

● Data analytics

● Reporting systems

● Real-time applications

● Transaction processing systems

Key Characteristics of DML

Data Manipulation Language has several important characteristics:

1 Works on Existing Data

DML commands operate only on data stored inside tables. They do not affect the table structure.

2 Transaction-Oriented

Each DML operation is treated as a transaction, which ensures data consistency and reliability.

3 Supports Rollback and Commit

● Changes made by DML commands can be:

● Saved permanently using COMMIT

● Undone using ROLLBACK before commit

4 Performance-Oriented

DML commands are optimized for fast data access and modification, even in large databases.

5 Widely Used

DML commands are the most frequently executed SQL commands in real-world applications.

Transaction Control in DML

One of the most powerful features of DML is transaction control. A transaction is a sequence of operations performed as a single logical unit of work.

Example:

The inserted record will be removed because the transaction was rolled back.

Why Transactions Matter:

● Prevent partial updates

● Maintain database consistency

● Handle system failures gracefully

This makes DML safer compared to DDL commands, which are auto-commit.

Types of Data Manipulation Language

DML can be classified into two major types:

1 High-Level or Non-Procedural DML

High-level DML is also known as set-oriented DML. In this type, users specify what data they want without describing how to retrieve it.

Characteristics:

● Easy to use

● Non-procedural

● Focuses on results

● Widely adopted

SQL commands like SELECT, INSERT, UPDATE, and DELETE belong to this category.

Example: SELECT * FROM students;

2 Low-Level or Procedural DML

Low-level DML is also called record-oriented DML. In this approach, users must specify both what data is needed and how it should be accessed.

Characteristics:

● Procedural in nature

● Complex syntax

● Integrated with programming languages

Examples: Oracle PL/SQL & DB2 SQL PL

Difference Between High-Level and Low-Level DML

High-Level DML

Low-Level DML

Non-procedural

Procedural

Set-at-a-time

Record-at-a-time

Easy to learn

Complex

SQL statements

PL/SQL, SQL PL

Focus on output

Focus on logic

DML Commands Overview

Data Manipulation Language mainly consists of the following four commands:

1. SELECT – Retrieves data