Music Tutorial/zh

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.找出歌曲收錄在2隻以上的大碟中。列出收錄次數。

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