DROP Table in SQL

DROP TABLE statement is used to remove an existing table and all associated data from a database. This operation is permanent and it irreversibly deletes the table and its contents.

Syntax

The basic syntax of the DROP TABLE statement is as follows:

DROP TABLE table_name;

• DROP TABLE: The keyword indicating the intention to remove a table.
• table_name: The name of the table to be dropped.

Example: Dropping the Courses Table

For the purpose of this tutorial, let’s continue with the “School” database which has two tables: Students and Courses.

Students Table

student_id	first_name	last_name	age
1	John	Doe	21
2	Jane	Smith	20
3	Mike	Johnson	22

Courses Table

course_id	course_name	instructor
101	Introduction SQL	Professor A
102	Advanced SQL	Professor B
103	Database Design	Professor C

Let’s say we want to remove the “Courses” table from our database. We would execute the following SQL command:

DROP TABLE Courses;

After executing this command, if you query the database for the “Courses” table, you’ll find that it no longer exists.

Verifying the Removal

To confirm the table has been dropped, you can attempt to query it:

SELECT * FROM Courses;

You should receive an error message indicating that the table doesn’t exist.

Caution

• Irreversible Operation: The DROP TABLE statement is irreversible, and there is no built-in “undo” mechanism. Ensure you have a backup of your data before executing this command, especially in a production environment.
• Permissions: Depending on the database system, users might need appropriate permissions to execute the DROP TABLE statement.