Music Tutorial
| Language: Project:Language policy | English • 日本語 • 中文 |
|---|
The 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)
More details about 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 ...).
Find the title and artist
who recorded the
song 'Alison'.
SELECTtitle,artist FROMalbumJOINtrack ON(album.asin=track.album) WHEREsong='Alison'
SELECTtitle,artist FROMalbumJOINtrack ON(album.asin=track.album) WHEREsong='Alison'
Which artist recorded the song
'Exodus'?
SELECTartist FROMalbumJOINtrackON(asin=album) WHEREsong='Exodus'
Show the song for each track on
the album 'Blur'
SELECTsong FROMalbumJOINtrackON(asin=album) WHEREtitle='Blur'
We can use the aggregate functions and GROUP BY expressions on the joined table.
For each album show the title
and the total number of track.
SELECTtitle,COUNT(*) FROMalbumJOINtrackON(asin=album) GROUPBYtitle
SELECTtitle,COUNT(*) FROMalbumJOINtrackON(asin=album) GROUPBYtitle
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
SELECTtitle,COUNT(*) FROMalbumJOINtrackON(asin=album) WHEREsongLIKE'%Heart%' GROUPBYtitle
A "title track" is where the song is the
same as the title. Find the title tracks.
SELECTsong FROMalbumJOINtrackON(asin=album) WHEREsong=title
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.
SELECTtitle FROMalbum WHEREartist=title
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.
SELECTsong,COUNT(DISTINCTasin) FROMalbumJOINtrackONasin=album GROUPBYsong HAVINGCOUNT(DISTINCTasin)>2
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.
SELECTtitle,price,COUNT(song) FROMalbumJOINtrackONasin=album GROUPBYtitle,price HAVINGprice/COUNT(song)<0.50
Wagner's Ring cycle has an imposing 173 tracks, Bing Crosby clocks up 101 tracks.
SELECTtitle,COUNT(asin) FROMalbumJOINtrackONasin=album GROUPBYasin,title ORDERBY2DESC,title