mysql> CREATE TABLE IF NOT EXISTS cabinets
-> (
-> id INT AUTO_INCREMENT PRIMARY KEY,
-> wood CHAR(10) NOT NULL,
-> item CHAR(20) NOT NULL
-> );
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> INSERT INTO cabinets (wood, item) VALUES ("Pine", "Bookcase");
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO cabinets (wood, item) VALUES ("Beech", "Bookcase");
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO cabinets (wood, item) VALUES ("Oak", "Bookcase");
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO cabinets (wood, item) VALUES ("Pine", "Display Case");
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO cabinets (wood, item) VALUES ("Oak", "Display Case");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> SELECT * FROM cabinets;
+----+-------+--------------+
| id | wood | item |
+----+-------+--------------+
| 1 | Pine | Bookcase |
| 2 | Beech | Bookcase |
| 3 | Oak | Bookcase |
| 4 | Pine | Display Case |
| 5 | Oak | Display Case |
+----+-------+--------------+
5 rows in set (0.00 sec)
mysql>
mysql>
mysql> # get the number of woods for each type of item
mysql> SELECT item, COUNT(*) AS num_woods
-> FROM cabinets
-> GROUP BY item;
+--------------+-----------+
| item | num_woods |
+--------------+-----------+
| Bookcase | 3 |
| Display Case | 2 |
+--------------+-----------+
2 rows in set (0.00 sec)
mysql>
mysql> # delete this sample table
mysql> DROP TABLE IF EXISTS cabinets;
Query OK, 0 rows affected (0.00 sec)