A Gentle Introduction to


Pick an engine:

The Music database

This tutorial introduces the notion of a join. The music has two tables: album and track.

album(asin, title, artist, price, release, label, rank)
track(album, disk, posn, song) 

More details about the database.

How to do joins.

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'.

1b Which artist recorded the song 'Exodus'?

1c Show the song for each track on the album 'Blur'

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.

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).

2c A "title track" is where the song is the same as the title. Find the title tracks.

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.

3a Find the songs that appear on more than 2 albums. Include a count of the number of times each shows up.

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.

3c List all the tracks on the album with the most tracks.