Dropping a database in SQL is a critical operation that permanently deletes the database and all its associated objects, such as tables, views, procedures, and data. It is essential to exercise caution when performing this action as it cannot be undone, and all data will be lost irreversibly.

To drop a database in SQL, you typically use the DROP DATABASE statement followed by the name of the database you want to delete

SQL DROP Database Statement

Following are the important points to remember before you delete an existing database -

• Make sure you have taken proper backup of database before you delete it.
• Make sure no other application is connected and using this database.
• Make sure you have the necessary privilege to delete the database. Usually an admin can delete the database.

Syntax

Following is the syntax to delete a database in SQL -

DROP DATABASE DatabaseName;

Here, the DatabaseName is the name of the database that you want to delete. A database name is always unique within the RDBMS.

Example

First of all, let us create multiple databases into database system using the following SQL queries -

CREATE DATABASE sample_DB1;
CREATE DATABASE sample_DB2;
CREATE DATABASE Sample_DB3;
CREATE DATABASE Sample_DB4;

Let us verify whether the databases are created or not using the following query -

SHOW DATABASES;

This will list down all the available databases:

Now, let us try to delete the Sample_DB1 database using the SQL DROP DATABASE statement -

DROP DATABASE Sample_DB1;

Once we have deleted the Sample_Db1 database, we can verify whether it is deleted or not using the SQL SHOW DATABASES statement -

SHOW DATABASES;

This will list down all the available databases:

Great ! you have successfully deleted a database in SQL.

SQL DROP DATABASE IF EXISTS Statement

The SQL DROP DATABASE IF EXISTS statement includes a condition to check whether the database exists before trying to delete it. If the database does not exist in the database system, the "DROP DATABASE IF EXISTS" statement does not raise an error, but it simply terminates without taking any action.

Syntax

Following is the syntax of the DROP DATABASE IF EXISTS statement in SQL -

DROP DATABASE IF EXISTS DatabaseName;

Here, the DatabaseName is the name of the database that you want to delete.

Example

Let us try to delete an existing database sample_DB2 in the database system using the following SQL statement -

DROP DATABASE IF EXISTS sample_DB2;

When we execute the above SQL statement, the output is obtained as follows -

Query OK, 0 rows affected, 3 warnings (0.024 sec)

Dropping the Database that doesn't Exist

Let us try to drop a database Sample_DB2 that doesn't exist in the database system using the following SQL statement -

DROP DATABASE IF EXISTS Sample_DB2;

When we execute the above SQL statement, the output is obtained as follows -

Query OK, 0 rows affected, 1 warning (0.000 sec)