SQL DROP Database

The SQL Drop Database statement deletes the existing database permanently from the database system. This statement deletes all the views and tables if stored in the database, so be careful while using this query in SQL.

Following are the most important points which are required to learn before removing the database from the database system:

• This statement deletes all the data from the database. If you want to restore the deleted data in the future, you should keep the backup of data of that database which you want to delete.
• Another most important point is that you cannot delete that database from the system which is currently in use by another database user. If you do so, then the drop statement shows the following error on screen:

Cannot drop database "name_of_the_database" because it is currently in use.  

Syntax of Drop Database Statement in SQL

DROP DATABASE Database_Name;  

In this SQL syntax, we have to specify the name of that database which we want to delete permanently from the database system. We have to write the name of the database after the DROP DATABASE keyword in every example.

We can also delete multiple databases easily by using the single DROP syntax:

DROP DATABASE Database_Name1, [ Database_Name2, ......., Database_NameN ] ;  

Using this statement, we have no need to write multiple statements for deleting multiple databases. We can specify all the databases by using a comma in a single statement, as shown in the above syntax.

Examples of Drop Database Statement in SQL

In this article, we took the following two examples that will help how to run and perform the Drop Database query in SQL:

Example1:

Suppose, we want to delete the Student database with all its data from the database system so, firstly we have to check that the Student database exists in the system or not by using the following statement:

SHOW DATABASES ;  

If the Student database is shown in the output, then we have to type the following query in SQL for removing the Student database:

DROP DATABASE Student;  

Can't drop database 'Student'; database doesn't exist

If the Student database does not exist in the database system and we run the above query in SQL, then the query will show the following output:

Example2:

Suppose, we want to delete the College database with all its tables and views from the database system, firstly we have to check that if the College database exists in the system or not by using the following statement:

SHOW DATABASES;  

If the College database is shown in the output, then you have to type the following query in SQL for removing the College database permanently:

DROP DATABASE College;  

If the College database does not exist in the database system, and we run the above query in SQL, then this query will show the following output:

Can't drop database 'College'; database doesn't exist