DBMS Topics
DML Commands
Last Updated : 21 May, 2026
Data Manipulation Language DML commands are used to manipulate the data stored in database tables. DML operates on the instance rows/data, not the schema.
Overview
Data Manipulation Language (DML) commands are used to manipulate the data stored in database tables. DML operates on the instance (rows/data), not the schema.
The four core DML commands are: SELECT, INSERT, UPDATE, DELETE.
DML is the part of SQL used most frequently in real applications. Whenever a website stores a new user, updates a profile, shows a list of products, or removes an old record, it is using DML behind the scenes. In DBMS exams, DML questions usually test filtering, sorting, grouping, joins, subqueries, and safe modification of rows.
Related topics: DDL Commands, Constraints in SQL, Nested Queries, and Transaction Management.
Why DML is Important
DML commands change or read the actual content of a table. Unlike DDL, which changes the structure, DML works with records. Most DML commands can be controlled using transactions, so a wrong INSERT, UPDATE, or DELETE can often be rolled back before COMMIT.
For example, a banking system may use UPDATE to debit one account and credit another account. Both updates should succeed together. If one update fails, the transaction should roll back to keep the data consistent. This is why DML is closely connected with ACID properties and transaction control.
Sample Tables
CREATE TABLE Department (dept_id INT PRIMARY KEY, dept_name VARCHAR(100));
CREATE TABLE Employee (
emp_id INT PRIMARY KEY, name VARCHAR(100),
salary DECIMAL(10,2), dept_id INT,
hire_date DATE,
FOREIGN KEY (dept_id) REFERENCES Department(dept_id)
);INSERT
Adds new rows to a table.
Basic INSERT
-- Insert with all columns (order must match table definition)
INSERT INTO Department VALUES (1, 'Computer Science');
INSERT INTO Department VALUES (2, 'Mathematics');
INSERT INTO Department VALUES (3, 'Physics');
-- Insert with specific columns (safe — order-independent)
INSERT INTO Employee (emp_id, name, salary, dept_id, hire_date)
VALUES (101, 'Alice Johnson', 75000.00, 1, '2020-06-15');Insert Multiple Rows
INSERT INTO Employee (emp_id, name, salary, dept_id, hire_date) VALUES
(102, 'Bob Smith', 62000.00, 2, '2019-03-10'),
(103, 'Carol White', 88000.00, 1, '2021-01-20'),
(104, 'David Brown', 55000.00, 3, '2022-07-01'),
(105, 'Eve Davis', 71000.00, 2, '2020-11-05');INSERT from SELECT
-- Copy data from one table to another
INSERT INTO HighEarners (emp_id, name, salary)
SELECT emp_id, name, salary FROM Employee WHERE salary > 80000;SELECT
Retrieves data from one or more tables.
Basic SELECT
-- All columns, all rows
SELECT * FROM Employee;
-- Specific columns
SELECT name, salary FROM Employee;
-- With alias
SELECT name AS EmployeeName, salary AS AnnualSalary FROM Employee;
-- Distinct values
SELECT DISTINCT dept_id FROM Employee;WHERE Clause — Filtering Rows
-- Comparison operators
SELECT * FROM Employee WHERE salary > 70000;
SELECT * FROM Employee WHERE dept_id = 1;
SELECT * FROM Employee WHERE name = 'Alice Johnson';
-- Logical operators
SELECT * FROM Employee WHERE salary > 60000 AND dept_id = 1;
SELECT * FROM Employee WHERE dept_id = 1 OR dept_id = 2;
SELECT * FROM Employee WHERE NOT dept_id = 3;
-- BETWEEN (inclusive)
SELECT * FROM Employee WHERE salary BETWEEN 60000 AND 80000;
-- IN (match any in a list)
SELECT * FROM Employee WHERE dept_id IN (1, 2);
-- LIKE (pattern matching)
SELECT * FROM Employee WHERE name LIKE 'A%'; -- starts with A
SELECT * FROM Employee WHERE name LIKE '%son'; -- ends with son
SELECT * FROM Employee WHERE name LIKE '%ob%'; -- contains ob
-- IS NULL / IS NOT NULL
SELECT * FROM Employee WHERE dept_id IS NULL;
SELECT * FROM Employee WHERE dept_id IS NOT NULL;ORDER BY — Sorting
-- Ascending (default)
SELECT name, salary FROM Employee ORDER BY salary;
-- Descending
SELECT name, salary FROM Employee ORDER BY salary DESC;
-- Multiple columns
SELECT name, dept_id, salary FROM Employee ORDER BY dept_id, salary DESC;Aggregate Functions
SELECT COUNT(*) AS total_employees FROM Employee;
SELECT COUNT(dept_id) AS employees_with_dept FROM Employee;
SELECT SUM(salary) AS total_salary FROM Employee;
SELECT AVG(salary) AS avg_salary FROM Employee;
SELECT MAX(salary) AS highest_salary FROM Employee;
SELECT MIN(salary) AS lowest_salary FROM Employee;GROUP BY and HAVING
-- Count employees per department
SELECT dept_id, COUNT(*) AS emp_count, AVG(salary) AS avg_sal
FROM Employee
GROUP BY dept_id;
-- HAVING filters after grouping (like WHERE but for groups)
SELECT dept_id, AVG(salary) AS avg_sal
FROM Employee
GROUP BY dept_id
HAVING AVG(salary) > 65000;
-- Difference: WHERE vs HAVING
SELECT dept_id, AVG(salary)
FROM Employee
WHERE hire_date > '2020-01-01' -- filters rows BEFORE grouping
GROUP BY dept_id
HAVING AVG(salary) > 65000; -- filters groups AFTER groupingUPDATE
Modifies existing rows in a table.
-- Update a specific row
UPDATE Employee SET salary = 80000 WHERE emp_id = 101;
-- Update multiple columns
UPDATE Employee
SET salary = 90000, dept_id = 2
WHERE emp_id = 103;
-- Update with expression
UPDATE Employee SET salary = salary * 1.10 WHERE dept_id = 1;
-- Update all rows (no WHERE — be careful!)
UPDATE Employee SET hire_date = CURRENT_DATE;Caution: Always include WHERE unless you intend to update all rows.
DELETE
Removes rows from a table.
-- Delete a specific row
DELETE FROM Employee WHERE emp_id = 104;
-- Delete rows matching a condition
DELETE FROM Employee WHERE salary < 50000;
-- Delete all rows (table structure preserved)
DELETE FROM Employee;
-- Delete based on subquery
DELETE FROM Employee
WHERE dept_id IN (SELECT dept_id FROM Department WHERE dept_name = 'Physics');Caution: DELETE without WHERE removes all rows. Use TRUNCATE for that purpose instead (it's faster).
DML Summary
| INSERT | Add new rows |
| SELECT | Read / retrieve rows |
| UPDATE | Modify existing rows |
| DELETE | Remove rows |
Common DML Mistakes
- Running
UPDATEorDELETEwithout aWHEREclause. - Using
WHEREafterGROUP BYinstead ofHAVINGfor aggregate conditions. - Selecting unnecessary columns with
SELECT *in production queries. - Forgetting that
NULLmust be checked withIS NULL, not= NULL. - Assuming
ORDER BYis automatic. SQL result order is not guaranteed unlessORDER BYis used.
Exam and Interview Notes
In interviews, DML questions often ask the difference between DELETE, TRUNCATE, and DROP; the difference between WHERE and HAVING; or how to find duplicate records. In semester exams, practice writing complete queries with sample tables because marks are usually given for correct syntax, correct condition, and correct output logic.
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for DML Commands.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this DBMS topic.
Search Terms
dbms, database management system, database notes, sql, unit, dml, commands, dml commands
Related DBMS Topics