Difference Between Delete and Truncate and Drop.

DELETE

• DELETE is a DML statement.
• DELETE removes some rows if WHERE clause is used
• DELETE keeps records in buffers(temporary storage) till the first ROLLBACK or COMMIT. 
• Can be rolled back
• Can be used with or without WHERE clause
• Does not reset identity of the table
• Triggers will be fired.

When DELETE operation is performed, all the data get copied into Rollback Tablespace first,
and then delete operation get performed. Hence we can get back the data by ROLLBACK command.

SYNTAX:
To delete a particular row

DELETE FROM table_name
WHERE column_name = column_value

To delete all rows

DELETE FROM table_name
Or
DELETE * FROM table_name

DROP

1. DROP is a DDL statement.
2. Removes a table from the database. Table structures, indexes, privileges, constraints will
also be removed.
3. Cannot be rolled back
4. No Triggers will be fired.

SYNTAX:
DROP TABLE table_name

TRUNCATE
1. TRUNCATE is a DDL Statement.
2. Removes all rows from a table, but the table structures and its columns, constraints, indexes
remains.
3. Cannot be rolled back
4. Resets the identity of the table
5. Truncate is faster and uses fewer system and transaction log than delete.
6. Cannot use TRUNCATE on a table referenced by a FOREIGN KEY constraint.
7. No Triggers will be fired.
8. Cannot use WHERE conditions.
9.TRUCATE is faster and doesn't use as much undo space as a DELETE.

SYNTAX:
TRUNCATE TABLE table_name