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

