助け合いフォーラム

Oracle DB

Oracle Master Silver SQL 2019(1Z0-071)

問題ID : 26683

EMPLOYEES表の構造を確認して下さい。

次のSQL文のうち正常に実行されるものはどれですか(該当するものをすべて選択してください)。

SELECT employee_id, employee_name, DECODE(salary, NULL, 150000) sal FROM employees;

SELECT employee_id, employee_name, DECODE(salary, salary > 400000, 'High', salary < 200000, 'low', 'middle') sal FROM employees;

SELECT employee_id, employee_name, DECODE(NULLIF(salary, 500000), NULL, '-', salary) sal FROM employees;

SELECT employee_id, employee_name, DECODE(NULLIF(salary, 500000), salary, 250000*1.1, NULL, hiredate) sal FROM employees;

SELECT employee_id, employee_name, DECODE(NULLIF(salary, 500000), NULL, salary, '-') sal FROM employees;

正解

SELECT employee_id, employee_name, DECODE(salary, NULL, 150000) sal FROM employees;

SELECT employee_id, employee_name, DECODE(NULLIF(salary, 500000), NULL, '-', salary) sal FROM employees;

解説

DECODE関数は、第1引数に指定された式の値と、第2引数以降に指定された条件を順に判定し、値が合致した条件に対応する戻り値を返します。

なお、DECODE関数の条件に、WHERE句の条件に指定するような比較演算子を使用した条件を記述することはできません。
また、条件が複数指定された場合の戻り値のデータ型は、第3引数で指定された戻り値のデータ型が採用されます。したがってそれぞれの戻り値は、第3引数の戻り値のデータ型と同じデータ型の値を指定するか、暗黙的なデータ変換で第3引数の戻り値のデータ型と同じデータ型となるような値を指定しなければなりません。

以上より、
・SELECT employee_id, employee_name, DECODE(salary, NULL, 150000) sal FROM employees;
・SELECT employee_id, employee_name, DECODE(NULLIF(salary, 500000), NULL, '-', salary) sal FROM employees;
が正解となります。

正解のSQL文の実行結果は次のようになります。

SQLを表示

SELECT employee_id, employee_name,
DECODE(salary, NULL, 150000) sal
FROM employees;

SQLを表示

SELECT employee_id, employee_name,
DECODE(NULLIF(salary, 500000), NULL, '-', salary) sal
FROM employees;

・SELECT employee_id, employee_name, DECODE(NULLIF(salary, 500000), NULL, '-', salary) sal FROM employees;
上記のSQL文で、DECODE関数に指定された第3引数は文字データ('-')であるのに対し、デフォルトの戻り値として数値データ(salary)が指定されています。
データ型が異なるのにエラーとならずに正常に実行できるのは、数値データが暗黙的データ変換により文字データに変換されるためです。

その他の選択肢については次のとおりです。

・SELECT employee_id, employee_name, DECODE(salary, salary > 400000, 'High', salary < 200000, 'low', 'middle') sal FROM employees;
DECODE関数の条件に比較演算子を使用することはできません。

・SELECT employee_id, employee_name, DECODE(NULLIF(salary, 500000), salary, 250000*1.1, NULL, hiredate) sal FROM employees;
・SELECT employee_id, employee_name, DECODE(NULLIF(salary, 500000), NULL, salary, '-') sal FROM employees;
DECODE関数の戻り値のデータ型は第3引数に指定した戻り値の型が採用されます。異なる型を指定した場合、暗黙的なデータ変換が行われればエラーとなりませんが、暗黙的なデータ変換ができない場合はエラーとなります。
1つめのSQL文では、第3引数には数値データ(250000*1.1)が指定されており、その後、2つ目の戻り値として日付データ(hiredate)が指定されています。日付データは暗黙的データ変換で数値データへ変換できないため、エラーとなります。
また、2つ目のSQL文では、第3引数には数値データ(salary)が指定されており、その後、2つ目の戻り値として文字データ('-')が指定されています。'-'は暗黙的データ変換で数値データへ変換できないため、このSQL文もエラーとなります。

参考

SQL文中で分岐処理を行うには、DECODE関数を使用する方法とCASE式を使用する方法があります。

[DECODE関数]
DECODE関数はOracleデータベース固有の関数で、SQL文の中で分岐処理を行うための関数です。
使用法は以下の通りです。

　DECODE(式, 条件1, 戻り値1
　　　　　　　　　[, 条件2, 戻り値2 …]
　　　　　　　　　[, デフォルトの戻り値])

DECODE関数は、第1引数に指定された式の値と、第2引数以降に指定された条件を順に判定し、値が合致した条件に対応する戻り値を返します。
なお、式の値がどの条件にも合致しない場合は、デフォルトの戻り値が返されますが、デフォルトの戻り値が指定されていない場合はNULL値を返します。

　

<img src="/mondai3/img/jpg/kk48953.jpg">

SQLを表示

SELECT department_id, department_name,
DECODE(department_id, 1, 1013
, 2, 1014
, 3, 1015
, 4, 1016
, manager_id) new_manager
FROM departments;

<img src="/mondai3/img/jpg/kkk48953.jpg">

SQLを表示

SELECT department_id, department_name,
DECODE(department_id, 1, 1013
, 2, 1014
, 3, 1015
, 4, 1016) new_manager
FROM departments;

DECODE関数に複数の条件と戻り値を指定する場合、戻り値のデータ型は最初に指定された戻り値(第3引数に指定された戻り値)のデータ型が採用されます。
ただし、暗黙的なデータ変換が行われる場合は異なるデータ型の戻り値を指定してもエラーとはなりません。

<img src="/mondai3/img/jpg/kkk48954.jpg">

SQLを表示

SELECT department_id, department_name,
DECODE(department_id, 1, manager_id
, 2, 1014
, 3, 'none') new_manager
FROM departments;

<img src="/mondai3/img/jpg/kkkk48954.jpg">

SQLを表示

SELECT department_id, department_name,
DECODE(department_id, 1, manager_id
, 2, 1014
, 3, '1015') new_manager
FROM departments;

[CASE式]
CASE式を使用すると、SQL文の中でIF-THEN-ELSEの分岐処理を行えます。
CASE式には単純CASE式と検索CASE式の2種類があります。

単純CASE式は次のように記述します。

　CASE 式 WHEN 条件1 THEN 戻り値1
　　　　　　　　　　[WHEN 条件2 THEN 戻り値2 ...]
　　　　　　　　　　[ELSE デフォルトの戻り値]
　END

単純CASE式は、式の値と、条件の値を条件1から順に判定し、値が合致した条件に対応する戻り値を返します。
なお、式の値がどの条件にも合致しない場合は、ELSE句に指定されたデフォルトの戻り値が返されますが、ELSE句が指定されていない場合はNULL値を返します。

　

<img src="/mondai3/img/jpg/kkk48955.jpg">

SQLを表示

SELECT department_id, department_name,
CASE department_id WHEN 1 THEN 1013
WHEN 2 THEN 1014
ELSE manager_id END new_manager
FROM departments;

<img src="/mondai3/img/jpg/kkkk48955.jpg">

SQLを表示

SELECT department_id, department_name,
CASE department_id WHEN 1 THEN 1013
WHEN 2 THEN 1014 END new_manager
FROM departments;

次に、検索CASE式は次のように記述します。

　CASE WHEN 条件1 THEN 戻り値1
　　　　　　　　　[WHEN 条件2 THEN 戻り値2 ...]
　　　　　　　　　[ELSE デフォルトの戻り値]
　END

検索CASE式は、条件を条件1から順番に判定し、条件が真の場合に条件に対応する戻り値を返します。
なお、真となる条件が1つも存在しない場合は、ELSE句に指定されたデフォルトの戻り値が返されますが、ELSE句が指定されていない場合はNULL値を返します。

　

<img src="/mondai3/img/jpg/kk48956.jpg">

SQLを表示

SELECT department_id, department_name,
CASE WHEN department_id < 3 THEN 1013
WHEN department_id > 4 THEN 1014
ELSE manager_id END new_manager
FROM departments;

<img src="/mondai3/img/jpg/kkk48956.jpg">

SQLを表示

SELECT department_id, department_name,
CASE WHEN department_id < 3 THEN 1013
WHEN department_id > 4 THEN 1014 END new_manager
FROM departments;

参考URL

Oracle® Database SQL言語リファレンス「DECODE」

上に戻る

データの暗黙的型変換について

E Engineer1993

投稿日 2023/07/07

解説に
①数値→文字への暗黙的な型変換が出来る
②文字→数値への暗黙的な型変換は出来ない

と記載があるのですが、
①も②も型変換の暗黙的型変換は出来ると思います。
私の考えは間違っておりますでしょうか？ご意見願います。

r rink_rew

2023/07/07 17:15

解説に
①数値→文字への暗黙的な型変換が出来る
②文字→数値への暗黙的な型変換は出来ない

解説のどの部分でしょうか？最後に記述されている以下の文章でしょうか。

また、2つ目のSQL文では、第3引数には数値データ(salary)が指定されており、その後、2つ目の戻り値として文字データ('-')が指定されています。'-'は暗黙的データ変換で数値データへ変換できないため、このSQL文もエラーとなります。

もしこの文章のことであれば、「文字→数値への暗黙的な型変換は出来ない」とは書かれておらず、「'-'は暗黙的データ変換で数値データへ変換できない」なので、'-' が数値データへの変換ができないという意味かなと思いました。
※例えば、'abc'のような文字列の場合、数値に変換しようがない。'-'も同様に数値に変換しようがない。'100'のような文字列であれば、Oracleは数値の 100 に暗黙的に変換できる。

Engineer1993 さんが仰っている通り、私も「文字→数値の暗黙的な型変換もできる」という理解ですが、あくまでもその変換が意味を持つ場合という認識です。

参考までに、マニュアルの暗黙的なデータ変換に関する部分に以下の記述がありました。

https://docs.oracle.com/cd/E16338_01/server.112/b56299/sql_elements002.htm#i46862

暗黙的なデータ変換
あるデータ型から別のデータ型への変換が意味を持つ場合、Oracle Databaseは値を自動的に変換します。

的外れな意見となっていましたらごめんなさい。

E Engineer1993

2023/10/25 01:25

ご回答ありがとうございます😭 無事に試験に合格できました。

この投稿に対して返信しませんか？

助け合いフォーラム