An Introduction to the SQL Drop Command

Udemy Editor

Programming languages are used to accomplish desired solutions to different problems. SQL is a language that operates on a database, which means that it creates, alters, and deletes data using specific commands. The SQL language is widely applied to all types of databases and platforms. It’s a part of the relational database structure, otherwise known as RDBMS, and it is designed to manage data. There are four types of SQL commands: data definition language, data manipulation language, data control language and transaction control language. Queries or command statements are the syntax used to manipulate the database in SQL. SQL statements generally terminate with a semicolon (“;”). SQL uses tables to store the data in a systematic format. Tables are created and the data is filled inside the tables using queries. The data can then be altered whenever the need arises. Sometimes, data becomes obsolete or old and in other cases, the data is stored incorrectly and the table needs to be deleted. You can use the SQL “drop table” command to delete a table and its structure.

Learn more about SQL programming by taking a course at Udemy.com

Importance of the SQL drop table Command

One of the most important DDL (Data Definition Language) commands is “DROP”. It’s essential to drop a table from the database for maintenance purposes since it can become extremely difficult for database administrators to manage tables that are of no use. These tables take up extra space in the database and thus, they need to be deleted.

DDL commands are generally used to deal with the database structure and not on the data. Other DDL commands are CREATE, RENAME, TRUNCATE and ALTER. All these commands are used to alter the shape and size of the columns of the table, also known as the table structure. They have no impact on data. Basically, DROP is used to drop the table or delete the table with its structure. It removes objects from the database, and this command can’t be rolled back. Rollback is a transaction control language command, which allows a user to roll back to the previous saved state and continue again.

Data manipulation is always required in any production database. Another command almost similar to DROP TABLE is known as the TRUNCATE table. The main difference between the DROP and TRUNCATE commands is that the TRUNCATE command deletes data but not the structure. The table’s structure remains the same whereas the DROP command is used to kill the table completely, including attributes. The DELETE command is also another similar command, but it only deletes specific data or rows from the table and maintains the database schema and attributes.

General DROP Syntax

Drop objecttype objectname;

Example:

Now let’s take a table name “employees” as an example, which has a specified structure and attributes.

Employees

DROP employees;

Or mostly it is preferred as:

DROP TABLE employees;

Both the above commands will completely delete the table “employees” from the database, which means that it will delete both the table structure and data. Now, if you try to run any SQL command on the employees table, an error message “Table first_db.employees does not exist” will pop up. Here, “first_db” is the name of our database and “employees” is our table.

Deleting an Entire Database

The DROP command also gives you the flexibility to delete the entire database.

Learn everything about SQL programming at Udemy.com

DROP DATABASE database_name;

Example:

As you can see, the syntax is similar to the DROP TABLE command used earlier. The difference is this command erases the existence of the database. Using the previous database name example, you can use the following command to delete the entire database named “first_db”:

Drop database first_db;

This command will completely delete the database with all its content. Obviously, you want to use this command with caution. Be sure you want to indeed delete your entire database and not just another object such as a table or stored procedure.

We can also use the DROP command for deleting the index of the table and can use it with the ALTER command which is a DDL command.

Deleting an Index:

ALTER TABLE tablename
DROP INDEX indexname;

When an index is no longer useful, you can delete it permanently by using the above command. Indexes sort data and help speed up queries. If the index is not helping performance, it should be dropped from the table.

When you want to delete a particular column from the database table, the DROP COLUMN command is used with the ALTER command.

Dropping a Table Column:

ALTER TABLE tablename
DROP COLUMN columnname;

Example:

ALTER TABLE employees
DROP COLUMN Emp_city;

The above example will delete the column “Emp_city” from the table “employees” (used in a previous example) without affecting the rest of the columns. Now, the table will look like this:

Dropping Multiple Tables

It’s also possible to delete multiple tables from the database at the same time. It’s one of the best features offered by the DROP command.

Syntax:

DROP TABLE table_name1,table_name2,table_name3,……;

Example:

As an example, let’s take another table named “consumers” and our previous table “employees”. If you want to delete the tables named “employees” and “consumers” from the database, it will look like this:

DROP TABLE consumers, employees;

In certain scenarios, you might accidentally attempt to delete a table from the database that doesn’t exist. In this situation, you get an error message that says “Table databasename.tablename does not exist”. ORACLE and MYSQL provides a command that prevents errors like this from occurring. “IF EXISTS” can be placed between the DROP TABLE and table name. This phrase signals the database to execute the DROP statement only if the table exists. If the table does not exist, nothing will be executed and no error message will be shown.

Learn SQL programming in just six days with a course at Udemy.com

Syntax: