What are the differences between DELETE vs TRUNCATE vs DROP ?
The first one is DML whereas the last two are DDL but can which are rollbackle and which are not ?

It will depends the RDBMS => DELETE vs TRUNCATE vs DROP :

Rollbackable ?	SQL Server	Oracle	PostgreSQL	MySQL
DELETE	Yes	Yes	Yes	Yes
TRUNCATE	Yes	Yes	Yes	No
DROP TABLE	Yes	No	No	No

Notes:

• SQLServer:

  • Transaction Log: Every modification (whether it’s data modification or schema modification) in SQL Server is logged in the transaction log before the actual data pages are changed. This write-ahead logging ensures that the database can always be restored to a consistent state.

  • DROP TABLE or TRUNCATE TABLE Operation: When you issue a DROP TABLE or TRUNCATE TABLE, SQL Server minimally logged this operation in the transaction log. It records the deallocation of the pages associated with the table. The actual data pages might still be present, but they are marked as deallocated and will be eventually overwritten during normal database operations.

  • ROLLBACK: If you roll back the transaction after a DROP TABLE, SQL Server uses the transaction log to undo the operation. It reads the log to see which pages were deallocated and restores them. This brings the table back to its state before the DROP.

• Oracle:

  TRUNCATE is technically rollbackable in Oracle because it generates a minimal amount of redo and undo log records. However, since it’s a DDL operation, it will auto-commit by default unless you have set the session to use an autonomous transaction.

  
  DECLARE
      PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
      -- Truncate the table
      EXECUTE IMMEDIATE 'TRUNCATE TABLE your_table_name';
  
      ROLLBACK;
  END;
  /  

• PostgreSQL :

  PostgreSQL handles TRUNCATE within a transaction context similarly to how it deals with other SQL commands. That is, if you TRUNCATE a table within a transaction and then rollback that transaction, the table’s data will be restored.

  Internally, PostgreSQL achieves this by using its Multi-Version Concurrency Control (MVCC) system. When you execute a TRUNCATE, it effectively creates a new empty version of the table and makes it immediately visible. The old data still exists in the background. If you commit the transaction, the old data gets marked for cleanup. If you rollback, the old version of the table gets reinstated, and the new empty version is discarded.

• MySQL:

  • **For the InnoDB storage engine (which supports transactions), the TRUNCATE TABLE statement is not logged and hence is non-rollbackable within a transaction.

I hope this article help you to clarify differences between DELETE vs TRUNCATE vs DROP