← SQL roadmap

Topic 7 of 52 · Beginner

DROP TABLE

Delete a table completely: structure, data, indexes and permissions.

01DROP TABLE

Module 02 – Database & Table Management (DDL)

02What is it?

DROP TABLE removes the table itself from the database. Its rows, columns, indexes, triggers and constraints all disappear. It cannot be undone with ROLLBACK.

03Why do we need it?

Temporary or staging tables must be cleaned up after a pipeline run, and obsolete tables should be removed to keep the schema understandable.

04Syntax

DROP [TEMPORARY] TABLE [IF EXISTS] table1 [, table2 ...];

05Parameter explanation

TEMPORARY
MySQL: only drops temporary tables (safety guard).
IF EXISTS
No error if the table isn't there — useful in scripts.
table1, table2
MySQL can drop several tables at once.

06Sample database

Every example and exercise below starts from a fresh copy of this data.

CREATE TABLE departments (
  dept_id   INT PRIMARY KEY,
  dept_name VARCHAR(50) NOT NULL
);
CREATE TABLE employees (
  emp_id    INT PRIMARY KEY,
  name      VARCHAR(60) NOT NULL,
  dept_id   INT,
  salary    DECIMAL(10,2),
  hire_date DATE
);
INSERT INTO departments VALUES (10,'Engineering'),(20,'Sales'),(30,'HR');
INSERT INTO employees VALUES
 (1,'Asha',10,85000,'2021-04-12'),
 (2,'Ravi',20,52000,'2022-01-03'),
 (3,'Meena',10,97000,'2019-09-20'),
 (4,'Karan',30,46000,'2023-06-15'),
 (5,'Divya',20,61000,'2020-11-30');
CREATE TABLE old_logs (id INT, msg TEXT);
INSERT INTO old_logs VALUES (1,'boot'),(2,'login');

07Basic coding example

Basic example · runs on the real engine

08Step-by-step execution

  1. 1.MySQL checks old_logs exists (and no foreign key from another table blocks it).
  2. 2.The table definition and all rows are removed.
  3. 3.The name is free to be reused.

09Before and after output

Generated live by running the basic example against the sample database.

Running on the SQL engine…

10Intermediate coding example

Intermediate example

11Real-world business scenario

Re-runnable ETL scripts start with DROP TABLE IF EXISTS stg_x; CREATE TABLE stg_x ... so each run starts clean and never fails because a previous run left the table behind.

12Common mistakes

DROP TABLE reports;

Table doesn't exist → error stops the script.

DROP TABLE IF EXISTS reports;

DROP TABLE departments; -- while employees references it

With an enforced foreign key, MySQL blocks dropping a parent table.

Drop the child table or the FK first.

DROP TABLE employees; -- wanted to empty it

This deletes the table itself.

TRUNCATE TABLE employees;

13Important comparisons

DROP TABLETRUNCATEDELETE
Removes structureYesNoNo
Removes rowsAllAllFiltered or all
WHERE allowedNoNoYes
Rollback (InnoDB)NoNoYes in a transaction

14Practical exercises

Beginner1. Drop old_logs and list the remaining tables.

Reveal solution (15)
Solution · run it

Only departments and employees remain.

Beginner2. Drop a table called ghost safely even though it doesn't exist.

Reveal solution (15)
Solution · run it

IF EXISTS avoids the error.

Beginner3. Create t1, then drop it.

Reveal solution (15)
Solution · run it

0 rows: it's gone.

Intermediate4. Build a summary table of salary per department, show it, then drop it.

Reveal solution (15)
Solution · run it

Typical temporary-result pattern.

Intermediate5. Show the error when querying a dropped table.

Reveal solution (15)
Solution · run it

Expected: no such table.

Intermediate6. Write a re-runnable script that recreates stg_emp from employees.

Reveal solution (15)
Solution · run it

Running it twice proves it's idempotent.

Advanced7. Back up employees, drop it, and restore it from the backup.

Reveal solution (15)
Solution · run it

Note: CREATE TABLE AS SELECT doesn't copy keys/indexes.

Advanced8. Drop two scratch tables in one script.

Reveal solution (15)
Solution · run it

MySQL allows DROP TABLE IF EXISTS a, b; in one statement.

16Key takeaways

  • DROP TABLE removes structure and data permanently.
  • Use IF EXISTS in scripts.
  • Foreign keys can block dropping a parent.
  • Want to keep the table? Use TRUNCATE or DELETE.

17Knowledge check

1. What survives DROP TABLE?

2. Which keyword prevents an error if the table is missing?

3. To keep the table but remove all rows quickly, use…

Interview questions

Why might DROP TABLE fail on a parent table?

Another table has a FOREIGN KEY referencing it. Drop the child/constraint first, or (carefully) disable FOREIGN_KEY_CHECKS.

Practise SQL with the AI mentor

Lesson test

Take an 8-question test on “DROP TABLE” — concepts, output prediction, bug spotting and interview questions.

Can't find a SQL topic you need for exams or placements? Request it and we'll add it.