Page 266 - Information_Practice_Fliipbook_Class11
P. 266

The execution of the following statement will delete all rows of the table EMPLOYEE:

        DELETE FROM EMPLOYEE;
        However, the table EMPLOYEE remains part of the database and can be populated again.

        9.5.7 DROP TABLE Statement

        The DROP TABLE statement deletes an existing table in a database. The syntax of the DROP TABLE statement is as
        follows:
        DROP TABLE table-name;
        For example, to drop the table EMPLOYEE from the database COMPANY, we can use the following SQL statement:
        DROP TABLE EMPLOYEE;


               DROP TABLE: to remove a table and its data



        9.5.8 ALTER TABLE Statement

        ALTER TABLE statement allows us to add, change, and delete attributes of a table. It can also be used to create and
        remove SQL constraints from tables.


               ALTER TABLE: to add, modify, and delete attributes in a table



        Add Attributes

        To add attributes to an existing table; we use the ALTER TABLE statement with the ADD keyword. The syntax of the
        ALTER TABLE-ADD statement is as follows:

        ALTER TABLE table_name ADD attribute data_type [constraint];
        For example, to add a new attribute Phone in the EMPLOYEE table, the SQL statement is as follows:

        ALTER TABLE EMPLOYEE
        ADD Phone DECIMAL(10,0);
        On executing the above statement, the tuples that already exists in the EMPLOYEE table will be assigned NULL value
        for Phone attribute, as shown in Table 9.6:

        +-------+--------+----------+--------+-------------------------+----------------+----------+------------+--------+---------+---------+
        | ID    | FName  | LName    | Gender | Address                 | City           | Pin_Code | DOB        | Salary | Dept_No |   Phone |
        +-------+--------+----------+--------+-------------------------+----------------+----------+------------+--------+---------+---------+
        | 10001 | Raj    | Reddy    | M      | West Godavari           | Andhra Pradesh | 534197   | 1980-06-13 | 100000 |       2 |    NULL |
        | 10002 | Dhiraj | Bora     | M      | Dispur, Kamrup, Assam   | Guwahati       | 781005   | 1975-09-30 |  85000 |       1 |    NULL |
        | 10003 | Muskan | Taneja   | F      | 8/33, Geeta Colony      | Delhi          | 110031   | 1990-01-25 | 100000 |       2 |    NULL |
        | 10004 | Hiten  | Oberoi   | M      | 15, Dimna Road, Mango   | Jamshedpur     | 831018   | 1985-06-24 | 100000 |       4 |         |
        | 10005 | Anshul | Verma    | M      | House 10, Sector 16,    | Noida          | 201304   | 1990-01-01 | 100000 |       1 |    NULL |
        |     |      |         |        | Gautum Budh Nagar      |            |      |   |      |        |         |
        | 10006 | Rajit  | Gadh     | F      | 12, Beldih Triangle,    | Jamshedpur     | 831001   | 1960-05-07 |  60000 |       4 |    NULL |
        |      |      |        |        | Bistupur                |             |      |    |    |         |         |
        | 10007 | Taran  | Adarsh   | M      | B-76, CST Road, Kalina, | Mumbai         | 400098   | 1965-01-13 |  70000 |       5 |    NULL |
        |      |      |        |        | Santacruz East          |             |      |    |    |         |         |
        | 10008 | Naval  | Dhingra  | M      | E-14 Vivek Vihar        | Delhi          | 110095   | 1975-08-04 |  70000 |       2 |    NULL |
        | 10009 | Naveen | Basra    | F      | 28, Aambagan Road,      | Jamshedpur     | 831001   | 1980-09-24 |  60000 |       4 |    NULL |
        |      |      |        |        | Sakechi                 |             |      |    |    |         |         |
        | 10010 | Savita | Ambedkar | F      | C-49, G-Block,          | Mumbai         | 400051   | 1987-07-11 |  50000 |       5 |    NULL |
        |      |      |        |        | Bandra Kurla, Bandra East |             |      |    |    |         |         |
        +-------+--------+----------+--------+-------------------------+----------------+----------+------------+--------+---------+---------+
                                     Table 9.6: Phone attribute is added to the table EMPLOYEE




          252  Touchpad Informatics Practices-XI
   261   262   263   264   265   266   267   268   269   270   271