DDL Statements - Alter Table || DBMS Tutorial 5

DDL Statements - Alter Table
DBMS Tutorial 5

Alter Table:

How do we add a new column to a table that already exists? One option is to drop the table and create it again. What if the table has a lot of data and we do not want to lose existing data? We cannot drop the table now. One option is to take a backup of data, then drop and recreate the table and finally load the data from backup into the modified table. This approach might work but if a number of rows are large then this process will take a lot of time. Is there an easier way to avoid all these troubles?

We need to use the ALTER TABLE command through which the structure of the existing table can be changed without any loss of data. ALTER table can also be used to rename a column, change the data type of a column and add or remove constraints. The syntax for ALTER TABLE command is provided below:

DDL Statements - Alter Table || DBMS Tutorial 5

We will now understand the first four syntaxes of Alter Command. Adding and dropping constraints is out of scope for the current course.

Let us try to modify the structure of the existing table Student with ALTER TABLE statement

Table Description

NAME	Null?	TYPE
ID		NUMBER
NAME		VARCHAR2(10)
GNDR		CHAR(1)
DOJ		DATE
DOB		DATE
MOBNO		NUMBER(10)

Table Records

ID	NAME	GNDR	DOJ	DOB	MOBNO
1001	Alice	F	20-JAN-90	27-DEC-92	11111
1001	Jack	M	22-FEB-92	27-DEC-92	NULL
NULL	Andy	F	12-SEP-91	27-DEC-92	NULL
1004	NULL	M	08-APR-89	27-DEC-92	44444

The column can be removed from the table with DROP clause. The column dropped cannot be recovered back. At least one column should be present in the table after dropping the columns.

Example 1:

Query:

ALTER TABLE Student DROP (DOB);

Output:

Table altered.

NAME	Null?	TYPE
ID		NUMBER
NAME		VARCHAR2(10)
GNDR		CHAR(1)
DOJ		DATE
MOBNO		NUMBER(10)

ALTER statement can also be used to drop multiple columns.

Example 2:

Query:

ALTER TABLE Student DROP (GNDR, MobNo);

Output:

Table altered.

NAME	Null?	TYPE
ID		NUMBER
NAME		VARCHAR2(10)
DOJ		DATE
DOB		DATE

The Address column can be added to the existing student table with ADD clause. The new Address column will become the last column in the table. The existing rows contain a NULL value for the newly added column.

Example 3:

Query:

ALTER TABLE Student ADD Address VARCHAR2 (20);

Output:

Table altered.

NAME	Null?	TYPE
ID		NUMBER
NAME		VARCHAR2(10)
DOJ		DATE
DOB		DATE
ADDRESS		VARCHAR2(20)

ALTER statement can also be used to add multiple columns. The columns should be placed in the parentheses.

Example 4:

Query:

ALTER TABLE Student ADD (Course VARCHAR2 (20), Marks NUMBER (10));

Output:

Table altered.

NAME	Null?	TYPE
ID		NUMBER
NAME		VARCHAR2(10)
DOJ		DATE
DOB		DATE
ADDRESS		VARCHAR2(20)
COURSE		VARCHAR2(20)
MARKS		NUMBER(10)

The data type of the column can be modified with the MODIFY clause. The size of the data type can be increased or decreased. The column should be empty for decreasing the size or for changing the data type from one type to another.

Example 5:

Query:

ALTER TABLE Student MODIFY Name VARCHAR2(50);

Output:

Table altered.

NAME	Null?	TYPE
ID		NUMBER
NAME		VARCHAR2(50)
DOJ		DATE
DOB		DATE
ADDRESS		VARCHAR2(20)
COURSE		VARCHAR2(20)
MARKS		NUMBER(10)

The default value can be added to a column with the MODIFY clause and DEFAULT option. If no value is inserted in a column then the default value will be inserted. The data type of the column and default expression must be the same.

Example 6:

Query:

ALTER TABLE Student MODIFY DOJ DEFAULT SYSDATE;

Output:

Table altered.

NAME	Null?	TYPE
ID		NUMBER
NAME		VARCHAR2(50)
DOJ		DATE
DOB		DATE
ADDRESS		VARCHAR2(20)
COURSE		VARCHAR2(20)
MARKS		NUMBER(10)

Quiz:

Consider the current table structure and desired table structure.

ActualTable

Attribute	Data Type
EmpId	NUMBER
EmpName	VARCHAR2(30)
Email	VARCHAR2(15)
BatchName	VARCHAR2(10)
SectionName	VARCHAR2(3)
Stream	VARCHAR2(10)
ClassRoom	NUMBER

ExpectedResult

Attribute	Data Type
EmpId	NUMBER
Name	VARCHAR2(30)
Email	VARCHAR2(25)
BatchName	VARCHAR2(10)
SectionName	VARCHAR2(3)
Stream	VARCHAR2(10)

Quiz 55: Identify the statements that will achieve the result.

i. ALTER TABLE Trainee DROP COLUMN (ClassRoom);
ii. ALTER TABLE Trainee RENAME COLUMN EmpName to Name;
iii. ALTER TABLE Trainee ADD Email VARCHAR2(25)
iv. ALTER TABLE Trainee DROP (ClassRoom);
v. ALTER TABLE Trainee MODIFY Email VARCHAR2(25)
vi. ALTER TABLE Trainee MODIFY COLUMN EmpName to Name;

a) i,ii,v
b) iv,v,vi
c) ii,iv,v ---
d) ii,iii,iv

Exercise 6:

Add 'MatchesPlayed', 'MatchesWon' columns of type NUMBER to Player table described below:

Column Name	Data Type	Constraint	Description
PId	INTEGER	PRIMARY KEY	Unique Player Id
PName	VARCHAR2(20)	NOT NULL	Player Name
Ranking	INTEGER		Player's ranking
ContactNo	NUMBER(10)		Player Contact Number

Exercise 7:

Drop Column 'ContactNo' from table Player.

Column Name	Data Type	Constraint	Description
PId	INTEGER	PRIMARY KEY	Unique Player Id
PName	VARCHAR2(20)	NOT NULL	Player Name
Ranking	INTEGER		Player's ranking
ContactNo	NUMBER(10)		Player Contact Number

Exercise 8:

Rename Column 'PId' to 'PlayerId' in table Player.

Column Name	Data Type	Constraint	Description
PId	INTEGER	PRIMARY KEY	Unique Player Id
PName	VARCHAR2(20)	NOT NULL	Player Name
Ranking	INTEGER		Player's ranking
ContactNo	NUMBER(10)		Player Contact Number

Exercise 9:

Modify datatype of PName to VARCHAR2(50) in Player table.

Column Name	Data Type	Constraint	Description
PId	INTEGER	PRIMARY KEY	Unique Player Id
PName	VARCHAR2(20)	NOT NULL	Player Name
Ranking	INTEGER		Player's ranking
ContactNo	NUMBER(10)		Player Contact Number