DML (INSERT, UPDATE, DELETE) in DBMS Theory - Deep Dive

Overview - DML (INSERT, UPDATE, DELETE)

What is it?

DML stands for Data Manipulation Language, a set of commands used to add, change, or remove data in a database. The main DML commands are INSERT, UPDATE, and DELETE. INSERT adds new data, UPDATE changes existing data, and DELETE removes data. These commands help manage the actual information stored in tables.

Why it matters

Without DML commands, databases would be static and unable to reflect real-world changes. For example, a store's inventory or customer records would never update, making the data useless. DML allows dynamic interaction with data, enabling applications to keep information current and accurate.

Where it fits

Before learning DML, you should understand basic database concepts like tables, rows, and columns. After mastering DML, you can learn about querying data with SELECT, database transactions, and advanced data integrity techniques.

Mental Model

Core Idea

DML commands are the tools that let you add, change, or remove the actual data stored inside a database's tables.

Think of it like...

Think of a database table like a spreadsheet. INSERT is like adding a new row, UPDATE is like changing the content of existing cells, and DELETE is like removing a row entirely.

┌─────────────┐
│   Table     │
├─────────────┤
│ Row 1       │
│ Row 2       │
│ Row 3       │
└─────────────┘

INSERT → Add new row at bottom
UPDATE → Change cells in existing row
DELETE → Remove a row from table

Build-Up - 7 Steps

1

Foundation

Understanding Database Tables and Rows

Concept: Introduce what tables and rows are in a database context.

A database stores data in tables, which look like grids with columns and rows. Each row holds one record, like a single person's data, and each column holds a type of information, like name or age.

Result

You can picture data as organized in rows and columns, ready to be manipulated.

Understanding tables and rows is essential because DML commands operate directly on these structures.

2

Foundation

What is Data Manipulation Language (DML)?

Concept: Explain the purpose of DML in databases.

DML is a group of commands that let you change the data inside tables. Unlike commands that create or delete tables, DML focuses on the data itself. The main commands are INSERT, UPDATE, and DELETE.

Result

You know that DML is about managing the actual data records in a database.

Knowing that DML targets data, not structure, helps separate it from other database languages like DDL.

3

Intermediate

How INSERT Adds New Data

🤔Before reading on: do you think INSERT can add multiple rows at once or only one? Commit to your answer.

Concept: Learn how to add new records to a table using INSERT.

The INSERT command adds new rows to a table. You specify the table name, the columns you want to fill, and the values for those columns. You can add one row or many rows in a single command.

Result

New data appears as new rows in the table.

Understanding INSERT lets you grow your database with fresh information, a fundamental operation for any dynamic system.

4

Intermediate

Using UPDATE to Modify Existing Data

🤔Before reading on: does UPDATE change all rows or only specific ones? Commit to your answer.

Concept: Learn how to change data already stored in a table.

UPDATE changes values in existing rows. You specify which rows to change using conditions (called WHERE clauses). Without conditions, UPDATE changes every row, which can be dangerous.

Result

Selected rows have their data changed as specified.

Knowing how to target specific rows prevents accidental data loss or unwanted changes.

5

Intermediate

Deleting Data with DELETE Command

🤔Before reading on: does DELETE remove data permanently or just hide it? Commit to your answer.

Concept: Learn how to remove rows from a table.

DELETE removes rows from a table based on conditions. Like UPDATE, if you omit the condition, all rows get deleted. This command permanently removes data, freeing space and keeping the database clean.

Result

Specified rows are removed from the table.

Understanding DELETE helps maintain data relevance and manage storage effectively.

6

Advanced

Importance of WHERE Clause in DML

🤔Before reading on: what happens if you run UPDATE or DELETE without WHERE? Commit to your answer.

Concept: Learn how conditions control which rows DML commands affect.

The WHERE clause filters rows for UPDATE and DELETE. Without it, these commands apply to every row, which can cause major data loss or unwanted changes. Using WHERE carefully ensures only intended data is affected.

Result

Only targeted rows are updated or deleted, protecting other data.

Knowing the power and risk of WHERE clauses is critical for safe data manipulation.

7

Expert

Transaction Control and DML Safety

🤔Before reading on: do you think DML commands are automatically permanent or can be undone? Commit to your answer.

Concept: Understand how transactions group DML commands to ensure data integrity.

Databases use transactions to group DML commands so they either all succeed or all fail together. This prevents partial changes that could corrupt data. Commands like COMMIT save changes, and ROLLBACK undoes them if needed.

Result

Data changes are safe, consistent, and recoverable.

Understanding transactions prevents data corruption and supports reliable applications.

Under the Hood

When a DML command runs, the database engine parses it, checks permissions, and locates the affected rows. For INSERT, it allocates space and writes new data. UPDATE reads rows, modifies values, and writes changes. DELETE marks rows as removed and frees space later. These operations are logged for recovery and can be grouped in transactions to ensure atomicity.

Why designed this way?

DML commands were designed to separate data changes from structural changes for clarity and safety. Logging and transactions were added to protect data integrity and allow recovery from errors or crashes. This design balances flexibility with reliability.

┌───────────────┐
│  DML Command  │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Parser & Plan │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Data Access   │
│ & Modification│
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Transaction   │
│ Control & Log │
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does UPDATE without WHERE change all rows or none? Commit to your answer.

Common Belief: UPDATE without WHERE only changes rows that match some default condition.

Reality: UPDATE without WHERE changes every row in the table.

Why it matters: Accidentally updating all rows can corrupt data and cause major errors in applications.

Quick: Does DELETE remove data permanently or can it be recovered easily? Commit to your answer.

Common Belief: DELETE just hides data and it can be easily recovered later.

Reality: DELETE permanently removes data from the table, though recovery may be possible only with backups or logs.

Why it matters: Assuming DELETE is reversible can lead to data loss if backups are not in place.

Quick: Can INSERT add multiple rows in one command? Commit to your answer.

Common Belief: INSERT can only add one row at a time.

Reality: Most databases allow INSERT to add multiple rows in a single command.

Why it matters: Knowing this improves efficiency by reducing the number of commands needed.

Quick: Does a DML command automatically save changes permanently? Commit to your answer.

Common Belief: DML commands immediately and permanently change data without possibility to undo.

Reality: DML changes are often part of transactions and can be rolled back before committing.

Why it matters: Understanding this prevents panic and mistakes when changes need to be undone.

Expert Zone

1

Some databases optimize DELETE by marking rows as deleted and cleaning them later, affecting performance and storage.

2

Using RETURNING clauses with DML commands can return affected rows, useful for chaining operations without extra queries.

3

Batching multiple INSERTs in one command reduces network overhead and improves performance significantly.

When NOT to use

DML is not suitable for bulk data loading where specialized bulk import tools are better. Also, for complex data transformations, ETL (Extract, Transform, Load) tools or scripts are preferred over manual DML commands.

Production Patterns

In real systems, DML commands are wrapped in transactions with error handling. Soft deletes (marking rows as inactive instead of deleting) are common to preserve history. Auditing triggers track changes made by DML for compliance.

Connections

Transactions

DML commands are often grouped inside transactions to ensure all-or-nothing changes.

Understanding transactions helps grasp how DML changes can be safely applied or undone as a unit.

Data Integrity Constraints

DML commands must respect constraints like primary keys and foreign keys to keep data valid.

Knowing constraints helps prevent errors when inserting or updating data that violates rules.

Version Control Systems

Both DML and version control track changes over time, but DML manages data changes inside databases while version control manages code changes.

Seeing this parallel highlights the importance of tracking and managing changes in different domains.

Common Pitfalls

#1 Updating all rows unintentionally by omitting WHERE clause.

Wrong approach:

UPDATE employees SET salary = salary * 1.1;

Correct approach:

UPDATE employees SET salary = salary * 1.1 WHERE department = 'Sales';

Root cause:

Forgetting to specify conditions leads to applying changes to every row.

#2 Deleting all data by mistake without condition.

Wrong approach:

DELETE FROM orders;

Correct approach:

DELETE FROM orders WHERE order_date < '2023-01-01';

Root cause:

Not using WHERE clause causes removal of entire table data.

#3 Trying to insert data without specifying columns when table structure changes.

Wrong approach:

INSERT INTO customers VALUES ('John', 'Doe', 'NY');

Correct approach:

INSERT INTO customers (first_name, last_name, city) VALUES ('John', 'Doe', 'NY');

Root cause:

Assuming column order or count matches leads to errors when table schema changes.

Key Takeaways

DML commands INSERT, UPDATE, and DELETE are essential for managing the actual data inside database tables.

Using WHERE clauses carefully with UPDATE and DELETE prevents accidental changes or data loss.

Transactions group DML commands to ensure data changes are safe, consistent, and recoverable.

Understanding how DML works under the hood helps avoid common mistakes and improves database reliability.

Real-world use of DML includes patterns like soft deletes, auditing, and batch inserts for performance and compliance.

Practice

1. Which DML command is used to add new rows to a database table?

easy

A. INSERT

B. UPDATE

C. DELETE

D. SELECT

Solution

1. Step 1: Understand the purpose of DML commands

   DML commands are used to manipulate data in tables: adding, changing, or removing rows.

2. Step 2: Identify the command for adding rows

   The INSERT command adds new rows, UPDATE changes existing rows, and DELETE removes rows.

3. Final Answer:

   INSERT -> Option A

4. Quick Check:

   Add new rows = INSERT [OK]

Hint: Add new data? Use INSERT command [OK]

Common Mistakes:

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

2. Which of the following is the correct syntax to update the salary to 5000 for employee with id 10?

easy

A. UPDATE employees SET salary 5000 WHERE id = 10;

B. UPDATE employees salary = 5000 WHERE id = 10;

C. UPDATE employees SET salary = 5000 WHERE id = 10;

D. UPDATE employees SET salary = 5000;

Solution

1. Step 1: Recall the UPDATE syntax

   The correct syntax is UPDATE table_name SET column = value WHERE condition; to update specific rows.

2. Step 2: Check each option

   UPDATE employees SET salary = 5000 WHERE id = 10; matches the correct syntax with a WHERE clause to target employee id 10. Options B and C miss the SET keyword or equal sign. UPDATE employees SET salary = 5000; lacks WHERE, so it updates all rows.

3. Final Answer:

   UPDATE employees SET salary = 5000 WHERE id = 10; -> Option C

4. Quick Check:

   UPDATE with SET and WHERE = UPDATE employees SET salary = 5000 WHERE id = 10; [OK]

Hint: UPDATE needs SET and WHERE for specific rows [OK]

Common Mistakes:

• Omitting SET keyword
• Missing WHERE clause
• Wrong assignment syntax

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

DELETE FROM products WHERE price < 100;

medium

A. All products with price greater than 100 will be removed.

B. All products with price less than 100 will be removed.

C. No products will be removed because condition is incorrect.

D. Syntax error due to missing semicolon.

Solution

1. Step 1: Understand DELETE with WHERE condition

   The DELETE command removes rows matching the WHERE condition. Here, it targets rows where price is less than 100.

2. Step 2: Analyze the condition and syntax

   The condition price < 100 correctly selects products priced below 100. The semicolon is optional in many systems but good practice.

3. Final Answer:

   All products with price less than 100 will be removed. -> Option B

4. Quick Check:

   DELETE with price < 100 removes matching rows [OK]

Hint: DELETE removes rows matching WHERE condition [OK]

Common Mistakes:

• Confusing less than with greater than
• Thinking DELETE needs all rows
• Assuming syntax error without semicolon

4. Identify the error in this SQL statement:

UPDATE employees SET salary = 6000 WHERE;

medium

A. Missing condition after WHERE clause.

B. Incorrect use of SET keyword.

C. Salary value should be in quotes.

D. UPDATE cannot be used without DELETE.

Solution

1. Step 1: Check the UPDATE statement structure

   UPDATE requires SET to assign new values and WHERE to specify which rows to update.

2. Step 2: Identify the error in the WHERE clause

   The statement ends with WHERE but no condition follows, which is a syntax error.

3. Final Answer:

   Missing condition after WHERE clause. -> Option A

4. Quick Check:

   WHERE must have a condition [OK]

Hint: WHERE must always have a condition [OK]

Common Mistakes:

• Leaving WHERE empty
• Putting value in quotes unnecessarily
• Confusing UPDATE with DELETE

5. You want to increase the price by 10% for all products priced below 50. Which SQL statement correctly does this?

hard

A. UPDATE products SET price = price + 0.10 WHERE price < 50;

B. UPDATE products SET price = price + 10 WHERE price < 50;

C. UPDATE products SET price = price * 0.10 WHERE price < 50;

D. UPDATE products SET price = price * 1.10 WHERE price < 50;

Solution

1. Step 1: Understand the requirement to increase price by 10%

   Increasing by 10% means multiplying the current price by 1.10 (100% + 10%).

2. Step 2: Analyze each option

   UPDATE products SET price = price * 1.10 WHERE price < 50; correctly multiplies price by 1.10 for prices less than 50. UPDATE products SET price = price + 10 WHERE price < 50; adds 10 units, not 10%. UPDATE products SET price = price * 0.10 WHERE price < 50; multiplies by 0.10, reducing price. UPDATE products SET price = price + 0.10 WHERE price < 50; adds 0.10 units, a small increase.

3. Final Answer:

   UPDATE products SET price = price * 1.10 WHERE price < 50; -> Option D

4. Quick Check:

   Multiply price by 1.10 to increase by 10% [OK]

Hint: Multiply by 1.10 to increase by 10% [OK]

Common Mistakes:

• Adding fixed amount instead of percentage
• Multiplying by 0.10 reduces price
• Forgetting WHERE clause