DELETE command operates at the table level, and you use the WHERE clause to restrict the set of rows to delete.
If you omit the WHERE clause, the DELETE command results in an empty table.
Consider the following two commands:
drop table departments delete from departments
The DROP TABLE command not only removes the contents of the table, but also the table itself, including all dependent objects/structures such as indexes and privileges.
DROP TABLE is a data definition (DDL) command.
The DELETE command does not change the database structure, but only the contents-it is a data manipulation (DML) command.
A DROP TABLE command cannot be undone with a ROLLBACK command, as opposed to the effects of a DELETE command, which can.
Example of a DELETE Command
SQL> SQL>-- from w w w . j a va 2 s .c om SQL> drop table salgrades; Table dropped. SQL> create table salgrades( 2 grade NUMBER(2) primary key, 3 lowerlimit NUMBER(6,2) not null check (lowerlimit >= 0), 4 upperlimit NUMBER(6,2) not null, 5 bonus NUMBER(6,2) not null); SQL> SQL> insert into salgrades values (1, 700,1200, 0); SQL> insert into salgrades values (2, 1201,1400, 50); SQL> insert into salgrades values (3, 1401,2000, 100); SQL> insert into salgrades values (4, 2001,3000, 200); SQL> insert into salgrades values (5, 3001,9999, 500); SQL> SQL> select * 2 from salgrades; GRADE | LOWERLIMIT | UPPERLIMIT | BONUS --------- | ---------- | ---------- | --------- 00001.00 | 00700.00 | 01200.00 | 00000.00 00002.00 | 01201.00 | 01400.00 | 00050.00 00003.00 | 01401.00 | 02000.00 | 00100.00 00004.00 | 02001.00 | 03000.00 | 00200.00 00005.00 | 03001.00 | 09999.00 | 00500.00 SQL> SQL> delete from salgrades 2 where grade = 5; 1 row deleted. SQL> SQL> SQL> select * 2 from salgrades; GRADE | LOWERLIMIT | UPPERLIMIT | BONUS --------- | ---------- | ---------- | --------- 00001.00 | 00700.00 | 01200.00 | 00000.00 00002.00 | 01201.00 | 01400.00 | 00050.00 00003.00 | 01401.00 | 02000.00 | 00100.00 00004.00 | 02001.00 | 03000.00 | 00200.00 SQL>