Complete Guide to DML Statements in SQL (with Tables and All Use Cases)

1. INSERT Statement – All Use Cases

a) Insert a Single Row (Full Columns)

Before: Students

					INSERT INTO Students (StudentID, Name, Age)
VALUES (102, 'Neha', 19);
				

After:

b) Insert a Row with Partial Columns

					INSERT INTO Students (Name)
VALUES ('Priya');
				

Result:

c) Insert Multiple Rows

					INSERT INTO Students (StudentID, Name, Age)
VALUES
(104, 'Anil', 20),
(105, 'Karan', 21);
				

New Rows Added:

d) Insert from Another Table

					INSERT INTO Graduates (StudentID, Name)
SELECT StudentID, Name FROM Students WHERE Age > 20;
				

Copies students aged above 20 to Graduates table.

2. UPDATE Statement – All Use Cases

a) Update a Single Column for a Row

					UPDATE Students
SET Age = 22
WHERE StudentID = 105;
				

Before:

b) Update Multiple Columns

					UPDATE Students
SET Name = 'Karan Sharma', Age = 23
WHERE StudentID = 105;
				

c) Update Multiple Rows

					UPDATE Students
SET Age = Age + 1
WHERE Age < 21;
				

Increases age by 1 for all students below 21.

d) Update Using Subquery

					UPDATE Students
SET Age = (SELECT MAX(Age) FROM Students)
WHERE StudentID = 104;
				

Sets Anil’s age to the highest in the table.

3. DELETE Statement – All Use Cases

a) Delete Specific Row

					DELETE FROM Students
WHERE StudentID = 103;
				

Removes Priya.

b) Delete Multiple Rows

					DELETE FROM Students
WHERE Age < 20;
				

Removes all students under 20.

c) Delete All Rows

					DELETE FROM Students;
				

Note: Table remains, but all data is removed.

d) Delete Using Subquery

					DELETE FROM Students
WHERE StudentID IN (SELECT StudentID FROM Graduates);
				

Removes students already in Graduates .

4. MERGE Statement – Advanced Use Case

Used to insert or update rows based on matching conditions.

Source Table: TempStudents

					MERGE INTO Students AS target
USING TempStudents AS source
ON target.StudentID = source.StudentID
WHEN MATCHED THEN
    UPDATE SET target.Age = source.Age
WHEN NOT MATCHED THEN
    INSERT (StudentID, Name, Age)
    VALUES (source.StudentID, source.Name, source.Age);
				

Result:

• Updates Karan’s age
• Inserts Alia as a new student

DML Summary Table