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
   260   261   262   263   264   265   266   267   268   269   270