Description

The SQL DROP statement can be used to remove a table or a database from a database server permanently.

  • The DROP can be used to remove a table or a database from the database server.
  • The DROP action is irreversible, which cannot be revered.
  • It also removed the table metadata that defines the table in the data dictionary.

In the previous chapter, we discussed how to insert, select, and update table data.

Now, let's see how to remove a table and database using the DROP statement.

Syntax

Here is the basic syntax of a DROP statement.

  • The table_name represents the table name.
  • The database_name represents the database name.
DROP TABLE table_name;
DROP DATABASE database_name;

Table Data

Let's consider the below data in the table employees before executing any of the below queries.

employee_id employee_name gender birth_date hire_date salary
1 Robin Hood M 1990-10-10 2010-10-15 25000
2 Tony Blank M 1982-08-07 2010-01-05 89000
3 Andrew Russel M 1998-05-04 2012-02-20 28000
4 James Cooper M 2000-10-20 2015-05-10 45000
5 Rose Cutler F 1985-08-08 2015-06-21 65000

Remove Table from a Database

The below SQL statement removes a table from a database.

Run this on IDE

DROP TABLE employees;

After successful execution, use the SELECT statement to fetch the records from the table, which returns an error stating no such table exists.

no such table exists: employees

Remove Database

The below SQL statement removes a database.

Run this on IDE

DROP DATABASE demo;

After successful execution, if we try to use the database using the USE command, an error message will be issued stating that either the "database doesn't exist" or "unknown database".

Database does not exist.
Unknown database.

Tips & Suggestions

Never use the DROP statement unless necessary, as dropping a table or a database is irreversible.

Overall

We now know how to remove a table or database from a database server.

Related Links