Page 169 - Informatics_Practices_Fliipbook_Class12
P. 169

4.4.13 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 4.20.


                                            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

                                    Table 4.20 Employees who work in department number 1, 2, or 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 4.21.


                                            FName            LName           Dept_No
                                             Hiten           Oberoi             4
                                             Rajit            Gadh              4
                                            Naveen            Basra             4

                                 Table 4.21 Employees who do not work in department number 1, 2, or 5
            Query: Display the department names and their locations for the departments located in Mumbai or Delhi.

                   SELECT Dept_Name, Location
                   FROM DEPARTMENT
                   WHERE Location IN ('Mumbai', 'Delhi');




                                                                                        Database Query using SQL  155
   164   165   166   167   168   169   170   171   172   173   174