DROP table Command Example in Oracle, MySQL and SQL Server

DROP is one of the basic SQL commands, which is used to DROP database objects. Since it's part of ANSI SQL, the DROP command is supported by all major database vendors, including Oracle, MySQL, and Microsoft SQL Server. A SQL DROP TABLE statement is used to delete a table definition and all data from a table, but DROP can also be used drop index, view, trigger, or any other database object. By the way, be careful with using the DROP table command because once a table is deleted, all the information available in the table is lost forever.

DROP is sometimes also used to clear tables; for example, removing a large table with millions of records by using SQL DELETE command may take a long time; alternatively, you can DROP and recreate the table. In-fact, the difference between TRUNCATE, DELETE, and DROP is one of the popular SQL Interview questions.

Though DELETE allows you to remove selective records, DROP doesn't provide that option. Also, DELETE is a DML command and can be rolled back, but DROP cannot be rolled back. DROP will also remove all indexes, rows, and privileges, and no DML triggers will be fired.

Though DROP behavior is more or less consistent across MySQL, Oracle, and SQL Server, there are some differences like from Oracle 10g table can be "undropped". Execution of the DROP command is also controlled by access control, so you just cannot drop a table if you don't have sufficient privileges.

Btw, If you are new to SQL and don't understand fundamentals like ALTER, DROP, or co-related sub-queries then I highly recommend you go through a comprehensive SQL course like The Complete SQL Bootcamp by Jose Portilla on Udemy. It's one of the best and also most affordable courses to learn SQL online





How to DROP tables in Oracle, MySQL and SQL Server

Let's see the syntax to drop the table from the database.

DROP TABLE "table_name";

This syntax is the purest form, which DROP table with "table_name" in the current database or schema.

1. SQL DROP TABLE Example in MySQL

Let's see the command to drop a table from the MySQL database.

DROP TABLE Session;


2. SQL DROP TABLE Example in Oracle


Let's see the command to drop a table from the Oracle database.

DROP TABLE marketing_emp;


3. How to drop table in Microsoft SQLServer


Here is the SQL DROP table command for Microsoft SQLServer. It's actually similar to MySQL and Oracle. The following command will drop table Employee in your current database or selected schema.

If you are running this from an SQL Script, you can either use USE database command or can prefix, schema name to the table name. This will give enough hint to the database about dropping a table from which schema.

DROP TABLE Employee;   // drop table from currently selected schema

USE company;
DROP TABLE Employee;   // drop table from company schema

DROP TABLE company.Employee;  // another way to drop table from the arbitrary schema

If you want to learn more about DROP command in SQL Server then you can also check out Microsoft SQL for Beginners course on Udemy. It's a great course and also very affordable, you can buy in just $10 in several Udemy sales

DROP table Command Example in Oracle, MySQL and SQL Server
.

Things to remember about DROP command in SQL

1) DROP is a DDL command.

2) DROP Command cannot be rolled back, except Oracle 10g which provides additional functionality to "undrop" a table as shown in the following example :

SQL> FLASHBACK TABLE Product TO BEFORE DROP;

Flashback complete.

3) DROP table command removes all rows, indexes, and privileges for that table.

4) No DML triggers will be fired as a result of the DROP table command.

5) DROP can be used to clear the database by dropping and recreating them.

6) Difference between DROP and the TRUNCATE command is, DROP command will delete the entire row also the structure. But TRUNCATE command will only remove the contents only, not the structure, so no need to give specifications for another table creation.


That's all about how to drop a table in MySQL, SQL Server, and Oracle. We have not only see DROP table command examples in these three primary databases but also learned a bit about the DROP command in SQL. Since two database/schema may contain tables with the same name, it's wise to prefix schema name before table name while using the DROP command in SQL Server or Oracle. If you do use the DROP command for database clean-up, remember it cannot be rolled back, and no DML trigger is fired.

Other Useful Programming Resources You May Like:
Top 5 Courses to learn SQL and Database Fundamentals
Top 5 Courses to learn Microsoft SQL Server
Top 5 Courses to learn MySQL Database
Top 5 Books for Database Design and Modeling
The 2020 Frontend and Backend Developer RoadMap
Top 4 Books to learn Oracle and PL/SQL
10 Things Every Programmer Should Learn 
Top 5 Advanced SQL Books for Programmers 
5 Free T-SQL and SQL Server Courses for Programmers and DBAs
11 Things Java Developer Should learn in 2020
Top 5 Courses to learn PostgreSQL in 2020

Thanks for reading this article so far. If you like this article then please share it with your friends and colleagues. If you have any questions or feedback then please drop a note.

P. S. - If you are new to SQL and Database in general and want to learn DB fundamentals and SQL basic before deep dive on PostgreSQL then you can also check out this list of free Websites to learn SQL and Database for beginners.


No comments:

Post a Comment