mysql> # create a table called "product"
mysql> CREATE TABLE IF NOT EXISTS product
-> (
-> code CHAR(8) PRIMARY KEY,
-> model VARCHAR(25) NOT NULL,
-> fabric VARCHAR(25) DEFAULT "Cloth",
-> price DECIMAL(6,2) NOT NULL
-> );
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> INSERT INTO product (code, model, price) VALUES ("1", "Clerk", 19.99);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO product (code, model, price) VALUES ("2", "Secretary", 34.99);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO product (code, model, fabric, price) VALUES ("617/9131", "Manager", "Leather", 49.99);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO product (code, model, fabric, price) VALUES ("621/0258", "Captain", "Wood", 99.99);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO product (code, model, fabric, price) VALUES ("619/6444", "Executive", "Leather", 124.99);
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> # show all data in the "product" table
mysql> SELECT * FROM product;
+----------+-----------+---------+--------+
| code | model | fabric | price |
+----------+-----------+---------+--------+
| 1 | Clerk | Cloth | 19.99 |
| 2 | Secretary | Cloth | 34.99 |
| 617/9131 | Manager | Leather | 49.99 |
| 621/0258 | Captain | Wood | 99.99 |
| 619/6444 | Executive | Leather | 124.99 |
+----------+-----------+---------+--------+
5 rows in set (0.00 sec)
mysql>
mysql> # show all records where the fabric is "Leather"
mysql> SELECT * FROM product WHERE fabric = "Leather";
+----------+-----------+---------+--------+
| code | model | fabric | price |
+----------+-----------+---------+--------+
| 617/9131 | Manager | Leather | 49.99 |
| 619/6444 | Executive | Leather | 124.99 |
+----------+-----------+---------+--------+
2 rows in set (0.00 sec)
mysql>
mysql> # show all records where the fabric is not "Leather"
mysql> SELECT * FROM product WHERE fabric != "Leather";
+----------+-----------+--------+-------+
| code | model | fabric | price |
+----------+-----------+--------+-------+
| 1 | Clerk | Cloth | 19.99 |
| 2 | Secretary | Cloth | 34.99 |
| 621/0258 | Captain | Wood | 99.99 |
+----------+-----------+--------+-------+
3 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>