Mega Code Archive
Display each part number, name and price
<![CDATA[
mysql>
mysql> CREATE TABLE IF NOT EXISTS parts
-> (
-> num INT PRIMARY KEY,
-> name CHAR(20) NOT NULL
-> );
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> # insert 3 records into the "parts" table
mysql> INSERT INTO parts (num, name) VALUES (382131, "Standard bracket");
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO parts (num, name) VALUES (382132, "Slide bracket");
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO parts (num, name) VALUES (382133, "Low-mount bracket");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> # create a table called "parts_prices"
mysql> CREATE TABLE IF NOT EXISTS parts_prices
-> (
-> num INT PRIMARY KEY,
-> price DECIMAL(6,2) NOT NULL
-> );
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> # insert 3 records into the "parts_prices" table
mysql> INSERT INTO parts_prices (num, price) VALUES (382131, 8.99);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO parts_prices (num, price) VALUES (382132, 10.99);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO parts_prices (num, price) VALUES (382133, 29.99);
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> # display all data in the "parts" and "parts_prices" table
mysql> SELECT * FROM parts;
+--------+-------------------+
| num | name |
+--------+-------------------+
| 382131 | Standard bracket |
| 382132 | Slide bracket |
| 382133 | Low-mount bracket |
+--------+-------------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM parts_prices;
+--------+-------+
| num | price |
+--------+-------+
| 382131 | 8.99 |
| 382132 | 10.99 |
| 382133 | 29.99 |
+--------+-------+
3 rows in set (0.00 sec)
mysql>
mysql> SELECT p.*, pp.price
-> FROM parts AS p, parts_prices AS pp
-> WHERE p.num = pp.num;
+--------+-------------------+-------+
| num | name | price |
+--------+-------------------+-------+
| 382131 | Standard bracket | 8.99 |
| 382132 | Slide bracket | 10.99 |
| 382133 | Low-mount bracket | 29.99 |
+--------+-------------------+-------+
3 rows in set (0.00 sec)
mysql>
mysql> # delete these sample tables
mysql> DROP TABLE IF EXISTS parts;
Query OK, 0 rows affected (0.00 sec)
mysql> DROP TABLE IF EXISTS parts_prices;
Query OK, 0 rows affected (0.00 sec)
mysql>
]]>