Comparison operators 2 : Operator « Select Clause « SQL / MySQL

1. SQL / MySQL
2. Select Clause
3. Operator

Comparison operators 2

      
mysql>
mysql> CREATE TABLE IF NOT EXISTS product
    -> (
    ->   code           CHAR(8)         PRIMARY KEY,
    ->   make           VARCHAR(25)     NOT NULL,
    ->   model          VARCHAR(25)     NOT NULL,
    ->   price          DECIMAL(3,2)    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 ("512/4792", "Alba", "C2108", 6.75);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO product (code, make, model, price)   VALUES ("512/4125", "Hitachi", "KC30", 8.99);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO product (code, make, model, price)   VALUES ("512/1458", "Philips", "AJ3010", 19.99);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> INSERT INTO product (code, make, model, price)   VALUES ("512/3669", "Morphy Richards", "28025", 19.99);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> INSERT INTO product (code, make, model, price)   VALUES ("512/1444", "Sony", "C253", 29.49);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql>
mysql> # show records in "product" if price is below 19.99
mysql> SELECT * FROM product WHERE price < 19.99;
+----------+-----------------+--------+-------+
| code     | make            | model  | price |
+----------+-----------------+--------+-------+
| 512/4792 | Alba            | C2108  |  6.75 |
| 512/4125 | Hitachi         | KC30   |  8.99 |
| 512/1458 | Philips         | AJ3010 |  9.99 |
| 512/3669 | Morphy Richards | 28025  |  9.99 |
| 512/1444 | Sony            | C253   |  9.99 |
+----------+-----------------+--------+-------+
5 rows in set (0.00 sec)

mysql>
mysql> # show records in "product" if price is above 19.99
mysql> SELECT * FROM product WHERE price > 19.99;
Empty set (0.00 sec)

mysql>
mysql> # show records in "product" if price is 19.99 or less
mysql> SELECT * FROM product WHERE price <= 19.99;
+----------+-----------------+--------+-------+
| code     | make            | model  | price |
+----------+-----------------+--------+-------+
| 512/4792 | Alba            | C2108  |  6.75 |
| 512/4125 | Hitachi         | KC30   |  8.99 |
| 512/1458 | Philips         | AJ3010 |  9.99 |
| 512/3669 | Morphy Richards | 28025  |  9.99 |
| 512/1444 | Sony            | C253   |  9.99 |
+----------+-----------------+--------+-------+
5 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