Oracle University 講師が ORACLE MASTER 取得を目指している方に向けて試験トピックを解説するブログ連載講座。「ORACLE MASTER Silver SQL 2019」資格取得に向けた試験トピックについて解説します。

————————————————-

今回はORACLE MASTER Silver SQLの試験トピック「副問合せを使用した問合せの解決 – 単一行の副問合せの使用 -」に関連する問題をご紹介いたします。ORACLE MASTER Silver SQL(Exam Number: 1Z0-071-JPN)の詳細につきましては以下URLをご参考ください。

Oracle Database SQL Exam Number: 1Z0-071

URL: https://education.oracle.com/ja/oracle-database-sql/pexam_1Z0-071

今回は副問合せに関する問題を扱っていきますが、副問合せも色々と種類がありますので今回から連続で以下のように取り扱っていきたいと思います。

  • 第４回 単一行の副問合せを使用した問合せ (今回の講義内容)
  • 第５回 複数行の副問合せを使用した問合せ
  • 第６回 相関副問合せを使用した行の問合せ

さっそく問題を見ていきましょう。

 

(解説)

では解説していきます。先ずは「そもそも副問合せとは？」を確認していきましょう。副問合せはSQL文の中にさらにSQL文が組み込まれている入れ子のような構文になっています。どのような場合に使うのか例題を確認しながら説明しましょう。以下のような情報を参照したいとします。

「従業員の中で一番高い給与をもらっている人の名前と職種は？」

問題文にもあった従業員表(EMPLOYEES)を参考に、条件を満たすためのSQL文を作ってみたいと思います。

名前はLAST_NAME列、職種はJOB_ID列で表示するとして、あとはWHERE句で「従業員の中で一番高い給与」情報で絞り込めばいけそうですね。

？の部分は「従業員の中で一番高い給与」の情報を入れる必要があるわけですが、こちらは別途確認しないと分かりません。それを確認するSQL文も作ってみます。

結果が27000と出ましたので、最初の(A)のSQL文の???の部分に27000の値を入れれば条件を満たすことができます。しかしこの方法だと2ステップの実行が必要となりますのでこのような場合に副問合せを使うとSQL文を1つにまとめることが可能になります。

いかがでしょうか。副問合せのイメージが掴めたのではないかと思います。

それでは問題の選択肢を見ていきましょう。

選択肢１は「各部門内の最高給与」を出すということで部門番号ごとにグループ化して処理する必要があります。そのためGROUP BY句を使用します。実際のSQL文を書いてみましょう。

選択肢１：SELECT department_id, MAX(salary) FROM employees GROUP BY department_id;

副問合せを使用する必要はありませんので誤りになります。

選択肢２は給与をWHERE句で範囲指定すれば条件を満たしそうですね。こちらもSQL文を書いてみます。

選択肢２：SELECT first_name FROM employees WHERE salary >=3000 AND salary <= 8000;

(SELECT first_name FROM employees WHERE salary BETWEEN 3000 AND 8000 でも可)

こちらも副問合せは必要ありません。誤りです。

選択肢３は、条件として「従業員全の平均給与」の情報が必要になります。この平均給与の情報を副問合せを使って取得し、その結果を主問合せ側に渡すことで必要な情報を絞り込めるかと思います。

選択肢３：SELECT first_name, department_id FROM employees WHERE salary >

(SELECT AVG(salary) FROM employees);

副問合せが必要となるので正しい選択肢になります。

選択肢４を見ていきましょう。入社した従業員情報を表示するのですが絞り込み条件としてJohnさんの入社情報を基準した内容となっています。つまりJohnさんの入社情報を先に知っておく必要があるわけですがこれを副問合せにすればいいわけですね。以下、SQL文です。

選択肢４：SELECT first_name, department_id FROM employees WHERE hire_date >

(SELECT hire_date FROM employees WHERE employee_id=145 AND first_name= ‘John’);

選択肢５は従業員の数をカウントしたいわけですが、条件として部門番号と職種を限定しています。COUNT関数とWHERE句で条件を指定すればできそうです。

選択肢５：SELECT COUNT(*) FROM employees WHERE department_id=80 AND job_id=’SA_MAN’;

副問合せは必要ありませんので誤りです。

正解は ３、４になります。

今回の問題をSQL文の実行で確認が出来るように定義のサンプルと実行した結果も参考に乗せておきます。皆さんも実際に操作してみましょう。

— sample テーブル作成 —
drop table employees;
create table employees(
        employee_id        number(6)        primary key,
        first_name          varchar2(20),
        last_name          varchar2(25)      not null,
        hire_date           date                   not null,
        job_id                varchar2(10)      not null,
        salary                number(8,2),
        manager_id        number(6),
        department_id    number(4)
);

— sample データ挿入 —
insert into employees values(100, ‘Steven’, ‘King’, ’03-06-17′, ‘AD_PRES’, 27000, NULL, 90);
insert into employees values(101, ‘Neena’, ‘Kochhar’, ’05-09-21′, ‘AD_PRES’, 17000, 100, 90);
insert into employees values(120, ‘Matthew’, ‘Weiss’, ’04-07-18′, ‘ST_MAN’, 8000, 100, 50);
insert into employees values(126, ‘Irene’, ‘Mikkilineni’, ’06-09-28′, ‘ST_CLERK’, 2700, 120, 50);
insert into employees values(127, ‘James’, ‘Landry’, ’07-01-14′, ‘ST_CLERK’, 2400, 120, 50);
insert into employees values(128, ‘Steven’, ‘Markle’, ’08-03-08′, ‘ST_CLERK’, 2200, 120, 50);
insert into employees values(145, ‘John’, ‘Russell’, ’04-10-01′, ‘SA_MAN’, 14000, 100, 80);
insert into employees values(146, ‘Karen’, ‘Partners’, ’05-01-05′, ‘SA_MAN’, 13500, 100, 80);
insert into employees values(154, ‘Nanette’, ‘Cambrault’, ’06-12-09′, ‘SA_REP’, 7500, 145, 80);
insert into employees values(155, ‘Oliver’, ‘Tuvault’, ’07-11-23′, ‘SA_REP’, 7000, 145, 80);
insert into employees values(156, ‘Janette’, ‘King’, ’04-01-30′, ‘SA_REP’, 10000, 146, 80);
insert into employees values(205, ‘Shelley’, ‘Higgins’, ’02-06-07′, ‘AC_MGR’, 12000, 101, 110);
commit;

※日付書式はRR-MM-DDとしています。

正しい選択肢は選べたでしょうか。さて、今回の講義内容を終了する前に、次の講義につながる重要な内容をお話しますのでまだ気を抜かずに最後までしっかり集中してください。ここまで副問合せに関する問題を見ていきましたが今回実行した副問合せは「単一行副問合せ」になります。単一行副問合せは、副問合せ側の結果を必ず1行で戻す必要があります。どうゆうことか？以下のSQL文をご覧ください。

一見、問題なさそうに見えるかもですが、副問合せ側の「SELECT salary FROM employees」の結果が複数件ヒットした場合どうなるでしょうか。WHERE句で指定している salary 列と =(イコール)条件としているため、複数件ヒットした場合、どの値と比較すればいいのか分からなくなってしまいます。

実際にこのようなSQL文を実行するとエラーになります。先ほど紹介したサンプルのテーブルに対して実行した結果を載せておきましょう。

単一行副問合せは、副問合せで得られた結果を用いて主問合せ側のWHERE句で比較する際、必ず1行を戻す必要があることが理解できたのではないでしょうか。尚、比較には単一行比較演算子として「=、>、>=、<、<=、<>」などを使用することも合わせて覚えておきましょう。

「複数行を戻す場合は副問合せは使えないのか？」と考えた方もいると思いますが、副問合せには複数行を扱うことができる「複数行副問合せ」があります。こちらは次回の講義で扱っていきたいと思いますので今回の講義はこれまでとします。

それでは皆様お疲れ様でした！

————————————————-

– ORACLE MASTER Silver SQL 2019 のご紹介

– ORACLE MASTER Silver SQL 2019 試験記事トピック一覧

————————————————-

その他の ORACLE MASTER 試験トピック解説講座シリーズ：

【Oracle University講師によるORACLE MASTER Gold DBA 2019 試験トピック解説講座】トピック一覧

————————————————-