GeekInterview.com
Series: Subject:
Question: 151 of 234

What is the difference between DROP,DELETE and TRUNCATE.. Can anyone tell why we require EXISTS with example.

Asked by: Interview Candidate | Asked on: Sep 26th, 2006
Showing Answers 1 - 13 of 13 Answers

Hi Abhi,

Drop and Truncate are DDL, With Drop Command we can remove entire Table or columns from database. With Truncate we can remove the records in the table by keeping table structure.

Drop and Truncate are AutoCommit.

By using Delete command we can remove rows in the table but its not autocommit

  
Login to rate this answer.
Lalita.p

Answered On : Sep 29th, 2006

Hi

Delete Command removes all the rows from the table and the deleted record get logged into transaction log which slow down the performance. While truncate table also deletes the records from the table but does not log the deleted record. Records deleted with truncate table can be autocomitted.

Drop table is used to drop the table.

Delete table deletes rows as well as remove the table skeleton. while truncate table only removes rows from the table.

  
Login to rate this answer.

Command

Table structure deleted

Records deleted

Auto-commit

DROP

Y

NA

Y

TRUNCATE

N

Y-all records

Y

DELETE

N

Y-selectively

N

 

Yes  1 User has rated as useful.
  
Login to rate this answer.

Hi By using DROP we can delete the table(which includes records) from the Database.However by using TRUNCATE we can delete the records in the table permanently.DELETE comand is also used to delete the records from the table.The difference is using DELETE we can retrieve datas using ROLLBACK but this can't be done in the case of TRUNCATE.

  
Login to rate this answer.

Delete is a DML(Data Manipulation Language). Delete command is deletes the record from the existing table.The systax for Delete is

1)sql> Delete from .

This Deletes All the Record From The TAble

2) sql> Delete from

   Where 

This deletes a perticular set of record.

Note:Delete is not Autocommit Statment(Infact None os the DML are auto commit)

Drop and Truncate both are DDL(Data Definition Language).

Drop {Delete or drops} the table with it's structure. It is as autocommit statment.Drops Once fired can not be rolled back.

syntax:

   sql>drop table

Trucate is the command used to delete all record from table. but the structure of the table remain same.It is also a autocommit statment.

syntax;

sql>truncate table

  
Login to rate this answer.
suresh kumar chappali

Answered On : Nov 9th, 2006

Delete:It's an DML command which is used for deleting particular table in the database...even though we delete the data we can regain it by rollback command.

Example:SQL> SELECT COUNT(*) FROM emp;

  COUNT(*)
----------
        14

SQL> DELESQL> SELECT COUNT(*) FROM emp;

  COUNT(*)
----------
        14

SQL> DELETE FROM emp WHERE job = 'CLERK';

4 rows deleted.

SQL> COMMIT;

Commit complete.

Truncate:This command is a DDL Command which is used to delete a table...I Just Conclude it as Truncate=Delete+Commit...once u applied this on table u can't get the data back...but the structure will be there..

Example:

SQL> TRUNCATE TABLE emp;

Table truncated.

SQL> SELECT COUNT(*) FROM emp;

  COUNT(*)
----------
         0

Drop:It is used to destroy entire structure of a table...once we apply this command on any particular table we can't get it in anyway...so be cautious while applying this command.

Example:

SQL> DROP TABLE emp;

Table dropped.

SQL> SELECT * FROM emp;
SELECT * FROM emp
              *
ERROR at line 1:
ORA-00942: table or view does not exist

  
Login to rate this answer.
Pawan Ahuja

Answered On : Dec 4th, 2006

Drop means we can delete the table from database,Delete means we can delete the record from table bcz it's a DML command we can also rollback.

but in truncate we can not rollback the records.

Exists Command:

Example:

select * from emp where sal in(select sal from emp)

exists means

select * from emp where exists(select sal from emp)

Regards

Pawan Ahuja

  
Login to rate this answer.
shelkumar handa

Answered On : Feb 7th, 2007

Hi to all of you

Drop command generally removes the table element, but another copy of record is also present in oracle server engine. When you issue delete command it removes the record from the table but the memory space that is taken by the record is not deleted that is why using ROLLBACK command that records are returned. But in case of TRUNCATE command memory taken by record is also erased and hence ROLLBACK command will not work, so after using TRUNCATE command will give better performance in oracle server.

  
Login to rate this answer.
subrahmanyam

Answered On : Feb 10th, 2007

Guys,Everyone here is correct.But you missed another important point here.TRUNCATE WILL NOT FIRE DELETE TRIGGERThank you

  
Login to rate this answer.
velsowmya

Answered On : Aug 7th, 2007

View all answers by velsowmya

Delete is a DML command.we can remove more than one row using delete command
Truncate is used for deleting the entire rows in the table.we can get the data back.where as in delete we can rollback data.
Drop cammand is used for dropping the entire tabel

  
Login to rate this answer.
Guru Shetty

Answered On : Aug 7th, 2007

Drop command is used 2 drop the whole table;
It will release the space which is occupied by the database.

Through delete command
you can delete the records as well as you can rollback also.
Autocommit is not in built here but it will not release the space viz is occupied.
For e.g
you have a table of 50MB & you have deleted 50% of its records from the table then your table size should be 25MB. But it will not be so in delete case, it will be around 45MB After deleting because it will not release the space.

You can also give conditions for delete.

Truncate removes all the records from a table releasing space also.

  
Login to rate this answer.
lakan reddy

Answered On : Sep 21st, 2012

delete:delets the records from the table by using where claus
truncate:removes all the records from the table and frees the space containing by the table
drop:drops the table and its structure

  
Login to rate this answer.
Nazeera Jaffar

Answered On : Sep 26th, 2012

Delete:
Deletes the contents of the table.Also allows us to delete particular row(s) by condition.We can undo the delete operation and write trigger on delete action.
Truncate:
Delete the contents of the table leaving the structure.we Cannot undo or use condition,triggers .
Drop:
Delete the structure as well as the entire table from the database. We can undo the delete operation and write trigger on delete action.

  
Login to rate this answer.

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

Related Open Questions

Ads

Connect

twitter fb Linkedin GPlus RSS

Ads

Interview Question

 Ask Interview Question?

 

Latest Questions

Interview & Career Tips

Get invaluable Interview and Career Tips delivered directly to your inbox. Get your news alert set up today, Once you confirm your Email subscription, you will be able to download Job Inteview Questions Ebook . Please contact me if you there is any issue with the download.