助け合いフォーラム

Oracle DB

Oracle Master Silver SQL 2019(1Z0-071)
問題ID : 26876
問題を開く
次の問合せの実行結果について正しいものはどれですか。

　SELECT department_id, AVG(salary)
　FROM employees
　GROUP BY department_id
　HAVING AVG(salary) <ALL (SELECT MAX(salary) / 2 FROM employees GROUP BY department_id);

正解

各部署の最高給与の半分の最小値より、部署の平均給与が少ない部署を検索する

解説

設問のSQL文の副問合せは、部署ごとの最高給与の半分の値を取り出す複数行副問合せです。
<ALLは、左辺の値が右辺のリスト内の最小値よりも小さい場合にTRUEを返す複数行演算子です。
単一行副問合せ、複数行副問合せで使用する演算子はそれぞれ次のとおりです。



以上より、
・各部署の最高給与の半分の最小値より、部署の平均給与が少ない部署を検索する
が正解となります。

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

・副問合せが複数行データを返すため、エラーとなる
<ALLは複数行副問合せの条件の判定が行える複数行演算子のため、エラーになりません。

・各部署の最高給与の半分の最大値より、部署の平均給与が少ない部署を検索する
「副問合せの結果のうち、最大値よりも小さい」という判定は、<ANYで行います。

・比較演算子を<ANYに置き換えられる
<ANYは「副問合せの結果のうち、最大値よりも小さい」という意味なので、<ALLと同義ではありません。

参考

Oracle DatabaseではSQL文の中に別のSQL文を入れ子にして実行することができ、入れ子の内側の問合せのことを副問合せといいます(副問合せに対し、外側の問合せを主問合せといいます)。
副問合せは次のように記述します。

　SELECT 列名[, 列名 ...]
　FROM 表名
　WHERE 列名 比較演算子 (SELECT 列名 FROM 表名 [WHERE 条件]);

副問合せの部分は()括弧で囲みます。(INSERT文で副問合せを使用してデータの追加を行う場合は、()は必須ではありません。分野「DML文」のINSERT文の参考をご参照ください。)
上記では比較演算子の右辺に副問合せを記述していますが、副問合せを左辺に定義してもかまいません。

通常の副問合せを使用したSQL文ではまず副問合せが実行され、副問合せの実行結果をもとに主問合せが実行されます。


　
SQLを表示
SELECT employee_name, salary
FROM employees
WHERE salary > (SELECT AVG(NVL(salary, 0)) FROM employees);


副問合せからNULL値が戻された場合、主問合せの実行結果は0件となります(エラーとはなりません)。
次のような場合に、副問合せは主問合せにNULL値を返します。

[副問合せの結果、データが1件も取り出されない場合]

　
SQLを表示
SELECT * FROM employees WHERE employee_id = 1;

SELECT employee_name, hiredate, salary
FROM employees
WHERE salary >
　(SELECT salary FROM employees WHERE employee_id = 1);


[副問合せの結果がNULL値である場合]

　
SQLを表示
SELECT employee_id FROM employees
WHERE salary IS NULL;

SELECT employee_name, hiredate, salary
FROM employees
WHERE salary >
　(SELECT salary FROM employees
　WHERE employee_id = 1017);


副問合せには、次のようにいろいろな使用方法があります。
・SELECT文のSELECT句、FROM句、WHERE句、HAVING句、ORDER BY句や、INSERT文、UPDATE文等のDML文で使用できる
・主問合せと副問合せで異なる表にアクセスできる
・1つの主問合せに対し、複数の副問合せを指定できる
・副問合せをネストできる(WHERE句に指定した副問合せでは255レベルのネストが可能)
・副問合せの中でGROUP BY句やHAVIMG句、ORDER BY句を使用できる

なお、副問合せは主問合せに戻すデータの件数により、単一行副問合せと複数行副問合せに分類されます。


※複数行副問合せは結果として1件のデータを戻す場合もあります。

副問合せがWHERE句などに指定する条件の一部として使用される場合、単一行副問合せでは単一行演算子を、複数行副問合せでは複数行演算子を使用して条件の判定を行います。



[単一行副問合せ]

　
SQLを表示
SELECT AVG(salary) FROM employees;

SELECT employee_name, hiredate, salary
FROM employees
WHERE salary >
　(SELECT AVG(salary) FROM employees);


[複数行副問合せ]

SQLを表示
SELECT MIN(salary) FROM employees
GROUP BY department_id;

SELECT employee_name, hiredate, salary
FROM employees
WHERE salary =ANY
　(SELECT MIN(salary) FROM employees
　GROUP BY department_id);


なお、単一行副問合せの場合、比較演算子に複数行演算子を使用してもエラーにならず正常に実行されます。しかし、複数行副問合せに単一行演算子を使用するとエラーとなります。


　
SQLを表示
SELECT AVG(salary) FROM employees;

SELECT employee_name, hiredate, salary
FROM employees
WHERE salary >ANY
　(SELECT AVG(salary) FROM employees);



　
SQLを表示
SELECT AVG(salary) FROM employees
GROUP BY department_id;

SELECT employee_name, hiredate, salary
FROM employees
WHERE salary >
　(SELECT AVG(salary) FROM employees
　GROUP BY department_id);


また、副問合せでは複数の列を返し、その値の組合せで比較する事もできます。複数の列を返す副問合せを、複数列副問合せといいます。
複数列副問合せは、単一行副問合せ、複数行副問合せのどちらとも組合せて使用できます。


　
SQLを表示
SELECT employee_id, employee_name
FROM employees
WHERE (department_id, employee_id) IN
　(SELECT department_id, manager_id
　FROM departments);
上に戻る

最小値？

J Jaune
投稿日 2024/06/27

こんにちは
"各部署の最高給与の半分の最小値より、部署の平均給与が少ない部署を検索する"というのが答えみたいなのですが、最小値というのはどこで判定するのでしょうか？

SELECT department_id, AVG(salary)
　FROM employees
　GROUP BY department_id
　HAVING AVG(salary) <ALL (SELECT MAX(salary) / 2 FROM employees GROUP BY department_id);

このクエリだと最高給与の半分よりも平均給与が少ないように見えてしまうのですが...

無知な僕に教えていただけないでしょうか？

よろしくお願いいたします。

r rink_rew
2024/06/27 14:09

この問題の副問合せ(SELECT MAX(salary) / 2 FROM employees GROUP BY department_id)は、複数行を返す可能性があります。
「ALL」は「リスト内の全ての値が条件を満たす場合TRUE」です。この問題では「<ALL」が使用されているので、「左辺の値（部署の平均給与）が、右辺の値（全ての部署の最高給与の半分）より少ないもの」が結果として返されます。

「全ての値より少ない」は「最小値より少ない」に言い換えられると思います。

コメント

J Jaune

2024/06/27 17:22

返信していただきありがとうございます！ そういう解釈をすればよかったんですね、勉強になりました。

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

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

助け合いフォーラム