基本情報技術者試験(SQL)

基本情報技術者試験

【基本情報技術者試験のSQLの問題】中でもSELECT文が頻出!さらに覚えておきたい10個の命令など紹介!!

2020年1月19日

 

実際に出題された基本情報技術者試験SQLのテーマ、特にSELECT文に関する過去問と解答、解説をしていきます。

さらに、これだけは覚えておきたい試験によく出る10個の命令や関数を、具体的なSQL文を使って紹介!

これで基本情報技術者試験のSQL文(SELECT)は、確実に得点することができるようになりますよ。

では早速はじめていきます。

 

令和元年秋期の基本情報技術者試験に、実際に出題された問題を例をご紹介します。

"得点"表から,学生ごとに全科目の点数の平均を算出し,平均が80点以上の学生の学生番号とその平均点を求める。[a]に入れる適切な字句はどれか。ここで,実線の下線は主キーを表す。

得点(学生番号科目,点数)

〔SQL文〕
SELECT 学生番号,AVG(点数)

FROM 得点

GROUP BY [   a   ]

ア.科目 HAVING AVG(点数) >= 80

イ.科目 WHERE 点数 >= 80

ウ.学生番号 HAVING AVG(点数) >= 80

エ.学生番号 WHERE 点数 >= 80

出典:基本情報技術者試験 令和元年秋期 問26

 

解説

[   a   ] の前に「GROUP BY」があります。

ですので、[   a   ] にはグループ化する列を記述します。

問題文に「学生ごとに全科目の点数の平均を求める」とあるので、グループ化する列は「学生番号」。

 

また「平均が80点以上の学生を抽出したい」ので、「学生番号」でグループ化したあと、HAVINGを付与し条件(平均が80点以上)をつけます。

こうすることにより、条件に合致するグループだけが結果として得ることができるのです。

よって答えは、ウの「学生番号 HAVING AVG(点数) >= 80」となります

 

この問題のポイントは、

GROUP BY

「GROUP BY」は、SELECT文において特定の列の値が等しい行ごとに表をグループ化することです。

※この問題では、学生番号が等しい行ごとに表をグループ化しています。

また、グループ化すると、選択列(SELECTの次に書かれている列)にはGROUP BYで指定した列、または集計関数(AVGやSUMなど)を使用した式のみ書くことができます。

※提示されているSQLを見ると、「SELECT 学生番号,AVG(点数) 」と書かれています。

HAVINGには、集計関数(SUMとかAVG)を書くことができ、この条件に合致するグループだけが結果として得られるのです。

 

実際に「得点表」と、問題文のSQLを実行した結果を見てみましょう。

※全科目は国語と数学の2科目とします。

まずは「得点表」です。

学生番号 科目 AVG(点数)
1 国語 70
1 数学 90
2 国語 60
2 数学 65
3 国語 85
3 数学 90

 

以下はSQL実行結果です。

学生番号 得点
1 80
3 90

学生番号2は、平均が63点で80点に達していないので抽出されません。

 

SELECT文とよく一緒に書かれる「GROUP BY」について解説しました。

このほかにも、集約関数(SUM、AVG、COUNT)やBETWEEN、整列(ORDER BY)などもSELECT文と一緒に試験によく出てきます!

そんなよく出る命令などを、次節SQL文や表を使ってくわしく解説していきます。

 

試験によく出る10個の命令や関数など

よく出る10個の命令など

基本情報技術者試験のSELECT文関連の出題は、下記のような問題が多いです。

  • SELECT文の穴埋め
  • SQL実行結果(どんなデータが抽出されるか)を問うもの
  • 実行結果を得るためのSQL文を選ぶ

ですので、色んなSQL文の意味を読み取ることが必要です。

そのため、基本情報技術者試験でよく出るSELECT文の条件指定や各種命令を10個紹介します。

  1. GROUP BY
  2. WHERE
  3. BETWEEN
  4. LIKE
  5. DISTINCT
  6. ORDER BY
  7. 集約関数
  8. ビュー
  9. 副問合せ
  10. 関係代数

 

では順番に解説します。

 

1.GROUP BY

1.GROUP BY

「GROUP BY」は記事冒頭の過去問の中で解説しています。

ですので、こちらでは解説を割愛させていただきます。

 

2.WHERE

2.WHERE

「WHERE」は条件に合ったデータを抽出する場合に使われます

例えば、以下「社員表」から"性別が女"という条件のデータを抽出したい場合を考えます。

「社員表」

名前 性別
山田一郎
加藤れいこ
鈴木えみこ

 

"性別が女"のデータを抽出するSQLは以下の通り。

SELECT 名前 FROM 社員表 WHERE  性別 = "女"

WHEREの後に、性別= "女" と記述するだけです。

 

上記SQL実行結果は以下となります。

名前
加藤れいこ
鈴木えみこ

 

「WHERE」はSQL文でよく使用されます。

SELECTだけでなく、更新(UPDATE)、追加(INSERT)、削除(DELETE)命令などでも頻繁に使用されますので、確実覚えておいてくださいね。

 

3.BETWEEN

3.BETWEEN

「BETWEEN」は範囲に合ったデータを抽出する場合に使われます

例えば、以下「社員表」から"生年月日が1990年4月1日から2000年3月31日"の間のデータを抽出したい場合を考えます。

「社員表」

名前 生年月日
山田一郎 2000-12-10
加藤れいこ 1990-3-3
鈴木えみこ 2000-2-10

 

"生年月日が1990年4月1日から2000年3月31日"のデータを抽出するSQLは以下の通り。

SELECT 名前, 生年月日 FROM 社員表 WHERE BETWEEN  "1990-4-1" AND "2000-3-31"

WHEREのあとに"BETWEEN" 、続けて"1990-4-1" AND "2000-3-31" と抽出したい期間を記述するだけです。

 

上記SQL実行結果は以下となります。

名前 生年月日
鈴木えみこ 2000-2-10

 

4.LIKE

4.LIKE

「LIKE」はパターンマッチング検索といわれ、任意で指定する文字列を含むデータを抽出する場合に使われます

その任意で指定する文字列は"%"を使います。

例えば、以下「社員表」から"名前の末尾に「太」"を含むデータを抽出したい場合、、、

「社員表」

名前 生年月日
山田健太 2000-12-10
加藤れいこ 1990-3-3
鈴木えみこ 2000-2-10

"名前末尾に「太」"を含むデータを抽出するSQLは以下の通り。

SELECT 名前 FROM 社員表 WHERE  名前 LIKE "%太"

WHEREの後に、名前 LIKE "%太" と記述するだけです。

 

上記SQL実行結果は以下となります。

名前 生年月日
山田太郎 2000-2-10

 

では、実際に基本情報技術者試験で出題された問題をやってみましょう!

"BOOKS"表から書名に"UNIX"を含む行を全て探すために次のSQL文を用いる。aに指定する文字列として,適切なものはどれか。ここで,書名は"BOOKS"表の"書名"列に格納されている。

SELECT * FROM BOOKS WHERE 書名 LIKE "a"

ア.%UNIX

イ.%UNIX%

ウ.UNIX

エ.UNIX%

出典:基本情報技術者試験 平成25年春期 問29

答えはイとなります。

 

以下解説します。

"BOOKS"表から書名に"UNIX"を含む行をすべて抽出したいことから、LIKEと任意の文字列"%"を使います。

選択肢イは、UNIXの直前と直後に"%"が記述されているので、書名前後に”UNIX”が含まれるデータが抽出されます。

(例えば「よく分かるUNIX」「UNIXがよく分かる本」「初心者がUNIXを勉強するための本」など)

"UNIX"を含む行をすべて抽出できるのでこれが正解となります。

 

選択肢アは、UNIXの直前に"%"が記述されているので、これだと書名の末尾が”UNIX”で終わるデータしか抽出できません。

(例えば「よく分かるUNIX」など)

 

選択肢ウでは、任意文字列である"%"がないので、書名が”UNIX”のデータしか抽出できません。

(例えば「UNIX」)

 

選択肢エでは、UNIXの直後に"%"が記述されているので、これだと書名の先頭が”UNIX”ではじまるデータしか抽出できません。

(例えば「UNIXがよく分かる本」など)

 

ポイントは、%の位置をどこに記述するかです。

含まれる文字列の直前なのか直後、または、前後なのか、、、

問題文から見極めることが大切です。

 

5.DISTINCT

5.DISTINCT

「DISTINCT」は重複行を取り除いて結果を返してくれます。

例えば、以下「売上表」から"売上があった日"を抽出したい場合を考えます。

「売上表」

売上ID 社員名 売上日
1 山田一郎 2019-12-10
2 山田一郎 2019-2-16
3 河本次郎 2019-12-10
4 加藤れいこ 2019-3-3
5 佐藤一郎 2019-10-10
6 鈴木えみこ 2019-12-10

見て欲しいのは赤字部分のデータで、売上日が"2019-12-10"とデータが3つ(売上げDが1,3,6)あります。


"売上があった日"
を抽出するSQLは以下の通り。

SELECT DISTINCT 売上日 FROM 売上表

SELECTのあとに"DISTINCT"を記述し、重複を除外するカラム名(売上日)を指定するだけです。

 

上記SQL実行結果は以下となります。

売上日
2019-12-10
2019-2-16
2019-3-3
2019-10-10

 

6.ORDER BY

6.ORDER BY

「ORDER BY」はデータの整列(ソート)をする場合に指定します。

整列(ソート)は昇順や降順で並べ替えることができます。

 

例えば、以下「社員表」を"年齢が若い順"(昇順)に並べたい場合、、、

「社員表」

名前 年齢
山田健太 22
加藤れいこ 20
鈴木えみこ 40

 

"年齢が若い順"に並べるSQLは以下の通り。

SELECT 名前 FROM 社員表 ORDER BY  年齢 ASC

ORDER BY  年齢 ASC と記述するだけです。

ちなみに、昇順でデータを並び替える場合、ASCはなくても構いません。

ない場合ですと、デフォルトは昇順となります。

 

上記SQL実行結果は以下となります。

名前 年齢
加藤れいこ 20
山田健太 22
鈴木えみこ 40

 

また、降順(高齢順)に並べる場合は以下のようになります。

ORDER BY  年齢 DESC

 

では、実際に出題された基本情報技術者試験の過去問をやってみましょう。

"中間テスト"表からクラスごと,教科ごとの平均点を求め,クラス名,教科名の昇順に表示するSQL文中のaに入れるべき字句はどれか。

中間テスト(クラス名,教科名,学生番号,名前,点数)

〔SQL文〕

SELECT クラス名,教科名,AVG(点数) AS 平均点

FROM 中間テスト

[a]

ア.GROPU BY クラス名, 教科名 (ORDER BY クラス名, AVG(点数))

イ.GROPU BY クラス名, 教科名 (ORDER BY クラス名, 教科名)

ウ.GROPU BY クラス名, 教科名, 学生番号 (ORDER BY クラス名, 教科名,平均)

エ.GROPU BY クラス名, 平均点 (ORDER BY クラス名, 教科名)

 

出典:基本情報技術者試験 平成25年春期 問28

答えはイ。

 

解説します。

まず問題文よりクラスごと、教科ごとの平均点を求めるので、グループ化するので、

GROUP BY クラス名,教科名

 

クラス名、教科名の昇順に行いますから以下になります。

ORDER BY クラス名,教科名

 

選択肢のウやエは、問題で指定された項目でグループ化していないので誤り。

また、選択肢のアは、平均点順で整列されますので誤りです。

 

7.集約関数

7.集約関数

集約関数は、複数の行をまとめて値の平均や合計など計算結果を返します

以下に主な集約関数を書いておきます。

AVG() :平均を求める

SUM() :合計を求める

MAX() :最大値を求める

MIN() :最小値を求める

COUNT() :個数(件数)を求める

上記はSELECT文とよく出る関数ですので、しっかり覚えておきましょう。

 

では、これから以下「社員表」をもとに、集約関数のSQL文例を2つだけ紹介し解説します。

「社員表」

社員名 給料
山田健太 200000
加藤れいこ 300000
鈴木えみこ 190000

 

①社員の給料合計を求める。

合計を求めるSQLは以下の通り。

SELECT SUM(給料) FROM 社員表

 

上記SQL実行結果は以下となります。

SUM(給料)
690000

各社員の給料を合計した結果となります。

200000+300000+190000=690000

 

②社員の登録件数を求める。

件数を求めるSQLは以下の通り。

ただし、条件として給料が200000以上の社員です。

SELECT COUNT(給料) FROM 社員表 WHERE 給料 >= 200000

 

上記SQL実行結果は以下となります。

COUNT(給料)
2

 

8.ビュー(VIEW)

8.ビュー(VIEW)

ビューとは、SELECT文に名前をつけて、データベース上に保存した仮想的なテーブルのこと

ビューにアクセスすると、定義されたクエリが実行され、実テーブルから必要なデータが抽出されます。

ですので、複雑なクエリをビューとして定義しておけば、プログラムは単純なクエリを実行するだけですむというメリットがあります。

 

実際に基本情報技術者試験で、出題された問題文をもとに解説します。

関係データベースの“製品”表と“売上”表から,売上報告のビュー表を定義するSQL 文中の [a]に入るものはどれか。

CREATE VIEW 売上報告(製品番号、製品名、納品数、売上年月日、売上金額)

AS [a] 製品.製品番号、製品.製品名、売上.納品数、売上.売上年月日、売上.納品数 * 製品.単価

FROM 製品、売上

WHERE 製品.製品番号= 売上.製品番号

 

表名 列名
製品 製品番号、製品名、単価
売上 製品番号、納品数、売上年月日

ア.GRANT

イ.INSERT

ウ.SCHEMA

エ.SELECT

 

出典:基本情報技術者試験 平成21年春期 問33

答えはエの「SELECT」

 

解説します。

ビュー名(売上報告)の後には、抽出するデータの列名を記述します。

この場合は、製品番号、製品名、納品数、売上年月日、売上金額です。

(ただし、列名を省略した場合、SELECT 文で指定した列の名前がそのまま使用されます)

ビュー名(売上報告)と抽出するデータの列名の後には、AS に続けて SELECT 文によるクエリを記述します。

 

AS以降、長く複雑なSQL文(SELECT)が記述されています。

しかし、ビューを使用することで、次回からは売上報告という仮想テーブルを使用する(下記SQL)だけで(ビューで書いたSELECT文の)結果が得られます。

SELECT * FROM 売上報告

 

9.副問合せ

9.副問合せ

副問合せとは、SELECT文のWHEREの後ろに指定する条件文の中で、別のSELECT文を書くことです

副問合せはサブクエリとも呼ばれます。

 

では、どういうものなのか、実際にSQL文にて解説します。

まずは、「社員表」は以下の通り。

社員名 給料
佐藤一郎 200000
山本次郎 190000
鈴木三郎 230000

 

副問合せを行っているSQLです。

SELECT 社員名、給料 FROM 社員表 WHERE 給料 >= (SELECT AVG(給料) FROM 社員表

上記SQL文の太字部分が副問合せです。

SQL文の意味は、「社員表」から給料が平均給料以上(206000)の社員の名前と、その給料を抽出しています。

実行結果は以下の通り。

社員名 給料
鈴木三郎 230000

 

副問い合わせを使う場面としては、テーブルの検索結果に基づいた検索を1回のSQL文で実行したい時です。

1回のSQL文で実行出来れば、その分DBの負荷も軽減でき、パフォーマンスも良くなるというメリットがあります。

 

10.関係代数

10.関係代数

下記操作のことを、関係代数と呼びます。

  1. SELECTを使って、条件に一致した特定の行を抽出する操作➡︎「選択」
  2. SELECTの後ろに列の名前を指定して、特定列を抽出する操作➡︎「射影」
  3. 複数の表を結び付けて1つの表にする操作➡︎「結合」

 

順番に具体的に図解します。

①選択:SELECTを使って、条件に一致した特定の行を抽出する操作

選択

 

②射影:SELECTを使って、指定された特定の列を抽出する操作

射影

 

③結合:複数の表を結び付けて1つの表にする操作

結合

 

では実際に基本情報技術者試験で出題された問題をやってみましょう!

関係モデルにおいて表Xから表Yを得る関係演算はどれか。

関係演算の問題

ア.結合(join)

イ.射影(projection)

ウ.選択(selection)

エ.併合(merge)

出典:基本情報技術者試験  平成31年春期 問28

答えはイの「射影」。

表Yは、表Xの商品番号(列)と数量(列)を抽出したものです。

 

上記のように実際に表を用いた問題も出ます。

ですので、「選択」「射影」「結合」の意味はもちろん、表を操作した結果が3つのどれに当てはまるのか判断できるようにしておいてください。

 

まとめ

基本情報技術者試験で出題されるSQL文(SELECT)を解説しました。

SELECT文関連の出題は、下記のような問題が多いです。

  • SELECT文の穴埋め
  • SQL実行結果(どんなデータが抽出されるか)を問うもの
  • 実行結果を得るためのSQL文を選ぶ

上記を解くためにも、SELECTと一緒に出てくる合計10個の各種命令を覚えておくことで、SQL文(SELECT)は読み解くことができます。

  1. GROUP BY
  2. WHERE
  3. BETWEEN
  4. LIKE
  5. DISTINCT
  6. ORDER BY
  7. 集約関数
  8. ビュー
  9. 副問合せ
  10. 関係代数

 

SQLを覚えることは、基本情報技術者試験だけでなく、実際のIT技術に関する仕事でも必ず役立ちます。

この機会にぜひ得意になって、スキルアップに役立ててください!

 

人気記事【基本情報技術者試験の過去問(午前)】何年分解けばいい?よく出る問題をランキング形式で紹介し解説!

 

この記事はいかがでしたでしょうか✨?

-基本情報技術者試験

© 2020 WEB系エンジニア-マー坊ブログ