The phrase FROM album JOIN track ON album.asin=track.album
represents the join of the tables album and
track. This JOIN has one row for
every track. In addition to the track fields (album,
disk, posn and song)
it includes the details of the corresponding album
(title, artist ...).
1a. Find the title and artist
who recorded the
song'Alison'.
Results
1b. Which artist recorded the song'Exodus'?
Results
1c. Show the song for each track on
the album'Blur'
Results
We can use the aggregate functions and GROUP BY
expressions on the joined table.
2a. For each album show the title
and the total
number of track.
Results
2b. For each album show the title
and the total
number of tracks containing the word 'Heart'
(albums with no such tracks need not be shown).
Use song LIKE '%Heart%' to find the songs
that include the word Heart
Results
2c. A "title track" is where the song is the
same as the title. Find the title tracks.
Results
2d. An "eponymous" album is one where the title is
the same as the artist (for example the album
'Blur' by the band 'Blur').
Show the eponymous albums.
The HAVING clause can be used outside of the
GROUP BY.
Results
3b.
A "good value" album is one where the price per track
is less than 50 pence. Find the good value album -
show the title, the price and the number of tracks.
Results
3c.
Wagner's Ring cycle has an imposing 173 tracks, Bing Crosby
clocks up 101 tracks.
List albums so that the album with the most tracks is first.
Show the title and the number of tracks
