mysql>
mysql> CREATE TABLE author
-> (
-> a_id INT UNSIGNED NOT NULL AUTO_INCREMENT, # author ID
-> name VARCHAR(30) NOT NULL, # author name
-> PRIMARY KEY (a_id),
-> UNIQUE (name)
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE book
-> (
-> a_id INT UNSIGNED NOT NULL, # author ID
-> p_id INT UNSIGNED NOT NULL AUTO_INCREMENT, # book ID
-> title VARCHAR(100) NOT NULL, # title of book
-> state VARCHAR(2) NOT NULL, # state where purchased
-> price INT UNSIGNED, # purchase price (dollars)
-> INDEX (a_id),
-> PRIMARY KEY (p_id)
-> );
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> INSERT INTO author (name) VALUES
-> ('Tom'),
-> ('Monet'),
-> ('Jack'),
-> ('Picasso'),
-> ('Mary')
-> ;
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql>
mysql>
mysql>
mysql> INSERT INTO book (a_id,title,state,price)
-> SELECT a_id, 'Database', 'IN', 34 FROM author WHERE name = 'Tom';
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql>
mysql> INSERT INTO book (a_id,title,state,price)
-> SELECT a_id, 'SQL', 'MI', 87 FROM author WHERE name = 'Tom';
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql>
mysql> INSERT INTO book (a_id,title,state,price)
-> SELECT a_id, 'MySQL', 'KY', 48 FROM author WHERE name = 'Jack';
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> INSERT INTO book (a_id,title,state,price)
-> SELECT a_id, 'XML', 'KY', 67 FROM author WHERE name = 'Jack';
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> INSERT INTO book (a_id,title,state,price)
-> SELECT a_id, 'Java', 'IA', 33 FROM author WHERE name = 'Jack';
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql>
mysql> INSERT INTO book (a_id,title,state,price)
-> SELECT a_id, 'HTML', 'NE', 64 FROM author WHERE name = 'Mary';
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql>
mysql>
mysql>
mysql> SELECT * FROM author, book
-> WHERE author.a_id = book.a_id;
+------+------+------+------+----------+-------+-------+
| a_id | name | a_id | p_id | title | state | price |
+------+------+------+------+----------+-------+-------+
| 1 | Tom | 1 | 1 | Database | IN | 34 |
| 1 | Tom | 1 | 2 | SQL | MI | 87 |
| 3 | Jack | 3 | 3 | MySQL | KY | 48 |
| 3 | Jack | 3 | 4 | XML | KY | 67 |
| 3 | Jack | 3 | 5 | Java | IA | 33 |
| 5 | Mary | 5 | 6 | HTML | NE | 64 |
+------+------+------+------+----------+-------+-------+
6 rows in set (0.00 sec)
mysql>
mysql> SELECT * FROM author LEFT JOIN book
-> ON author.a_id = book.a_id;
+------+---------+------+------+----------+-------+-------+
| a_id | name | a_id | p_id | title | state | price |
+------+---------+------+------+----------+-------+-------+
| 1 | Tom | 1 | 1 | Database | IN | 34 |
| 1 | Tom | 1 | 2 | SQL | MI | 87 |
| 2 | Monet | NULL | NULL | NULL | NULL | NULL |
| 3 | Jack | 3 | 3 | MySQL | KY | 48 |
| 3 | Jack | 3 | 4 | XML | KY | 67 |
| 3 | Jack | 3 | 5 | Java | IA | 33 |
| 4 | Picasso | NULL | NULL | NULL | NULL | NULL |
| 5 | Mary | 5 | 6 | HTML | NE | 64 |
+------+---------+------+------+----------+-------+-------+
8 rows in set (0.00 sec)
mysql>
mysql> drop table book;
Query OK, 0 rows affected (0.00 sec)
mysql> drop table author;
Query OK, 0 rows affected (0.00 sec)