How to use DROP command in SQL?

How to use DROP command in SQL?

5 mins read13.7K Views Comment
clickHere
Vikram
Vikram Singh
Assistant Manager - Content
Updated on Jun 21, 2023 17:22 IST

DROP command in SQL is a DDL command that permanently removes the data (or existing table) from the database and free the space from the memory. In this article, we will briefly discuss how to drop column, table, or database in SQL.

2022_08_MicrosoftTeams-image-237.jpg

In the previous article, we have discussed how to remove the data from the existing table using SQL TRUNCATE and SQL DELETE command. Now in this article, we will again discuss how to remove the data or table from the database using DROP command.

So, what’s the difference between SQL TRUNCATE, SQL DELETE and SQL DROP and how SQL DROP works.

Let’s explore in this article.

Table of Content

What is the DROP command in SQL?

DROP command in SQL removes table definition and all the data from one or more database tables.

  • It is a Data Definition Language (DDL) command.
  • It removes all data/indexes/triggers/constraints/ and permission specific to that table
  • SQL DROP TABLE statement permanently removes the data from the tables and the metadata that defines the table in the data dictionary.

Note 

  1. Dropping a table or a database is an irreversible process, so be extra careful while dropping the table or database
  2. SQL DROP TABLE command can’t be used to remove a table that a Foreign Key references

Syntax

The syntax to drop the table is a straightforward DROP TABLE command followed by the table names, i.e.

 
DROP TABLE table_names;
Copy code

Note: You can drop multiple tables at a time.

Now, let’s have some examples to understand better how to use the SQL DROP command.

So, firstly we will create a student table of a class that contains the record of a student who scored maximum marks in a particular subject using CREATE TABLE  and INSERT command in SQL.

 
CREATE TABLE Student(
    RollNo int PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Gender TEXT NOT NULL;
    Subject VARCHAR(30),
    MARKS INT (3)
);
INSERT INTO Student VALUES (1, Vaibhav, M, Mathematic, 100);
INSERT INTO Student VALUES (2, Vishal, M, Physics, 79);
INSERT INTO Student VALUES (3, Saumya, F, Chemistry, 95);
INSERT INTO Student VALUES (4, Arun, M, English, 78);
INSERT INTO Student VALUES (5, Anjum, F, Hindi, 83);
INSERT INTO Student VALUES (6, Radhika, F, Biology, 57);
INSERT INTO Student VALUES (7, Harpreet, F, Physical Education, 68);
SELECT * FROM Student;
Copy code

Output

RollNo Name Gender Subject Marks
1 Vaibhav M Mathematics 100
2 Vishal M Physics 79
3 Saumya F Chemistry 95
4 Arun M English 78
5 Anjum F Hindi 83
6 Radhika F Biology 57
7 Harpreet F Education 68

Must Read: SQL SELECT

Now, we will use the above Student table in our examples.

SQL DROP COLUMN

SQL DROP COLUMN is used to delete the specified column(s) from the table or the database.

Syntax

 
ALTER TABLE table_name
DROP COLUMN column_names;
Copy code

Must Read: Introduction to SQL

Single Column

Example: Drop the column name Marks from the Student table.

Query

 
ALTER TABLE Student
DROP COLUMN Marks;
Copy code

Output

RollNo Name Gender Subject
1 Vaibhav M Mathematics
2 Vishal M Physics
3 Saumya F Chemistry
4 Arun M English
5 Anjum F Hindi
6 Radhika F Biology
7 Harpreet F Education

Must Read: UPDATE query in SQL

Must Read: How to CREATE, UPDATE, INSERT and DELETE SQL views

Multiple Columns

Example: Drop marks and subject columns from the Student table.

Query

 
ALTER TABLE Student
DROP COLUMN Marks, Subject;
Copy code

Output

RollNo Name Gender
1 Vaibhav M
2 Vishal M
3 Saumya F
4 Arun M
5 Anjum F
6 Radhika F
7 Harpreet F

Must Read: SQL Logical Operator

Must Read: Introduction to Normalization

SQL DROP TABLE

SQL DROP TABLE commands are used to delete the specified table from the database.

Syntax

 
DROP TABLE table_name;
Copy code

Example: Remove the Student table from the database.

Query

 
DROP TABLE Student;
Copy code

We can drop indexes, constraints, and databases like the SQL DROP COLUMN and DROP TABLE.

Syntax for DROP Database

 
DROP DATABASE database_name;
Copy code

Syntax for DROP Constraints

 
ALTER TABLE table_name
DROP CONSTRAINTS constraint name;
Copy code

Syntax to DROP Index

 
DROP INDEX index_name;
Copy code

Must Read: What is the difference between SQL and MySQL?

Must Read: Difference between SQL and NoSQL

DROP TABLE IF EXISTS

When there are multiple tables in any database, and you want to remove any table from the database but are unsure whether that table exists in the database or not. So, in that case, we can add an optional IF EXISTS command while dropping the table.

Syntax

 
DROP TABLE IF EXISTS table_name;
Copy code

Must Read: SQL LIMITS

Must Check: Database and SQL Online Course and Certifications

DELETE vs. TRUNCATE vs. DROP

DELETE TRUNCATE DROP
It is a DML command It is a DDL command. It is a DDL command
DELETE FROM tble_nameWHERE conditions; TRUNCATE TABLE table_name; DROP TABLE table_name;
It removes one or more records from the table It removes all the rows from the existing table It completely removes the table from the database 
It doesn’t free the allocated space of the table. It doesn’t free the allocated space of the table. It completely removes the allocated space for the table from the memory.
It is slower than the DROP and TRUNCATE command as it deletes one row at a time base on the specified conditions It is faster than both the DELETE and TRUNCATE command as it deletes all the records at a time without any condition It is faster than DELETE but slower than TRUNCATE asit firstly deletes the rows and then the table from the database. 

Must Read: All about DML Command in SQL

Must Read: SQL ACID Properties

Conclusion

In this article, we have briefly discussed how to use drop command in sql with examples.

Hope this article, will help you in your data science/data analysis journey.

Keep Learning!!
Keep Sharing!!

FAQs

Can I use the DROP command without confirmation?

By default, many database management systems (DBMS) prompt for confirmation before executing the DROP command. However, some DBMSs provide options to disable the confirmation prompt or provide a way to automatically confirm the operation. It is important to be cautious when using these options to avoid accidental deletions.

Are there any restrictions on using the DROP command?

Yes, the DROP command may have restrictions depending on the database system and the user's permissions. Typically, only users with sufficient privileges, such as database administrators or owners of the object, can execute the DROP command. It is important to have the necessary permissions to avoid unauthorized deletions.

Can I recover the dropped objects using the DROP command?

No, the DROP command permanently removes the specified object and its data from the database. Once an object is dropped, it cannot be easily recovered. It is important to exercise caution when using the DROP command and ensure that you have a backup or a plan in place to handle data loss.

Download this article as PDF to read offline

Download as PDF
clickHere
About the Author
author-image
Vikram Singh
Assistant Manager - Content

Vikram has a Postgraduate degree in Applied Mathematics, with a keen interest in Data Science and Machine Learning. He has experience of 2+ years in content creation in Mathematics, Statistics, Data Science, and Mac... Read Full Bio

Comments

We use cookies to improve your experience. By continuing to browse the site, you agree to our Privacy Policy and Cookie Policy.