SQL Drop Table

In this lesson, you will learn how to delete a database table using SQL.

Drop table in SQL

The DROP TABLE statement is used to delete an existing SQL table.

Syntax

DROP TABLE table_name;

You should replace table_name with the name of the table that you want to delete.

Delete Table Example

The following SQL statement deletes the "books" table that we created in the previous lesson.

DROP TABLE books;
    

Delete Table If Exists

If you try to delete a table that is not exists in the database, you will get an error response saying:

If you try to delete the "books" table again without the optional clause IF EXISTS, you will get an error as follows.

DROP TABLE books;
    

However, if you try to delete the "books" table with the optional clause IF EXISTS, you will get a warning message instead of the error message.

DROP TABLE IF EXISTS books;
    

Delete Table Exercise

1- Write a query that creates a new database called "training".
Note: you should recreate it if it already exists.

2- Write a query that creates a table called "products" and check whether it exists or not. the table should contain the following fields:

• title VARCHAR(200)
• description VARCHAR(255)
• production_date DATE
• expiry_date DATE
• price DECIMAL(5,2)

3- Write a query that deletes the "products" table if it exists.

4- Write a query that deletes the "training" database if it exists.

DROP DATABASE IF EXISTS training;

CREATE DATABASE training;

USE training;

CREATE TABLE IF NOT EXISTS products (
    title VARCHAR(200),
    description VARCHAR(255),
    production_date DATE,
    expiry_date DATE,
    price DECIMAL(5,2)
);

DROP TABLE IF EXISTS products;

DROP DATABASE IF EXISTS training;