IPA SQL・関係代数 — 頻出論点と過去問

頻出論点

1. この分野は何か

関係データベースに蓄えられた表から、目的のデータを取り出したり加工したりする演算と問い合わせ言語を扱う分野です。理論の土台である関係代数と、実装言語であるSQLの両面から、複数の表をどう結びつけ、どの単位で集計・絞り込みを行うかが問われます。特に、値が存在しないことを表すNULLが結合・比較・集計に混ざったときの振る舞いや、重複行が排除されるか残るかの違いが、つまずきやすいところです。

2. 実際の出題のされ方

具体的な表のデータに対して、条件に合う行の抽出や集計を手作業で追わせる問題が繰り返し出題されています。SQL文を示さずに日本語の条件と複数の表だけを与え、期間や合計額の条件に合うデータを読み取らせる問題が、ITパスポートで出ています。同じくITパスポートでは、関係代数の基本操作の名前を選ばせる問題や、%と_を使ったパターンマッチングの該当件数を数えさせる問題も出ています。

SQL文の実行結果の行数や値を求める問題、目的の結果を得る字句の空欄補充、機能の性質を問う問題は、応用情報技術者の午前と高度試験の午前Iで出題されています。たとえば、更新可能なビューの定義文を選ぶ問題、ストアドプロシージャの性質を問う問題、横に並んだ複数期の売上列を縦の行に並べ替えるSQL文を選ぶ問題、ウィンドウ関数のOVER句に入れる指定を選ぶ問題が出ています。

自己参照する表に相関副問合せを使ったときの結果や、GRANT文で利用者に渡る権限の範囲を選ばせる問題が、情報処理安全確保支援士の午前IIにあります。システム監査技術者の午前IIでは、自然結合と同じ結果になるSQL文を選ぶ問題が出ています。

データベーススペシャリストの午前IIでは、より踏み込んだSQLの構文と関係代数の理論まで問われます。SQLでは、ウィンドウ関数でグループごとの順位付けに使う関数を選ぶ問題、一方の表にだけある行を取り出す結合の種類や集合演算子による表の比較、3値論理の真理値、制約の検査時期を切り替える指定、シーケンスとロールバックの関係などが出題されています。関係代数では、商演算の結果の表や、属性が$n$個ある関係の異なる射影の個数を求める問題が出ています。これに加えて、商演算や等結合の基本演算への分解、差演算を用いた共通集合の表現、演算ごとの和両立の要否など、演算どうしの関係を問う問題も出題されています。

3. 学習のポイント

関係代数の演算は、結果の列構成と重複の扱いを軸に整理します。特定の行を取り出す選択や特定の列を取り出す射影のほか、集合演算は2つの関係の属性の数と対応する定義域がそろっている（和両立である）ことが前提ですが、すべての行の組合せを作る直積にはこの前提がありません。等結合は直積と選択の組合せで表され、結合キーの列が結果に2つとも残りますが、自然結合では共通列の重複が取り除かれて1つになります。また、関係$R(A, B)$を$S(B)$で割る商演算$R \div S$は、$S$のどの行とも組になっている$A$の値だけを取り出す操作で、$R$の$A$の値と$S$の直積から$R$にある組を除くと、$R$に現れない組合せだけが残るので、そこに含まれる$A$を元の$A$から引く手順で表せます。

SQLの結合と副問合せでは、一致しない行の扱いと評価の向きを押さえます。LEFT OUTER JOINでは左表の全行が結果に残り、右表に一致する行がなければ右表の列がNULLになります。このとき、右表の列に対する絞り込み条件をON句に書くかWHERE句に書くかで結果が変わります。相関副問合せでは、外側の表の各行について内側の副問合せを評価します。NOT EXISTSは内側の検索結果が0件のときに真となるため、関連する行が1つも無い行を取り出すのに使われます。また、<や>などの比較演算子の右辺に副問合せを置く場合、副問合せは単一の値を返す必要があります。

集合演算子とウィンドウ関数、DDL・制御文の仕様も重要です。UNION・EXCEPT・INTERSECTはいずれもデフォルトで重複行を1つにまとめます（UNION ALLは重複を残します）。ウィンドウ関数では、OVER句の中でPARTITION BYにより集計や順位付けのグループを分け、ORDER BYでグループ内の順序を指定します。データベースオブジェクトの仕様としては、ビューが更新できるのは、ビューの1行が基底表の1行に対応していて、定義にDISTINCT・GROUP BY・集約関数などの行の対応を崩す構文を含まないときです。ストアドプロシージャは、複数のSQL文をまとめてDBMS側に保存しておき、アプリケーションから1回呼び出すだけで実行させる仕組みです。制約をDEFERRABLEと定義しておくと、SET CONSTRAINTSで検査の時期をコミットのときまで遅らせ、トランザクションの途中で一時的に制約を満たさない状態を許せます。なお、シーケンスの採番はトランザクションの取消しに連動しないため、ロールバックすると番号に欠けが出ることがあります。GRANT文では、WITH GRANT OPTIONを付けると対象表の操作権限だけでなく、その権限を他者に再付与する付与権も一緒に渡ります。

4. 間違えやすいところ

NULLが関わる3値論理・集約関数・外部結合の組合せは、結果を1行ずつ追って確かめる必要があります。SQLの論理演算には真・偽のほかに不定（Unknown）があります。ORでは一方が真なら全体が真に決まりますが、偽と不定のORは不定のままです。WHERE句は条件が真の行だけを残し、不定の行は除外します。また、AVGやSUMなどの集約関数は、COUNT(*)を除いてNULLの行を無視して計算します。たとえば点数の列にNULLの行があると、AVGはその行を分母に入れずに平均を出すので、NULLを0と数えたときの平均とは値が変わります。NULLを別の値に置き換えたいときは、引数のうち最初にNULLでないものを返すCOALESCE関数を使います。

SELECT句にDISTINCTが付いている問題では、抽出された行数をそのまま数えた誤答肢がありました。条件を満たす行を抜き出したあと、指定された列の値が何種類あるかを数え上げる必要があります。

差演算を重ねた式は、引く側の集合が元の集合と重なる部分を持つかどうかをベン図で確かめると、結果が変わるかどうかが分かります。共通部分を持たない集合を引いても、元の集合の要素は減りません。

男女それぞれの最年長を除く問題では、比較演算子の右辺の副問合せをGROUP BYで性別ごとにまとめた誤答肢がありました。この副問合せは複数の行を返すので、単一の値との比較ができずにエラーになります。

ストアドプロシージャの利点として、ディスク装置への入出力の回数が減るとする誤答肢がありました。複数のSQL文をDBMS側にまとめておいても、処理対象のデータ量や必要なディスクアクセス自体は変わりません。