DML (INSERT, UPDATE, DELETE) in DBMS Theory - Step-by-Step Execution

Concept Flow - DML (INSERT, UPDATE, DELETE)

Start

↓

Choose DML Operation

↓

INSERT

↓

Add new

↓

rows

↓

Commit

↓

End

The flow starts by choosing one of the three DML operations: INSERT to add rows, UPDATE to modify existing rows, or DELETE to remove rows, then commits the changes.

Execution Sample

DBMS Theory

INSERT INTO students (id, name) VALUES (1, 'Alice');
UPDATE students SET name = 'Alicia' WHERE id = 1;
DELETE FROM students WHERE id = 1;

This code adds a student named Alice, changes her name to Alicia, then removes her record.

Analysis Table

Step	Operation	Condition/Values	Action	Result
1	INSERT	id=1, name='Alice'	Add new row	Row with id=1 added
2	UPDATE	WHERE id=1	Change name to 'Alicia'	Row with id=1 updated
3	DELETE	WHERE id=1	Remove row	Row with id=1 deleted
4	END	-	-	No more operations

💡 All DML operations executed; no more commands to process.

State Tracker

Table 'students'	Start	After Step 1	After Step 2	After Step 3
Rows	empty	[{id:1, name:'Alice'}]	[{id:1, name:'Alicia'}]	empty

Key Insights - 3 Insights

Why does the UPDATE operation only change the row with id=1?

Because the WHERE clause specifies id=1, so only rows matching this condition are updated, as shown in execution_table step 2.

What happens if DELETE has no WHERE clause?

All rows in the table would be deleted, removing all data, unlike step 3 where only id=1 is deleted.

Does INSERT overwrite existing rows?

No, INSERT adds new rows; it does not modify or remove existing rows, as seen in step 1 where a new row is added.

Visual Quiz - 3 Questions

Look at the execution_table at step 2, what is the new name of the student with id=1?

A Alicia

B Alice

C Alison

D None

💡 Hint

Check the 'Result' column in execution_table row for step 2.

At which step does the table become empty again?

A Step 1

B Step 3

C Step 2

D Never

💡 Hint

Look at variable_tracker for 'Rows' after each step.

If the DELETE command had no WHERE clause, how would the variable_tracker change after step 3?

A Rows would still contain the updated row

B Rows would have multiple copies of the row

C Rows would be empty

D Rows would contain only the original inserted row

💡 Hint

Deleting without WHERE removes all rows, so variable_tracker 'Rows' would be empty.

Concept Snapshot

DML commands modify data in tables:
- INSERT adds new rows
- UPDATE changes existing rows matching conditions
- DELETE removes rows matching conditions
Always use WHERE in UPDATE/DELETE to target specific rows
Changes affect table data immediately after execution

Full Transcript

This visual execution shows how DML commands INSERT, UPDATE, and DELETE work step-by-step. First, INSERT adds a new row with id=1 and name 'Alice'. Then UPDATE changes the name to 'Alicia' for the row where id=1. Finally, DELETE removes the row where id=1. The variable tracker shows the table's state after each step, starting empty, then containing the inserted row, then the updated row, and finally empty again after deletion. Key points include the importance of WHERE clauses to target rows and that INSERT adds rows without overwriting. The quiz checks understanding of these changes by referencing the execution table and variable tracker.