Page 265 - Information_Practice_Fliipbook_Class11
P. 265
In text exercise:
Write SQL statements to insert more rows into EMPLOYEE table so that it appears as shown in Table 9.4)
In text exercise:
Table 9.4: EMPLOYEE table after inserting some tuples
+------+------+--------+------+-----------------------+--------------+--------+----------+------+-------+
|ID |FName |LName |Gender|Address |City |Pin_Code|DOB |Salary|Dept_No|
+------+------+--------+------+-----------------------+--------------+--------+----------+------+-------+
|10001 |Raj |Reddy |M |West Godavari |Andhra Pradesh|534197 |1980-06-13|100000| 2|
|10002 |Dhiraj|Bora |M |Dispur, Kamrup, Assam |Guwahati |781005 |1975-09-30| 85000| 1|
|10003 |Muskan|Taneja |F |8/33, Geeta Colony |Delhi |110031 |1990-01-25|100000| 2|
|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|
| | | | |Gautum Budh Nagar | | | | | |
|10006 |Rajit |Gadh |F |12, Beldih Triangle, |Jamshedpur |831001 |1960-05-07| 60000| 4|
| | | | |Bistupur | | | | | |
|10007 |Taran |Adarsh |M |B-76, CST Road, Kalina,|Mumbai |400098 |1965-01-13| 70000| 5|
| | | | |Santacruz East | | | | | |
|10008 |Naval |Dhingra |M |E-14 Vivek Vihar |Delhi |110095 |1975-08-04| 70000| 2|
|10009 |Naveen|Basra |F |28, Aambagan Road, |Jamshedpur |831001 |1980-09-24| 60000| 4|
| | | | |Sakechi | | | | | |
|10010 |Savita|Ambedkar|F |C-49, G-Block, Bandra |Mumbai |400051 |1987-07-11| 50000| 5|
| | | | |Kurla, Bandra East | | | | | |
+------+------+--------+------+-----------------------+--------------+--------+----------+------+-------+
In Table 9.4, we have shown the tuples in the order in which they were inserted in the table. However, a relational
DBMS does not ensure any specific order of the tuples. Placing the tuples in a particular sequence does not impact the
database operations.
9.5.6 DELETE Statement
To delete one or more tuples (satisfying a particular criterion) from a table, we use the DELETE statement. The syntax
of the delete statement is as follows:
DELETE FROM table_name
[WHERE criterion-for-selecting-specific-tuple];
For example, to delete the record of the employee from the EMPLOYEE table whose ID is 10007, we execute the
following SQL statement:
DELETE FROM EMPLOYEE
WHERE ID = 10007;
DELETE: to delete rows from a table
Let us now execute the statement that shows the current state of the table from which the tuple corresponding to the
employee having ID 10007 has been deleted, as shown in Table 9.5:
+-------+--------+----------+--------+-------------------------+----------------+----------+------------+--------+---------+
| ID | FName | LName | Gender | Address | City | Pin_Code | DOB | Salary | Dept_No |
+-------+--------+----------+--------+-------------------------+----------------+----------+------------+--------+---------+
| 10001 | Raj | Reddy | M | West Godavari | Andhra Pradesh | 534197 | 1980-06-13 | 100000 | 2 |
| 10002 | Dhiraj | Bora | M | Dispur, Kamrup, Assam | Guwahati | 781005 | 1975-09-30 | 85000 | 1 |
| 10003 | Muskan | Taneja | F | 8/33, Geeta Colony | Delhi | 110031 | 1990-01-25 | 100000 | 2 |
| 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 |
| | | | | Gautum Budh Nagar | | | | | |
| 10006 | Rajit | Gadh | F | 12, Beldih Triangle, | Jamshedpur | 831001 | 1960-05-07 | 60000 | 4 |
| | | | | Bistupur | | | | | |
| 10008 | Naval | Dhingra | M | E-14 Vivek Vihar | Delhi | 110095 | 1975-08-04 | 70000 | 2 |
| 10009 | Naveen | Basra | F | 28, Aambagan Road, | Jamshedpur | 831001 | 1980-09-24 | 60000 | 4 |
| | | | | Sakechi | | | | | |
| 10010 | Savita | Ambedkar | F | C-49, G-Block, | Mumbai | 400051 | 1987-07-11 | 50000 | 5 |
| | | | | Bandra Kurla, Bandra East | | | | | |
+-------+--------+----------+--------+-------------------------+----------------+----------+------------+--------+---------+
Table 9.5: Result of DELETE statement on EMPLOYEE table
Database Concepts and the Structured Query Language 251

