Diifference between Drop and Delete SQL statemets

[Solaris]

I am a student of SQL ,actually speaking I am a commerce background student , but currently pursuing a certification course in which we make use of The SQLServer2005 tool. We were being told about the different sorts of SQL statements being used in the SQL in order to fetch and modify the contents of the database , since those statements includes a big number. However I was concerned about knowing the primary difference the Delete and the Drop statements . I was very confused in that please explain in brief.

[charithainfadev]

DELETE is a DML statement. That means Data Manipulation Language statement. It is used to manipulate the data existing in a table. If we give

DELETE from <table name>

deletes the data in that table.

On the other hand, DROP is a DDL statement. Data Definition Language. It is used to manipulate the Database objects like for dropping tables or constraints or indexes. Its syntax is like this.

DROP <table name> 
DROP <index name>

Hope that helps you.

[Shreevats]

There is a vast difference between the Delete and the Drop table the Delete statements a DML statement and is basically used for the manipulation of the records manipulation of the record of a table stored in any database while , it is used to delete any record based on certain conditions there is no change in the table structure after its execution. While as far as the DROP statements are concerned they will remove the entire table from the database, they fall under DDL statements .

[Vandam]

DELETE from Emplyee 
	WHERE emp_sal> 3000

As you can see a simple example of the Delete statement , the Delete statement comes first then comes the FROM Clause in which you have to mention the name of the table from which you want to delete the record and then comes the WHERE clause that includes all the conditions that are needed to be satisfied , in the given example the SQL will delete all the records for the employee whose salary exceeds 3000, if you do not mention the WHERE clause then by default all the records will get deleted but the structure of the table will still remain as it is , you will be able to see just the name of the fields without any records.

[Villiers]

The Drop table is used to drop the entire table from the database , as it is said earlier as well that it is a Data Definition statement or you can say that it is a DDL statement , the syntax for the DROP table is given below :

DROP TABLE TABLENAME

Go through the example shown below.

DROP TABLE Employee

The above statement will remove the entire table that is with the name Employee from the database , unlike Delete statement it will remove the structure . I think now you got the difference.

[Ronchi]

Well you just went through and are now familiar with the fundamental difference that lies between the Delete and Drop table statements , but there is one thing that applicable for bot the table. That is about the constraint . If there is any foreign key constraint being applied on any of the table and if you execute any of the given statement then this execution will not have produce any result but infact it will ask you to remove the constraint first and then perform the requred action . This privileges are in the hands of administrator.