助け合いフォーラム

Oracle DB

Oracle Master Silver SQL 2019(1Z0-071)

問題ID : 26792

問題を開く

DEPARTMENTS表とEMPLOYEES表の構造とデータを確認して下さい。

次のSQL文のうち、エラーとなるものはどれですか。

SELECT employee_id, employee_name, salary FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE department_id BETWEEN 1 AND 3);

SELECT employee_id, employee_name, salary FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE department_name = '開発') OR salary >ANY (SELECT MIN(AVG(salary)) FROM employees GROUP BY department_id);

SELECT employee_id, employee_name, salary FROM employees WHERE department_id =ANY (SELECT department_id FROM departments WHERE department_id NOT BETWEEN 1 AND 5);

SELECT employee_id, employee_name, salary FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE department_id >ALL (SELECT department_id FROM employees WHERE salary > (SELECT MIN(salary) FROM employees)));

正解

SELECT employee_id, employee_name, salary FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE department_id BETWEEN 1 AND 3);

解説

副問合せから戻されるデータの件数が複数件の場合に、比較演算子に単一行演算子を使用するとエラーとなります。

選択肢のSQL文において、副問合せが返すデータの件数と比較演算子に注目すると、下記の副問合せは3件のデータを返しますが、
　SELECT department_id FROM departments WHERE department_id BETWEEN 1 AND 3
比較演算子に"="が使用されているため、エラーが発生します。

以上より、
・SELECT employee_id, employee_name, salary FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE department_id BETWEEN 1 AND 3);
が正解となります。

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

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

・SELECT employee_id, employee_name, salary FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE department_name = '開発') OR salary >ANY (SELECT MIN(AVG(salary)) FROM employees GROUP BY department_id);
WHERE句に使用されている副問合せ
　SELECT MIN(AVG(salary)) FROM employees GROUP BY department_id
は1件のデータを返しますが、比較演算子に複数行演算子>ANYが使用されています。単一行副問合せと複数行演算子とを組み合せて使用してもエラーにはならないため、このSQL文は正常に実行されます。

・SELECT employee_id, employee_name, salary FROM employees WHERE department_id =ANY (SELECT department_id FROM departments WHERE department_id NOT BETWEEN 1 AND 5);
WHERE句に使用されている副問合せ
　SELECT department_id FROM departments WHERE department_id NOT BETWEEN 1 AND 5
は1件もデータを返しません。従って主問合せも1件もデータを返しませんが、エラーになるわけではありません。

・SELECT employee_id, employee_name, salary FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE department_id >ALL (SELECT department_id FROM employees WHERE salary > (SELECT MIN(salary) FROM employees)));
副問合せはネストすることができます。また、次の副問合せは1件もデータを返さない可能性もありますが、副問合せがデータを1件も返さない場合でもエラーとはなりません。
　SELECT department_id FROM departments WHERE department_id >ALL (SELECT department_id FROM employees WHERE salary > (SELECT MIN(salary) FROM employees))

参考

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言語リファレンス「比較条件」

@IT Webブラウザで気軽に学ぶ実践SQL講座（11）「副問合せを使った複雑な条件指定」

上に戻る

選択肢が2つ？

k kkjjj

投稿日 2023/12/16

選択肢の「SELECT employee_id, employee_name, salary FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE department_name = '開発') OR salary >ANY (SELECT MIN(AVG(salary)) FROM employees GROUP BY department_id);」は、余計な括弧が最後についているので、これもエラーになると思います。

r rink_rew

2023/12/17 22:34

Ping-tさん提供の検証用データで試してみましたが、結果は以下の通りエラーとはなりませんでした。

SQL> SELECT employee_id, employee_name, salary FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE department_name = '開発') OR salary >ANY (SELECT MIN(AVG(salary)) FROM employees GROUP BY department_id);

EMPLOYEE_ID EMPLOYEE_NAME		       SALARY
----------- ------------------------------ ----------
       1001 山田二郎                           500000
       1002 佐藤昭夫                           500000
       1003 山口洋子                           500000
       1004 田中浩介                           500000
       1005 加藤昭彦                           500000
       1006 佐々木明子                         800000
       1007 菊池浩二                           800000
       1008 中山大輔                           400000
       1009 星野健一                           400000
       1010 斎藤京子                           400000
       1011 吉田亜希                           400000

EMPLOYEE_ID EMPLOYEE_NAME		       SALARY
----------- ------------------------------ ----------
       1012 阿部伊吹                           400000
       1015 橋本淳                             300000

13行が選択されました。

SQL>

参考までに、以下は整形したSQL文ですが、特に余計な括弧はなさそうです

SELECT
    employee_id,
    employee_name,
    salary
FROM
    employees
WHERE
    department_id IN (
        SELECT
            department_id
        FROM
            departments
        WHERE
            department_name = '開発'
    )
    OR salary > ANY (
        SELECT
            MIN(AVG(salary))
        FROM
            employees
        GROUP BY
            department_id
    );

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

助け合いフォーラム