DELETE Statement - SQL Query

Different possible ways to delete Statement

Consider the following table 'Students'

Studentid	Firstname	Lastname	Email	Phone
1	Jaya	Singh	Jaya@tutorialride.com	34542892
2	Surendra	Morya	Surendra@tutorialride.com	92384900

i) Delete a row where Stud_ID = 2.
ii) Delete all rows from the table.
iii) Delete table from database.

Answer:

i) To delete row where Stud_ID = 2. The query should be written as:

DELETE FROM Students WHERE Stud_ID= 2

Result:

Studentid	Firstname	Lastname	Email	Phone
1	Jaya	Singh	Jaya@tutorialride.com	34542892

ii) To remove all rows, the query should be written as:

DELETE FROM Students

This query will delete only records present in the table and not table. Another command is used to remove all rows from the table:

TRUNCATE TABLE Students

Note: If the column contains constraints or other dependencies, an error message will be displayed. This error is resolved by deleting the referenced constraints. The TRUNCATE Command is DDL(Data Definition Language) therefore  TRUNCATE TABLE statement is much faster than DELETE FROM Statement.

Result:

Studentid	Firstname	Lastname	Email	Phone

iii) To delete table, the query should be written as:

DROP TABLE Students

This query will delete table 'students' permanently.

Note: The delete command is used to delete a records from column.

DROP Table command is used to delete a whole table.
The DROP is DDL(Data Definition Language and The DELETE is DML (data Manipulation Language) therefore the DELETE command can be rolled back, while DROP command can not be rolled back.