過去問解きまくり研究所

平成27年度 春期 午前Ⅱ 問11

データ操作

相関副問合せに関する問題

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

CREATE TABLE 観測 (
   通番   INTEGER,
   鳥名   CHAR(20),
   観測数 INTEGER,
PRIMARY KEY (通番, 鳥名))

INSERT INTO 観測
  SELECT DISTINCT obs1.通番, obs2.鳥名, 0
     FROM 観測 AS obs1, 観測 AS obs2
    WHERE NOT EXISTS (
     SELECT * FROM 観測 AS obs3
       WHERE [   a   ]
         AND obs2.鳥名= obs3.鳥名)
答えと解説を見る

✓ これが正解ウobs1.通番 = obs3.通番

解説

通番と鳥名の組がまだ記録にないことを、obs3で確かめます。

このINSERT文は、obs1から通番を、obs2から鳥名を取り出してすべての組合せを作り、その組のうち観測表にまだ行がないものについて、観測数0の行を挿入します。まだ行がないことを確かめるのがNOT EXISTSの副問合せで、obs3の中に、通番がobs1の通番と等しく、鳥名がobs2の鳥名と等しい行があるかを調べます。鳥名の条件はすでに書かれているので、空欄にはobs1.通番 = obs3.通番が入ります。こうすると、ある通番の観測でその鳥がいなかった組だけが選ばれ、0の行が補われます。外側の二つの表から作る組を、内側の表で一つずつ照合する形として読むと、相関副問合せの条件が組み立てやすくなります。

ほかの選択肢はなぜ違うのか

出典：平成27年度 春期 データベーススペシャリスト試験 午前Ⅱ 問11

この解説に誤りを見つけたら教えてください。直して、直した記録を残します。誤りを報告する（メールが開きます）