Using Joins and Subqueries - MySQL - page 475

PRIMARY KEY ( a_id ),
UNIQUE ( name )
);
CREATE TABLE painting
(
a_id INT UNSIGNED NOT NULL , # artist ID
p_id INT UNSIGNED NOT NULL AUTO_INCREMENT , # painting ID
title VARCHAR ( 100 ) NOT NULL , # title of painting
state VARCHAR ( 2 ) NOT NULL , # state where purchased
price INT UNSIGNED , # purchase price ( dollars )
INDEX ( a_id ),
PRIMARY KEY ( p_id )
);
You've just begun the collection, so the tables contain only a few rows:
mysql> SELECT * FROM artist ORDER BY a_id;
+------+----------+
| a_id | name |
+------+----------+
| 1 | Da Vinci |
| 2 | Monet |
| 3 | Van Gogh |
| 4 | Renoir |
+------+----------+
mysql> SELECT * FROM painting ORDER BY a_id, p_id;
+------+------+-------------------+-------+-------+
| a_id | p_id | title | state | price |
+------+------+-------------------+-------+-------+
| 1 | 1 | The Last Supper | IN | 34 |
| 1 | 2 | Mona Lisa | MI | 87 |
| 3 | 3 | Starry Night | KY | 48 |
| 3 | 4 | The Potato Eaters | KY | 67 |
| 4 | 5 | Les Deux Soeurs | NE | 64 |
+------+------+-------------------+-------+-------+
The low values in the price column of the painting table betray the fact that your
collection actually contains only cheap imitations, not the originals. Well, that's all right:
who can afford the originals?
Each table contains partial information about your collection. For example, the ar
tist table doesn't tell you which paintings each artist produced, and the painting table
lists artist IDs but not their names. To use the information in both tables, write a query
that performs a join. A join names two or more tables after the FROM keyword. In the
output column list, use * to select all columns from all tables, tbl_name .* to select all
columns from a given table, or name specific columns from the joined tables or ex‐
pressions based on those columns.