DML (INSERT, UPDATE, DELETE) in DBMS Theory - Full Explanation

Introduction

Imagine you have a list of contacts on paper, and you want to add new friends, change phone numbers, or remove old contacts. Managing data in a database works similarly, and DML commands help you do these tasks easily.

Explanation

INSERT

INSERT adds new data into a database table. You specify the table and the values for each column you want to add. This is like writing a new contact on your list.

INSERT is used to add new records to a table.

UPDATE

UPDATE changes existing data in a table. You choose which rows to change by setting conditions, then specify the new values. This is like correcting a phone number on your contact list.

UPDATE modifies existing records based on conditions.

DELETE

DELETE removes data from a table. You select which rows to delete using conditions. This is like erasing a contact you no longer need from your list.

DELETE removes records from a table based on conditions.

Real World Analogy

Think of a notebook where you keep your friends' contact details. When you meet a new friend, you write their info down (INSERT). If they change their phone number, you cross out the old one and write the new number (UPDATE). If you lose touch, you erase their details (DELETE).

INSERT → Writing a new friend's contact details in your notebook

UPDATE → Changing a friend's phone number in your notebook

DELETE → Erasing a friend's contact details from your notebook

Diagram

Diagram

┌─────────────┐       ┌─────────────┐       ┌─────────────┐
│   INSERT    │──────▶│   UPDATE    │──────▶│   DELETE    │
│ Add records │       │ Modify data │       │ Remove data │
└─────────────┘       └─────────────┘       └─────────────┘

This diagram shows the three main DML operations: adding, modifying, and removing data.

Key Facts

DML → Data Manipulation Language commands used to manage data in database tables.

INSERT → Adds new rows of data into a table.

UPDATE → Changes existing data in one or more rows based on conditions.

DELETE → Removes rows of data from a table based on conditions.

WHERE clause → A condition used in UPDATE and DELETE to specify which rows to affect.

Code Example

DBMS Theory

CREATE TABLE contacts (id INT, name TEXT, phone TEXT);
INSERT INTO contacts (id, name, phone) VALUES (1, 'Alice', '12345');
UPDATE contacts SET phone = '67890' WHERE id = 1;
DELETE FROM contacts WHERE id = 1;

Common Confusions

Believing DELETE removes the entire table instead of just rows.

Believing DELETE removes the entire table instead of just rows. DELETE removes only selected rows; to remove the whole table structure, DROP TABLE is used.

Thinking UPDATE adds new rows if no matching rows exist.

Thinking UPDATE adds new rows if no matching rows exist. UPDATE only changes existing rows; it does not add new rows. Use INSERT to add new data.

Assuming INSERT can modify existing rows.

Assuming INSERT can modify existing rows. INSERT only adds new rows; it cannot change existing data.

Summary

DML commands let you add, change, and remove data in database tables.

INSERT adds new records, UPDATE changes existing ones, and DELETE removes records.

Using conditions with UPDATE and DELETE helps target specific rows safely.

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