Page 268 - Information_Practice_Fliipbook_Class11
P. 268

On executing the above statement, we get the result as shown in Table 9.8:
                                  +----------+---------------+------+-----+------------+-------+
                                  | 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  | int           | YES  |     | NULL       |       |
                                  | Phone    | decimal(10,0) | YES  |     | NULL       |       |
                                  | Mobile   | decimal(10,0) | YES  |     | 9999999999 |       |
                                  | Email    | varchar(50)   | NO   |     | NULL       |       |
                                  +----------+---------------+------+-----+------------+-------+
                                 Table 9.8: Structure of EMPLOYEE table after modifying Dept_No
        Drop Attributes
        To drop an attribute from a table, we use the ALTER TABLE statement with the DROP keyword. The syntax of the
        ALTER TABLE-DROP statement is as follows:

        ALTER TABLE table_name DROP attribute;
        For example, to drop the attributes Phone, Mobile, and Email from the EMPLOYEE table, we can execute the
        following SQL statements:

        ALTER TABLE EMPLOYEE DROP Phone;
        ALTER TABLE EMPLOYEE DROP Mobile;
        ALTER TABLE EMPLOYEE DROP Email;
        As before, we describe the modified table EMPLOYEE as follows:
        DESC EMPLOYEE;
        On executing the above statement, we get the result as shown in Table 9.9:
                                    +----------+-------------+------+-----+---------+-------+
                                    | 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  | int         | YES  |     | NULL    |       |
                                    +----------+-------------+------+-----+---------+-------+
                      Table 9.9: Structure of EMPLOYEE table after dropping the Phone, Mobile, and Email attributes
        Add and Remove Primary Key Constraint

        Using an ALTER TABLE statement, we can also add and/or remove the primary key constraints. The primary key
        constraint can be enforced on an attribute using the following ALTER TABLE statement:

        ALTER TABLE EMPLOYEE ADD PRIMARY KEY(ID);
        To drop the primary key constraint from an attribute, the following ALTER TABLE statement can be used:
        ALTER TABLE EMPLOYEE DROP PRIMARY KEY(ID);



          254  Touchpad Informatics Practices-XI
   263   264   265   266   267   268   269   270   271   272   273