組織変更
配点 : 30点難易度 : 3
制限実行時間 : 1000ms制限メモリ使用量 : 128 MB
SQLiteの注意点

TOPSIC SQLでは、SQLの実行環境にSQLiteを使用しています。SQLite以外のRDBMSの文法を使用することはできません。
問題を解く際に注意すべきSQLiteの制約について、以下に記載してありますのでご確認下さい。

  1. NUMERIC、INTEGER、REALは、自身のクラスへの変換が推奨されていますが、変換できなかった場合でも、そのまま格納されます。
  2. 新しくデータを追加した場合、既に格納されているデータの中で ROWID の値が最大のものを探し、それに1を加えた値が新しく追加されるデータの ROWID の値として保存されます。
  3. 外部結合は、左外部結合を行う LEFT OUTER JOIN 句のみ使用できます。
    ※2022年12月以降、LEFT / RIGHT / FULL OUTER JOIN が使用可能となりました。
  4. 指定した日付と時刻のタイムゾーンはUTCとして扱われます。
  5. 日付関数や文字列関数については、SQLiteで固有の関数があります。
  6. テーブル項目の論理名が「◯◯日」となっている場合は、日付項目の時分秒は設定されていません。論理名が「◯◯日時」となっている場合は、日付項目の時分秒まで設定されています。
    • 「受注日」の場合は「2021-01-01」
    • 「受注日時」の場合は「2021-01-01 15:15:15」

その他のSQLiteの制約については、SQLiteの公式ドキュメントをご確認ください。
また、ER図の読み方やSQLiteの関数および構文などについては、「受験ルール・用語」をご確認ください。

問題

組織変更により部門テーブル(DEPARTMENT)の部門コード(DEPT_CODE)='A100' と'A200'が、新設される部門コード='B300'に統合されることになった。 組織変更の対応として、下記の処理を行いなさい。 また、部門テーブルは組織変更の履歴管理のために開始日と終了日を持っており、変更があった部門の終了日には変更された日付が設定されている。

セミコロン「;」でSQL文を区切ることで、複数のクエリを実行できます。また、最後にSELECT文を合わせて記載することで、データの更新結果を確認できます。

処理1 :新設された部門('B300')を'A100'のデータを利用して作成する。

  • DEPT_CODE = 'B300'
  • START_DATE = '2023-04-01'
  • END_DATE = NULL
  • DEPT_NAME = '新設部門'
  • LAYER = 'A100'のLAYER
  • UP_DEPT_CODE = 'A100'のUP_DEPT_CODE
  • VALID_FLG = '1'
  • UPDATE_USER_NAME = 'A100'のUPDATE_USER_NAME
  • USER_UPDATE_DATETIME = '2023-04-01 00:00:00'

処理2 :統合される部門('A100','A200')を無効に変更する。

  • END_DATE = '2023-03-31'
  • VALID_FLG = '0'
  • UPDATE_USER_NAME = 'ORG_CHG'
  • USER_UPDATE_DATETIME = '2023-03-31 23:59:59'

処理3 : 社員テーブル(EMP)の部門コード(DEPT_CODE)が統合される部門コードになっているデータの部門コードを、新設される部門コードに変更する。ただし、有効なデータ(VALID_FLG = '1')のみ対象とする。

  • DEPT_CODE ='B300'
  • START_DATE = '2023-04-01'
  • UPDATE_USER_NAME = 'ORG_CHG'
  • USER_UPDATE_DATETIME = '2023-04-01 00:00:00'

ER図 (半角:物理名、全角:論理名)


テーブル定義

テーブル名: DEPARTMENT

主キー 列名 データ型 必須 デフォルト値
✔︎ DEPT_CODE VARCHAR ✔︎ NULL
✔︎ START_DATE VARCHAR ✔︎ NULL
END_DATE VARCHAR NULL
DEPT_NAME VARCHAR NULL
LAYER INTEGER ✔︎ 0
UP_DEPT_CODE VARCHAR ✔︎ NULL
VALID_FLG VARCHAR ✔︎ '1'
UPDATE_USER_NAME VARCHAR NULL
USER_UPDATE_DATETIME VARCHAR CURRENT_TIMESTAMP

テーブル名: EMP

主キー 列名 データ型 必須 デフォルト値
✔︎ EMP_CODE VARCHAR ✔︎ NULL
EMP_LAST_NAME VARCHAR ✔︎ NULL
EMP_FIRST_NAME VARCHAR ✔︎ NULL
EMP_KANA_NAME VARCHAR NULL
EMP_ENG_NAME VARCHAR NULL
EMAIL_ACCOUNT VARCHAR NULL
DEPT_CODE VARCHAR ✔︎ NULL
START_DATE VARCHAR ✔︎ NULL
VALID_FLG VARCHAR ✔︎ '1'
UPDATE_USER_NAME VARCHAR NULL
USER_UPDATE_DATETIME VARCHAR CURRENT_TIMESTAMP

サンプルデータ

テーブル名: DEPARTMENT

DEPT_CODE START_DATE END_DATE DEPT_NAME LAYER UP_DEPT_CODE VALID_FLG UPDATE_USER_NAME USER_UPDATE_DATETIME
0000 2010-04-01 NULL 全社 0 0000 1 SYSTEM 2023-03-23 05:47:47
1000 2010-04-01 NULL 営業部 1 0000 1 SYSTEM 2023-03-23 05:47:47
2000 2010-04-01 NULL 製造部 1 0000 1 SYSTEM 2023-03-23 05:47:47
3000 2010-04-01 NULL 管理部 1 0000 1 SYSTEM 2023-03-23 05:47:47
4000 2010-04-01 NULL 企画室 1 0000 1 SYSTEM 2023-03-23 05:47:47
A100 2010-04-01 NULL 営業１課 2 1000 1 SYSTEM 2023-03-31 23:59:59
A200 2010-04-01 NULL 営業２課 2 1000 1 SYSTEM 2023-03-31 23:59:59
K100 2010-04-01 NULL 人事課 2 3000 1 SYSTEM 2023-03-23 05:47:47
K200 2010-04-01 NULL 経理課 2 3000 1 SYSTEM 2023-03-23 05:47:47
S100 2010-04-01 NULL 製造１課 2 2000 1 SYSTEM 2023-03-23 05:47:47
S200 2010-04-01 NULL 製造２課 2 2000 1 SYSTEM 2023-03-23 05:47:47

テーブル名: EMP

EMP_CODE EMP_LAST_NAME EMP_FIRST_NAME EMP_KANA_NAME EMP_ENG_NAME EMAIL_ACCOUNT DEPT_CODE START_DATE VALID_FLG UPDATE_USER_NAME USER_UPDATE_DATETIME
10000 山田 秀行 ヤマダヒデユキ Hideyuki Yamada 10000@test.jp 1000 2010-04-01 1 SYSTEM 2020-02-22 22:22:22
10001 佐々木 さくや ササキサクヤ Sakuya Sasaki 10001@test.jp 0000 2010-04-01 1 SYSTEM 2020-02-22 22:22:22
10002 山 田 秀行 ヤマダヒデユキ Hideyuki Yamada 10002@test.jp 2000 2010-04-01 1 SYSTEM 2020-02-22 22:22:22
10004 千島 大志 チシマタイシ Taishi Chishima 10004@test.jp A100 2010-04-01 1 SYSTEM 2020-02-22 22:22:22
10005 山谷 健悟 ヤマヤケンゴ Kengo Yamaya 10005@test.jp A200 2010-04-01 1 SYSTEM 2020-02-22 22:22:22
10006 小山 新吾 コヤマシンゴ Singo Koyama 10006@test.jp S200 2010-04-01 1 SYSTEM 2020-02-22 22:22:22
10007 山田 秀行 ヤマダヒデユキ Hideyuki Yamada 10007@test.jp A100 2010-04-01 0 SYSTEM 2020-02-22 22:22:22
10008 山田 秀行 ヤマダヒデユキ Hideyuki Yamada 10008@test.jp A200 2010-04-01 0 SYSTEM 2020-02-22 22:22:22
10009 小 山 新吾 コヤマシンゴ Singo Koyama 10009@test.jp S100 2010-04-01 1 SYSTEM 2020-02-22 22:22:22
10010 西倉 一郎 ニシクライチロウ Ichiro Nishikura 10010@test.jp S100 2010-04-01 1 SYSTEM 2020-02-22 22:22:22
10011 山中 善明 ヤマナカヨシアキ Yoshiaki Yamanaka 10011@test.jp S200 2010-04-01 1 SYSTEM 2020-02-22 22:22:22
10012 古原 啓志 フルハラタカシ Takashi Furuhara 10012@test.jp A100 2010-04-01 1 SYSTEM 2020-02-22 22:22:22
10013 薬師寺 なつき ヤクシジナツキ Natsuki Yakushiji 10013@test.jp S100 2010-04-01 1 SYSTEM 2020-02-22 22:22:22
10015 山内 夏季 ヤマウチナツキ YAMAUCHI NATSUKI 10015@test.jp S100 2010-04-01 1 SYSTEM 2020-02-22 22:22:22
10016 下島 胡桃 シモジマコモモ shimoji makomomo 10016@test.jp A100 2010-04-01 1 SYSTEM 2020-02-22 22:22:22
10017 山岡 聡子 ヤマオカソウコ Souko Yamaoka 10017@test.jp A100 2010-04-01 1 SYSTEM 2020-02-22 22:22:22
10018 山村 由美 ヤマムラユミ Yumi Yamamura 10018@test.jp A200 2010-04-01 1 SYSTEM 2020-02-22 22:22:22
10019 本荘 結依 ホンソウユイ Yui Honsou 10019@test.jp K100 2010-04-01 1 SYSTEM 2020-02-22 22:22:22
10020 大瀧 英俊 オオタキヒデトシ Hidetoshi Otaki 10020@test.jp A200 2010-04-01 1 SYSTEM 2020-02-22 22:22:22
10021 山根 俊二 ヤマネシュンジ Shunji Yamane 10021@test.jp A200 2010-04-01 1 SYSTEM 2020-02-22 22:22:22
10023 山田 慧 ヤマダケイ Kei YAMADA 10023@test.jp K100 2010-04-01 1 SYSTEM 2020-02-22 22:22:22
10024 山田 正吾 ヤマダショウゴ Shogo Yamada 10024@test.jp K100 2010-04-01 1 SYSTEM 2020-02-22 22:22:22
10026 山田 真宏 ヤマダマヒロ MahiroYamada 10026@test.jp S200 2010-04-01 0 SYSTEM 2020-02-22 22:22:22
10027 千島 秀行 チシマヒデユキ Hideyuki Chishima 10027@test.jp S100 2010-04-01 1 SYSTEM 2020-02-22 22:22:22
Sorry, Japanese Only.