Page 276 - Information_Practice_Fliipbook_Class11
P. 276

Table 9.25: Employees whose date of birth is outside the interval ['1985-01-01', '1990-12-31']
                                              +--------+----------+-------------+
                                              | FName  | LName    |  DOB        |
                                              +--------+----------+-------------+
                                              | Raj    | Reddy    | 1980-06-13  |
                                              | Dhiraj | Bora     | 1975-09-30  |
                                              | Rajit  | Gadh     | 1960-05-07  |
                                              | Taran  | Adarsh   | 1965-01-13  |
                                              | Naval  | Dhingra  | 1975-08-04  |
                                              | Naveen | Basra    | 1980-09-24  |
                                              +--------+----------+-------------+

        9.9 IN Operator

        The IN operator checks whether a value exists in the given list of values. Thus, the  IN operator provides an alternative
        to using multiple OR operators. If we wish to exclude specific values, IN may be preceded by NOT. An SQL statement
        involving IN operator has the following syntax:
        SELECT attribute1, attribute2, ...

        FROM table_name
        WHERE attributeJ [NOT] IN (value1, value2, ...);
        For example, to display the first name and the last name of those employees who work in the department number 1,
        2, or 5.
        Solution:
        SELECT FName, LName, Dept_No

        FROM EMPLOYEE
        WHERE Dept_No IN (1,2,5);
        Execution of the above SQL statement will display the output shown in Table 9.26.

                                 Table 9.26: Employees who work in department number 1, 2, or 5
                                               +--------+----------+----------+
                                               | FName  | LName    | Dept_No  |
                                               +--------+----------+----------+
                                               | Raj    | Reddy    | 2        |
                                               | Dhiraj | Bora     | 1        |
                                               | Muskan | Taneja   | 2        |
                                               | Anshul | Verma    | 1        |
                                               | Taran  | Adarsh   | 5        |
                                               | Naval  | Dhingra  | 2        |
                                               | Savita | Ambedkar | 5        |
                                               +--------+----------+----------+
        Next, to display those employees who work for the departments other than department number 1, 2, or 5, we use NOT
        operator in SQL statement as follows:

        SELECT FName, LName, Dept_No
        FROM EMPLOYEE

        WHERE Dept_No NOT IN (1,2,5);
        Executing the above statement will produce  the output shown in Table 9.27.

                              Table 9.27: Employees who do not work in department number 1, 2, or 5
                                               +--------+----------+----------+
                                               | FName  | LName    | Dept_No  |
                                               +--------+----------+----------+
                                               | Hiten  | Oberoi   | 4        |
                                               | Rajit  | Gadh     | 4        |
                                               | Naveen | Basra    | 4        |
                                               +--------+----------+----------+



          262  Touchpad Informatics Practices-XI
   271   272   273   274   275   276   277   278   279   280   281