Why DML operations modify data in MySQL - Visual Breakdown

Concept Flow - Why DML operations modify data

Start with existing data

↓

Execute DML command

↓

Check command type

↓

Add new

↓

rows

↓

Data in table is modified

↓

End

DML commands (INSERT, UPDATE, DELETE) change the data in a table by adding, changing, or removing rows.

Execution Sample

MySQL

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

This sequence adds a user, changes the user's name, then removes the user from the table.

Execution Table

Step	Command	Action	Affected Rows	Table Data After
1	INSERT INTO users (id, name) VALUES (1, 'Alice');	Add new row with id=1, name='Alice'	1	[{"id":1, "name":"Alice"}]
2	UPDATE users SET name = 'Bob' WHERE id = 1;	Change name to 'Bob' where id=1	1	[{"id":1, "name":"Bob"}]
3	DELETE FROM users WHERE id = 1;	Remove row where id=1	1	[]
4	No more commands	Execution ends	0	[]

💡 All commands executed, data modified accordingly, no more commands to run.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3
users table	[]	[{"id":1, "name":"Alice"}]	[{"id":1, "name":"Bob"}]	[]

Key Moments - 2 Insights

Why does the UPDATE command change data only for rows matching the condition?

Because UPDATE modifies only rows that meet the WHERE condition, as shown in execution_table step 2 where only the row with id=1 is changed.

Does DELETE remove all rows or only some?

DELETE removes only rows matching the WHERE condition. In step 3, only the row with id=1 is removed, leaving the table empty.

Visual Quiz - 3 Questions

Look at the execution_table at step 2, what is the name of the user after the UPDATE command?

A Bob

B No name

C Alice

D NULL

💡 Hint

Check the 'Table Data After' column in execution_table row for step 2.

At which step does the users table become empty?

A After Step 2

B After Step 3

C After Step 1

D Never

💡 Hint

Look at the 'Table Data After' column in execution_table for each step.

If the DELETE command had no WHERE clause, what would happen to the table?

A Only one row deleted

B No rows deleted

C All rows deleted

D Error occurs

💡 Hint

DELETE without WHERE removes all rows from the table.

Concept Snapshot

DML commands modify data in tables:
- INSERT adds new rows
- UPDATE changes existing rows matching conditions
- DELETE removes rows matching conditions
These commands change the table's data immediately.

Full Transcript

DML operations like INSERT, UPDATE, and DELETE modify data in database tables. INSERT adds new rows, UPDATE changes existing rows based on conditions, and DELETE removes rows based on conditions. Each command changes the table's data, which you can see step-by-step in the execution table. For example, inserting a user adds a row, updating changes that row's data, and deleting removes it. Understanding these steps helps you see how data changes in real time.

Practice

1. Which of the following DML commands is used to add new rows to a table in MySQL?

easy

A. UPDATE

B. SELECT

C. DELETE

D. INSERT

Solution

1. Step 1: Understand the purpose of DML commands

   DML commands modify data in tables by adding, changing, or removing rows.

2. Step 2: Identify the command for adding data

   INSERT adds new rows, UPDATE changes existing rows, DELETE removes rows, and SELECT only reads data.

3. Final Answer:

   INSERT -> Option D

4. Quick Check:

   Add new rows = INSERT [OK]

Hint: INSERT adds rows, UPDATE changes, DELETE removes [OK]

Common Mistakes:

• Confusing UPDATE with INSERT
• Thinking SELECT modifies data
• Using DELETE to add data

2. Which of the following is the correct syntax to update a user's email in a table named users?

easy

A. SELECT email = '[e-mail]' FROM users WHERE id = 5;

B. INSERT INTO users SET email = '[e-mail]' WHERE id = 5;

C. UPDATE users SET email = '[e-mail]' WHERE id = 5;

D. DELETE FROM users SET email = '[e-mail]' WHERE id = 5;

Solution

1. Step 1: Recognize the correct DML command for changing data

   To change existing data, UPDATE is used with SET to assign new values.

2. Step 2: Check syntax correctness

   UPDATE users SET email = '[e-mail]' WHERE id = 5; correctly updates the email for user with id 5.

3. Final Answer:

   UPDATE users SET email = '[e-mail]' WHERE id = 5; -> Option C

4. Quick Check:

   Update data uses UPDATE + SET + WHERE [OK]

Hint: UPDATE uses SET and WHERE to change data [OK]

Common Mistakes:

• Using INSERT instead of UPDATE to modify data
• Omitting WHERE clause causing all rows to update
• Using DELETE or SELECT incorrectly

3. Given the table products with columns id, name, and price, what will be the result of this query?

UPDATE products SET price = price * 1.1 WHERE id = 3;

medium

A. The price of the product with id 3 will increase by 10%.

B. All product prices will increase by 10%.

C. The product with id 3 will be deleted.

D. Syntax error due to missing WHERE clause.

Solution

1. Step 1: Understand the UPDATE statement with WHERE clause

   The query updates only rows where id = 3, so only that product is affected.

2. Step 2: Analyze the price update expression

   The price is multiplied by 1.1, increasing it by 10% for the product with id 3.

3. Final Answer:

   The price of the product with id 3 will increase by 10%. -> Option A

4. Quick Check:

   UPDATE with WHERE affects specific rows [OK]

Hint: WHERE limits UPDATE to specific rows [OK]

Common Mistakes:

• Assuming all rows update without WHERE
• Confusing UPDATE with DELETE
• Thinking syntax error occurs without WHERE

4. Identify the error in this DELETE statement:

DELETE users WHERE id = 10;

medium

A. Missing FROM keyword after DELETE.

B. WHERE clause cannot be used with DELETE.

C. DELETE cannot remove rows based on conditions.

D. Table name should be in quotes.

Solution

1. Step 1: Recall correct DELETE syntax

   The correct syntax is DELETE FROM table_name WHERE condition;. The FROM keyword is required.

2. Step 2: Identify the missing keyword

   The statement misses FROM after DELETE, causing a syntax error.

3. Final Answer:

   Missing FROM keyword after DELETE. -> Option A

4. Quick Check:

   DELETE requires FROM before table name [OK]

Hint: DELETE syntax: DELETE FROM table WHERE condition [OK]

Common Mistakes:

• Omitting FROM keyword
• Using DELETE without WHERE to remove specific rows
• Quoting table names unnecessarily

5. You want to increase the salary by 5% for all employees in the employees table who work in the 'Sales' department. Which query correctly performs this update?

hard

A. DELETE FROM employees WHERE department = 'Sales';

B. UPDATE employees SET salary = salary * 1.05 WHERE department = 'Sales';

C. INSERT INTO employees SET salary = salary * 1.05 WHERE department = 'Sales';

D. UPDATE employees WHERE department = 'Sales' SET salary = salary * 1.05;

Solution

1. Step 1: Choose the correct DML command for modifying existing data

   To change salaries, UPDATE is used, not INSERT or DELETE.

2. Step 2: Verify correct syntax order

   The correct syntax is UPDATE table SET column = value WHERE condition;. UPDATE employees SET salary = salary * 1.05 WHERE department = 'Sales'; follows this order correctly.

3. Final Answer:

   UPDATE employees SET salary = salary * 1.05 WHERE department = 'Sales'; -> Option B

4. Quick Check:

   UPDATE + SET + WHERE with correct order [OK]

Hint: UPDATE SET comes before WHERE clause [OK]

Common Mistakes:

• Using INSERT to modify data
• Placing WHERE before SET in UPDATE
• Deleting rows instead of updating