Page 277 - Information_Practice_Fliipbook_Class11
P. 277

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');
            Executing the above statement will produce  the output shown in Table 9.28.

                                          Table 9.28: Departments located in Mumbai or Delhi.
                                                   +----------------+-----------+
                                                   | Dept_Name      | Location  |
                                                   +-------+--------+-----------+
                                                   | Administration | Delhi     |
                                                   | Home Goods     | Mumbai    |
                                                   | Textile        | Mumbai    |
                                                   +-------+--------+-----------+
            9.10 IS Operator

            Recall from the previous chapter that when the value of an attribute is not known, we use either the default value (if
            specified) or the  NULL value. We can check whether a value is NULL using IS  NULL or IS NOT NULL. Using
            the following syntax:

            SELECT attribute1, attribute2, ...
            FROM table_name
            WHERE attributeJ IS [NOT] NULL;
            The operator  IS NULL returns TRUE if the specified attribute (attributeJ in the above description) has a NULL
            value and  FALSE  otherwise. The operator  IS NOT NULL returns TRUE if the specified attribute (attributeJ in
            the above description)  has a non-NULL value  and  FALSE  otherwise.
            For example, to display the department name and the manager ID (from Table 9.29) for the departments having  a
            manager.

            SELECT Dept_Name, Mgr_Id
            FROM DEPARTMENT
            WHERE Mgr_Id IS NOT NULL;
            Executing the above statement will produce  the output shown in Table 9.29.

                                  Table 9.29: Dept_Name, Mgr_Id for the departments with a manager
                                                   +----------------+-----------+
                                                   | Dept_Name      | Mgr_Id    |
                                                   +-------+--------+-----------+
                                                   | Administration | 10005     |
                                                   | Home Goods     | 10003     |
                                                   | Automobile     | 10007     |
                                                   | Textile        | 10004     |
                                                   +-------+--------+-----------+
            9.11 LIKE Operator

            The SQL LIKE operator uses wildcard operators % and _ to define a pattern.  The per cent  (%) operator denotes zero,
            one, or more occurrences of arbitrary characters. The underscore (_) operator represents exactly one occurrence of
            an arbitrary character. If required, we may use a combination of % and _ operators. The syntax for the LIKE operator
            is given below:

            SELECT attribute1, attribute2, ...
            FROM table_name

            WHERE attributeJ LIKE 'pattern';



                                                                Database Concepts and the Structured Query Language  263
   272   273   274   275   276   277   278   279   280   281   282