SQL alter table statement usage

Rename a table
The basic syntax for renaming a table is:

The code is as follows:	Copy code
Alter table table_name Rename to new_table_name; For example: Alter table suppliers

This will rename the Supplier table vendor.

 

Add columns in the table (S)
Syntax #1

To add an existing TABLE column, the alter table syntax is:

The code is as follows:	Copy code
Alter table table_name ADD column_name column-definition; For example: Alter table supplier ADD supplier_name varchar2 (50 );

This will add a column named supplier_name in the supplier table.

 

Syntax #2

To add multiple columns to an existing TABLE, the alter table syntax is:

The code is as follows:	Copy code
Alter table table_name ADD (column_1 column-definition, Column_2 column-definition, ... Column_n column_definition ); For example: Alter table supplier ADD (supplier_name varchar2 (50 ), City varchar2 (45 ));

This will add two columns (supplier_name city) to the supplier table.

 

Modify columns (S) in a table)
Syntax #1

To modify an existing TABLE column, the alter table syntax is:

The code is as follows:	Copy code
Alter table table_name MODIFY column_name column_type; For example: Alter table supplier MODIFY supplier_name varchar2 (100) not null;

This modifies the so-called supplier_name, which is a VARCHAR2 data type (100) and forces the column to not allow null values.

 

Syntax #2

To modify multiple columns in an existing TABLE, the alter table syntax is:

The code is as follows:	Copy code
Alter table table_name MODIFY (column_1 column_type, Column_2 column_type, ... Column_n column_type ); For example: Alter table supplier MODIFY (supplier_name varchar2 (100) not null, City varchar2 (75 ));

This modifies the supplier_name and City columns.

 

(S) delete columns in a table
Syntax #1

To delete an existing TABLE column, the alter table syntax is:

The code is as follows:	Copy code
Alter table table_name Drop column column_name; For example: Alter table supplier Drop column supplier_name;

This will drop the table column named supplier supplier_name.

 

Rename a column in the table (S)
(New in Oracle 9i 2nd)
Syntax #1

Starting with Oracle9i 2nd, you can rename columns now.

To rename a column in an existing TABLE, the alter table syntax is:

The code is as follows:	Copy code
Alter table table_name Rename column old_name to new_name; For example: Alter table supplier Rename column supplier_name to sname;

This will rename the column called supplier_name to sname.

Acknowledgements: Thanks to Dave M., Craig A., and Susan W. for contributing to this solution!

 

Practice Exercise #1:

Based on the specified ments table below, rename the specified ments table to depts.

The code is as follows:	Copy code
Create table orders ments (Department_id number (10) not null, Department_name varchar2 (50) not null, CONSTRAINT departments_pk primary key (department_id) );

 

Solution:

The following alter table statement wocould rename the given ments table to depts:

The code is as follows:	Copy code
Alter table orders ments Rename to depts;

Practice Exercise #2:

Based on the employees table below, add a column called salary that is a number (6) datatype.

The code is as follows:	Copy code
Create table employees (Employee_number number (10) not null, Employee_name varchar2 (50) not null, Department_id number (10 ), CONSTRAINT employees_pk primary key (employee_number) );

Solution:

The following alter table statement wocould add a salary column to the employees table:

The code is as follows:	Copy code
Alter table employees ADD salary number (6 );

Practice Exercise #3:

Based on the customers table below, add two columns-one column called contact_name that is a varchar2 (50) datatype and one column called last_contacted that is a date datatype.

The code is as follows:	Copy code
CREATE TABLE MERS (Customer_id number (10) not null, Customer_name varchar2 (50) not null, Address varchar2 (50 ), City varchar2 (50 ), State varchar2 (25 ), Zip_code varchar2 (10 ), CONSTRAINT customers_pk primary key (customer_id) );

Solution:

The following alter table statement wocould add the contact_name and last_contacted columns to the MERS table:

The code is as follows:	Copy code
ALTER TABLE MERS ADD (contact_name varchar2 (50 ), Last_contacted date );

Practice Exercise #4:

Based on the employees table below, change the employee_name column to a varchar2 (75) datatype.

The code is as follows:	Copy code
Create table employees (Employee_number number (10) not null, Employee_name varchar2 (50) not null, Department_id number (10 ), CONSTRAINT employees_pk primary key (employee_number) );

 

Solution:

The following alter table statement wocould change the datatype for the employee_name column to varchar2 (75 ):

The code is as follows:	Copy code
Alter table employees MODIFY employee_name varchar2 (75 );

Practice Exercise #5:

Based on the customers table below, change the customer_name column to NOT allow null values and change the state column to a varchar2 (2) datatype.

The code is as follows:	Copy code
CREATE TABLE MERS (Customer_id number (10) not null, Customer_name varchar2 (50 ), Address varchar2 (50 ), City varchar2 (50 ), State varchar2 (25 ), Zip_code varchar2 (10 ), CONSTRAINT customers_pk primary key (customer_id) );

Solution:

The following alter table statement wocould modify the customer_name and state columns accordingly in the customers table:

The code is as follows:	Copy code
ALTER TABLE MERS MODIFY (customer_name varchar2 (50) not null, State varchar2 (2 ));

Practice Exercise #6:

Based on the employees table below, drop the salary column.

The code is as follows:	Copy code
Create table employees (Employee_number number (10) not null, Employee_name varchar2 (50) not null, Department_id number (10 ), Salary number (6 ), CONSTRAINT employees_pk primary key (employee_number) );

Solution:

The following alter table statement wocould drop the salary column from the employees table:

The code is as follows:	Copy code
Alter table employees Drop column salary;

Practice Exercise #7:

Based on the attributes table below, rename the department_name column to dept_name.

The code is as follows:	Copy code
Create table orders ments (Department_id number (10) not null, Department_name varchar2 (50) not null, CONSTRAINT departments_pk primary key (department_id) );

Solution:

The following alter table statement renames the department_name column dept_name department TABLE:

The code is as follows:	Copy code
Alter table orders ments Rename column department_name to dept_name;