Difference between revisions of "Music Tutorial/zh"

From SQLZOO
Jump to navigation Jump to search
Line 62: Line 62:
 
</div>
 
</div>
  
We can use the aggregate functions and <code>GROUP BY</code> expressions on the joined table.
+
我們可以在合拼表格時,使用群組函數和<code>GROUP BY</code>
 
<div class='qu'>
 
<div class='qu'>
For each <code>album</code> show the <code>title</code>
+
為每一大碟<code>album</code>顯示歌名<code>title</code>和每大碟的歌曲。
and the total number of <code>track</code>.
+
<code>track</code>數量。
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
SELECT title, COUNT(*)
+
SELECT title FROM album JOIN track ON (asin=album)
  FROM album JOIN track ON (asin=album)
 
 
  GROUP BY title
 
  GROUP BY title
 
</source>
 
</source>
Line 80: Line 79:
  
 
<div class='qu'>
 
<div class='qu'>
For each <code>album</code> show the <code>title</code>
+
為每一大碟<code>album</code>列出碟名<code>title</code>
and the total
+
歌名中有<code>'Heart'</code>一詞的歌曲數量。
number of tracks containing the word <code>'Heart'</code>
+
(沒有這些歌的大碟不用列出).
(albums with no such tracks need not be shown).
+
<div class='hint' title='提示'>
<div class='hint' title='hint'>
+
使用 song LIKE '%Heart%' 來找尋有<code>Heart</code>一詞的歌名。
Use song LIKE '%Heart%' to find the songs that include the word <code>Heart</code>
 
 
</div>
 
</div>
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>

Revision as of 00:01, 26 March 2016

音樂數據庫The Music database

此教程使用簡介表格連接的使用。音樂數據庫有兩個表格:album 大碟 和 track曲目。

album(asin, title, artist, price, release, label, rank) 
大碟(asin, 碟名, 歌手, 售價, 推出, 標籤, 排名)


track(album, dsk, posn, song)
曲目(大碟, 碟號, 軌號, 歌名)

More details about about the database|更多有關音樂數據庫的資訊(英文)

如何進行表格合拼

語句code>FROM album JOIN track ON album.asin=track.album 代表合拼表格 albumtrack。 這個合拼 JOIN 得出的每一個紀錄是一首歌。除了歌曲的欄位外(album, disk, posnsong),結果還包括每首歌對應的大碟資料album (title, artist ...)。

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

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

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

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

為大碟album 'Blur', 顯示每一首歌的歌名 song

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

我們可以在合拼表格時,使用群組函數和GROUP BY

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

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

為每一大碟album列出碟名title 歌名中有'Heart'一詞的歌曲數量。 (沒有這些歌的大碟不用列出).

使用 song LIKE '%Heart%' 來找尋有Heart一詞的歌名。

SELECT title, COUNT(*)
  FROM album JOIN track ON (asin=album)
 WHERE song LIKE '%Heart%'
 GROUP BY title

A "title track" is where the song is the same as the title. Find the title tracks.

SELECT song
  FROM album JOIN track ON (asin=album)
 WHERE song = 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.

SELECT title
  FROM album
 WHERE artist = 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.

SELECT song, COUNT(DISTINCT asin)
  FROM album JOIN track ON asin=album
GROUP BY song
HAVING COUNT(DISTINCT asin)>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.

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

Wagner's Ring cycle has an imposing 173 tracks, Bing Crosby clocks up 101 tracks.

List albums so that the album with the most tracks is first. Show the title and the number of tracks
SELECT title, COUNT(asin)
FROM album JOIN track ON asin=album
GROUP BY asin,title
ORDER BY 2 DESC

Movie Database tutorial