Using Joins and Subqueries - MySQL - page 479

• Who painted the Mona Lisa ? Again, use the a_id column to join the rows, but this
time use the WHERE clause to restrict output to rows that contain the title, and select
the artist name from those rows:
mysql> SELECT artist.name
-> FROM artist INNER JOIN painting ON artist.a_id = painting.a_id
-> WHERE painting.title = 'Mona Lisa';
+----------+
| name |
+----------+
| Da Vinci |
+----------+
• For which artists did you purchase paintings in Kentucky or Indiana? This is similar
to the previous statement, but tests a different column in the painting table ( state )
to restrict output to rows for KY or IN :
mysql> SELECT DISTINCT artist.name
-> FROM artist INNER JOIN painting ON artist.a_id = painting.a_id
-> WHERE painting.state IN ('KY','IN');
+----------+
| name |
+----------+
| Da Vinci |
| Van Gogh |
+----------+
The statement also uses DISTINCT to display each artist name just once. Try it
without DISTINCT ; Van Gogh appears twice because you obtained two Van Goghs
in Kentucky.
• Joins used with aggregate functions produce summaries. This statement shows how
many paintings you have per artist:
mysql> SELECT artist.name, COUNT(*) AS 'number of paintings'
-> FROM artist INNER JOIN painting ON artist.a_id = painting.a_id
-> GROUP BY artist.name;
+----------+---------------------+
| name | number of paintings |
+----------+---------------------+
| Da Vinci | 2 |
| Renoir | 1 |
| Van Gogh | 2 |
+----------+---------------------+
A more elaborate statement uses aggregates to also show how much you paid for
each artist's paintings, in total and on average:
mysql> SELECT artist.name,
-> COUNT(*) AS 'number of paintings',
-> SUM(painting.price) AS 'total price',
-> AVG(painting.price) AS 'average price'
-> FROM artist INNER JOIN painting ON artist.a_id = painting.a_id
-> GROUP BY artist.name;