助け合いフォーラム

Oracle DB

Oracle Master Silver SQL 2019(1Z0-071)

問題ID : 26720

問題を開く

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

次の問合せのうち、正常に実行されるものはどれですか。
なお、EMPLOYEES表には以下の件数のデータが登録されています。

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

SELECT employee_id, employee_name, salary FROM employees WHERE salary = (SELECT AVG(salary) FROM employees GROUP BY department_id);

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

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

SELECT employee_id, employee_name, salary FROM employees WHERE salary = (SELECT salary FROM employees WHERE department_id = 5);

正解

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

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

解説

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

比較演算子に単一行演算子を使用している場合、副問合せから複数件のデータが返されるとエラーとなります。また、1件のデータでも比較演算子の左辺と右辺で列数が異なる場合はエラーとなります。

以上より、
・SELECT employee_id, employee_name, salary FROM employees WHERE salary >= (SELECT AVG(salary) FROM employees);
・SELECT department_id, AVG(salary) FROM employees WHERE salary > (SELECT MIN(salary) FROM employees) GROUP BY department_id;
が正解となります。

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

・SELECT employee_id, employee_name, salary FROM employees WHERE salary = (SELECT AVG(salary) FROM employees GROUP BY department_id);
・SELECT employee_id, employee_name, salary FROM employees WHERE salary = (SELECT salary FROM employees WHERE department_id = 5);
これらは下記の副問合せの結果では複数行が戻されますが、条件の判定に単一行演算子を使用しているのでエラーとなります。
　　SELECT AVG(salary) FROM employees GROUP BY department_id;
　　SELECT salary FROM employees WHERE department_id = 5;

・SELECT employee_id, employee_name, salary FROM employees WHERE salary = (SELECT employee_id, employee_name FROM employees);
副問合せの結果、複数の列（employee_id, employee_name）の値が戻されますが、条件の判定に単一行演算子を使用しているのでエラーとなります。

参考

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値である場合]

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

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句を使用できる

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

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

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

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

[単一行副問合せ]

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

SQLを表示

SELECT AVG(salary) FROM employees;

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

[複数行副問合せ]

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

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);

参考URL

Oracle® Database SQL言語リファレンス「スカラー副問合せ式」

上に戻る

複数行が返されると思うのですが、なぜ単一演算子で通るのでしょうか。

c coeri24

投稿日 2023/09/06

下記解説が引っかかりました。

演算子の右辺にて、
department_id(部署)ごとにMIN(salary)で給与の最小値を出しているので
複数行が返されると思うのですが、なぜ単一演算子で通るのでしょうか。

・SELECT department_id, AVG(salary) FROM employees WHERE salary > (SELECT MIN(salary) FROM employees) GROUP BY department_id;
が正解となります。

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

a arashi1977

2023/09/06 22:36

演算子の右辺にて、department_id(部署)ごとにMIN(salary)で給与の最小値を出しているので複数行が返されると思う

ここがよくわからなかったのですが、SELECT salary FROM employeesしたら複数行返ってくるものの、その中の最小値だけを返すMIN()関数を使っているので、（単一の）salaryの最小値で比較するので何もおかしくないように思うのですが…

https://docs.oracle.com/cd/E57425_01/121/SUTIL/GUID-62980B89-1B57-46B1-BCD9-27AF5B8AE861.htm

質問の意味を誤読していたらごめんなさい。

c coeri24

2023/09/07 11:07

おっしゃる通りでした。。完全にdepartment_idもSELECT句にあるかのような錯覚を起こしていました。ありがとうございます。

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

助け合いフォーラム