8.SQL演習(3/3)

SELECT continent, name, population FROM world x

  WHERE population >= ALL

    (SELECT population FROM world y

        WHERE y.continent=x.continent

          AND population>0)

■問題文

6.JOIN

UEFA欧州選手権 UEFA EURO 2012 の試合データを使った演習問題

(サッカーの大会、ポーランドとウクライナで開催)

game ゲームテーブル 試合の日時、参加チームを記録

id 試合のid

mdate 試合の日

stadium スタジアム

team1 チーム１

team2 チーム２

goal ゴールテーブル ゴールの記録

matchid 試合id

teamid チームid

player プレイヤー

gtime ゴール時間

eteam 参加チームテーブル

id チームのid（国）

teamname チーム名前

coach コーチ

6-1

最初の例として Bender のゴール記録が示されている。

ドイツの matchid と player名 を表示

（ヒント） where の条件は teamid = 'GER'

（ヒント）

SELECT ????????????? FROM goal 

  WHERE teamid = 'GER'

6-2

上記のクエリ―では、Lars Benderのゴールは ゲーム 1012 で確認できる。

goalテーブルで試合番号は matchid だが、gameテーブルでは id となっていることに注意する。

game 1012 の id, stadium, team1, team2 を表示する。  

（ヒント）

SELECT id,stadium,team1,team2

  FROM game 

 WHERE ????????????????

6-3

JOIN を利用して、２ステップで単独のクエリ―を組み合わせる事が出来る。

gameとgoalの詳細を得るには、

（ＳＱＬの例文）

問題： player, teamid ,stadium, mdate を全てのドイツのゴールについて表示する。

表の結合の条件 と データの検索の条件 の２つのタイプの条件が必要。

 and で記述しても on と where に分けて記述してもよい。

（ヒント１）

SELECT ????????????????

  FROM game JOIN goal ON (id=matchid and ??????)

（ヒント２）

SELECT ????????????????

  FROM game JOIN goal ON (id=matchid)

  where ??????

6-4

上の問題と同様に JOIN を利用して、

Marioという名前の選手のゴールについて、team1, team2 , player を表示する。

（ヒント）

3の問題が解けたらコピーして条件の部分を修正

6-5

eteam には各参加国のコーチが記載されている。

JOIN で goal を eteam に結合する。

goal JOIN eteam on teamid=id を条件に使用する。

最初の１０分間でゴールしたという条件で、 player, teamid, coach, gtime を表示。

6-6

team1のコーチが 'Fernando Santos' となる試合日とチーム名を表示。

6-7

 'National Stadium, Warsaw' スタジアムで開催された試合でゴールした選手を表示する。

6-8

ドイツと対戦して、ゴールした選手の名前を全て表示する。

（ヒント）

例として、ドイツ―ギリシャ戦の全試合を表示するクエリーが入力してある。

6-9

チーム名とゴール数の合計を表示する。

（ヒント）

select の項目で count(*)を使用し、 GROUP BY teamname を利用する。

6-10

スタジアムの名前と、そのスタジアムでのゴール数を各スタジアムごとに表示する。

6-11

ポーランド（POL）が参戦している全試合の matchid と 日程 とその試合のゴール数 を表示する。

6-12

ドイツが得点した試合の matchid と 日程 と ドイツの得点 を表示する。

6-13

この問題は、ここまでまだ未解説のSQL構文「CASE WHEN」 を使用する。

下に示す様に、各試合ごとに各チームの得点を表示する。

mdate team1 score1 team2 score2 日程 チーム名１ 得点１ チーム名２ 得点２

注意） 全得点状況が、記録されている。もし、チーム名が goal に記録されていれば、

その時点でチームが１得点していることにななり、チーム名が記載されていなければ、得点は０点である。

チーム名有り → １

チーム名なし → ０

この、得点状況を １と０ に CASE WHEN で変換した結果をSUM で集計すれば、そのチームの得点を集計できる。

結果は、日程順で並べ替えて出力する(日程が同じなら、idの順番)。

※難しいので長文で解説

例文のＳＱＬ：

SELECT mdate,  team1,

  CASE WHEN teamid=team1 THEN 1 ELSE 0 END score1

  FROM game JOIN goal ON matchid = id

ＳＱＬの解釈）

SELECT mdate,  game.team1, goal.teamid

  FROM game JOIN goal ON goal.matchid = game.id

で、game と goal が結合できる。

この結合方法では、team1 が参加した試合について、自国の得点と、対戦国の得点の両方が出力される。

そこで、出力結果が、自国の場合は １点、 他国の場合は ０点に 出力を変換して表示する。

その為に、

以下の様に selectの表示項目で、CASE WHEN を利用する。

    CASE WHEN teamid=team1 THEN 1 ELSE 0 END score1

上記の条件部分で、  goal.teamid が  game.team1 に一致するかを判定して、  THEN 1 で一致の場合は １ に ELSE 0 で不一致の場合は 0 に変換している。

ENDの後ろの score1 で、出力結果の見出しに score1 という名前を付けている。（AS score1 の省略形）

(ヒント)

自国の得点状況を集計するには、

SELECT mdate,

  team1,

  sum(

  CASE WHEN teamid=team1 THEN 1 ELSE 0 END

  )  as score1

 FROM game

 LEFT JOIN goal ON goal.matchid = game.id

 GROUP BY game.id,mdate,team1,team2

これに、team2 の国名と 得点の集計結果を同様に付け加える。

JOINではなく、 LEFT JOIN を使用する必要が有る。試合で両国とも無得点 0-0 の場合は、 goalには、記録がない。

eteamにチーム名が記載されていても、goalにゴールの記録がないチームは、JOIN でテーブルを結合すると、結合結果には残らない（inner join 内部結合のため）。

そのため、LEFT JOINで結合し、得点の欄は空欄(NULL)となるように結合する。

sumで集計した際に、得点の無い試合の集計結果は 0点になる。

集計結果は、最終的に日程順に並べ替える。 order by を使う。

7.More JOIN eperations

映画のデータベース

このチュートリアルで join の理解を深める。データベースは、3つのテーブルで構成される。

movie（映画） と actor（役者） と casting（出演） である。

テーブルの詳細は、以下である。

(SELECT MAX(c) FROM

(SELECT yr,COUNT(title) AS c FROM

   movie JOIN casting ON movie.id=movieid

         JOIN actor   ON actorid=actor.id

 WHERE name='John Travolta'

 GROUP BY yr) AS t

)

■エラーメッセージなど

You have an error in your SQL syntax 文法エラー

Too many rows 行が多すぎる（条件の指定ミス）

Too few rows 行が少なすぎる（条件の指定ミス）

Too many columnss 列が多すぎる（表示項目の指定ミス）

Too few columnss 列が少なすぎる（表示項目の指定ミス）

Unknown column 'xxxxxxxxx' in 'field list'  (xxxxxxxxx がフィールド名に無い。スペルミスか、テーブルの指定ミス)

Show correct result 正しい答えを表示する（表示内容が間違っていた場合表示される。クリックで正解の出力結果を表示）