http://sqlzoo.net/wiki/Music_Tutorial/zh
音樂數據庫有兩個表格：album 大碟 和 track曲目。
album(asin, title, artist, price, release, label, rank)
大碟(asin, 碟名, 歌手, 售價, 推出, 標簽, 排名)
track(album, dsk, posn, song)
曲目(大碟, 碟號, 軌號, 歌名)
Asin=album
1.找出收錄歌曲song 'Alison'碟名title和歌手 artist。.
Select title,artist
From album join track
On asin=album
Where song='Alison'
或者
SELECT *
? FROM album JOIN track
? ? ? ? ?ON (album.asin=track.album)
?WHERE song = 'Alison'
2.哪一歌手artist 錄了歌曲 song 'Exodus'?
Select artist
From album join track
On Asin=album
Where song ='Exodus'
3.為大碟album 'Blur'， 顯示每一首歌的歌名 song 。
Select song?
From album join track
On Asin=album
Where title='Blur'
4.為每一大碟album顯示歌名title和每大碟的歌曲track(數量)
Select title,count（song）
From album join track
On Asin=album
Group by title
5.為每一大碟album列出碟名title 歌名中有'Heart'一詞的歌曲數量。 (沒有這些歌的大碟不用列出).
SELECT TITLE,COUNT(SONG)
From album join track
On Asin=album
WHERE SONG LIKE ‘%Heart%’
GROUP BY TITLE
6.主題歌曲是歌名 song 和大碟名字 title相同。找出主題歌曲。
SELECT SONG
FROM ALBUM JOIN TRACK
ON ASIN=ALBUM
WHERE SONG=TITLE
7.同名大碟是指大碟和歌手名字相同。 (例如大碟'Blur' 是由樂隊 'Blur'主唱)。 找出同名大碟。
album(asin, title, artist, price, release, label, rank)
大碟(asin, 碟名, 歌手, 售價, 推出, 標簽, 排名)
track(album, dsk, posn, song)
曲目(大碟, 碟號, 軌號, 歌名)
Asin=album
Select distinct(title)
FROM album JOIN track
On Asin=album
Where title=artist
8.找出歌曲收錄在２隻以上的大碟中。列出收錄次數。
Select song,count(distinct(title))
From track left join album
On Asin=album
Group by song
having count(distinct(title))>2
9.好價大碟是指大碟中每一首歌曲的價格是少于5角。 找出好價大碟，列出大碟名字，售價和歌曲數量。
Select title,price,count(song)
From album join track?
On Asin=album
Group by title,PRICE
HAVING price/COUNT(song)<0.50
10.按歌曲量(多至少)列出每一大碟的碟名和歌曲數量。
Select title,count(song) as num_song
From album join track
On asin=album
Group by title
Order by num_song desc
