Dropping a table is very easy. The format of the command is as follows:

A drop table command will drop the table and commit the changes to the database. If you have indexes or grants on the table the drop command will also drop them and all entries to them in the system catalog.

We will use the DROP command in the exercise below.

Looking at our EMPLOYEE table we see we still have the TEST column contained therein. Suppose we want to return our EMPLOYEE table to its previous state before the alter commands. We may do so by following these steps:

1. Create a temporary table, TEMP off the EMPLOYEE table with all the columns except the TEST column using the 'AS SELECT' clause. A create table command done in this fashion will insert all the rows into the table being created from the original table indicated in the FROM clause.
2. Drop the EMPLOYEE table.
3. Create a new EMPLOYEE table from the TEMP table selecting all the columns. This will also insert all the rows into the table.
4. Drop the TEMP table.

Describe the EMPLOYEE table to verify the presence of the TEST column.

Step 1. Create a temporary table and populate it with data from the EMPLOYEE table.

Describe the TEMP table. Note the TEST column is not present in this table. Select all rows to view the data in the table.

The CREATE table command not only built our table, it also populated all the rows in it.

Step 2. Now drop the EMPLOYEE table because it still contains the TEST column.

The table is dropped. 

Step 3. Issue another create table command to build and populate the new EMPLOYEE table:

The table is created. Describe the EMPLOYEE table. Select all rows from the EMPLOYEE table to view the data.

Step 4. We no longer need the TEMP table so we drop it.

Last revised 11/4/1997 by J. Warren, Kennesaw State University

Department of Computer Science and Information Systems