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
   154   155   156   157   158   159   160   161   162   163   164