Music Tutorial/ja

音楽データベース

このチュートリアルで結合(join)の文法を紹介する。音楽データベースには2つのテーブル: アルバムalbum と トラックtrack がある。
訳者注　このデータベースはMySQLでないと動かない。データベースの設定を確認すること。

album(asin, title, artist, price, release, label, rank)

    asin Amazon商品識別番号
    title タイトル
    artist アーティスト
    price 価格
    release リリース
    label レーベル
    rank ランク

track(album, dsk, posn, song)

    album アルバム
    dsk ディスク
    posn 再生順
    song 曲

このデータベースについての詳細（訳者注 リンク切れ）

結合方法

このフレーズ FROM album JOIN track ON album.asin=track.album はテーブルalbumとtrackの結合を表している。 このJOIN(結合)で全てのトラックについて1行が得られる（訳者注 トラックの行数と同じ行数になる）。 trackのフィールド (album,disk, posn and song)に加えてalbumに関する詳細(title, artist ...)が含まれる。

1.

曲song 'Alison'がレコーディングされた（訳者注 アルバムの）タイトルtitleとアーティストartistを見つける。

SELECT title, artist
  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'

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

result

2.

どのアーティストartistが曲song 'Exodus'をレコーディングしたか？

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

Submit SQLrestore default

result

3.

アルバムalbum 'Blur'の各トラックtrackの曲songを表示する。

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

Submit SQLrestore default

result

集約関数とGROUP BYをテーブル結合の式で使うことができる。

4.

各albumについてタイトルtitleとトラックtrackの総数を表示する。

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

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

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

result

5.

各アルバムalbumのタイトルtitleと'Heart'という単語を含むトラックtrackの総数を表示する。 (そのようなトラックを含まないアルバムは表示する必要はない)。

ヒント

song LIKE '%Heart%' をHeartを含むtrackの検索で使う。

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

Submit SQLrestore default

result

6.

"タイトル トラック" とは曲songがタイトルtitleと同じもののこと. タイトル トラックを見つける。

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

Submit SQLrestore default

result

7.

"代名詞"アルバムalubmとはタイトルtitleがアーティストartistと同じもののこと。 (例えば、バンド'Blur'のアルバム'Blur'。) 代名詞アルバムを表示する。

ヒント

この例ではテーブル1つにしかアクセスする必要がない。だからJOINは使わないこと。

SELECT title
  FROM album
 WHERE artist = title

Submit SQLrestore default

result

8.

2枚を超えるアルバムalbumに現れる曲songを見つける。登場回数も含めて表示する。

ヒント

HAVING節をGROUP BYの後で使う。 訳者ヒント song = のレコードを複数含むアルバムがいくつかある。登場回数のカウントでは重複を除外すること。

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

Submit SQLrestore default

result

9.

"お値打ち"アルバムalbumとは1トラックtrack辺りの価格priceが50ペンス（訳者注 0.50 と比較）を下回るもののこと。 お値打ちアルバムを見つける。タイトルtitleと価格とtrack総数を表示する。

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

Submit SQLrestore default

result

10.

ワーグナーの Ring cycle にはご立派にも173ものトラックがあり、ビング・クロスビーは101トラックを記録した。

トラック数の多い順にアルバムのリストを作る。 タイトルtitleとトラックtrack数を表示する。

2つ以上の同じトラック数のアルバムが有ったらアルファベット順に並べる。

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

Submit SQLrestore default

result

映画データベースのチュートリアル