The SELECT Statement and Basic Querying Techniques | 161

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 inset sec)
You can seethe 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 playtime 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 inset sec)
mysql> SELECT album_id FROM album
-> WHERE artist_id = 1 AND album_name = "Brotherhood";
+----------+
| album_id |
+----------+
| 7 |
+----------+
1 row inset 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.
162 | Chapter 5:
Basic SQL

Download 4.24 Mb.

Share with your friends: