mysql>
mysql>
mysql> CREATE TABLE IF NOT EXISTS games
-> (
-> id VARCHAR(10) 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>
mysql> # insert 5 records into the "games" table
mysql> INSERT INTO games (id, vendor, name, price) VALUES ("371/2209", 1, "Scrabble", 14.50);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO games (id, vendor, name, price) VALUES ("373/2296", 2, "Jenga", 6.99);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO games (id, vendor, name, price) VALUES ("360/9659", 1, "Uno", 11.99);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO games (id, vendor, name, price) VALUES ("373/5372", 3, "Connect", 5.99);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO games (id, vendor, name, price) VALUES ("370/9470", 3, "Bingo", 8.99);
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> # create a table called "vendors"
mysql> CREATE TABLE IF NOT EXISTS vendors
-> (
-> id INT PRIMARY KEY,
-> name CHAR(20) NOT NULL,
-> location CHAR(20) NOT NULL
-> );
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> # insert 3 records into the "vendors" table
mysql> INSERT INTO vendors (id, name, location) VALUES (1, "Mattel Inc", "El Segundo, Ca, USA");
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO vendors (id, name, location) VALUES (2, "Hasbro Inc", "Pawtucket, RI, USA");
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO vendors (id, name, location) VALUES (3, "J.W.Spear Plc", "Enfield, Middx, UK");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> SELECT games.id AS ProductCode,
-> games.name AS Game,
-> vendors.name AS Vendor,
-> games.price AS Price
-> FROM games, vendors
-> WHERE vendors.id = games.vendor;
+-------------+----------+---------------+-------+
| ProductCode | Game | Vendor | Price |
+-------------+----------+---------------+-------+
| 371/2209 | Scrabble | Mattel Inc | 14.50 |
| 373/2296 | Jenga | Hasbro Inc | 6.99 |
| 360/9659 | Uno | Mattel Inc | 11.99 |
| 373/5372 | Connect | J.W.Spear Plc | 5.99 |
| 370/9470 | Bingo | J.W.Spear Plc | 8.99 |
+-------------+----------+---------------+-------+
5 rows in set (0.00 sec)
mysql>
mysql> # delete these sample tables
mysql> DROP TABLE games;
Query OK, 0 rows affected (0.00 sec)
mysql> DROP TABLE vendors;
Query OK, 0 rows affected (0.00 sec)
mysql>