データベーススペシャリスト試験 2017年 午後1 問03

• 【問題文】「同じ年の二つの月、例えば,2017年1月と 2017年2月の売上額の差を求める SQL」とある。
• さらに「ホスト変数の hv1 及び hv2 には、それぞれ ‘201701’ 及び ‘201702’ が設定」と記載。

• “月別売上B” では「売上額1月」「売上額2月」… と 1 行に 12 か月分を保持する縦持ち→横持ち（ワイドテーブル）設計に変更済み。
• 双方の月を同一行内で参照できるため、SELECT 句は

  売上額2月 - 売上額1月
  

  で目的の差分が得られる。従来の自己結合は不要。

• 列として「売上年」が存在し、比較月は列名で確定しているため、条件は年のみ。
• hv1・hv2 の先頭 4 桁が “2017” とわかるので

  売上年 = '2017'
  

  が適切。

• (a) = 「売上額2月 - 売上額1月」
• (b) = 「売上年 = '2017'」

WHERE X.売上年月 = :hv1 AND Y.売上年月 = :hv2

売上額

売上額1月

売上額2月

売上額3月

売上額5月

• 問題1：同一社員行内で電話番号が重複してはいけない

• 電話番号は社員をまたいで重複してもよい

• NULL は排除済みなので一意性検証は値比較だけで済む

CHECK ( ((c) AND (d) AND 電話番号1 <> 電話番号2) OR ((c) AND (e)) )

• 問題1：同じ番号を重複登録
• 問題2：

  電話番号1

  が

  NULL

  、

  電話番号2

  のみ有値
• 問題3：両方

  NULL

電話番号1

① 電話番号1 IS NOT NULL

電話番号2

③ 電話番号2 IS NOT NULL

電話番号2

④ 電話番号2 IS NULL

• c：

  ① 電話番号1 IS NOT NULL

• d：

  ③ 電話番号2 IS NOT NULL

• e：

  ④ 電話番号2 IS NULL

  
  が正答となります。

SELECT 社員ＩＤ、1, 電話番号１ … WHERE □(f)
UNION
SELECT 社員ＩＤ、2, 電話番号２ … WHERE 電話番号１ <> 電話番号２
UNION
SELECT 社員ＩＤ、1, 電話番号２ … WHERE □(g) AND □(h)

• 目的：有効な “電話番号1” をそのままコピー。
• 必要条件：

  電話番号1

  が NULL でないこと → “①”。
• 重複なしの要件とも矛盾しない。
  ⇒ (f)=①。

• WHERE 句に既に

  電話番号１ <> 電話番号２

  がある。
• “電話番号1 = 電話番号2” 行（【問題文】“問題1”）を除外して二件目として登録する意図。
• 追加の述語は不要なので (g)(h) ではない。

• 目的：

  電話番号1

  が未入力で

  電話番号2

  だけ登録されている行（【問題文】“問題2”）を救済。
• 条件①

  電話番号1 IS NULL

  → “②”。
• 条件②

  電話番号2 IS NOT NULL

  → “③”。
• 両条件は AND で連結、順序は問われていない。
  ⇒ (g)=②、(h)=③（順不同）。

• 移行元行は【問題文】「表３」に５件。
• 移行先は【問題文】「“社員連絡先Ｂ” テーブルの電話番号列に NOT NULL 制約を定義」なので

  NULL

  は登録不可。

SELECT

• 【問題文】「SELECT 社員ＩＤ、1, 電話番号１ … WHERE □（f）」
  • 要件：電話番号１が存在する行だけを採用 → (f) は “電話番号1 IS NOT NULL”。
  • 取得行：E1／E2／E3。
• 【問題文】「SELECT 社員ＩＤ、2, 電話番号２ … WHERE 電話番号１ <> 電話番号２」
  •

  <>

  は両列が非 NULL かつ値が異なる場合にのみ真。
  • 取得行：E1（1111≠3333）だけ。E2 は同値、E4 は NULL 比較で偽。
• 【問題文】「SELECT 社員ＩＤ、1, 電話番号２ … WHERE □（g） AND □（h）」
  • 要件２の「電話番号１が無いが電話番号２はある」パターンを救済。
  (g) は “電話番号1 IS NULL”、(h) は “電話番号2 IS NOT NULL”。
  • 取得行：E4 のみ。

• ①＋②＋③ ＝ {E1-1111, E2-2222, E3-3333, E1-3333, E4-4444}
• 表示順は

  SELECT

  固定値で決定。
• E5 は両列 NULL なのでどの

  SELECT

  にも該当しない。

• 【問題文】「テーブルに一意性制約を定義した。」により
  

  (社員ID, 表示順)

  または

  (社員ID, 電話番号)

  の重複は登録不可。
  取得結果には重複がないため制約違反は発生しない。

社員ID  表示順  電話番号
E1      1       1111
E1      2       3333
E2      1       2222
E3      1       3333
E4      1       4444