Join BabySteps

Music database

This tutorial introduces the notion of a join. The music has two tables: album and track in a one-to-many relationship.

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

album

Find the asin, title and artist for the album with title Revolver.

SELECT asin, title, artist
  FROM album 
 WHERE title = 'Legend'

SELECT asin, title, artist
  FROM album 
 WHERE title = 'Revolver'

track

Use the ASIN value from question 1 to list the tracks of Revolver. asin is the primary key in the album table and the column album is foreign key in the tracks table.

SELECT album, posn, song
  FROM track 
 WHERE album = 'B000001FY9'

SELECT album, posn, song
  FROM track 
 WHERE album = 'B000002UAR'