基本情報技術者 PR

【基本情報】SQLについて解説-Select文の全て

記事内に商品プロモーションを含む場合があります

●この記事で学べること
・Selectの色々
・Order by
・Group by

SQL

SQLはデータベースを操作するための言語です。SQLを使えばデータの登録・更新・抽出・削除が出来ます。基本情報技術者の午前試験ではデータの抽出しか出題されません。

Select文の基本形

Select文ではデータベースからデータを抽出できます。

Select 列1, 列2, ……             抽出する列を指定する
From 表1, 表2, ……            抽出対象の表を指定する
Where 条件式1, 条件式2, ……      抽出条件を指定する

抽出条件を’or’で指定する

Where 条件式1 or 条件式2と指定すれば、条件式1と条件式2のどちらかを満たすデータが抽出できます。

Select 社員名,役職
From 社員表
Where 社員番号=’001′ or 社員番号=’004′

社員番号=’001′ or 社員番号=’004’が条件なので、どちらか一方でも満たすデータが抽出対象になります。

抽出条件を’and’で指定する

Where 条件式1 and 条件式2と指定すれば、条件式1と条件式2の両方を満たすデータが抽出できます。

Select 社員番号
From 社員表
Where 部門番号=’A1′ and 役職=’部長’

部門番号=’A1’と役職=’部長’の両方の条件を満たすデータだけが抽出対象になります。

表を結合して抽出する

Fromで抽出対象の表を指定して、Whereで結合する条件を指定します。

Select 社員番号, 部門名
From 社員表, 部門表
Where 社員表.部門番号 = 部門表.部門番号

「From 社員表, 部門表」なので社員表と部門表を結合します。
「Where 社員表.部門番号 = 部門表.部門番号」なので社員表と部門表の部門番号同士で結合します。

表を結合、条件を指定して抽出

表を結合した上で条件を指定することも可能です。

Select 社員番号, 部門名
From 社員表, 部門表
Where 社員表.部門番号 = 部門表.部門番号 and 内線 = ‘201’

結合した表に対して、更に内線=’201’のデータを抽出します。内線=’201’に該当する部門の部門名は’営業部’なので営業部のデータだけが抽出対象になります。

Select文 – 並び替え

Select文でデータを抽出する時、抽出結果を並び替えることができます。

Select 列1, 列2, ……             抽出する列を指定する
From 表1, 表2, ……            抽出対象の表を指定する
Where 条件式1, 条件式2, ……      抽出条件を指定する
Order by 列           並び替えの基準となる列を指定する(昇順)

昇順で並び替える

Order byで並び替えの基準となる列を指定します。

Select 社員名,役職
From 社員表
Where 社員番号=’005′ or 社員番号=’001′
Order by 社員番号

社員番号=’005’と’001’のデータを抽出した上で、抽出結果を社員番号に対して昇順で並び替えます。昇順なので小さいもの順になります。

(おまけ)降順で並び替える場合
降順で並び替える場合は「Order by 社員番号 Desc」のようにDescを後ろに付けます。

Select文 – グループ化(集計・平均・カウント)

Select文でデータを抽出する時、データを集計したり、平均値を求めたり、抽出対象の数をカウントできます。

Select 列1, 集計関数(列名) ……   集計関数や抽出する列を指定する
From 表1, 表2, ……           抽出対象の表を指定する
Group by 列 ……               グループ化する列を指定する
Having 集計関数の条件 ……     集計関数に対する条件を指定する

集計関数には主に3つの種類があります。

Sum(列名) 指定した列の合計値を算出する
Avg(列名) 指定した列の平均値を値を算出する
Count(*) 抽出対象の行数を算出する

Group byでグループ化する列を指定します。
例えば、「Select Ave(英語の得点) …… Group by クラス名」と指定すればクラス毎の英語の平均点を算出します。

Havingで集計関数に対する条件を指定します。
例えば、「Select Ave(英語の得点) …… Group by クラス名 Having Ave(英語の得点)>80」と指定すれば、英語の平均点が80点を超えるクラスを抽出します。

例1:クラス毎の合計点を抽出する

Select クラス, Sum(数学の点数)
From 得点表
Group by クラス

「Select Sum(数学の点数)・・・Group by クラス」なので、クラス毎の数学の点数の合計点を求めます。

例2:国語の平均点が8点以上のクラスを抽出する

Select クラス, Ave(国語の点数)
From 得点表
Group by クラス
Having Ave(国語の点数)>=8

「Having Ave(国語の点数)>=8」なので、平均点が8点以上のクラスを抽出します。A組の平均点が5点、B組の平均点が9点、C組の平均点が8点なので、B組とC組のみが抽出対象になります。

例3:クラス・教科毎の平均点を抽出する

Select クラス, 教科, Ave(点数)
From 得点表
Group by クラス, 教科

Group byでは複数の列を指定することが出来ます。今回はクラスと教科を指定しているので、クラス毎・教科毎の平均点を抽出します。

基本情報技術者試験での出題例

令和元年度秋期問26

基本情報技術者
午前試験 令和元年度秋期問26

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

ア 科目 HAVING AVG(点数) >= 80
イ 科目 WHERE 点数 >= 80
ウ 学生番号 HAVING AVG(点数) >= 80
エ 学生番号 WHERE 点数 >= 80

正解と解説

正解は”ウ”

学生毎に平均点を出すので、Group Byで指定するのは学生番号です。
また、平均点が80点以上の学生を抽出するのでHaving Ave(点数)で指定します。

平成31年度春期問27

基本情報技術者
午前試験 平成31年度春期問27

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

ア GROUP BY クラス名,教科名 ORDER BY クラス名,AVG(点数)
イ GROUP BY クラス名,教科名 ORDER BY クラス名,教科名
ウ GROUP BY クラス名,教科名,学生番号 ORDER BY クラス名,教科名,平均点
エ GROUP BY クラス名,平均点 ORDER BY クラス名,教科名

正解と解説

正解は”イ”

クラス毎・教科毎の平均点を求めるのでGroup by クラス名, 教科名になります。
クラス名・教科名で昇順に並び替えるのでOrder by クラス名, 教科名になります。

平成31年度春期問29

基本情報技術者
午前試験 平成31年度春期問29

“学生”表と”学部”表に対して次のSQL文を実行した結果として,正しいものはどれか。

正解と解説

正解は”ウ”

学部.住所 = ‘新宿’なので、学部表の住所が新宿になっている工学部理学部が抽出対象になります。よって、工学部と理学部の応用花子と情報太郎を抽出対象としている’ウ’が正解です。

平成28年度秋期問29

基本情報技術者
午前試験 平成28年度秋期問29

“社員”表と”部門”表に対し,次のSQL文を実行したときの結果はどれか。

ア 1     イ 2     ウ 3     エ 4

正解と解説

正解は”ウ”

部門.フロア=’2’なので、部門表のフロアが’2’になっている情報システム・経理・法務・購買が抽出対象になります。これらに該当する社員の数は3なので正解は’ウ’になります。

関連記事