Working with multiple tables 21

Grouping songs

Now that we have actual songs, we can look up, for example, how many songs we have in

each genre.

select genre, count(*) as songcount from songs group by genre

We can do everything in this table that we could in the other one, just on different data.

Relating tables

So we have a song table with song information, and an album table with album information.

Suppose we want to combine the two on our display? We need to have a field that relates the

two tables. In this case, the item that is the same is likely to be the album name.

select song, songs.artist, songs.album, purchasedate, songs.rating/10 as

"Song Rating", albums.rating as "Album Rating"

from songs, albums

where songs.album=albums.album

order by artist

limit 10

I've chosen to  limit 10  so that I only see ten rows. Otherwise, the screen would fill up with

several  thousand  rows.  When  you  are  testing  your  queries,  it  is  often  useful  to  limit  the

number of records you ask for.