Page 270 - PYTHON-12

12.17.1 Conditions Based on a Range—BETWEEN...AND

SQL provides a BETWEEN operator that defines a range of values that the column value must fall
within for the condition to become true. The range includes both the lower and upper value. The
values can be numbers, text or dates.
Syntax for BETWEEN:
mysql>SELECT <column_name(s)>

FROM <table_name>

WHERE <column_name> BETWEEN <value1> AND <value2>;
For example,

mysql> SELECT Rollno, Name, Marks FROM student WHERE Marks BETWEEN 80 AND 100;

The above command displays Rollno, Name along with Marks of those students whose Marks lie in
the range of 80 to 100 (both 80 and 100 are included in the range).
Resultant table: student
Rollno Name Marks
1. Raj Kumar 93
2. Deep Singh 98
5. Payal Goel 82
6. Diksha Sharma 80
8. Akshay Dureja 90
5 rows in a set (0.02 sec) NOT BETWEEN

The NOT BETWEEN operator works opposite to the BETWEEN operator. It retrieves the rows
which do not satisfy the BETWEEN condition.
For example,

mysql> SELECT Rollno, Name, Marks FROM student WHERE Marks NOT BETWEEN 80 AND 100;
Resultant table: student
Rollno Name Marks
3. Ankit Sharma 76
4. Radhika Gupta 78
9. Shreya Anand 70
10. Prateek Mittal 75
4 rows in a set (0.02 sec)

Computer Science with Python–XII 12.40 To specify a list of values, IN operator is used. This operator selects values that match any value in
12.17.2 Conditions Based on a List—IN

the given list. The SQL IN condition is used to help reduce the need for multiple OR conditions in a
SELECT statement.
Syntax for IN:
SELECT <column_name(s)>

FROM <table_name>

WHERE <column_name> IN (value1,value2,...);