Page 161 - Informatics_Practices_Fliipbook_Class12
P. 161

Rename Attributes

            To rename an attribute of an existing table, we use the ALTER TABLE statement with the CHANGE keyword. The
            syntax of the ALTER TABLE CHANGE statement is as follows:

                   ALTER TABLE table_name CHANGE old_name new_name;

            For example, to modify the definition of an existing attribute Pin_Code in the EMPLOYEE table, to PinCode,
            we execute the statement:

                   ALTER TABLE EMPLOYEE CHANGE Pin_Code PinCode CHAR(6);
            Next, we examine the modified table EMPLOYEE, by executing the following statement:

                   DESC EMPLOYEE;
            On executing the above statement, we get the result as given in Table 11.6d:

                           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       int(11)        YES                      NULL

                      Table 11.6d: Structure of EMPLOYEE table after renaming Pin_Code to PinCode.

            4.4.8 UPDATE-SET statement

            Suppose the salary of an employee having ID value 10003  has been revised recently. SQL provides an UPDATE
            statement for making changes in the tuples already stored in a table. The syntax of the update statement is as follows:

                   UPDATE table_name
                   SET Attribute_name = value

                   [WHERE criterion_for_selecting_specific_tuple];
            The following UPDATE statement finds a tuple in the EMPLOYEE  table having an ID equal to 10003 and sets the

            value of its Salary attribute to 90000.
                   UPDATE EMPLOYEE
                   SET Salary = 90000
                   WHERE ID = '10003';

            On  executing  the  above statement, SQL  will  update the EMPLOYEE  tuple  for  the  employee whose  ID  is  10003
            (see Table 4.4).

               ID     FName    LName   Gender        Address         City   Pin_Code      DOB      Salary  Dept_No
             10003   Muskan  Taneja    F        8/33, Geeta Colony  Delhi   110031     1990-01-25  90000   2
                                        Table 4.4: Result of UPDATE statement on EMPLOYEE table




                                                                                        Database Query using SQL  147
   156   157   158   159   160   161   162   163   164   165   166