【過去問解説】平成30年度春期 基本情報技術者試験 データベース(午後問3)

平成30年度春期 基本情報技術者試験 午後問3

問3 子供会の名簿データベース

難易度:★★★☆ 普通

設問1

設問1の問題文は以下の通りです。

設問1

6年生を対象に実施するイベントの安形を配布するために, 6年生の保護者の氏名と住所を抽出する。ここで, 同一の保護者は重複して抽出しない。また, 同じ住所に氏名が同じ保護者は, 複数人いないものとする。正しいSQL文を, 解答群の中から選べ。

 

解答群

ア SELECT DISTINCT 保護者表.保護者氏名, 保護者表.住所

  FROM 保護者表

  WHERE 保護者表.保護者番号 NOT IN

   (SELECT 児童表.保護者番号 FROM 児童表 WHERE 児童表.学年 = 6

イ SELECT DISTINCT 保護者表.保護者氏名, 保護者表.住所

  FROM 保護者表, 児童表

  WHERE 児童表.学年 = 6

  GROUP BY 保護者表.保護者氏名, 保護者表.住所

ウ SELECT DISTINCT 保護者表.保護者氏名, 保護者表.住所

  FROM 保護者表, 児童表

  WHERE 保護者表.保護者番号 = 児童表.保護者番号 AND 児童表.学年 = 6

エ SELECT 保護者表.保護者氏名, 保護者表.住所

  FROM 保護者表, 児童表

  WHERE 保護者表.保護者表番号 = 児童表.保護者番号

  GROUP BY 保護者表.保護者氏名, 保護者表.住所 HAVING 児童表.学年 = 6

出所:https://www.jitec.ipa.go.jp/1_04hanni_sukiru/mondai_kaitou_2018h30_1/2018h30h_fe_pm_qs.pdf 


正答:ウ

4つある選択肢の中から「6年生の保護者の氏名と住所」を抽出するSQL文を選択する問題です。SQLをある程度理解していれば、ウが正解なのは何となく理解できると思うので、当記事では「なぜウ以外が不正解なのか?」に焦点を当て、実際にSQLを動かしながら解説します

実際にSQLを実行してみた(正答ウ)

各テーブルの状況を確認しましょう。問題文に記載されている行に対していくつか独自の行(赤字)を加えているのでご注意ください。

保護者表(parents)

保護者番号

parent_no

保護者氏名

name

電話番号

phone_number

住所

address

12021

情報花子 03-1111-2222 東京都**区1-2-3
12022 麻生太郎 03-2222-2222 東京都**区1-1-1
12023 小泉純一郎 03-1111-1111 東京都**区9-9

 

児童表(children)

児童番号

child_no

児童氏名

name

学年

grade

保護者番号

parent_no

12027 情報一郎 6 12021
14021 情報二郎 4 12021
15000 麻生太郎ジュニア 6 12022
15001 小泉進次郎 1 12023

 

活動表(activities)

児童番号

child_no

イベント番号

event_no

12027 18001
12027 18002
14021 18001
15000 18001
15000 18002
15000 18003

 

 イベント表(events)

イベント番号

event_no

イベント名

event_name

開催日

event_date

18001 歓迎会 2018-04-07 00:00:00
18002 地域清掃 2018-04-14 00:00:00
18003 ソフトボール大会 2018-04-21 00:00:00

「6年生の保護者の氏名と住所」を抽出するSQLですので、6年生の情報一郎の親である「情報花子」と6年生の麻生太郎ジュニアの親である「麻生太郎」が抽出されるのが期待値となります。

早速SQLを実行してみましょう。以下は正答であるウのSQLを私のデータベース上で動作できるように書き換えたものです。

SELECT DISTINCT parents.name, parents.address
FROM parents, children WHERE parents.parent_no = children.parent_no and children.grade = 6;

保護者氏名

parent_name

住所

address

情報花子

東京都**区1-2-3
麻生太郎 東京都**区1-1-1

 

期待通りの結果になりました。 

このSQLについての解説はあえて省略し、ア、イ、エについて解説していきます。

?

広告

 

アのSQLを考える

ア SELECT DISTINCT 保護者表.保護者氏名, 保護者表.住所

  FROM 保護者表

  WHERE 保護者表.保護者番号 NOT IN

   (SELECT 児童表.保護者番号 FROM 児童表 WHERE 児童表.学年 = 6)

 このSQLでは副問合せを使用しています。副問合せとは、ある問合せ(SELECT)を実現するために、その問合せの中で使用される問合せ(SELECT)のことです。具体的には(SELECT 児童表.保護者番号 FROM 児童表 WHERE 児童表.学年 = 6)」の部分を指します。

副問合せの結果は以下の通りです。6年生の児童の行をWHERE句で抽出した後、「保護者番号」の列だけを取得しています。6年生の子供を持つ情報花子と麻生太郎の保護者番号が抽出されています。

保護者番号

parent_no

12021

12022

この副問合せでは外への参照(外側の問合せで使用する列を副問合せ中で使用すること)がないので、副問合せはSQL全体で一度だけ実行され、保持された実行データに対して主問合せ(SELECT DISTINCTの方)の各行を比較することになります。このことは問題を解くうえで意識する必要はありませんが、SQLのパフォーマンスを考えるうえで重要なポイントです。ちなみに、外への参照がある場合は主問合せの1行ずつ、副問合せが毎回実行されることになります。

次に、副問合せの結果に対する主問合せの検索条件を見てみましょう。 

WHERE 保護者表.保護者番号 NOT IN (副問合せ)

「列名 IN 副問合せ」を使うと、主問合せの1行ずつについて「副問合せの結果のいずれかと合致しているか?」を評価し、評価が真(いずれかと合致している)となった主問合せの行を抽出します。ちなみに、「列名 IN 副問合せ」と「列名 =ANY 副問合せ」は等価です。反対に「NOT IN」の場合は「副問合せの結果のいずれにも合致していない(すべて異なる)」場合に評価が正となります。

このSQLでNOT INを使っているということは・・・?

保護者表の行の中から、副問合せで抽出した「6年生の子供の親の保護者番号」の結果のいずれにも合致していない行が抽出されます


SELECT DISTINCT parents.name, parents.address
FROM parents WHERE parents.parent_no NOT IN (SELECT children.parent_no FROM children WHERE children.grade=6);

保護者氏名

parent_name

住所

address

小泉純一郎

東京都**区9-9

 

「NOT」がついているがために不正解となりますが、「IN」であれば、「6年生の子供の親の保護者番号」の結果のいずれかに合致する行を抽出するため正解となります。

ちなみに、EXISTSで「6年生の保護者の氏名と住所」を抽出する場合は以下のようなSQLになります。EXISTSは、主問合せの各行を評価したとき、副問合せの結果が1行以上になるような主問合せの行を結果として返します

先ほど少し言及した「外への参照」を含むSQLになっているため、このSQLは主問合せの行ごとに副問合せが実行されることになります。したがって、外への参照のないINの方が無駄が少ないです。数行、数百行レベルのテーブル同士であれば性能の差は微々たるものですが、100万、1000万行レベルになると秒単位の差が出てくる可能性もあります。ただ、SQLの内容や主問合せ・副問合せのデータ量によってパフォーマンスが決まるので、一概に「INの方がEXISTSよりも速い」ということはできません。


SELECT DISTINCT parents.name, parents.address
	FROM parents
    WHERE EXISTS
		(SELECT * FROM children WHERE children.parent_no = parents.parent_no and children.grade=6);
イのSQLを考える

次にイのSQLを分析してみましょう。

イ SELECT DISTINCT 保護者表.保護者氏名, 保護者表.住所

  FROM 保護者表, 児童表

  WHERE 児童表.学年 = 6

  GROUP BY 保護者表.保護者氏名, 保護者表.住所

このSQLの問題はSELECT文が結合ではなく直積演算になっていることです。

正答であるウの場合、WHERE 保護者表.保護者番号 = 児童表.保護者番号」のように、複数の異なる表(保護者表と児童表)を特定の列(保護者番号)を使用して結合しています。これにより、SELECT対象となる行は、保護者表に親を持つ子とその親の組み合わせが1行として出力されることになります。求めているSQLが「6年生の保護者の氏名と住所」であるため、上記のように複数の表を特定の条件で結合する必要があるのです。

しかし、このイのSQLではWHERE句がWHERE 児童表.学年 = 6」のみであるため、直積演算の結果に対してWHERE句が実行されることになります。直積演算の結果(WHERE句を除く)は以下のようになります。

SELECT *
	FROM parents, children;
parent_no name phone_number address child_no name grade parent_no
12021 情報花子 03-1111-2222 東京都**区1-2-3 12027 情報一郎 6 12021
12022 麻生太郎 03-2222-2222 東京都**区1-1-1 12027 情報一郎 6 12021
12023 小泉純一郎 03-1111-1111 東京都**区9-9 12027 情報一郎 6 12021
12021 情報花子 03-1111-2222 東京都**区1-2-3 14021 情報二郎 4 12021
12022 麻生太郎 03-2222-2222 東京都**区1-1-1 14021 情報二郎 4 12021
12023 小泉純一郎 03-1111-1111 東京都**区9-9 14021 情報二郎 4 12021
12021 情報花子 03-1111-2222 東京都**区1-2-3 15000 麻生太郎ジュニア 6 12022
12022 麻生太郎 03-2222-2222 東京都**区1-1-1 15000 麻生太郎ジュニア 6 12022
12023 小泉純一郎 03-1111-1111 東京都**区9-9 15000 麻生太郎ジュニア 6 12022
12021 情報花子 03-1111-2222 東京都**区1-2-3 15001 小泉進次郎 1 12023
12022 麻生太郎 03-2222-2222 東京都**区1-1-1 15001 小泉進次郎 1 12023
12023 小泉純一郎 03-1111-1111 東京都**区9-9 15001 小泉進次郎 1 12023

FROMで複数の表を指定した場合、直積の結果が出力されます。表A, Bの直積の場合、出力される行数は表Aの行数×表Bの行数、列数は表Aの列数+表Bの列数となります。つまり、表Aの1行に対して、表Bに対するすべての行を組み合わせて表示するのです。正答ウのような保護者番号による結合がないため、表A・表B間でまったく関係のないデータが1行に組み合わされてしまいます。

例えば、上から3行はすべて「情報一郎」ですが、保護者表の名前は「情報花子」、「麻生太郎」、「小泉純一郎」となっており、「情報一郎」の親ではない保護者との組み合わせが行として表示されます。ある児童の保護者は、保護者表にいるすべての保護者になることを意味してしまうのです。

上記のSQL結果に対してWHERE 児童表.学年 = 6」を実行したとしても、対象データが正しくないため結果も正しくありません。以下がイのSQL結果です。小泉純一郎は6年生の子供を持たないにもかかわらず、SQLの結果として表示されてしまっているため、イは不正解となります。

SELECT DISTINCT parents.name, parents.address
	FROM parents, children
    WHERE children.grade = 6
    GROUP BY parents.name, parents.address;

保護者氏名

parent_name

住所

address

情報花子 東京都**区1-2-3
麻生太郎 東京都**区1-1-1
小泉純一郎 東京都**区9-9

 

エのSQLを考える

最後にエのSQLを分析していきましょう。

エ SELECT 保護者表.保護者氏名, 保護者表.住所

  FROM 保護者表, 児童表

  WHERE 保護者表.保護者表番号 = 児童表.保護者番号

  GROUP BY 保護者表.保護者氏名, 保護者表.住所 HAVING 児童表.学年 = 6

 GROUP BY句に続けてHAVING句が使用されています。HAVING句を使えば、保護者氏名と住所でグループ化された結果に対して取り出すグループを制限することができます。ちなみに「グループ化する」というのはこういうことです。

SELECT parents.name, parents.address
	FROM parents, children
    WHERE parents.parent_no = children.parent_no
    GROUP BY parents.name, parents.address;

保護者氏名

parent_name

住所

address

情報花子 東京都**区1-2-3
麻生太郎 東京都**区1-1-1
小泉純一郎 東京都**区9-9

 「GROUP BY parents.name, parents.address」によって保護者の名前と住所の組み合わせでグループを作っています。本来グループ化は、COUNT(*)やSUM(列名)を使ってグループごとの演算結果を出力するために使用します。例えば、SELECTにCOUNT(*)を加えれば、「保護者氏名ごと(正しくは保護者氏名と住所ごと)の子供の数」を求めてることができるのです。

ちなみに、エのSQLは解答の中で唯一DISTINCT(重複排除)が指定されていません。グループ化を理解すればわかると思いますが、グループ化によって保護者氏名と住所が同じ行は一つのグループに統合されるため、GROUP BY句で指定した列名の組み合わせで重複が生じることはありません

さて、そろそろエのSQLが不正解である理由を説明します。一言でいうと、SQLが不正だからです。具体的には下記のようなエラーが出ます(MySQL)。

0 69 23:21:01 SELECT parents.name, parents.address FROM parents, children     WHERE parents.parent_no = children.parent_no     GROUP BY parents.name, parents.address HAVING children.grade=6 LIMIT 0, 1000 Error Code: 1054. Unknown column 'children.grade' in 'having clause' 0.000 sec

「児童表の学年なんて列知りません」と言われました。その通りです。HAVING句では、集合関数(COUNT, SUM, AVGなど)かGROUP BY句 で指定された列以外は指定できません。ということは、そういうことです。

SELECT parents.name, parents.address, children.grade
	FROM parents, children
    WHERE parents.parent_no = children.parent_no
    GROUP BY parents.name, parents.address, children.grade HAVING children.grade=6;
保護者指名 住所 学年
情報花子 東京都**区1-2-3 6
麻生太郎 東京都**区1-1-1

6

 

最後に

設問2以降は気が向いたら更新します。。

またはリクエストがあれば記事化します。 

解説が明らかに誤っている場合はご連絡ください。

 

問題冊子・公式解答

IPA 独立行政法人 情報処理推進機構:問題冊子・配点割合・解答例・採点講評(2018、平成30年)

勉強時に使用したテキスト(最新版)

 

広告