助け合いフォーラム

Oracle DB

Oracle Master Silver SQL 2019(1Z0-071)

問題ID : 26628

問題を開く

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

給与の高い順に、上位15%の従業員の情報を表示します。SALARY列に値が入っていない従業員は表示しません。
どの問合せを使用しますか。

SELECT employee_id, employee_name, salary FROM employees
ORDER BY salary
FETCH FIRST 15 PERCENT ROWS ONLY;

SELECT employee_id, employee_name, salary FROM employees
ORDER BY salary DESC
FETCH FIRST 15 PERCENT ROWS ONLY;

SELECT employee_id, employee_name, salary FROM employees
ORDER BY salary DESC NULLS LAST
FETCH FIRST 15 PERCENT ROWS ONLY;

SELECT employee_id, employee_name, salary FROM employees
ORDER BY salary NULLS LAST
FETCH FIRST 15 PERCENT ROWS ONLY;

正解

SELECT employee_id, employee_name, salary FROM employees
ORDER BY salary DESC NULLS LAST
FETCH FIRST 15 PERCENT ROWS ONLY;

解説

Oracle 12cより、SELECT文の問合せ結果として返される行数を制限できる機能、row_limiting_clause(行制限の条件)が加わりました。これにより、例えば上位10番目から20番目のデータを簡単に取り出せます。

row_limiting_clauseのFETCH句は返される行数、または行の割合を指定して、SELECT文の結果として返される行数を制限します。

　FETCH { FIRST | NEXT }
　{ 返される行数 | 返される行の割合 PERCENT }
　{ ROW | ROWS }
　{ ONLY | WITH TIES }

設問では給与の高い順に15%という条件ですので、まずORDER BY句でSALARY列を降順(DESC)にソートしますが、その際NULL値の行が先頭に表示されないようにNULLS LAST句を記述します。
ソートした後、FETCH句で15%を指定します。

以上より、
・SELECT employee_id, employee_name, salary FROM employees
　ORDER BY salary DESC NULLS LAST
　FETCH FIRST 15 PERCENT ROWS ONLY;
が正解となります。

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

SQLを表示

SELECT employee_id, employee_name, salary FROM employees
ORDER BY salary DESC NULLS LAST
FETCH FIRST 15 PERCENT ROWS ONLY;

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

・SELECT employee_id, employee_name, salary FROM employees
　ORDER BY salary
　FETCH FIRST 15 PERCENT ROWS ONLY;
・SELECT employee_id, employee_name, salary FROM employees
　ORDER BY salary NULLS LAST
　FETCH FIRST 15 PERCENT ROWS ONLY;
ORDER BY句がデフォルトの昇順(ASC)のため、給与の低い順に表示されます。

・SELECT employee_id, employee_name, salary FROM employees
　ORDER BY salary DESC
　FETCH FIRST 15 PERCENT ROWS ONLY;
NULLS LAST句がないためNULL値の行が先頭に表示されてしまい、設問の条件を満たしていません。

参考

Oracle 12cより、SELECT文の問合せ結果として返される行数を制限できる機能、row_limiting_clause(行制限の条件)が加わりました。これにより、例えば上位10番目から20番目のデータを簡単に取り出せます。
row_limiting_clauseにはOFFSET句とFETCH句があり、SELECT文で次のように指定します。

　SELECT 列名[, 列名 ...]
　FROM 表名
　[WHERE 条件]
　[ORDER BY 列名[, 列名 ...]]
　[OFFSET ...]
　[FETCH ...]

ORDER BY句は省略できますが、省略した場合はどのような順番で問合せ結果が返されるか保証されません。そのため、「SALARY列の値の大きい順に上位N件を表示する」など、ソートした順番でデータを取り出したい場合は、ORDER BY句を指定する必要があります。
ORDER BY句を指定する場合は、ORDER BY句の後にrow_limiting_clauseを記述します。

・OFFSET句
スキップする行数を数値で指定します。

　OFFSET スキップする行数 { ROW | ROWS }

　ROW, ROWS : 意味を明確にするためのキーワードです。両者に区別はありませんが、OFFSET句を指定する場合は省略できません。

OFFSET句を省略するか、負の数値を指定すると0(ゼロ)とみなされ、行のスキップは行われません。
NULLを指定した場合や、問合せ結果の行数以上の数値を指定すると、返される行数は0行になります。

・FETCH句
返される行数、または行の割合を指定します。

　FETCH { FIRST | NEXT }
　{ 返される行数 | 返される行の割合 PERCENT }
　{ ROW | ROWS }
　{ ONLY | WITH TIES }

　FIRST, NEXT : 意味を明確にするためのキーワードです。両者に区別はありませんが、FETCH句を指定する場合は省略できません。
　ROW, ROWS : 意味を明確にするためのキーワードです。両者に区別はありませんが、FETCH句を指定する場合は省略できません。
　ONLY : 返される行数、または行の割合に指定したとおりの行数が返されます。
　WITH TIES : 最後にフェッチ(取得)された行と同じソートキーの値を持つ全ての行が返されます。ソートキーとはORDER BY句に指定した列のことなので、WITH TIESを指定する場合は、ORDER BY句を指定する必要があります。

FETCH句を省略すると、「OFFSET句でスキップする行数」の次の行から始まる全ての行が返されます。
負の数値やNULLを指定すると、返される行数は0行になります。
問合せ結果の行数以上の数値や、100%以上の割合を指定すると、問合せ結果の全ての行が返されます。

以下の例では、価格の安い順に10%の商品を表示しています。

SQLを表示

SELECT prod_name, list_price FROM new_products
ORDER BY list_price
FETCH FIRST 10 PERCENT ROWS ONLY;

以下の例では、給与の低い順に5行返されるように指定していますが、WITH TIESキーワードも指定しているため最後にフェッチされた行と同じソートキーの値を持つ全ての行も返されます。ソートキーとはORDER BY句に指定した列のことなので、WITH TIESを指定する場合は、ORDER BY句を指定する必要があります。
この例では、5行目と6行目の従業員が同じ給与であるため、6行目までが返されます。

SQLを表示

SELECT employee_id, employee_name, salary FROM employees
ORDER BY salary
FETCH FIRST 5 ROWS WITH TIES;

以下の例では、給与の高い順から先頭の9行をスキップし、10行目から5行分の14行目までのデータを表示しています。ONLYキーワードを指定しているため、14行目と15行目が同じ給与であっても、14行目までしか表示されません。
給与がNULLの行もあるため、NULLS LASTキーワードでNULL値が含まれる行を最後に表示しています。

SQLを表示

SELECT employee_id, employee_name, salary FROM employees
ORDER BY salary DESC NULLS LAST
OFFSET 9 ROWS
FETCH FIRST 5 ROWS ONLY;

参考URL

Oracle® Database SQL言語リファレンス「SELECT」: row_limiting_clause

上に戻る

85%以上がnullの場合でもこの式は成立しますか？

f fugusuki

公開日 2024/01/26

問題
給与の高い順に、上位15%の従業員の情報を表示します。SALARY列に値が入っていない従業員は表示しません。
どの問合せを使用しますか。

答え
SELECT employee_id, employee_name, salary FROM employees ORDER BY salary DESC NULLS LAST FETCH FIRST 15 PERCENT ROWS ONLY;

まず、
ORDER BY salary DESC NULLS LASTでNULLを最後に降順にすると理解しました。
次に、
FETCH FIRST 15 PERCENT ROWS ONLY;
とあるので、順番変更後の上位15%を表示しています。
85%がnullの場合はどうなるのでしょうか？

r rink_rew

2024/01/29 15:09

成立しますね。SELECT文のとおり、並び替えられた上で上位15%（この問題の例であれば4行）が返されます。
ping-tさん提供のサンプルデータで、salaryの85%をnullにして試した結果は以下のとおりです。

SQL> SELECT employee_id, employee_name, salary FROM employees ORDER BY salary DESC NULLS LAST;

EMPLOYEE_ID EMPLOYEE_NAME		       SALARY
----------- ------------------------------ ----------
       1003 山口洋子                           500000
       1002 佐藤昭夫                           500000
       1001 山田二郎                           500000
       1007 菊池浩二
       1008 中山大輔
       1009 星野健一
（中略）

22行が選択されました。

SQL> SELECT employee_id, employee_name, salary FROM employees ORDER BY salary DESC NULLS LAST FETCH FIRST 15 PERCENT ROWS ONLY;

EMPLOYEE_ID EMPLOYEE_NAME		       SALARY
----------- ------------------------------ ----------
       1003 山口洋子                           500000
       1002 佐藤昭夫                           500000
       1001 山田二郎                           500000
       1007 菊池浩二

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

助け合いフォーラム