Oracle University 講師が ORACLE MASTER 取得を目指している方に向けて試験トピックを解説するブログ連載講座。「ORACLE MASTER Silver SQL 2019」資格取得に向けた試験トピックについて解説します。

————————————————-

今回はORACLE MASTER Silver SQLの試験トピック「グループ関数を使用した集計データのレポート – データ・グループの作成 -」に関連する問題をご紹介いたします。ORACLE MASTER Silver SQL(Exam Number: 1Z0-071-JPN)の詳細につきましては以下URLをご参考ください。

Oracle Database SQL Exam Number: 1Z0-071

URL: https://education.oracle.com/ja/oracle-database-sql/pexam_1Z0-071

————————————————-

今回はグループ関数を使用したSQL文の問題に取り組んでいきましょう。

(解説)

では解説をしていきます。今回の問題の選択肢にはグループ関数(複数行関数)を使用したSQL文が並んでいますね。グループ関数は、複数のデータを扱って1つの結果を出力する関数になります。まずは主なグループ関数の説明についてについて以下にまとめておきます。

関数

目的

AVG(列名)

列の平均値。NULL値は無視

MAX(列名)

列の最大値。NULL値は無視

MIN(列名)

列の最小値。NULL値は無視

SUM(列名)

列の合計値。NULL値は無視

COUNT(列名)

NULL値以外の列の値がある行数 (※1)

※1 COUNT(*)とした場合はNULLも含めた全行数をカウント

それぞれの関数を使用した実行結果も載せておきましょう。12件あるデータを用いてSAL列(給料)に対し操作しています。SQL文を実行するための定義情報やサンプル・データは記事の最後に掲載しておくので皆さんも実際に操作してみてくださいね。

グループ関数の動作が確認できたところで、問題の選択肢にあるGROUP BY句について触れていきましょう。

GROUP BYはどのよう使用するかというと、例えば給料の平均値を求めるときに従業員全体の平均値ではなく、部署(DEPTNO)ごとの平均値を求める必要が出てきたとします。その場合は各部署ごとにグループ分けをして、そのグループごとに平均値を求める関数AVGを使用しないといけないですよね。このような要件があった場合に使用できるのがGROUP BY句になります。GROUP BY句の動作として以下の図も参考にしてください。

このGROUP BY句を使用する際の注意事項としては、SELECT句に列名を指定する場合は、その列名はすべてGROUP BY句に含める必要があるということです。

実際にGROUP BY句を使用した実行例も載せておきましょう。

◆実行例4◆ がエラーとなるのはイメージできるでしょうか？SELECT句で指定しているDEPTNOとAVG(SAL)を分けて実行してみると理解しやすいかと思います。

これでは表示することができませんね。

ここまで確認ができましたら正解の選択肢を選ぶことが可能なので、各選択肢の内容を見ていきましょう。

選択肢1は◆実行例1◆でも確認したSQL文と同じですから大丈夫ですよね。正常に実行することができるので正解の選択肢です。

選択肢2はGROUP BY句にDEPTNO列とJOB列を指定しています。複数列を使用してグループ化することはできますので特に問題ではありません。SELECT句にグループ関数AVGとDEPTNO列しか指定してませんが、GROUP BY句に指定した列は必ずしもSELECT句に指定する必要はありませんのでこれも問題ありません。正常に実行することができるSQL文となるので正解の選択肢となります。

選択肢3のSQL文は実行するとエラーとなります。SELECT句にグループ関数AVGとDEPTNO列とJOB列を指定していますが、GROUP BY句にDEPTNO列しか指定していません。誤りです。

選択肢4は少し追加で説明しましょう。グループ関数も単一行関数と同じように関数のネストを行うことができますが、グループ関数の場合はネストの深さは2つまでになります。この違いはしっかり覚えておきましょう。選択肢4のSQL文の関数のネストは2つとなっているのでこの点は問題ありませんが、SELECT句でDEPTNO列とMAX(AVG(sal))を指定しているところに問題があります。GROUP BY句でDEPTNO列を指定しているのでグループ分けした結果が複数件(10、20、30といった値)、返ってくる形になりますが、MAX(AVG(sal))の結果は、DEPTNO列でグループ化して平均給与を求め、その中から一番大きい値を返すという内容になるため常に1件のデータを返すことになります。一緒に表示することはできませんのでエラーとなります。尚、「SELECT MAX(AVG(sal)) FROM emp_table GROUP BY deptno」 なら実行可能です

選択肢5のSQL文は正常に実行することができます。SELECT句で指定しているDEPTNO列とJOB列をGROUP BY句でも指定していますよね。

以上の結果から、正解の選択肢は１と２と５になります。

今回の問題文に記載してあったSQL文の実行結果と、SQL文が実行できるようにサンプル定義を以下に乗せておきますので参考にしてください。

(サンプル定義)

drop table emp_table;
create table emp_table (empno number(4) not null, ename varchar2(10), job varchar2(9), sal number(7,2), deptno number(2));

insert into emp_table values(7369, ‘SMITH’, ‘CLERK’, 800, 20);
insert into emp_table values(7499, ‘ALLEN’, ‘SALESMAN’, 1600, 30);
insert into emp_table values(7521, ‘WARD’, ‘SALESMAN’, 1250, 30);
insert into emp_table values(7566, ‘JONES’, ‘MANAGER’, 2975, 20);
insert into emp_table values(7654, ‘MARTIN’, ‘SALESMAN’,1250, 30);
insert into emp_table values(7698, ‘BLAKE’, ‘MANAGER’, 2850, 30);
insert into emp_table values(7782, ‘CLARK’, ‘MANAGER’, 2450, 10);
insert into emp_table values(7839, ‘KING’,  ‘PRESIDENT’, 5000, 10);
insert into emp_table values(7844, ‘TURNER’, ‘SALESMAN’, 1500, 30);
insert into emp_table values(7900, ‘JAMES’, ‘CLERK’, 950, 30);
insert into emp_table values(7902, ‘FORD’, ‘ANALYST’, 3000, 20);
insert into emp_table values(7934, ‘MILLER’, ‘CLERK’, 1000, 10);

commit;

グループ関数やGROUP BY句はよく使用する構文だと思いますので動作内容をしっかりイメージして、正しく使えるように学習に励んでくださいね。

それでは今回の講義は以上になります。また次回の講義にてお会いしましょう。

————————————————-

– ORACLE MASTER Silver SQL 2019 のご紹介

– ORACLE MASTER Silver SQL 2019 試験記事トピック一覧

————————————————-

その他の ORACLE MASTER 試験トピック解説講座シリーズ：

【Oracle University講師によるORACLE MASTER Gold DBA 2019 試験トピック解説講座】トピック一覧

————————————————-