データベーススペシャリスト 2018年 午後1 問02

'1'

DAYELEMENT(退職年月日) < 21

21

MONTHELEMENT(退職年月日) < MONTHELEMENT(CURRENT_DATE)

CURRENT_DATE

SELECT (d) 、COUNT((e) 従業員家族.従業員コード) AS 従業員数、
       COUNT(*) AS 扶養対象者数
  FROM 従業員、従業員家族
 WHERE 従業員.従業員コード = 従業員家族.従業員コード
   AND 従業員家族.扶養フラグ = '1'
   AND 従業員家族.生年月日 >= TO_DATE('2007-01-01')

GROUP BY (d)
(f) (d)

• 依頼は「部署コード順に 2007年1月1日以降に生まれた扶養対象者をもつ従業員数と扶養対象者数の一覧表」
• よって

  (d)

  に入るのは一覧のキーである

  従業員.部署コード

  。

• 同一従業員に家族が複数行存在するので、そのまま

  COUNT(従業員家族.従業員コード)

  では重複計上されます。
• 重複排除には

  COUNT(DISTINCT 従業員家族.従業員コード)

  の構文を使うため

  (e)

  は

  DISTINCT

  。

• 一覧表は「部署コード順」で要求されているため、

  ORDER BY

  句を付加して

  (f)

  は

  ORDER BY

  。

GROUP BY

ORDER BY

• 集計キー

  (d)

  を

  GROUP BY

  と

  ORDER BY

  双方に使用し、

  SELECT

  の先頭列でも同じ列を出力する。

NO ACTION

• 【表3】で “従業員”.“部署コード” の挙動は

  UPDATE／NO ACTION

  、

  DELETE／CASCADE

  、検査契機は「猶予モード」です。
• 挿入時に “部署コード” が

  NULL

  の場合、参照制約は適用されません（参照値が存在しないため）。
• よって制約違反は発生せず「正常終了」と判断できます。

• “部署”.“管理者従業員コード” は【表3】で

  DELETE／NO ACTION

  と定義されています。
• 当該従業員が “部署” テーブルから参照されている状態で “従業員” テーブルから削除すると、参照先が失われるため

  NO ACTION

  によりエラーが返されます。
• したがって「エラー」になり、その理由は「“部署”テーブルの管理者従業員コードの参照制約に違反するから」です。

DELETE

• テーブル名：従業員家族
• 実行タイミング：削除の後（行が削除された直後に

  OLD

  値が残る）
• 処理内容：

  INSERT INTO 家族退職履歴 ... VALUES (OLD.*)

  のようにアーカイブ表へ挿入

従業員／部署コード … DELETE CASCADE

• “従業員.部署コード” に対して「参照先：部署（部署コード）」
• 「UPDATE … NO ACTION」「DELETE … CASCADE」「検査契機モード：猶予モード」
  を設定すると宣言されています。

• 参照“先”が存在しない状態で参照“元”を INSERT/UPDATE すると制約違反になります。
• 逆に、参照“元”が残っている状態で参照“先”を DELETE しても制約違反です（今回の挙動モードは「CASCADE」ですが、猶予モードなのでトランザクション終了時まで違反が保留されます）。