助け合いフォーラム

Oracle DB

Oracle Master Silver SQL 2019(1Z0-071)
問題ID : 26875
問題を開く
NEW_PRODUCTS表の構造を確認して下さい。



以下のSQL文の実行結果について正しいものはどれですか。

　SELECT prod_name, list_price, CASE WHEN list_price >= (SELECT AVG(list_price) FROM new_products)
　THEN '高価格'
　ELSE '低価格' END "価格帯"
　FROM new_products;

正解

全製品を平均価格と比べて高いか安いかが表示される

解説

設問のSQL文は、副問合せでNEW_PRODUCTS表の平均価格を取り出し全製品の価格と比較して、平均以上の価格は「高価格」、それ以外は「低価格」と表示する分岐処理です。
検索CASE式でも副問合せが使用できます。

以上より、
・全製品を平均価格と比べて高いか安いかが表示される
が正解となります。

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


SQLを表示
SELECT prod_name, list_price, CASE WHEN list_price >= (SELECT AVG(list_price) FROM new_products)
THEN '高価格'
ELSE '低価格' END "価格帯"
FROM new_products;


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

・GROUP BY句がないためエラーとなる
グループ関数はネストしていなければGROUP BY句は必要ありません。AVG(list_price)はGROUP BY句なしで実行できます。

・単一行演算子>=でエラーとなる
単一行演算子>=が比較している副問合せは1件のデータを返す単一行副問合せのため、エラーにはなりません。

・CASE式で副問合せは使えないためエラーとなる
CASE式でも副問合せは使用できます。

・DECODE関数に置き換えられる
検索CASE式では、WHEN句で比較条件やINやLIKEなどの演算子が使用できますが、DECODE関数では使用できません。設問のような条件はDECODE関数では記述できません。

参考

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

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

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

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

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



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


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


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



　
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値を返します。

　
　
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;



　
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値を返します。

　
　
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;



　
SQLを表示
SELECT department_id, department_name,
CASE WHEN department_id < 3 THEN 1013
WHEN department_id > 4 THEN 1014 END new_manager
FROM departments;
上に戻る

試験範囲かどうか

R RyoKuro
公開日 2024/09/25

こちらのDECODE関数はオラクルマスターSQLシルバーの試験範囲ですか？
教科書には一切載っていない内容なのですが。

r rink_rew
2024/09/26 21:59

以前の試験体系ですとBronzeの科目として「SQL基礎」があって、その頃はDECODE関数も主要なテキストでは試験範囲として紹介されていたと思います。
確かに最新の黒本を見てみるとDECODE単体では取り上げられてないようですが、現在の試験体系では、Silver SQLがSQL関連の最も低いレベルに該当すると思いますので、無視してしまうとちょっと怖いかな、という気はします。

コメント

R RyoKuro

2024/09/30 08:19

勉強しておいて損はないということですね。 返信ありがとうございます！

この返信に対して
コメントを記入できます

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

助け合いフォーラム