To display the author who painted each book
mysql>
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.01 sec)
mysql>
mysql> CREATE TABLE states
-> (
-> name VARCHAR(30) NOT NULL, # state name
-> abbrev CHAR(2) NOT NULL, # 2-char abbreviation
-> statehood DATE, # date of entry into the Union
-> pop BIGINT, # population as of 4/1990
-> PRIMARY KEY (abbrev)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql>
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.01 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> 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> insert into states(name, abbrev, statehood, pop)values
-> ("Alabama","AL","1819-12-14",4040587),
-> ("Alaska","AK","1959-01-03",550043),
-> ("Arizona","AZ","1912-02-14",3665228),
-> ("Arkansas","AR","1836-6-15",2350725),
-> ("California","CA","1850-9-9",29760021),
-> ("Colorado","CO","1876-8-1",3294394),
-> ("Connecticut","CT","1788-1-9",3287116),
-> ("Delaware","DE","1787-12-7",666168),
-> ("Florida","FL","1845-3-3",12937926),
-> ("Georgia","GA","1788-1-2",6478216),
-> ("Hawaii","HI","1959-08-21",1108229),
-> ("Idaho","ID","1890-7-3",1006749),
-> ("Illinois","IL","1818-12-3",11430602),
-> ("Indiana","IN","1816-12-11",5544159),
-> ("Iowa","IA","1846-12-28",2776755),
-> ("Kansas","KS","1861-1-29",2477574),
-> ("Kentucky","KY","1792-6-1",3685296),
-> ("Louisiana","LA","1812-4-30",4219973),
-> ("Maine","ME","1820-3-15",1227928),
-> ("Maryland","MD","1788-4-28",4781468),
-> ("Massachusetts","MA","1788-2-6",6016425),
-> ("Michigan","MI","1837-1-26",9295297),
-> ("Minnesota","MN","1858-5-11",4375099);
Query OK, 23 rows affected (0.00 sec)
Records: 23 Duplicates: 0 Warnings: 0
mysql>
mysql>
mysql> SELECT author.name, book.title, states.name AS state
-> FROM author, book, states
-> WHERE author.a_id = book.a_id AND book.state = states.abbrev;
+------+----------+----------+
| name | title | state |
+------+----------+----------+
| Tom | Database | Indiana |
| Tom | SQL | Michigan |
| Jack | MySQL | Kentucky |
| Jack | XML | Kentucky |
| Jack | Java | Iowa |
+------+----------+----------+
5 rows in set (0.00 sec)
mysql>
mysql>
mysql> drop table states;
Query OK, 0 rows affected (0.00 sec)
mysql> drop table book;
Query OK, 0 rows affected (0.00 sec)
mysql> drop table author;
Query OK, 0 rows affected (0.00 sec)
mysql>
Related examples in the same category