DML Commands in MySQL | INSERT, UPDATE and DELETE Commands

After creating a database and its tables, the next step is to store and manage data. This is done using Data Manipulation Language (DML) commands. DML commands allow users to insert new records, update existing records, and delete unwanted records from database tables.

In this chapter, you will learn the three most commonly used DML commands in MySQL—INSERT, UPDATE, and DELETE. These commands are frequently used in real-world database applications such as school management systems, banking software, hospitals, and online shopping websites.

---

Learning Outcomes

After studying this chapter, you will be able to:

• Understand the purpose of DML commands.
• Insert records into a table using the INSERT command.
• Modify existing records using the UPDATE command.
• Delete records using the DELETE command.
• Differentiate between INSERT, UPDATE, and DELETE.

---

What are DML Commands?

---

Common DML Commands

Command	Purpose
INSERT	Adds new records to a table.
UPDATE	Modifies existing records.
DELETE	Removes records from a table.

---

Sample Student Table

The following Student table will be used throughout this chapter.

Roll_No	Name	Class	Marks
101	Aarav	XI	91.5
102	Diya	XI	88.0

---

1. INSERT Command

Syntax

INSERT INTO table_name
VALUES(value1, value2, value3, ...);

Example

INSERT INTO Student
VALUES(103, 'Riya', 'XI', 94.5);

The above command inserts a new student record into the Student table.

---

Table After INSERT

Roll_No	Name	Class	Marks
101	Aarav	XI	91.5
102	Diya	XI	88.0
103	Riya	XI	94.5

---

Inserting Values into Selected Columns

Instead of inserting values into every column, specific columns can also be mentioned.

Syntax

INSERT INTO table_name
(column1, column2, ...)
VALUES(value1, value2, ...);

Example

INSERT INTO Student
(Roll_No, Name)
VALUES(104, 'Kabir');

Only the specified columns receive values. The remaining columns store their default or NULL values (if allowed by the table definition).

---

Real-Life Example

Whenever a new student takes admission to a school, a new record is added to the Student table using the INSERT command.

INSERT INTO Student
VALUES(105, 'Ananya', 'XI', 92.0);

---

Important Notes

• The number of values must match the number of columns when column names are not specified.
• Values should be written in the same order as the table columns.
• Text values must be enclosed within single quotes (' ').
• Numeric values should not be enclosed in quotes.
• Each INSERT statement adds a new record to the table.

---

---

2. UPDATE Command

The UPDATE command is used to modify the existing data stored in one or more records of a table.

---

Syntax

UPDATE table_name
SET column_name = value
WHERE condition;

Example

UPDATE Student
SET Marks = 95.0
WHERE Roll_No = 103;

The above command updates the marks of the student whose Roll Number is 103.

---

Table Before UPDATE

Roll_No	Name	Class	Marks
101	Aarav	XI	91.5
102	Diya	XI	88.0
103	Riya	XI	94.5

Table After UPDATE

Roll_No	Name	Class	Marks
101	Aarav	XI	91.5
102	Diya	XI	88.0
103	Riya	XI	95.0

---

Updating Multiple Columns

More than one column can be updated in a single SQL statement.

Syntax

UPDATE table_name
SET column1 = value1,
column2 = value2
WHERE condition;

Example

UPDATE Student
SET Class = 'XII',
Marks = 96.5
WHERE Roll_No = 103;

---

Importance of WHERE Clause

Example without WHERE:

UPDATE Student
SET Marks = 100;

This command updates the Marks column of every student in the table.

---

3. DELETE Command

---

Syntax

DELETE FROM table_name
WHERE condition;

Example

DELETE FROM Student
WHERE Roll_No = 102;

The above command deletes the record of the student whose Roll Number is 102.

---

Table Before DELETE

Roll_No	Name	Class	Marks
101	Aarav	XI	91.5
102	Diya	XI	88.0
103	Riya	XI	95.0

Table After DELETE

Roll_No	Name	Class	Marks
101	Aarav	XI	91.5
103	Riya	XI	95.0

---

Deleting All Records

If the WHERE clause is omitted, all records are deleted from the table, but the table structure remains unchanged.

Example

DELETE FROM Student;

This command removes all records from the Student table.

---

Comparison of DML Commands

Command	Purpose	Example
INSERT	Adds new records.	INSERT INTO Student VALUES(...);
UPDATE	Modifies existing records.	UPDATE Student SET Marks=95 WHERE Roll_No=101;
DELETE	Removes records.	DELETE FROM Student WHERE Roll_No=101;

---

Solved Example 1

Insert a new student with Roll Number 106, Name "Mohit", Class XI, and Marks 89.5.

Solution:

INSERT INTO Student
VALUES(106, 'Mohit', 'XI', 89.5);

---

Solved Example 2

Update the marks of student with Roll Number 101 to 94.

Solution:

UPDATE Student
SET Marks = 94
WHERE Roll_No = 101;

---

Solved Example 3

Delete the record of the student whose Roll Number is 106.

Solution:

DELETE FROM Student
WHERE Roll_No = 106;

---

Common Errors

Mistake	Correct Practice
Forgetting single quotes around text values.	Write text values within single quotes.
Providing values in the wrong order.	Follow the column order or specify column names.
Updating records without a WHERE clause.	Always verify the WHERE condition before executing UPDATE.
Deleting records without a WHERE clause.	Use WHERE unless you intend to delete every record.
Incorrect data type while inserting values.	Ensure that inserted values match the column data types.

---

Interview Corner

---

---

Real-Life Applications of DML Commands

Organization	DML Command	Purpose
School	INSERT	Add records of newly admitted students.
School	UPDATE	Update examination marks or student details.
School	DELETE	Remove records of students who have left the school.
Bank	UPDATE	Update account balance after transactions.
Hospital	INSERT	Add new patient records.
Library	DELETE	Remove outdated member records.
E-commerce	UPDATE	Update product prices and stock availability.

---

Solved Example 4

Insert the following student record into the Student table.

Roll_No	Name	Class	Marks
107	Neha	XI	91.8

Solution:

INSERT INTO Student
VALUES(107, 'Neha', 'XI', 91.8);

---

Solved Example 5

Update the class of student with Roll Number 107 from XI to XII.

Solution:

UPDATE Student
SET Class = 'XII'
WHERE Roll_No = 107;

---

Solved Example 6

Delete the record of the student named "Neha".

Solution:

DELETE FROM Student
WHERE Name = 'Neha';

---

Solved Example 7

Insert values into selected columns only.

Solution:

INSERT INTO Student
(Roll_No, Name)
VALUES(108, 'Aditi');

The remaining columns will store their default or NULL values, depending on the table definition.

---

Solved Example 8

Update both Class and Marks of the student whose Roll Number is 108.

Solution:

UPDATE Student
SET Class = 'XI',
Marks = 93.5
WHERE Roll_No = 108;

---

Competency-Based Questions

1. A school admits a new student named Aryan with Roll Number 120, Class XI, and Marks 89.5. Write the SQL command to insert this record.
2. The marks of student Roll Number 105 have increased from 82 to 91 after re-evaluation. Write the SQL command to update the marks.
3. A student with Roll Number 115 has taken admission in another school. Write the SQL command to remove the student's record.
4. Why is the WHERE clause important while using UPDATE and DELETE commands? Explain with an example.
5. Differentiate between INSERT, UPDATE, and DELETE commands with one suitable example of each.

---

Multiple Choice Questions

1. DML stands for:
   • (a) Data Management Language
   • (b) Data Manipulation Language
   • (c) Database Manipulation Logic
   • (d) Data Modification Logic
2. Which SQL command is used to add a new record?
   • (a) UPDATE
   • (b) INSERT
   • (c) DELETE
   • (d) ALTER
3. Which command modifies existing records?
   • (a) INSERT
   • (b) UPDATE
   • (c) CREATE
   • (d) DROP
4. Which command removes records from a table?
   • (a) DROP
   • (b) DELETE
   • (c) REMOVE
   • (d) ERASE
5. Which clause specifies the records to be updated?
   • (a) FROM
   • (b) WHERE
   • (c) ORDER BY
   • (d) GROUP BY
6. Which command adds values to selected columns?
   • (a) UPDATE
   • (b) INSERT INTO table_name(column1, column2) VALUES(...)
   • (c) DELETE
   • (d) DROP TABLE
7. If the WHERE clause is omitted in an UPDATE statement:
   • (a) No record is updated.
   • (b) All records are updated.
   • (c) The command produces an error.
   • (d) Only the first record is updated.
8. If the WHERE clause is omitted in a DELETE statement:
   • (a) Only one record is deleted.
   • (b) All records are deleted.
   • (c) The table is dropped.
   • (d) No record is deleted.
9. Which command can update more than one column in a single statement?
   • (a) INSERT
   • (b) UPDATE
   • (c) DELETE
   • (d) CREATE
10. Which of the following is not a DML command?
    • (a) INSERT
    • (b) UPDATE
    • (c) DELETE
    • (d) CREATE TABLE

---

Quick Revision

Command	Purpose
INSERT	Adds new records to a table.
UPDATE	Modifies existing records.
DELETE	Removes records from a table.
WHERE	Specifies which records are affected.

---

Important Points to Remember

• DML commands work with the data stored inside database tables.
• INSERT is used to add new records.
• UPDATE modifies existing records.
• DELETE removes records from a table.
• Always use the WHERE clause with UPDATE and DELETE unless you intentionally want to affect all records.
• Text values should always be enclosed within single quotes.
• The number and order of values should match the table columns unless column names are specified.
• Every SQL statement should end with a semicolon (;).

---

CBSE Exam Tips

• Memorize the syntax of INSERT, UPDATE, and DELETE commands.
• Practice writing SQL statements with proper keywords and punctuation.
• Understand the importance of the WHERE clause, as it is commonly tested in CBSE examinations.
• Be able to differentiate between DDL and DML commands.
• Practice competency-based questions involving real-life scenarios such as school, bank, and hospital databases.

---

Summary

Data Manipulation Language (DML) is used to manage the records stored in database tables. The INSERT command adds new records, the UPDATE command modifies existing records, and the DELETE command removes records. The WHERE clause plays a crucial role in UPDATE and DELETE statements by ensuring that only the required records are affected. Mastering these commands enables users to efficiently maintain and manipulate data in MySQL databases.