SQLZOO答案-Music Tutorial

2024-01-19 09:07:10

1.找出　收錄　歌曲song 'Alison'　碟名title 和歌手　artist。

SELECT title, artist
  FROM album JOIN track
         ON (album.asin=track.album)
 WHERE song = 'Alison'

2.哪一歌手artist 錄了歌曲 song 'Exodus'?

SELECT artist
  FROM album JOIN track ON album.asin = track.album
WHERE track.song = 'Exodus'

3.為大碟album 'Blur'，顯示每一首歌的歌名 song 。

SELECT song 
  FROM album JOIN track ON album.asin = track.album
WHERE title = 'Blur'

4.為每一大碟album顯示歌名title和每大碟的歌曲。 track數量。

SELECT title, COUNT(song)
  FROM album JOIN track ON asin = album
GROUP BY title

6.主題歌曲是歌名 song 和大碟名字 title相同。找出主題歌曲。

SELECT song
  FROM album JOIN track ON asin = album
WHERE song = title

7.同名大碟是指大碟和歌手名字相同。 (例如大碟'Blur' 是由樂隊 'Blur'主唱)。找出同名大碟

SELECT title
  FROM album 
WHERE artist = title

8.找出歌曲收錄在２隻以上的大碟中。列出收錄次數。

SELECT song, COUNT(DISTINCT album)
  FROM album JOIN track ON asin = album
GROUP BY song 
  HAVING COUNT( DISTINCT album) > 2

9.好價大碟是指大碟中每一首歌曲的價格是少於5角。找出好價大碟，列出大碟名字，售價和歌曲數量。

SELECT title, price, COUNT(song)
  FROM album JOIN track ON asin = album
GROUP BY title, price
  HAVING price/ COUNT(song) *100< 50

10.歌手Wagner的大碟 Ring cycle 有173首歌曲, 歌手Bing Crosby有一大碟收錄了 101首歌曲。

按歌曲量(多至少)列出每一大碟的碟名和歌曲數量。

SELECT title, COUNT(song) AS num 
  FROM album JOIN track ON asin = album
GROUP BY title
ORDER BY num DESC

码农公寓