HOME»データベーススペシャリスト平成27年春期»午前Ⅱ 問11

データベーススペシャリスト平成27年春期 午前Ⅱ 問11

問11

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

分類

テクノロジ系 » データベース » データ操作

正解

ウ

解説

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

したがってaに入るのは「obs1.通番 = obs3.通番」です。
© 2016-2024　データベーススペシャリストドットコム　All Rights Reserved.
▲
Pagetop