SQL alter table statement usage

Source: Internet
Author: User

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;

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.