ORACLE MASTER Bronze Oracle Database 12c　「SQL基礎I」試験ポイント解説：Oracle Master 12cの攻め方（2）（4/4 ページ）

» 2014年03月31日 18時00分公開

[オラクルユニバーシティ平賀博司，＠IT]

副問い合わせに関する理解

例題　部門ごとの給与（salary）の平均が、全ての部門の最大の給与額の半分より少ない状態の部門（dept_id）を検索しようと考えています。以下のSQLのうち、目的を果たせるものはどれでしょうか。

SELECT dept_id,avg(salary) 
FROM employees 
GROUP BY dept_id 
HAVING avg(salary) < ALL (SELECT max(salary)/2 
                            FROM employees 
                            GROUP BY dept_id);

SELECT dept_id,avg(salary) 
FROM employees 
GROUP BY dept_id 
HAVING avg(salary) > ANY (SELECT max(salary)/2 
                            FROM employees 
                            GROUP BY dept_id);

SELECT dept_id,avg(salary) 
FROM employees 
HAVING avg(salary) < ALL (SELECT max(salary)/2 
                            FROM employees 
                            GROUP BY dept_id);

SELECT dept_id,avg(salary) 
FROM employees 
GROUP BY dept_id 
HAVING avg(salary) > ANY (SELECT max(salary)/2 
                            FROM employees);

　どうですか？グループ関数と副問い合わせの複合問題になっています。いろいろな要素が絡み合ってしまう分、ポイントを見い出すのが難しいですね。

　ここでは、HAVINGが1つのポイントです。HAVING句で利用している副問い合わせのSELECT文が値を1つだけ返すのか、複数返すのか。それにより、条件演算子で利用できるものが変わってきます。また、SELECT句では、グループ関数を利用しているので、グループ関数利用時の注意点が加味されます。

　ということで、正解は「A」。GROUP BYの正しい使い方と、ALL演算子の正しい使用方法がポイントです。

　試験では、無理に副問い合わせを使用しなくても値が得られてしまうようなケースを含め、副問い合わせを多用したSQL文が提示されます。副問い合わせのSELECT文に慣れておくことが攻略のポイントです。

DML文に関する理解

　DML（INSERT、UPDATE、DELETE文）についても、ここまで見てきたようにSQLを指定してどういう結果になるかを聞かれる問題が多く出題されます。ここでの例題は知識を問うものにしてみましょう。

例題　SAVEPOINTに関して正しくない記述はどれですか（全て選択してください）。

A　COMMITのみについて有効である

B　ROLLBACKに対して使用できる

C　DML文のみについて使用できる

D　COMMITとROLLBACKの両方について有効である

E　DML文とDDL文の両方に使用できる

　これはSAVEPOINTの基本理解ができていれば簡単です。「そうではない方」を選択すればいいのです。SAVEPOINTの機能は、後でトランザクション全部をさかのぼってROLLBACKするのではなく、途中に戻ってやり直すことを目的としています。そのため、ROLLBACKに対してのみ有効で、DMLについてのみ利用可能です。

　従って、ここでは「A、D、E」が回答ということになります。

　DMLにはこのようにトランザクションを制御するコマンドがどういうふうに動作するかの理解が大切です。また、COMMITやROLLBACKというSQLと同時に、Oracle Databaseサーバーの中で何が起こっているのかを少しイメージできるようになるとなお、分かりやすくなります。

DDL文に関する理解

　データ定義言語に関する問題もよく出題されます。特にCREATE TABLE文を利用した表の定義の仕方です。

例題　次のCREATE TABLE文を実行したところ、エラーが発生しました。

CREATE TABLE departments
     (dept_id NUMBER(10) PRIMARY KEY, 
     dept_name VARCHAR2(20), 
     address LONG, 
     phone VARCHAR2(20), 
     dept_description LONG, 
     loc_id NUMBER(3) CONSTRAINT dept_loc_id_fk 
                        REFERENCES locations(location_id) , 
     CONSTRAINT dept_name_nn NOT NULL(dept_name));

どのような原因が考えられますか（2つ選択してください）。

A　dept_name列に設定しようとしているNOT NULL制約は列制約構文でしか利用できない

B　dept_id列に設定しようとしている「PRIMARY KEY」の前にCONSTRAINTキーワードが必要である

C　loc_id列に設定しようとしているlocations表への外部キー制約のためには「FOREIGN KEY」というキーワードが必要である

D　LONG型は表1つに1つしか設定できない

　いかがですか？細かい部分で理解が必要ですね。

　ここでの正解は「A」と「D」です。NOT NULL制約は列制約構文でしか指定できません。また、FOREIGN KEYというキーワードは表制約構文で記述したときのみ必要になるキーワードで、列制約構文で記述したときには制約名は省略可能です。

　このように表定義構文で使える書き方、データ型の決まりなど、見過ごしがちなポイントを聞かれます。また、CREATE TABLE文は、開発に携わっているとあまり目にしたり記述したりしないSQL文なので、詳しくないという方もいらっしゃることと思います。この機会に基本的な表の定義の（SQLによる）方法、およびOracle Databaseで設定できる制約についてまとめておきましょう。

まとめ

　いかがでしたでしょうか。ここでは例題として各トピックに関連する問題を通して確認をしました。トピックごとに構文や意味に対する正確な理解が必要になることをお分かりいただけたでしょうか。

　最初にも書きましたが、業務中は試行錯誤して実機で試せるものが、試験では一切できません。逆に、試験対策的な学習を通して、製品としての正しいSQL文の使い方、意味をきちんと理解することで、業務中の試行錯誤の回数が減り、生産性も向上していきます。「試験」をきっかけにした学習にも、正しい理解を得て、最新の情報を入手するという大きな意義があるのです。

　次回は、Bronze DBA試験攻略に向けたポイント解説をお届けいたします。

修正履歴：「副問い合わせに関する理解」の例題に誤りがありました。お詫びして修正いたします（2014年5月12日）　