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'?
SELECT artist
FROM album JOIN track ON (asin=album)
WHERE song = 'Exodus'
|
|
| 1c |
Show the song for each track on
the album 'Blur'
SELECT song
FROM album JOIN track ON (asin=album)
WHERE title = '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.
SELECT title, COUNT(*)
FROM album JOIN track ON (asin=album)
GROUP BY title
|
|
| 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
SELECT title, COUNT(*)
FROM album JOIN track ON (asin=album)
WHERE LOWER(song) LIKE '%heart%'
GROUP BY title
|
|
| 2c |
A "title track" is where the song is the
same as the title. Find the title tracks.
SELECT song
FROM album JOIN track ON (asin=album)
WHERE song = title
|
|
| 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.
You only need to access one table in this example - so don't
use the JOIN.
SELECT title, artist
FROM album
WHERE artist = title
|
|
| 3a |
Find the songs that appear on more than 2
albums. Include a count of the number of times each shows
up.
The HAVING clause can be used outside of the
GROUP BY.
SELECT song, COUNT(*)
FROM album JOIN track ON asin=album
GROUP BY song
HAVING COUNT(*)>2
|
|
| 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.
SELECT title, price, COUNT(song)
FROM album JOIN track ON asin=album
GROUP BY title, price
HAVING price/COUNT(song) < 0.30
|
|
| 3c |
List all the tracks on the album with the most tracks.
SELECT song FROM track WHERE
album IN (
SELECT album FROM track
GROUP BY album
HAVING COUNT(song) >= ALL (
SELECT COUNT(song) FROM track
GROUP BY album)
)
|
|