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

