Page 159 - Informatics_Practices_Fliipbook_Class12
P. 159
ALTER TABLE table_name ADD (attribute1 data_type [constraint],
attribute2 data_type [constraint],
…
attributeN data_type
[constraint]);
For example, execution of the following SQL statement will add two attributes Mobile and EMAIL to the EMPLOYEE
table:
ALTER TABLE EMPLOYEE ADD (
MOBILE DECIMAL(10,0) DEFAULT '9999999999',
EMAIL VARCHAR(50) DEFAULT NULL);
We can examine the change in the EMPLOYEE table on the execution of above SQL statement by executing the
following DESC statement:
DESC EMPLOYEE;
On executing the above statement, SQL will output the following table:
Field Type Null Key Default Extra
ID char(5) NO PRI NULL
FName varchar(20) NO NULL
LName varchar(20) NO NULL
Gender char(1) NO NULL
Address varchar(30) YES NULL
City varchar(20) YES NULL
Pin_Code char(6) YES NULL
DOB date YES NULL
Salary int(11) NO NULL
Dept_No smallint(6) YES NULL
Phone decimal(10,0) YES 9999999999
MOBILE decimal(10,0) YES 9999999999
EMAIL varchar(50) YES NULL
Table 11.6a1: Structure of EMPLOYEE table after adding Mobile and EMAIL.
Modify Attributes
To modify the definition of attributes of an existing table; we use the ALTER TABLE statement with the MODIFY
keyword. The syntax of the ALTER TABLE MODIFY statement is as follows:
ALTER TABLE table_name MODIFY attribute data_type [constraint];
For example, to modify the definition of an existing attribute Dept_No in the EMPLOYEE table, we can execute
the following SQL statement:
ALTER TABLE EMPLOYEE MODIFY Dept_No INT;
We can examine the change in the EMPLOYEE table on the execution of above SQL statement by executing a DESC
statement as follows:
DESC EMPLOYEE;
Database Query using SQL 145

