DML (INSERT, UPDATE, DELETE) in DBMS Theory - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding INSERT statement behavior

What will happen if you execute the following SQL statement on a table Employees with columns id (primary key) and name where id 101 already exists?

INSERT INTO Employees (id, name) VALUES (101, 'Alice');

A The existing row will be deleted and a new row inserted with id 101 and name 'Alice'.

B A new row with id 101 and name 'Alice' will be added, duplicating the primary key.

C The statement will fail with a primary key violation error.

D The row with id 101 will be updated to have name 'Alice'.

💡 Hint

Primary keys must be unique in a table.

An INSERT statement tries to add a new row. Since id 101 already exists and is a primary key, the database will reject the insert to prevent duplicate keys, causing an error.

🔍 Analysis

intermediate

Effect of UPDATE with WHERE clause

Consider a table Products with columns product_id, price, and stock. What will be the effect of this SQL statement?

UPDATE Products SET price = price * 1.1 WHERE stock = 0;

A All products will have their price increased by 10%.

B Only products with zero stock will have their price increased by 10%.

C Only products with stock greater than zero will have their price increased by 10%.

D No products will be updated because the WHERE clause is invalid.

💡 Hint

The WHERE clause filters which rows are updated.

The UPDATE statement changes the price only for rows where stock equals zero. Other rows remain unchanged.

📋 Factual

advanced

DELETE statement without WHERE clause

What is the result of executing the following SQL command?

DELETE FROM Orders;

A Deletes all rows from the Orders table.

B Causes a syntax error because WHERE clause is required.

C Deletes only the first row in the Orders table.

D Deletes no rows because WHERE clause is missing.

💡 Hint

Think about what happens when no filter is applied in DELETE.

Without a WHERE clause, DELETE removes all rows from the table but keeps the table structure intact.

❓ Comparison

advanced

Difference between DELETE and TRUNCATE

Which of the following statements correctly describes a key difference between DELETE and TRUNCATE commands in SQL?

A DELETE removes rows one by one and can be rolled back; TRUNCATE removes all rows instantly and cannot be rolled back in most systems.

B DELETE removes all rows instantly and cannot be rolled back; TRUNCATE removes rows one by one and can be rolled back.

C Both DELETE and TRUNCATE remove rows one by one and can be rolled back.

D DELETE only removes rows matching a condition; TRUNCATE requires a WHERE clause.

💡 Hint

Consider how each command handles transaction logs and rollback.

DELETE removes rows individually and logs each deletion, allowing rollback. TRUNCATE quickly removes all rows by deallocating data pages and usually cannot be rolled back.

❓ Reasoning

expert

Predicting the final state after multiple DML operations

Given a table Inventory with columns item_id and quantity, initially containing:

item_id | quantity
1       | 10
2       | 5
3       | 0

What will be the contents of the table after executing these statements in order?

UPDATE Inventory SET quantity = quantity - 5 WHERE item_id = 1;
DELETE FROM Inventory WHERE quantity <= 0;
INSERT INTO Inventory (item_id, quantity) VALUES (3, 7);

A

<pre>item_id | quantity
1       | 5
2       | 5
3       | 0</pre>

B

<pre>item_id | quantity
1       | 5
2       | 5</pre>

C

<pre>item_id | quantity
1       | 10
2       | 5
3       | 7</pre>

D

<pre>item_id | quantity
1       | 5
2       | 5
3       | 7</pre>

💡 Hint

Apply each statement step-by-step and watch how rows change or get deleted.

First, item 1 quantity reduces from 10 to 5. Then rows with quantity <= 0 are deleted, so item 3 (quantity 0) is removed. Finally, item 3 is reinserted with quantity 7.

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