DROP and TRUNCATE behavior in MySQL - Mini Project: Build & Apply

Understanding DROP and TRUNCATE Behavior in MySQL

📖 Scenario: You are managing a small library database. You want to learn how to remove all books from a table quickly and how to completely delete the table itself.

🎯 Goal: Build SQL commands to create a table, add a helper variable for table name, use TRUNCATE to clear all rows, and finally DROP the table.

📋 What You'll Learn

Create a table named books with columns id (integer) and title (varchar 100).

Create a variable table_name holding the string 'books'.

Write a TRUNCATE statement using the table_name variable to remove all rows from the table.

Write a DROP TABLE statement using the table_name variable to delete the table.

💡 Why This Matters

🌍 Real World

Database administrators often need to clear data quickly or remove tables entirely during maintenance or development.

💼 Career

Knowing how to safely and efficiently remove data or tables is essential for database management roles.

1

Create the books table

Write a SQL statement to create a table called books with two columns: id as an integer and title as a varchar of length 100.

MySQL

-- Create the books table with id and title columns
-- Your code here

Need a hint?

Use CREATE TABLE books (id INT, title VARCHAR(100)); to create the table.

2

Create a variable for the table name

Create a SQL variable called @table_name and set it to the string 'books'.

MySQL

CREATE TABLE books (
  id INT,
  title VARCHAR(100)
);
-- Declare the @table_name variable and set it to 'books'
-- Your code here

Need a hint?

Use SET @table_name = 'books'; to create the variable.

3

Use TRUNCATE to remove all rows

Write a SQL statement to truncate the table using the variable @table_name. Use prepared statements to execute the TRUNCATE command.

MySQL

CREATE TABLE books (
  id INT,
  title VARCHAR(100)
);

SET @table_name = 'books';
-- Prepare and execute TRUNCATE TABLE using @table_name
-- Your code here

Need a hint?

Use PREPARE, EXECUTE, and DEALLOCATE PREPARE with a CONCAT of 'TRUNCATE TABLE ' and @table_name.

4

Use DROP TABLE to delete the table

Write a SQL statement to drop the table using the variable @table_name. Use prepared statements to execute the DROP TABLE command.

MySQL

CREATE TABLE books (
  id INT,
  title VARCHAR(100)
);

SET @table_name = 'books';

SET @sql = CONCAT('TRUNCATE TABLE ', @table_name);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- Prepare and execute DROP TABLE using @table_name
-- Your code here

Need a hint?

Use the same prepared statement pattern as TRUNCATE but with 'DROP TABLE ' and @table_name.