Page 271 - Information_Practice_Fliipbook_Class11
P. 271

Table 9.14: All details of employees in EMPLOYEE table
             +-------+--------+----------+--------+-------------------------+----------------+----------+------------+--------+---------+
             | 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      |                |          |            |        |         |
             +-------+--------+----------+--------+-------------------------+----------------+----------+------------+--------+---------+
            SQL provides a WHERE clause that facilitates retrieval of only those tuples that satisfy the condition specified in the
            WHERE clause. Syntax of a  SELECT statement with WHERE clause is given below:

            SELECT attribute_list
            FROM table_name
            WHERE condition;


                  SELECT statement: retrieves the attribute values from the table.


            Here condition may be a simple condition or may involve several simple conditions and  use of logical operators:
            AND, OR, and NOT. For example, the following SQL statement retrieves the names of all male employees as shown
            in Table 9.15:

            SELECT FName, LName
            FROM EMPLOYEE

            WHERE Gender = 'M';
            Executing the above statement will produce output shown in Table 9.15.

                                                 Table 9.15: Names of male employees
                                                    +------------+------------+
                                                    | FName      | LName      |
                                                    +----------------+--------+
                                                    | Raj        | Reddy      |
                                                    | Dhiraj     | Bora       |
                                                    | Hiten      | Oberoi     |
                                                    | Anshul     | Verma      |
                                                    | Rajit      | Gadh       |
                                                    | Taran      | Adarsh     |
                                                    | Naval      | Dhingra    |
                                                    +------------+------------+
            Similarly we can use any of the relational and logical operators to specify conditions in the where clause. Consider the
            following examples:
            Query: To display first name and the city of employees whose salary is greater than 70000 and who do not live in Delhi.
            SELECT FName,City FROM EMPLOYEE
            WHERE City != 'Delhi' AND Salary > 70000;
            Executing the above statement will produce output shown in Table 9.16.





                                                                Database Concepts and the Structured Query Language  257
   266   267   268   269   270   271   272   273   274   275   276