SQL DROP Table

In this tutorial, we will help you to understand how to delete the table from the database in SQL with the help of examples.

The DROP TABLE query is used to drop the table schema and all the data from the table; before using the drop table query on any table, take a backup of the table because once the drop table query is used on the table, the table is lost forever. It also removes the indexes, triggers, views, and permission mentioned in the table. The DROP TABLE query cannot be rollbacked.

The syntax of the SQL DROP TABLE query is as follows:

DROP TABLE Table_Name;

Let’s understand how to use the DROP TABLE statement with the help of examples.

First, check whether the Tourist table is available or not in the database. If the Tourist table is available, we will also check the data and then use the drop table query command on the Tourist table.

To check whether the Tourist table is available, use below query as shown below:

DESC Tourist;

Field	Type	Null	Key	Default	Extra
Tourist_Id	Int(11)	NO	PRI	NULL
Tourist_Name	Varchar(40)	NO		NULL
Tourist_Country	Varchar(40)	NO		NULL
Gender	Varchar(1)	NO		NULL

We will execute the select query to check the Tourist table data.

SELECT * FROM Tourist;

Tourist_Id	Tourist_Name	Tourist_Country	Gender
101	Prakash	India	M
102	Amol	India	M
103	Genifer	Australia	F
104	Meghann	Australia	F
105	Temba	South Africa	M
106	Dane	South Africa	F
107	Odean	West Indies	M
108	Ross	NewZealand	M
109	Hayley	West Indies	F
110	Sophie	NewZealand	F

The above two results show that the tourist table is available, and now we are ready to drop the tourist table.

Example 1: Write a query to drop table Tourist.

DROP TABLE Tourist;

We removed the table, indexes, views, and permission specification for the tourist table in the above query from the database.

Now, we will execute the DESC query or the select query on the tourist table to check whether the table is removed or not from the database.

SELECT * FROM Tourist;

The output of the above query is as follows:

As we can see, a tourist table doesn't exist.

We will take another example to drop the table.

First, check whether the Student_Information table is available or not in the database. If the Student_Information table is available, we will check the data and then use the drop table query command on the Student_Information table.

To check whether the Student_Information table is available, use below query as shown below:

DESC Student_Information;

Field	Type	Null	Key	Default	Extra
Studn_info_Id	Int(11)	NO	PRI	NULL
Student_Name	Varchar(40)	NO		NULL
Student_Gender	Varchar(01)	NO		NULL
Student_Age	Int(11)	NO		NULL
Marks	Int(11)	NO		NULL
Degree	Varchar(40)	NO		NULL

We will execute the select query to check the Student_Information table data.

SELECT * FROM Student_Information;

Studn_info_Id	Student_Name	Student_Gender	Student_age	Marks	Degree
1	Priya Chaudhary	F	23	560	BE
2	Utkarsh Kulkarni	M	23	550	B.Tech
3	Rakhi Jain	F	22	580	MCOM
4	Nikita Ingale	F	23	620	BE
5	Piyush Narkhede	M	22	600	BSC
6	Pawan Sharma	M	24	590	B.COM
7	Tushar Mahalle	M	22	680	B.Tech
8	Sakashi Sharma	F	21	650	BSC
9	Gaurav Gupta	M	22	635	B.COM
10	Manish Kapoor	M	23	500	MCOM

The above two results show that the Student_Information table is available, and now we are ready to drop the Student_Information table.

Example 2: Write a query to drop table Student_Information.        

DROP TABLE Student_Information;

In the above query, we removed the table, indexes, views, and permission specification for the Student_Information table from the database.

Now, we will execute the DESC query or the select query on the Student_Information table to check whether the table is removed or not from the database.

SELECT * FROM Student_Information;

The output of the above query is as follows:

As we can see, the Student_Information table doesn't exist.

We will take another example to drop the table.

First, check whether the TP table is available or not in the database. If the TP table is available, we will also check the data and then use the drop table query command on the TP table.

To check whether the TP table is available, use below query as shown below:

DESC TP;

Field	Type	Null	Key	Default	Extra
Tpid	Int(20)	YES		NULL
History	Varchar(30)	YES		NULL
Kilometer	Int(30)	YES		NULL
State	Varchar(15)	YES		NULL
Tpname	Varchar(30)	YES

We will execute the select query to check the TP table data.

SELECT * FROM TP;

Tpid	History	Kilometer	State	Tpname
11	Beauty	160	Karnataka	Beluru
12	Monuments	270	Kerala	Kochi
13	Beach	360	TamilNadu	Marina
14	History	300	Karnataka	Chikmagalur

The above two results show that the TP table is available, and now we are ready to drop the tourist table.

Example 3: Write a query to drop table TP.

DROP TABLE TP;

In the above query, we removed the table, indexes, views, and permission specification for the TP table from the database.

Now, we will execute the DESC query or the select query on the TP table to check whether the table is removed or not from the database.

SELECT * FROM TP;

The output of the above query is as follows:

As we can see, the TP table doesn’t exist.