DROP TABLE statement: It is used to remove a table. A user can drop his own table or if he has the permission he can drop any table from the database. For dropping a table, unqualified table name is used. When a DROP TABLE statement is used the definition and all the contents of the table are completely removed. There is no way to recover the contents of table.
DROP TABLE ORDERS
ALTER TABLE statement: It can be used for adding a column, dropping a column, modifying the default value for a column, adding or dropping a primary key, adding or dropping a new foreign key, adding or dropping a check constraint and adding or dropping a uniqueness constraint.
To add a column: ALTER statement with ADD clause is used to add a column. The new column is placed right most in the column.
ALTER TABLE ORDERS ADD CLIENT_NAME VARCHAR(30)
The default for a new column is that values can be NULL. If you wish to add a default value to the column you have to declare column as NOT NULL WITH DEFAULT.
ALTER TABLE ITEMS ADD MIN_QUANTITY INTEGER NOT NULL WITH DEFAULT 0
To drop a column: ALTER statement with DROP clause is used to remove a column or columns.
ALTER TABLE ORDERS DROP CLIENT_NAME
Drop rules in ALTER TABLE statement:
RESTRICT rule: If a object of a database like foreign key is related to column which needs to be dropped, then ALTER TABLE statement returns with an error and the column is not removed.
CASCADE rule: If a object of a database like foreign key is related to column which needs to be dropped, then ALTER TABLE statement drops the column and the foreign key.
To drop a primary key: ALTER statement is also used to drop a primary key.
ALTER TABLE COMPANIES DROP PRIMARY KEY (CITY)