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; |