応用情報技術者ではよくSQL文に関する問題が出題されます。ここでマスターしましょう。
SQL文とは
SQL文とはデータベースを操作するための言語で、
- Create ：データの作成
- Read ：データの読み出し
- Update：データの更新
- Delete ：データの削除
の4つの命令をデータベースに指示します。
この記事では応用情報技術者で出題されるSQL文に限定して紹介していきます。
Select文
データの読み出しの基本形
データの読み出しで使われるSQL文の基本形はSelect文です。
Select 取得したい列名 From 取得元テーブル Where 取得条件
例
Select 商品コード,販売単価 From 商品
Where 商品コード = ‘B002’ or 商品コード = ‘C003’
「商品」というテーブルから商品コードが’B002’又は’C003’のデータのうち、商品コードと販売単価を取得するSQL文です。
ちなみに、Where句は無くてもOKです。Where句を指定しなければ全てのデータが取得できます。
Select 商品コード,販売単価 From 商品
データの並び替え
データを昇順・降順に並び替えするSQL文は以下の通りです。降順に並び替えたい時はDescを付けます。
Select 取得したい列名 From 取得元テーブル
Order by 並び替えたい項目 (Desc)
例
Select 商品コード,販売単価 From 商品 Order By 商品コード
このSQL文では取得したデータを商品コードに関して昇順(あいうえお順)に並び替えます。
データの読み出し-重複行削除
データが重複している場合、重複しているデータを一括して表示したい場合に発行するSQL文は以下の通りです。
Select Distinct 重複削除したい項目 From 取得元テーブル
例
Select Distinct 仕入先 From 商品
このSQL文では重複している仕入先をまとめて取得します。
データの集計・平均
データの集計と平均の基本形は以下の通りです。
データの集計
Select SUM(集計したい項目) From 取得元テーブル
Group by グルーピングする項目 Having 集計結果に対する取得条件
データの平均
Select AVG(平均したい項目) From 取得元テーブル
Group by グルーピングする項目 Having 平均結果に対する取得条件
例
Select 製品分類,AVG(販売単価) From 商品
Group by 製品分類
このSQLでは製品分類毎に販売単価の平均値を取得しています。
Select 製品分類,AVG(販売単価) From 商品
Group by 製品分類 Having AVG(販売単価)<1000
このSQLでは製品分類毎に販売単価の平均値を算出し、平均値が1000未満のデータのみ取得します。
ちなみに、asを使うと列名に名前を付けることが出来ます。
Select 製品分類,AVG(販売単価) As ‘平均値’ From 商品
Group by 製品分類
このSQLでは販売単価の平均値を格納する列の名前を平均値にすることが出来ます。
テーブル同士の足し算
テーブル同士を単純に合体させるSQL文は以下の通りです。
Select 取得したい列名 From 取得元テーブル
Union (All) Select 取得したい列名 From 取得元テーブル
例
Select 商品コード,在庫数 From 東京在庫
Union All Select 商品コード,在庫数 From 大阪在庫
Union Allを使うとテーブル同士を単純に足し算出来ます。
Select 商品コード,在庫数 From 東京在庫
Union Select 商品コード,在庫数 From 大阪在庫
Allを省くと、重複した行を削除して足し算します。
応用情報技術者試験での出題
応用情報技術者試験を突破するのに必要な知識はこれだけです。
データの読み出し
基本形：
Select 取得したい列名 From 取得元テーブル Where 取得条件
昇順に並び替え：
Select 取得したい列名 From 取得元テーブル Order by 並び替えたい項目
重複行の削除：
Select Distinct 重複削除したい項目 From 取得元テーブル
データの平均値を取得：
Select AVG(平均したい項目) From 取得元テーブル
Group by グルーピングする項目 Having 集計結果に対する取得条件
テーブル同士の足し算：
Select 取得したい列名 From 取得元テーブル
Union (All) Select 取得したい列名 From 取得元テーブル
出題例
応用情報技術者 午前試験
令和4年度秋期問28
“商品”表に対して、次のSQL文を実行して得られる仕入先コード数は幾つか。
〔SQL文〕
SELECT DISTINCT 仕入先コード FROM 商品
WHERE (販売単価 – 仕入単価) >
(SELECT AVG (販売単価 – 仕入単価) FROM 商品)
ア 1 イ 2 ウ 3 エ 4
正解は”ウ”
SELECT AVG (販売単価 – 仕入単価) FROM 商品
まずはこのSQLの結果から求めます。このSQLでは商品テーブルに格納されている全データの販売単価 – 仕入単価の平均値を取得しています。よって、
(200+200+100+900+400+200+300+500+500+300)÷10=360
で、360という値を返します。
ここから問題文のSQLは以下のようになります。
SELECT DISTINCT 仕入先コード FROM 商品 WHERE (販売単価 – 仕入単価) >360
下の黄色のデータが(販売単価 – 仕入単価)が360超のデータです。
ここから仕入先コードの重複を削除するので結果は以下になります。
よって取得できる仕入先コードが3個なので答えはウです。
応用情報技術者 午前試験
令和3年度秋期問29
“部門別売上別”表から、部門コードごと、期ごとの売上を得るSQL文はどれか。
ア SELECT 部門コード,’第1期’ AS 期, 第1期売上 AS 売上
FROM 部門別売上
INTERSECT
(SELECT 部門コード,’第2期’ AS 期, 第2期売上 AS 売上
FROM 部門別売上)
ORDER BY 部門コード,期
イ SELECT 部門コード,’第1期’ AS 期, 第1期売上 AS 売上
FROM 部門別売上
UNION
(SELECT 部門コード,’第2期’ AS 期, 第2期売上 AS 売上
FROM 部門別売上)
ORDER BY 部門コード,期
ウ SELECT A.部門コード,’第1期’ AS 期, A.第1期売上 AS 売上
FROM 部門別売上 A
CROSS JOIN
(SELECT B.部門コード,’第2期’ AS 期, B.第2期売上 AS 売上
FROM 部門別売上 B) T
ORDER BY 部門コード,期
エ SELECT A.部門コード,’第1期’ AS 期, A.第1期売上 AS 売上
FROM 部門別売上 A
INNER JOIN
(SELECT B.部門コード,’第2期’ AS 期, B.第2期売上 AS 売上
FROM 部門別売上 B) T ON A.部門コード = T.部門コード
ORDER BY 部門コード,期
正解は”イ”
UNIONでは表同士を純粋に足し算します。
SELECT 部門コード,’第1期’ AS 期, 第1期売上 AS 売上 FROM 部門別売上
このSQLから得られる結果は以下の通りです。
SELECT 部門コード,’第2期’ AS 期, 第2期売上 AS 売上 FROM 部門別売上
このSQLから得られる結果は以下の通りです。
以上2つの表を足し算し、部門コード、期で昇順に並び替えると問題文の表が取得できます。
ちなみに、
ア INTERSECTでは2つの表で共通している項目が取得できます。今回だと共通している部分が無いので結果は何もなしになります。
ウ CROSS JOINでは2つの表に存在するデータの全ての組み合わせを取得できます。ちなみに問題文のSQLは引っ掛けになっているのでCROSS JOIN本来の使い方とは違う形で書かれています。
エ INNER JOINでは表同士を横に結合できます。こちらも問題文のSQLは引っ掛けになっているのでINNER JOIN本来の使い方とは違う形で書かれています。
応用情報技術者 午前試験
平成31年度春期問28
過去3年分の記録を保存している”試験結果”表から、2018年度の平均点数が600点以上となったクラスのクラス名と平均点数の一覧を取得するSQL文はどれか。ここで、実線の下線は主キーを表す。
試験結果（学生番号, 受験年月日, 点数, クラス名）
ア SELECT クラス名, AVG(点数), FROM 試験結果
GROUP BY クラス名 HAVING AVG(点数) >= 600
イ SELECT クラス名, AVG(点数), FROM 試験結果
WHERE 受験年月日 BETWEEN ‘2018-04-01’ AND ‘2019-03-31’
GROUP BY クラス名 HAVING AVG(点数) >= 600
ウ SELECT クラス名, AVG(点数), FROM 試験結果
WHERE 受験年月日 BETWEEN ‘2018-04-01’ AND ‘2019-03-31’
GROUP BY クラス名 HAVING 点数 >= 600
エ SELECT クラス名, AVG(点数), FROM 試験結果
WHERE 点数 >= 600
GROUP BY クラス名
HAVING (MAX(受験年月日)
BETWEEN ‘2018-04-01’ AND ‘2019-03-31’)
正解は”イ”
ア 期間を絞っていないので×です。
ウ HAVINGは集計結果や平均結果などグルーピングした結果にのみ適用されます。ここでは点数を指定しているのでエラーとなります。
エ MAX関数はグルーピングした結果に該当するのでエラーにはなりませんが、受験年月日が最も大きいデータ（2019-03-31）のみ集計することになるので×です。
