Range check with between ... and
mysql>
mysql> CREATE TABLE IF NOT EXISTS product
-> (
-> code CHAR(8) PRIMARY KEY,
-> make VARCHAR(25) NOT NULL,
-> model VARCHAR(25) NOT NULL,
-> price INT NOT NULL
-> );
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> # insert 5 records into the "product" table
mysql> INSERT INTO product (code, make, model, price) VALUES ("335/1914", "York", "Pacer 2120", 159);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO product (code, make, model, price) VALUES ("335/1907", "York", "Pacer 2750", 349);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO product (code, make, model, price) VALUES ("335/1921", "York", "Pacer 3100", 499);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO product (code, make, model, price) VALUES ("335/2717", "Proform", "7.25Q", 799);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO product (code, make, model, price) VALUES ("335/2652", "Reebok", "TR1 Power Run", 895);
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> # show all records where the price is between 300 - 500
mysql> SELECT * FROM product WHERE price BETWEEN 300 AND 500;
+----------+------+------------+-------+
| code | make | model | price |
+----------+------+------------+-------+
| 335/1907 | York | Pacer 2750 | 349 |
| 335/1921 | York | Pacer 3100 | 499 |
+----------+------+------------+-------+
2 rows in set (0.00 sec)
mysql>
mysql> # show records where the make is between "Proform" and "York"
mysql> SELECT * FROM product WHERE make BETWEEN "P" AND "Y";
+----------+---------+---------------+-------+
| code | make | model | price |
+----------+---------+---------------+-------+
| 335/2717 | Proform | 7.25Q | 799 |
| 335/2652 | Reebok | TR1 Power Run | 895 |
+----------+---------+---------------+-------+
2 rows in set (0.00 sec)
mysql>
mysql> # delete this sample table
mysql> DROP TABLE IF EXISTS product;
Query OK, 0 rows affected (0.00 sec)
mysql>
Related examples in the same category