Page 269 - Information_Practice_Fliipbook_Class11
P. 269

Rename Attributes
                   To rename an attribute of an existing table, we use the ALTER TABLE statement with the RENAME COLUMN
                   keyword. The syntax of the ALTER TABLE RENAME COLUMN statement is as follows:
                   ALTER TABLE table_name RENAME COLUMN old_name TO 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 RENAME COLUMN Pin_Code TO PinCode;
                   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 9.10:

                                           +---------+-------------+------+-----+---------+-------+
                                           | 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    |       |
                                           | PinCode | char(6)     | YES  |     | NULL    |       |
                                           | DOB     | date        | YES  |     | NULL    |       |
                                           | Salary  | int         | NO   |     | NULL    |       |
                                           | Dept_No | int         | YES  |     | NULL    |       |
                                           +---------+-------------+------+-----+---------+-------+
                                   Table 9.10: Structure of EMPLOYEE table after renaming Pin_Code to PinCode


            9.5.9 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 9.11).
                                        Table 9.11: Result of UPDATE statement on EMPLOYEE table
             +-------+--------+----------+--------+-------------------------+----------------+----------+------------+--------+---------+
             | 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 |
             +-------+--------+----------+--------+-------------------------+----------------+----------+------------+--------+---------+
            Next, suppose we want to assign a manager (ID = '10002')to the department having Dept_No 1. The following
            UPDATE statement finds a tuple in the DEPARTMENT table having Dept_No 1 and sets the value of its  Mgr_Id
            attribute to 10002.
            UPDATE DEPARTMENT



                                                                Database Concepts and the Structured Query Language  255
   264   265   266   267   268   269   270   271   272   273   274