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文がありますが、苦手意識を持たずにしっかりと内容を確認してくださいね。

(解説)

それでは解説をしていきますが、SQL文が複雑そうに見えるからといって絶対に適当に選択肢を選ばないようにしてくださいね。今回の問題は文字関数の使い方と、関数がネストされている場合にどのように解釈をすべきか、という点がメインで問われています。

関数のネストについてはとりあえず後回しにして、まずは文字関数の使い方について学習しましょう。文字関数とは、その名の通り文字に対して色々な操作が出来る関数のことを指します。いくつか種類がありますので主なものを以下にまとめておきます。

実際にSQL文を使って動作確認をしておきましょう。このような場合にDUAL表を使うと簡単に確認ができますので是非活用してください。(DUAL表については第9回の内容もご参考にしてください。)

 

個々の文字関数の動作さえ押さえてしまえば、後は関数のネスト部分をやっつけるだけです。

尚、関数のネストにつきましては「第17回 単一行関数を使用した出力のカスタマイズ – ROUND、TRUNCおよびMOD関数を使用した数値の操作 –」でも扱っていますのでそちらもあわせて参考にしてください。

改めて今回の問題の選択肢を使って解説します。

関数のネストというのは、今回の選択肢のように関数が入れ子状態になっていることを指します。この場合、最も深いレベル(最も内側)の関数から実行していき、そこで得られた結果を外側の関数に渡すという動作になります。問題文の選択肢1のSQLを例にして順を追って以下に詳細を記載します。

どうでしょう？関数が複雑に入れ子になっていても内側から1つ1つ解釈していけば実はそれほど難しい内容ではないと感じられたのではないでしょうか。ここまできたら得られた結果を用いて目的のデータが検索条件と比較してヒットするかどうかを確認していけばいいわけです。今回のデータであれば「Stephen Smith」と「patrick smith」がヒットすれば目的達成ということになりますよね。

それでは残りの説明についてはそれぞれの選択肢をみていきながら解説していきましょう。

選択肢1は、先ほど説明したとおり、文字関数のネストの部分で姓が抽出され、それがLOWER関数によりすべて小文字で扱われます。検索条件としてLIKE述語で LIKE LOWER(‘Sm%’) と記載がありますので、小文字のsmから始まり、残り任意の文字列であればヒットするので目的に一致します。正しい選択肢です。

選択肢2の関数のネスト部分である INITCAP(SUBSTR(emp_name, INSTR(emp_name, ‘ ‘) + 1)) は、姓の文字列が抽出されて、それをINITCAPで先頭大文字、残りは小文字にしています。LIKE述語の指定は LIKE ‘Sm%’ としているため Sm から始まり任意の文字列であればヒットしますので目的に合ってます。正しい選択肢です。

残りの選択肢はすべて誤りとなるわけですが、何が間違っているのかしっかり確認しておきましょう。

選択肢3と選択肢4は関数のネストの部分に誤りがあります。それぞれ以下のように記述してありますが、

選択肢3: LOWER(SUBSTR(emp_name, INSTR(emp_name, ‘ ‘)))

 選択肢4: INITCAP(SUBSTR(emp_name, INSTR(emp_name, ‘ ‘)))

INSTR関数の結果のところに着目してください。半角スペースの数値位置を結果として返しますが、その結果に「＋１」をしていないので次のSUBSTR関数の処理で半角スペースを開始位置として文字の抽出が行われます。つまり空白付きの姓の情報が抽出されるため先頭がsm や Sm の値がヒットしなくなり目的と合致しません。誤りの選択肢となります。

選択肢5は関数のネスト部分が LOWER(SUBSTR(emp_name, INSTR(emp_name, ‘ ‘) + 1)) であるので、姓の文字列が抽出され、すべて小文字として扱われます。LIKE述語が LIKE INITCAP(‘sm%’) と指定してあるので検索条件は先頭のみ大文字の Sm から始まる任意の文字列が条件となります。結果、データが一致することがないため誤りです。

選択肢6はIN述語の部分に誤りがありますね。IN述語は任意の文字列を示すワイルドカード％を使用することはできません。%は単に文字として扱われるため IN (‘sm%’, ‘Sm%’) という指定は、文字列「sm%」あるいは「Sm%」という文字列と一致していないとデータはヒットしません。間違いの選択肢になります。

以上の結果から、正解の選択肢は１と２になります。

今回の問題文に記載してあったSQL文の実行結果と、SQL文が実行できるようにサンプル定義を以下に乗せておきます。頭の中だけで理解するのではなく、実機で操作を行って理解を深めてください。

(サンプル定義)

drop table emp_table;
create table emp_table (emp_id number(3), emp_name varchar2(20));

insert into emp_table values(1, ‘Steven King’);
insert into emp_table values(2, ‘Vance Jones’);
insert into emp_table values(3, ‘Stephen Smith’);
insert into emp_table values(4, ‘PETER VARGAS’);
insert into emp_table values(5, ‘patrick smith’);

commit;

今回の講義はこれで終了とします。皆様、おつかれさまでした。

次回も張り切っていきましょう！

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

– ORACLE MASTER Silver SQL 2019 のご紹介

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

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

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

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

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