平成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.鳥名)
出典：平成27年度 春期 データベーススペシャリスト試験 午前Ⅱ 問11
- アobs1.通番 = obs1.通番
- イobs1.通番 = obs2.通番
- ウobs1.通番 = obs3.通番
- エobs2.通番 = obs3.通番
正解：ウ
解説
このSQL文は，観測表に実際に記録されている全ての通番（obs1）と全ての鳥名（obs2）の組合せのうち，その通番・鳥名の組合せで実際の観測記録（obs3）が存在しないものを見つけ出し，観測数0として明示的に挿入するものである。したがって，NOT EXISTSの副問合せでは，外側のobs1が示す通番と，外側のobs2が示す鳥名の両方に一致するobs3の行が存在するかどうかを判定する必要がある。副問合せ中には既にobs2.鳥名=obs3.鳥名という条件が与えられているため，aに入るべき条件は，obs1の通番とobs3の通番を一致させるobs1.通番=obs3.通番である。
選択肢ごとの解説
- ア誤り。obs1.通番=obs1.通番は常に真となる同義反復（トートロジー）であり，obs3の通番との関連付けが行われないため，「その通番でその鳥が観測されなかった」ことを正しく判定できない。
- イ誤り。obs1.通番=obs2.通番は，副問合せの内側にあるobs3を全く参照しておらず，観測表obs3に実際の記録が存在するかどうかを判定する条件になっていない。
- ウ正しい。obs1.通番=obs3.通番とすることで，外側のobs1が示す通番と，副問合せ側のobs3の通番を一致させることができ，obs2.鳥名=obs3.鳥名と合わせて，「obs1の通番でobs2の鳥が実際に観測されたか」を正しく判定できる。
- エ誤り。obs2.通番=obs3.通番としてしまうと，判定に使う通番がobs2（鳥名側）のものになってしまい，挿入すべき行の通番であるobs1の通番と対応しなくなるため，意図した判定にならない。