SHOEISHA iD

※旧SEメンバーシップ会員の方は、同じ登録情報(メールアドレス&パスワード)でログインいただけます

HRzine Day(エイチアールジン・デイ)は、人が活き会社が成長する人事のWebマガジン「HRzine」が主催するイベントです。毎回、人事の重要課題を1つテーマに設定し、識者やエキスパードが持つ知見・経験を、参加者のみなさんと共有しています。

直近開催のイベントはこちら!

HRzine Day 2024 Summer

2024年7月25日(木)10:30~17:30

主要製品スペック一覧

人事業務の効率・確度・精度を高めるために欠かせないHRテクノロジー。その主な製品の機能を分野ごとに比較できる資料群です。製品検討の参考資料としてご活用ください。

eラーニング・LMS<br>主要製品スペック一覧 2024

eラーニング・LMS
主要製品スペック一覧 2024

その他のスペック一覧

人事労務管理システム<br>主要製品スペック一覧 2023

人事労務管理システム
主要製品スペック一覧 2023

タレントマネジメントシステム<br>主要製品スペック一覧 2023

タレントマネジメントシステム
主要製品スペック一覧 2023

落とし穴はこれで回避! Oracle DB 12c SQL基礎 弱点克服スクール | 第7回

結合の使用による複数の表のデータの出力

  • Facebook
  • X
  • Pocket
  • note
  • hatena

自己結合

結合は異なるレコードを1つのレコードとして表示する機能です。 異なるレコードとは、異なる表のレコードばかりではなく、同じ表の異なるレコードの場合だってあります。 例えば、社員表には上司として働いている人もいれば、部下として働いている人も登録されています。つまり、部下のレコードと上司のレコードを1つのレコードにして表示することもできます。

1つの表(同一表)の異なるレコードを結合する方法を自己結合といいます。 自己結合の説明は次の例題を解きながら行いましょう。

問3

次の表を確認してください。

SQL> desc employees
 名前                          NULL?    型
 ----------------------------- -------- --------------------
 EMPLOYEE_ID                   NOT NULL NUMBER(6)
 FIRST_NAME                             VARCHAR2(20)
 LAST_NAME                     NOT NULL VARCHAR2(25)
 EMAIL                         NOT NULL VARCHAR2(25)
 PHONE_NUMBER                           VARCHAR2(20)
 HIRE_DATE                     NOT NULL DATE
 JOB_ID                        NOT NULL VARCHAR2(10)
 SALARY                                 NUMBER(8,2)
 COMMISSION_PCT                         NUMBER(2,2)
 MANAGER_ID                             NUMBER(6)
 DEPARTMENT_ID                          NUMBER(4)  

EMPLOYEE_IDとMANAGER_IDの間には、親子関係があります。 ただし、入社直後でまだ上司が決まっていない新入社員や上司のいない最高経営責任者も含まれている場合があります。

あなたは、従業員の名前と入社日と上司の名前を出力するレポートを作成しなければいけません。 上司が決まっていない今年入社の社員の場合は、上司の名前のかわりにFreshman、最高経営責任者の場合はCEOと表示する必要があります。 目的の結果を得るには、どのSQLを使用しますか。 最適なものを選択しなさい。

  • a. SELECT e.last_name,e.hire_date,
    NVL(m.last_name,DECODE(TO_CHAR(e.hire_date,'YYYY'),TO_CHAR(SYSDATE,
    'YYYY'),'Freshman','CEO'))
    FROM employees e
    LEFT OUTER JOIN employees m
    ON e.manager_id = m.employee_id
    WHERE e.department_id = 90;
  • b. SELECT e.last_name,e.hire_date,
    NVL(m.last_name,DECODE(TO_CHAR(e.hire_date,'YYYY'),TO_CHAR(SYSDATE,
    'YYYY'),'Freshman','CEO'))
    FROM employees e
    LEFT OUTER JOIN employees m
    ON m.manager_id = e.employee_id
    WHERE e.department_id = 90;
  • c. SELECT e.last_name,e.hire_date,
    NVL(m.last_name,DECODE(TO_CHAR(e.hire_date,'YYYY'),TO_CHAR(SYSDATE,
    'YYYY'),'Freshman','CEO'))
    FROM employees e
    RIGHT OUTER JOIN employees m
    ON m.manager_id = e.employee_id
    WHERE e.department_id = 90;
  • d. SELECT e.last_name,e.hire_date,
    NVL(m.last_name,DECODE(TO_CHAR(e.hire_date,'YYYY'),TO_CHAR(SYSDATE,
    'YYYY'),'Freshman','CEO'))
    FROM employees e
    RIGHT OUTER JOIN employees m
    ON e.manager_id = m.employee_id
    WHERE e.department_id = 90;

自己結合のポイントは、表別名を使って、部下のレコードを格納した表、上司のレコードを格納した表とみなすことです。 表別名を付けるだけなら簡単ですが、結合列を修飾する際に、みなした表別名を間違わないようにすることが重要です。 この例題の場合、部下表に「e」という別名を付けています。 どこで、それが分かるかというと、すべての選択肢において、入社年が今年かどうか判断する際に「DECODE(TO_CHAR(e.hire_date,'YYYY')……」としているところです。 FROM句に記述された別名「e」表が部下表ならば、部下レコードにおけるmanager_id(上司のID)が上司自身のID、つまり上司表としてみなされた「m」表のemployee_idと一致するかどうかが結合条件になります。

したがって、ON句にON e.manager_id = m.employee_idと記述されていない、選択肢bとcは不正解です。

残る選択肢aとdの違いは、外部結合がLEFTRIGHTかです。

「上司の名前の代わりにFreshman、最高経営責任者の場合はCEOと表示する」ということは、上司のいない部下(最高責任者を部下というのはおかしいですが)も表示したいという要件であると判断できるので、「e」という別名を付けたFROM句に記述した表のレコードを全部出力するLEFT OUTER JOINが正解です(選択肢aは正解、dは不正解)。

SQL> --選択肢a
SQL> SELECT e.last_name,e.hire_date,
  2  NVL(m.last_name,DECODE(TO_CHAR(e.hire_date,'YYYY'),TO_CHAR(SYSDATE,
     'YYYY'),'Freshman','CEO'))
  3  FROM employees e
  4  LEFT OUTER JOIN employees m
  5  ON e.manager_id = m.employee_id
  6  WHERE e.department_id = 90;

LAST_NAME       HIRE_DAT NVL(M.LAST_NAME,DECODE(TO
--------------- -------- -------------------------
De Haan         01-01-13 King
Kochhar         05-09-21 King
Allen           16-04-01 Freshman
King            03-06-17 CEO

SQL> --選択肢d
SQL> SELECT e.last_name,e.hire_date,
  2  NVL(m.last_name,DECODE(TO_CHAR(e.hire_date,'YYYY'),TO_CHAR(SYSDATE,
     'YYYY'),'Freshman','CEO'))
  3  FROM employees e
  4  RIGHT OUTER JOIN employees m
  5  ON e.manager_id = m.employee_id
  6  WHERE e.department_id = 90;

LAST_NAME       HIRE_DAT NVL(M.LAST_NAME,DECODE(TO
--------------- -------- -------------------------
De Haan         01-01-13 King
Kochhar         05-09-21 King

12c SQL試験の嫌なところは、設問や選択肢のSQLが長くて複雑に感じるところです。特に結合は、2つ以上の表が出てくるだけでなく、その表名や列名が長くて、読むだけでうんざりします。 自己結合はEMPLOYEES表を使用した設問が出題されますが、その他はSH(Sales History)やOE(Order Entry)スキーマの表が使用されることが多いです。無償版のOracleを使って勉強している人は、長い表名や列名に慣れておくといいですよ。

この記事は参考になりましたか?

  • Facebook
  • X
  • Pocket
  • note
  • hatena
めざせオラクルマスターホルダー! Oracle DB 12c SQL基礎 弱点克服スクール連載記事一覧

もっと読む

この記事の著者

林 優子(ハヤシ ユウコ)

日本オラクル株式会社の教育ビジネスのスタートアップを全面的に支援し、バージョン5の頃からOracleに携わるベテラン講師として知る人も多い。Oracle認定講師を表彰するExcellent Instructorを連続受賞。1ランク上のITスペシャリスト育成を目標に、データベース分野にとどまらず「プレゼンテーション」、「ロジカルシンキング」などのトレーニングも手がけている。著書に『オラクルマスター教科書』シリーズ(翔泳社)、『プロとしてのデータモデリング入門』(SBクリエイティブ)など。その他、雑誌執筆、著書・メディア出演も多数。

※プロフィールは、執筆時点、または直近の記事の寄稿時点での内容です

この記事をシェア

  • Facebook
  • X
  • Pocket
  • note
  • hatena
HRzine
https://hrzine.jp/article/detail/188 2016/09/13 14:00

Special Contents

AD

Job Board

AD

おすすめ

アクセスランキング

アクセスランキング

イベント

HRzine Day(エイチアールジン・デイ)は、人が活き会社が成長する人事のWebマガジン「HRzine」が主催するイベントです。毎回、人事の重要課題を1つテーマに設定し、識者やエキスパードが持つ知見・経験を、参加者のみなさんと共有しています。

2024年7月25日(木)10:30~17:30

イベントカレンダーを見る

新規会員登録無料のご案内

  • ・全ての過去記事が閲覧できます
  • ・会員限定メルマガを受信できます

メールバックナンバー

アクセスランキング

アクセスランキング