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

