助け合いフォーラム

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」

上に戻る

NULLIF関数の挙動について

s sekakuma3

投稿日 2024/02/24

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

上記の選択肢で、NULLIF関数の第一引数にsalary列が指定されています。
NULLIF関数で、第一引数にNULLを指定したらエラーになると理解していますが、
もしsalary列（制約なし）にnullが含まれていたらエラーになる可能性はあるのでしょうか？

r rink_rew

2024/02/26 15:57

他の設問ですが26677の参考のNULLIF関数の説明部分に以下の記載がありました。

なお、第1引数にはリテラルのNULL値以外の値を指定しなければなりません。

上記のように第1引数にリテラルのNULL値は指定できませんが、第1引数で指定した列の値などが結果としてNULL値となる場合は問題ありません。
第1引数が結果としてNULL値となる場合は、第2引数がいかなる値であっても、NULL値が返されます。（第2引数がNULL値であれば等しいのでNULL値、第2引数がNULL値以外であれば等しくないので第1引数のNULL値が返される為）

実際にsalary列にnullが含まれるデータで、選択肢のSQLを実行した場合エラーにはなりません。
NULLIF関数の結果としてnullが返され、DECODE関数の条件と一致して、結果、「-」として表示されます。

SQL> select employee_id, employee_name, salary from employees where salary is NULL;

EMPLOYEE_ID EMPLOYEE_NAME	     SALARY
----------- -------------------- ----------
       1017 渡辺和也
       1018 塚本孝
       1019 野口圭子
       
SQL> select employee_id, employee_name, NULLIF(salary, 500000) from employees where salary is NULL;

EMPLOYEE_ID EMPLOYEE_NAME	 NULLIF(SALARY,500000)
----------- -------------------- ---------------------
       1017 渡辺和也
       1018 塚本孝
       1019 野口圭子

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

EMPLOYEE_ID EMPLOYEE_NAME	 SAL
----------- -------------------- -------------------------
       1001 山田二郎             -
       1002 佐藤昭夫             -
       1003 山口洋子             -
       1004 田中浩介             -
       1005 加藤昭彦             -
       1006 佐々木明子           800000
       1007 菊池浩二             800000
       1008 中山大輔             400000
       1009 星野健一             400000
       1010 斎藤京子             400000
       1011 吉田亜希             400000
       1012 阿部伊吹             400000
       1013 米村真司             350000
       1014 伊藤佳奈             300000
       1015 橋本淳               300000
       1016 井上悦子             200000
       1017 渡辺和也             -
       1018 塚本孝               -
       1019 野口圭子             -
       1020 内田雄介             200000
       1021 高田明               200000
       1022 坂本真               200000

22行が選択されました。

s sekakuma3

2024/02/28 09:05

rink_rewさん、また教えていただきありがとうございます。第1引数にリテラルのNULL値は指定できないが、第1引数で指定した列の値などが結果としてNULL値となる場合は問題なく実行できることを理解出来ました。他の設問から探して、丁寧に教えてくれてありがとうございます。

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

助け合いフォーラム