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
   262   263   264   265   266   267   268   269   270   271   272