SQL Lesson 3

Lesson 3:

1)   Alter

This is the second command in DDL. It is used to alter/change the structure of data base object. For example if we want to change the name of table or data type then we will use this command.

Again this ALTER has 4 commands. Those are i) Add ii) Rename iii) Modify iv)Drop.

i) Add: By using this command we can add new columns to existing table.

Syntax: Alter table table_name add column_name data type;

For two or more columns: Alter table table_name add (col1 data type, col2 data type);

Ex: Alter table EMP add (gender char, place varchar2 (100));

After add check like below.

Even though we have data in table we can add new columns to that table.

ii)       Rename: By using this command we can change the name of a column.

Syntax: Alter table table_name RENAME column old_column_name to new_column_name

Here column is a keyword should require.

Ex: Alter table EMP rename column gender to sex;

After rename then check like below.

DESC EMP;

iii)   Modify: By using this command we can change the data type of columns and we can change size of data type. But it should satisfy some conditions.

Syntax: Alter table table_name modify column_name new_data_type (size)

Ex: Desc EMP;

Here for GENDER column CHAR data type is there. So we need to change it from CHAR to VARCHAR. Then use below syntax.

Alter table EMP modify gender varchar2 (10);

Then

DESC EMP;

We can modify multiple columns at a time.

Alter table EMP modify (gender varchar2 (10), place varchar2 (50), deptno number (3));

In above example we decreased the data type size of place from 100 to 50. If that column has already data which have more than 50 characters then it will throws an error.

ORA-01441: cannot decrease column length because some value is too big

If column don’t have any data we can modify anytime. But it have any data then it need to satisfy below conditions.

·         We can decrease data type size up to the max size of existing data.

·         If existing data has number data then we can’t change the data type.

·         If existing data has char or varchar data then we can change it to varchar or char but not number or date.

If existing data has char or varchar type and we try to modify it to number then the following error will occur.

ORA-01439: column to be modified must be empty to change data type

iv) Drop: This command is used to drop or remove a column or columns from existing table.

Syntax: Alter table table_name drop column column_name;

Here column is a keyword should be required for single column drop. We can drop multiple columns at a time, that time column keyword not required.

Ex: Desc EMP;

Now we need to drop gender and place columns.

Dropping multiple columns:

Alter table EMP drop (gender, place);

Dropping Single column:

Alter table EMP drop deptno;

Note: If we drop the column, it will remove the column along with data permanently.

This is all about ALTER command in DDL.
Lesson 1
Lesson 2
Lesson 4
Lesson 5
Lesson 6
Lesson 7