mysql>
mysql> CREATE TABLE IF NOT EXISTS items
-> (
-> id INT PRIMARY KEY,
-> vendor INT NOT NULL,
-> name CHAR(20) NOT NULL,
-> price DECIMAL(6,2) NOT NULL
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO items (id, vendor, name, price) VALUES (601, 2, "Elephants", 147.50);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO items (id, vendor, name, price) VALUES (602, 2, "Reindeers", 123.00);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO items (id, vendor, name, price) VALUES (603, 1, "Alligators", 185.00);
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> CREATE TABLE IF NOT EXISTS vendors
-> (
-> id INT PRIMARY KEY,
-> name CHAR(20) NOT NULL
-> );
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> INSERT INTO vendors (id, name) VALUES (1, "Alpha Inc");
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO vendors (id, name) VALUES (2, "Zeta Inc");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> CREATE TABLE IF NOT EXISTS orders
-> (
-> num INT PRIMARY KEY,
-> item INT NOT NULL,
-> qty INT NOT NULL
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO orders (num, item, qty) VALUES (2805, 603, 10);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO orders (num, item, qty) VALUES (2806, 603, 5);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO orders (num, item, qty) VALUES (2807, 601, 10);
Query OK, 1 row affected (0.00 sec)
mysql>
mysql>
mysql> SELECT orders.num AS Number,
-> orders.qty AS Qty,
-> items.name AS Toy,
-> vendors.name AS Vendor,
-> items.price * orders.qty AS Total
-> FROM items, vendors, orders
-> WHERE vendors.id = items.vendor
-> AND items.id = orders.item
-> AND orders.num = 2805;
+--------+-----+------------+-----------+---------+
| Number | Qty | Toy | Vendor | Total |
+--------+-----+------------+-----------+---------+
| 2805 | 10 | Alligators | Alpha Inc | 1850.00 |
+--------+-----+------------+-----------+---------+
1 row in set (0.00 sec)
mysql>
mysql> # delete these sample tables
mysql> DROP TABLE IF EXISTS items;
Query OK, 0 rows affected (0.00 sec)
mysql> DROP TABLE IF EXISTS vendors;
Query OK, 0 rows affected (0.00 sec)
mysql> DROP TABLE IF EXISTS orders;
Query OK, 0 rows affected (0.00 sec)