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

