Page 267 - Information_Practice_Fliipbook_Class11
P. 267
To add more than one attribute to a table, we can execute several ALTER TABLE statements. Alternatively, we can
include several attributes in a single ALTER TABLE statement using the following syntax:
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) NOT 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;
Execution of the above statement will produce the output as shown in Table 9.7:
+----------+---------------+------+-----+------------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+------------+-------+
| ID | int | NO | PRI | NULL | |
| FName | varchar(20) | NO | | NULL | |
| LName | varchar(20) | NO | | NULL | |
| Gender | char(1) | NO | | NULL | |
| Address | varchar(50) | YES | | NULL | |
| City | varchar(20) | YES | | NULL | |
| Pin_Code | char(6) | YES | | NULL | |
| DOB | date | YES | | NULL | |
| Salary | int | NO | | NULL | |
| Dept_No | smallint | YES | | NULL | |
| Phone | decimal(10,0) | YES | | NULL | |
| Mobile | decimal(10,0) | YES | | 9999999999 | |
| Email | varchar(50) | NO | | NULL | |
+----------+---------------+------+-----+------------+-------+
Table 9.7: Structure of EMPLOYEE table after adding Mobile and Email Attributes
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 Concepts and the Structured Query Language 253

