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.