DROP and TRUNCATE behavior in MySQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to remove the entire table named 'employees'.

MySQL

DROP TABLE [1];

Drag options to blanks, or click blank then click option'

Aemployee

Bstaff

Cemployee_list

Demployees

Attempts:

3 left

💡 Hint

Common Mistakes

Using a wrong table name that does not exist.

Confusing DROP with TRUNCATE.

The DROP TABLE command deletes the entire table named 'employees' including its structure and data.

2fill in blank

medium

Complete the code to remove all rows from the 'orders' table but keep its structure.

MySQL

TRUNCATE TABLE [1];

Drag options to blanks, or click blank then click option'

Aorders

Border

Corder_list

Dorder_details

Attempts:

3 left

💡 Hint

Common Mistakes

Using DROP instead of TRUNCATE when only data removal is needed.

Using a wrong table name.

The TRUNCATE TABLE command deletes all rows from the 'orders' table but keeps the table structure intact.

3fill in blank

hard

Fix the error in the code to delete all data from 'customers' but keep the table.

MySQL

DELETE FROM [1];

Drag options to blanks, or click blank then click option'

Acustomer

Bcustomers

Ccustomer_data

Dclient

Attempts:

3 left

💡 Hint

Common Mistakes

Using a singular table name that does not exist.

Confusing DELETE with DROP.

The DELETE FROM statement removes all rows from the 'customers' table but keeps the table structure.

4fill in blank

hard

Fill both blanks to create a command that deletes all rows from 'products' quickly and resets auto-increment.

MySQL

TRUNCATE TABLE [1]; -- This [2] the table data and resets auto-increment

Drag options to blanks, or click blank then click option'

Aproducts

Bremoves

Cdeletes

Ddrops

Attempts:

3 left

💡 Hint

Common Mistakes

Using DROP instead of TRUNCATE.

Using 'deletes' or 'drops' in the comment which is less precise.

TRUNCATE TABLE products; quickly removes all data and resets the auto-increment counter.

5fill in blank

hard

Fill all three blanks to write a command that deletes the 'sessions' table and then recreates it empty.

MySQL

DROP TABLE IF EXISTS [1];
CREATE TABLE [2] (id INT PRIMARY KEY AUTO_INCREMENT, user_id INT);
TRUNCATE TABLE [3];

Drag options to blanks, or click blank then click option'

Asessions

Dsession_data

Attempts:

3 left

💡 Hint

Common Mistakes

Using different table names in the commands.

Omitting the DROP or CREATE commands.

This sequence drops the 'sessions' table if it exists, recreates it with columns, and truncates it to ensure it's empty.

Practice

1. What is the main difference between DROP and TRUNCATE commands in MySQL?

easy

A. DROP only deletes data, TRUNCATE deletes the table structure.

B. TRUNCATE deletes the entire table and its data, DROP deletes only the data but keeps the table.

C. DROP deletes the entire table and its data, TRUNCATE deletes only the data but keeps the table.

D. DROP and TRUNCATE do exactly the same thing.

Solution

1. Step 1: Understand DROP behavior

   DROP removes the entire table including its structure and data permanently.

2. Step 2: Understand TRUNCATE behavior

   TRUNCATE removes all rows but keeps the table structure for future use.

3. Final Answer:

   DROP deletes the entire table and its data, TRUNCATE deletes only the data but keeps the table. -> Option C

4. Quick Check:

   DROP removes table, TRUNCATE empties table [OK]

Hint: DROP removes table, TRUNCATE empties data only [OK]

Common Mistakes:

• Confusing which command deletes the table structure
• Thinking TRUNCATE deletes the table itself
• Assuming DROP only deletes data

2. Which of the following is the correct syntax to remove all rows from a table named employees without deleting the table itself?

easy

A. DROP TABLE employees;

B. TRUNCATE TABLE employees;

C. DELETE TABLE employees;

D. REMOVE TABLE employees;

Solution

1. Step 1: Identify command to remove all rows but keep table

   TRUNCATE TABLE removes all rows quickly without deleting the table.

2. Step 2: Check syntax correctness

   TRUNCATE TABLE employees; is the correct syntax. DROP TABLE deletes the table, DELETE TABLE and REMOVE TABLE are invalid.

3. Final Answer:

   TRUNCATE TABLE employees; -> Option B

4. Quick Check:

   TRUNCATE syntax correct for emptying table [OK]

Hint: Use TRUNCATE TABLE to empty without deleting [OK]

Common Mistakes:

• Using DROP instead of TRUNCATE to empty table
• Using DELETE TABLE which is invalid syntax
• Confusing REMOVE TABLE as a valid command

3. Given a table products with 1000 rows, what will be the result after running TRUNCATE TABLE products;?

medium

A. All rows are removed but the table structure remains.

B. The table products is deleted completely.

C. Only half of the rows are deleted.

D. Syntax error occurs.

Solution

1. Step 1: Understand effect of TRUNCATE on data

   TRUNCATE TABLE removes all rows from the table quickly.

2. Step 2: Understand effect on table structure

   The table structure remains intact and ready for new data.

3. Final Answer:

   All rows are removed but the table structure remains. -> Option A

4. Quick Check:

   TRUNCATE empties table, keeps structure [OK]

Hint: TRUNCATE clears data, keeps table ready [OK]

Common Mistakes:

• Thinking TRUNCATE deletes the table itself
• Assuming partial row deletion
• Expecting syntax error

4. You want to remove a table named orders completely, but accidentally run TRUNCATE orders;. What is the problem with this command?

medium

A. It will cause a syntax error because TABLE keyword is missing.

B. It will delete the table and all its data.

C. It will only delete some rows, not all.

D. It will rename the table instead of deleting.

Solution

1. Step 1: Check syntax of TRUNCATE command

   The correct syntax requires the keyword TABLE: TRUNCATE TABLE orders;.

2. Step 2: Identify error from missing keyword

   Omitting TABLE causes a syntax error in MySQL.

3. Final Answer:

   It will cause a syntax error because TABLE keyword is missing. -> Option A

4. Quick Check:

   TRUNCATE requires TABLE keyword [OK]

Hint: Always include TABLE keyword in TRUNCATE [OK]

Common Mistakes:

• Omitting TABLE keyword in TRUNCATE
• Expecting TRUNCATE to delete table structure
• Confusing TRUNCATE with DROP syntax

5. You have a table customers with foreign key constraints referencing it from other tables. Which command should you use to quickly remove all data from customers without dropping the table and without breaking foreign key constraints?

hard

A. ALTER TABLE customers DROP;

B. TRUNCATE TABLE customers;

C. DROP TABLE customers;

D. DELETE FROM customers;

Solution

1. Step 1: Understand foreign key constraints effect

   TRUNCATE TABLE cannot be used if foreign keys reference the table; it will cause an error.

2. Step 2: Choose command that removes data safely

   DELETE FROM customers; removes all rows and respects foreign key constraints.

3. Step 3: Confirm other options

   DROP TABLE deletes the table, breaking references; ALTER TABLE DROP is invalid syntax.

4. Final Answer:

   DELETE FROM customers; -> Option D

5. Quick Check:

   DELETE respects foreign keys, TRUNCATE does not [OK]

Hint: Use DELETE when foreign keys exist, not TRUNCATE [OK]

Common Mistakes:

• Using TRUNCATE despite foreign key constraints
• Using DROP which deletes the table
• Trying invalid ALTER TABLE DROP syntax