ALTER TABLE Statement SQL

The ALTER TABLE statement allows you to rename an existing table. It can also be used to add, modify, or drop a column from an existing table.

Syntax for Rename a Table:

ALTER TABLE table_name RENAME TO new_table_name;

Example:

ALTER TABLE suppliers RENAME TO vendors;

This will rename the suppliers table to vendors.

---

Syntax for Add New Column:

ALTER TABLE table_name ADD column_name column-Datatype;

Example:

ALTER TABLE supplier ADD supplier_name  varchar(50);

Add Multiple Columns

ALTER TABLE supplier ADD (supplier_name varchar(50), city varchar(45) );

---

Modifying existing Column in Table:


Syntax:

ALTER TABLE table_name  MODIFY column_name column_type;

Example:

ALTER TABLE supplier  MODIFY supplier_name   varchar2(100)     not null;

This will modify the column called supplier_name to be a data type of varchar2(100) and force the column to not allow null values.

---

Modifying multiple existing Columns in Table:


Syntax:

ALTER TABLE table_name MODIFY (column_1column_type, column_2column_type, ... column_n column_type );

Example:

ALTER TABLE supplier MODIFY (supplier_name varchar(100) not null, city varchar(75) );

---

DROP COLUMNS FROM TABLE:


Syntax:

ALTER TABLE table_name DROP COLUMN column_name;

Example:

ALTER TABLE supplier DROP COLUMN supplier_name;

This will drop the column called supplier_name from the table called supplier.

---

Rename column(s) in a table:

Syntax:

ALTER TABLE table_name RENAME COLUMN old_name to new_name;

Example:

ALTER TABLE supplier RENAME COLUMN supplier_name to sname;

This will rename the column called supplier_name to sname.

Thanks