Basic SQL - Learning MySQL - page 162

album takes to play. You can do this by summing the times of the individual tracks
with the SQL SUM( ) function. Here's how it works:
mysql> SELECT SUM(time) FROM
-> album INNER JOIN track USING (artist_id, album_id)
-> WHERE album.artist_id = 1 AND album.album_id = 7;
+-----------+
| SUM(time) |
+-----------+
| 43.78 |
+-----------+
1 row in set (0.00 sec)
You can see the album runs for just under 44 minutes. The SUM( ) function reports the
sum of all values for the column enclosed in the parentheses—in this case, time —and
not the individual values themselves. Because we've used a WHERE clause to choose only
rows for the Brotherhood album, the sum of the time values is the total play time of the
album. Of course, to run this query, we needed to know that New Order's artist_id
is 1 and that the album_id of “Brotherhood” is 7. We discovered this by running two
other SELECT queries beforehand:
mysql> SELECT artist_id FROM artist WHERE artist_name = "New Order";
+-----------+
| artist_id |
+-----------+
| 1 |
+-----------+
1 row in set (0.00 sec)
mysql> SELECT album_id FROM album
-> WHERE artist_id = 1 AND album_name = "Brotherhood";
+----------+
| album_id |
+----------+
| 7 |
+----------+
1 row in set (0.00 sec)
We explain more features of SELECT and aggregate functions in Chapter 7.
The INSERT Statement
The INSERT statement is used to add new data to tables. In this section, we explain its
basic syntax and show you simple examples that add new rows to the music database.
In Chapter 6, we'll discuss how to load data from existing tables or from external data
sources.