平成27年春期試験問題　午前Ⅱ 問11

問11解説へ
庭に訪れた野鳥の数を記録する"観測"表がある。観測のたびに通番を振り，鳥名と観測数を記録している。AVG関数を用いて鳥名別に野鳥の観測数の平均値を得るために，一度でも訪れた野鳥については，観測されなかったときの観測数を0とするデータを明示的に挿入する。SQL文のaに入る字句はどれか。ここで，通番は初回を1として，観測のタイミングごとにカウントアップされる。
11.png

  • obs1.通番 = obs1.通番
  • obs1.通番 = obs2.通番
  • obs1.通番 = obs3.通番
  • obs2.通番 = obs3.通番
正解　ウ問題へ
分野：テクノロジ系
中分類：データベース
小分類：データ操作
広告
解説
"観測"表に存在しないすべての(通番，鳥名)の組み合わせについて、観測数を0にしてレコードを新規追加するSQLを考えます。以下はレコード追加後のイメージです。
11_1.png
まず最初に(通番，鳥名)のすべての組合せ(当然ですが、通番、鳥名は1行以上の記載があるものに限られます)を取得するために、"観測"表同士の直積を得ることを考えます。これは、
SELECT DISTINCT obs1.通番, obs2.鳥名
FROM 観測 AS obs1, 観測 AS obs2
によって得られます(重複をなくすためにDISTINCTは必要)。この結果セットをT1とします。
11_2.png
WHERE句では、この中から、"観測"表にまだ存在しない(通番，鳥名)のすべての組合せ、すなわちレコード追加の対象となる組合せを絞り込むことを目的としています。現在存在している(通番，鳥名)の組合せの一覧は、"観測"表(これをobs3とする)の射影によって得られるので、上記のT1の各行について、T1のobs1.通番およびobs2.鳥名を用いた相関副問合せで、まだobs3に存在していない(NOT EXISTSが真の)組合せのみを選択します。
WHERE NOT EXISTS (
　SELECT * FROM 観測 AS obs3
　　WHERE obs1.通番 = obs3.通番
　　AND obs2.鳥名 = obs3.鳥名)
11_3.png
以上により、それぞれの通番において観測されなかった鳥名の一覧が取得できます。あとはこの結果を観測テーブルに観測数"0"でINSERTすればよいことになります。

したがってaに入るのは「obs1.通番 = obs3.通番」です。
広告

前の問題次の問題

▲
Pagetop