http://sqlzoo.net/wiki/Music_Tutorial/zh
音樂數(shù)據(jù)庫有兩個(gè)表格：album 大碟 和 track曲目。
album(asin, title, artist, price, release, label, rank)
大碟(asin, 碟名, 歌手, 售價(jià), 推出, 標(biāo)簽, 排名)
track(album, dsk, posn, song)
曲目(大碟, 碟號(hào), 軌號(hào), 歌名)
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(數(shù)量)
Select title,count（song）
From album join track
On Asin=album
Group by title
5.為每一大碟album列出碟名title 歌名中有'Heart'一詞的歌曲數(shù)量楼入。 (沒有這些歌的大碟不用列出).
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' 是由樂隊(duì) 'Blur'主唱)。 找出同名大碟北专。
album(asin, title, artist, price, release, label, rank)
大碟(asin, 碟名, 歌手, 售價(jià), 推出, 標(biāo)簽, 排名)
track(album, dsk, posn, song)
曲目(大碟, 碟號(hào), 軌號(hào), 歌名)
Asin=album
Select distinct(title)
FROM album JOIN track
On Asin=album
Where title=artist
8.找出歌曲收錄在２隻以上的大碟中禀挫。列出收錄次數(shù)。
Select song,count(distinct(title))
From track left join album
On Asin=album
Group by song
having count(distinct(title))>2
9.好價(jià)大碟是指大碟中每一首歌曲的價(jià)格是少于5角拓颓。 找出好價(jià)大碟语婴，列出大碟名字，售價(jià)和歌曲數(shù)量驶睦。
Select title,price,count(song)
From album join track?
On Asin=album
Group by title,PRICE
HAVING price/COUNT(song)<0.50
10.按歌曲量(多至少)列出每一大碟的碟名和歌曲數(shù)量砰左。
Select title,count(song) as num_song
From album join track
On asin=album
Group by title
Order by num_song desc
