AP データベース SQL基礎

AP データベースに戻る

AP データベース SQL基礎2へ進む。

概要

　応用情報処理技術者試験の問題に取り上げられるようなSQLを紹介していきます。未完成です。2割くらいしかまだまとめれてないと思います。令和7年春の試験に間に合わず。力になれず申し訳なし。あれから、現状9割9分9厘くらい網羅出来たと思います。ここに無いキーワードの問題が次回以降出題されたら激しくお詫びしたくなると思います。

　

CREATE TABLE

sql number wrapCopy

CREATE TABLE 社員 (
  社員ID INT PRIMARY KEY,
  氏名 VARCHAR(20),
  所属部署 VARCHAR(20),
  給与 INT CHECK (給与 >= 0)
);

sql

Showing null of 6 linesscrollable

　表に以下の列が定義された状態のデータベースが作成されます。

• 社員ID 整数型 主キー

• 氏名 文字型20桁

• 所属部署 文字型20桁

• 給与 整数型 0以上の整数である検査付き

こういった列ごとの入力できる形式を定義することを制約 CONSTRAINT と呼んでいます。

社員

社員ID	氏名	所属部署	給与

　

INSERT

　INSERT句でデータを挿入できます。以下の例では4レコード挿入しています。これで表にデータがある状態になりました。ここで作った表をこの記事のベースにしたいと思います。

sql number wrapCopy

INSERT INTO 社員(社員ID, 氏名, 所属部署, 給与)
VALUES
  (1,	'山田太郎',	'営業部',	300000),
  (2,	'佐藤花子',	'開発部',	400000),
  (3,	'鈴木一郎',	'営業部',	350000),
  (4,	'田中美咲',	'総務部',	280000);

sql

社員

社員ID	氏名	所属部署	給与
1	山田太郎	営業部	300000
2	佐藤花子	開発部	400000
3	鈴木一郎	営業部	350000
4	田中美咲	総務部	280000

　

SELECT

　SELECT句でデータの抽出ができます。抜き出したい列を指定すると、以下のようになります。列に*を指定すると、すべての列が表示されます。

sql number wrapCopy

SELECT 氏名, 給与 FROM 社員;

sql

社員

氏名	給与
山田太郎	300000
佐藤花子	400000
鈴木一郎	350000
田中美咲	280000

　

FROM

sql number wrapCopy

SELECT 所属部署毎給与合計.所属部署, 給与合計 
FROM 
( SELECT 所属部署, SUM(給与) AS 給与合計 
  GROUP BY 所属部署
  FROM 社員) 所属部署毎給与合計;

sql

　表を指定するときにFROM句で指定します。FROM句の中で加工済みの表を指定する副問合せも指定できますので、SELECT句の後にまたSELECT句があるときはFROM句のような表参照のための句があると考えていいです。SELECT句の後のSELECT句が来る場合は、後で紹介する内部結合のINNER JOIN句、あるいは外部結合のLEFT OUTER JOIN句、RIGHT OUTER JOIN句、FULL OUTER JOIN句があると考えてよいでしょう。応用処理技術者試験ではよく穴埋めになる箇所です。SELECT句の直前が穴埋めになっている場合は、この副問合せか、後ほど紹介する内部結合、外部結合の句が使われます。表の中から列を抜き出す射影や再集計をしない場合はSELCT句が来ない場合もあるので、注意が必要です。

所属部署毎給与合計;

所属部署毎給与合計.所属部署	給与合計
営業部	650000
開発部	400000
総務部	280000

WHERE

WHERE句でグループ化される前のフィルタリング条件指定ができます。

sql number wrapCopy

SELECT * FROM 社員 WHERE 所属部署='営業部';

sql

社員

社員ID	氏名	所属部署	給与
1	山田太郎	営業部	300000
3	鈴木一郎	営業部	350000

BETWEEN A AND Bという条件指定についての言及をココに追加しないと行けないかなと思っています。抜けてたな。

　

ORDER BY

　昇順や降順に並べる列名を指定するときに使います。DESCで降順です。ASCで昇順です。ORDER BY句のASCは規定値です。なのでASCの指定は省略できます。

sql number wrapCopy

SELECT * FROM 社員 ORDER BY 給与 DESC;

sql

社員

社員ID	氏名	所属部署	給与
2	佐藤花子	開発部	400000
3	鈴木一郎	営業部	350000
1	山田太郎	営業部	300000
4	田中美咲	総務部	280000

　

GROUP BY

sql number wrapCopy

SELECT 所属部署, AVG(給与) AS 平均給与 
FROM 社員 
GROUP BY 所属部署;

sql

社員

所属部署	平均給与
営業部	325,000
開発部	400,000
総務部	280,000

　

HAVING

　HAVING句はWHERE句に似ていますが、以下のような違いがあります。

• WHERE句はグループ化前に個々の行にフィルタリングを行います。
• HAVING句はグループ化後に集約結果に対する条件を指定します。

　したがってGROUP BY 句のところでよく使われたりします。

sql number wrapCopy

SELECT 所属部署, AVG(給与) AS 平均給与 
FROM 社員 
GROUP BY 所属部署 
HAVING AVG(給与) > 300000;

sql

社員

所属部署	平均給与
営業部	325,000
開発部	400,000

　

DISTINCT

　抽出した結果に重複があれば、省略して結果表示してくれます。

sql number wrapCopy

SELECT DISTINCT 所属部署 FROM 社員;

sql

社員

所属部署
営業部
開発部
総務部

　

LIKE

　%によるワイルドカード指定ができます。IPAのSQLのDB変数みたいなものバインド変数またはプレースホルダですが、これを含めるときは '%' || :変数名 || '%'のように指定します。実際に使うときは先に記述した指定したとおりになりますが、プレースホルダの指定方法について、IPAでは特にきまりは無いので、'%:変数名%'として正解にしてくれていると思います。たぶん。実際はアプリケーションで組み込むときには違う表現になるような気もします。

PHPだとこんな感じ。

php number wrapCopy

$stmt = $pdo->prepare("SELECT * FROM 社員 WHERE 氏名 LIKE :name");
$stmt->execute([':name' => '%' . $searchWord . '%']);

php

　SQL自体での動作はシンプルは以下のようなものです。

sql number wrapCopy

SELECT * FROM 社員 WHERE 氏名 LIKE '田%';

sql

社員

社員ID	氏名	所属部署	給与
4	田中美咲	総務部	280000

　

UPDATE ～ SET ～

sql number wrapCopy

UPDATE 社員 SET 給与=310000 WHERE 氏名='山田太郎';

sql

社員

社員ID	氏名	所属部署	給与
1	山田太郎	営業部	310000
2	佐藤花子	開発部	400000
3	鈴木一郎	営業部	350000
4	田中美咲	総務部	280000

　

DELETE

sql number wrapCopy

DELETE FROM 社員 WHERE 所属部署='総務部';

sql

社員

社員ID	氏名	所属部署	給与
1	山田太郎	営業部	300000
3	鈴木一郎	営業部	350000

　

INNER JOIN、JOIN

　以下のテーブルを追加します。ON句のキーワードでくっつけ方をよく指定します。テーブル名.列名=テーブル名.列名という対応での指定をします。2個以上の列条件の一致が必要な場合はANDとかも使います。INNER JOINは単にJOINと書いても良いです。

部署テーブル

部署名	拠点
営業部	東京
開発部	大阪
総務部	名古屋

sql number wrapCopy

SELECT 氏名, 社員.所属部署, 拠点
FROM 社員 
INNER JOIN 部署
ON 社員.所属部署 = 部署.部署名;

sql

社員

氏名	所属部署	拠点
山田太郎	営業部	東京
佐藤花子	開発部	大阪
鈴木一郎	営業部	東京
田中美咲	総務部	名古屋

　

LEFT JOIN、LEFT OUTER JOIN

　以下のテーブルを追加します。LEFT JOIN と LEFT OUTER JOIN は同じです。

部署テーブル

部署名	拠点
営業部	東京
開発部	大阪

sql number wrapCopy

SELECT 氏名, 社員.所属部署, 拠点
FROM 社員 LEFT JOIN 部署
ON 社員.所属部署 = 部署.部署名;

sql

社員

氏名	所属部署	拠点
山田太郎	営業部	東京
佐藤花子	開発部	大阪
鈴木一郎	営業部	東京
田中美咲	総務部	NULL

この状態でINNER JOINで結合すると

sql number wrapCopy

SELECT 氏名, 社員.所属部署, 拠点
FROM 社員 INNER JOIN 部署
ON 社員.所属部署 = 部署.部署名;

sql

社員

氏名	所属部署	拠点
山田太郎	営業部	東京
佐藤花子	開発部	大阪
鈴木一郎	営業部	東京

　となり、総務部の情報の表示はなくなります。

　

FULL OUTER JOIN、FULL JOIN

　追加されたテーブルが以下のテーブルだったとします。FULL JOIN と FULL OUTER JOIN は同じです。

部署テーブル

部署名	拠点
営業部	東京
開発部	大阪
試作部	三重

sql number wrapCopy

SELECT 氏名, 社員.所属部署, 拠点
FROM 社員 FULL JOIN 部署
ON 社員.所属部署 = 部署.部署名;

sql

社員

氏名	所属部署	拠点
山田太郎	営業部	東京
佐藤花子	開発部	大阪
鈴木一郎	営業部	東京
田中美咲	総務部	NULL
NULL	試作部	三重

のように社員テーブルに該当の社員がいない試作部の情報も結合後のテーブルに表示されます。

　ここまでで示したように、内部結合INNER JOIN(JOIN)句、外部結合 LEFT OUTER JOIN(LEFT JOIN)句、RIGHT OUTER JOIN(RIGHT JOIN)句、FULL OUTER JOIN(FULL JOIN)句を使うときはON による結合条件を示す場合が殆どです。したがってONがある場合のSELECT句の直前の穴埋めは内部結合か外部結合の句が来ます。ない場合にSELECT句の前の穴埋めがあった場合はFROMと考えると良いでしょう。(令和7年春)はこのことが分かっていれば穴埋め問題に挑みやすかった問題です。

　

UNION、UNION ALL

sql number wrapCopy

SELECT 氏名 FROM 社員
UNION
SELECT 部署名 FROM 部署;

sql

社員

氏名
山田太郎
佐藤花子
鈴木一郎
田中美咲
営業部
開発部
総務部

のように列がまとめられて、すべての要素が表示されます。足し算のような効果＋重複チェックを生み出すのがUNIONだと思えばいいと思います。UNION ALLだと重複チェックを外してくれます。

sql number wrapCopy

SELECT 氏名, 社員.所属部署, 拠点
FROM 社員
LEFT JOIN 部署 ON 社員.所属部署 = 部署.部署名

UNION ALL

SELECT 氏名, 社員.所属部署, 拠点
FROM 社員
RIGHT JOIN 部署 ON 社員.所属部署 = 部署.部署名
WHERE 氏名 IS NULL;

sql

のようにするとFULL JOINが使えない環境でもUNIONとRIGHT JOINだけでFULL JOINが実現できます。

FULLは左外部結合と右外部結合のUNION(つまり足し算)と言えるからです。

社員

氏名	所属部署	拠点
山田太郎	営業部	東京
佐藤花子	開発部	大阪
鈴木一郎	営業部	東京
田中美咲	総務部	NULL
NULL	試作部	三重

　上記のようになります。UNION ALLでなくて UNIONでも結果は変わらないですが、FULL JOINと同じにしたい場合はUNION ALLにしておきます。例えば同姓同名で同じ部署の人がいた場合、UNIONを使った場合は重複を省くので、同姓同名でもちゃんと2レコードださないといけないときに、消えてしまいます。DISTINCTを掛けなければいけない場合はUNIONでいいということにもなります。

　

IN(副問合せ)

　以降、以下のテーブルを追加します。

部署テーブル

部署名	拠点
営業部	東京
開発部	東京
開発部	大阪
総務部	名古屋

複数の値が返ってくる可能性のあるデータと一致するかをそれぞれ確認してくれます。以下だと、東京にある部署の名前が返ってきて、社員テーブルの所属部署名と比較してくれます。一致するレコードはすべて抽出されます。

sql number wrapCopy

SELECT 氏名 FROM 社員
WHERE 所属部署 IN (SELECT 部署名 FROM 部署 WHERE 拠点='東京');

sql

社員

社員ID	氏名	所属部署	給与
1	山田太郎	営業部	300000
2	佐藤花子	開発部	400000
3	鈴木一郎	営業部	350000

　部署テーブルの拠点が東京のものには、営業部と、開発部がありますので、社員テーブルの営業部と開発部に一致するレコードが抽出されます。

sql number wrapCopy

SELECT 氏名 FROM 社員
WHERE 所属部署 IN ('営業部', '開発部');

sql

と実行したのと同じことです。INは副問合せ専用のキーワードではないです。INに指定されたデータに一致するものすべてという意味です。副問合せによく使うというだけです。

EXIST(副問合せ)

　レコードごとに副問合せの結果が存在したかどうかを判定します。

sql number wrapCopy

SELECT 氏名 FROM 社員 S
WHERE EXISTS (
  SELECT * FROM 部署 D
  WHERE D.部署名 = S.所属部署
    AND D.拠点='東京'
);

sql

社員

社員ID	氏名	所属部署	給与
1	山田太郎	営業部	300000
2	佐藤花子	開発部	400000
3	鈴木一郎	営業部	350000

　何かしらの抽出レコードが副問合せの結果に存在したか、どうかを判定するだけです。真偽です。あったか、なかったか。

　

CREATE VIEW

　参照するため専用のビュー(編集できない)を作成します。

sql number wrapCopy

CREATE VIEW 東京勤務社員 AS
SELECT 氏名, 所属部署 
FROM 社員
WHERE 所属部署 IN (
  SELECT 部署名 FROM 部署 WHERE 拠点='東京'
);

sql

東京勤務社員 ビュー

氏名	所属部署
山田太郎	営業部
鈴木一郎	営業部

　ビューを作った上でまた次のSQLを使い、このビューをFROM ビュー名のように指定することで利用ができるようになっています。これを一つのSQLでやろうとするのが次のWITH句です。CREATE VIEW ビュー名 AS SELECT * FROM xx;のような形式です。ASの後に()がいらないことを覚えましょう。東京勤務社員という表名の後ろに東京勤務社員(列名1, 列名2, …, 列名n)のように列名の別名が指定できて、この別名が最も優先されます。

　

WITH

　一時的なビューを作成しつつ、そのビューを使ったSQLを実行することができます。

sql number wrapCopy

WITH 部署別平均給与(平均給与表示用所属部署, 平均給与表示用平均給与) AS (
    SELECT
        所属部署,
        AVG(給与) AS 平均給与
    FROM
        社員
    GROUP BY
        所属部署
)
SELECT
    S.所属部署,
    COUNT(S.社員ID) AS 社員数,
    AVG_SAL.平均給与表示用平均給与
FROM
    社員 AS S
JOIN
    部署別平均給与 AS AVG_SAL
ON
    S.所属部署 = AVG_SAL.平均給与表示用所属部署
GROUP BY
    S.所属部署, AVG_SAL.平均給与表示用平均給与;

sql

WITH句で作成した一時テーブルは以下のようになります。一時テーブル表名直後のカッコ内で列の別名が指定でき、ここで指定した別名が最も優先されます。別名はSELECT句による列指定でも指定できます。

部署別平均給与（一時テーブル）

平均給与表示用所属部署	平均給与表示用平均給与
営業部	325000
開発部	400000
総務部	280000

　この一時テーブルを利用しつつSQLを実行した結果、得られる表は以下のようなものです。

部署別社員数と平均給与

所属部署	社員数	平均給与
営業部	2	325000
開発部	1	400000
総務部	1	280000

　のようにWITH句で作成した部署別平均給与という表を使って上記のような結果を得ることができます。「WITH 一時的に作成する表名 AS (表を実現するSQL) SELECT * FROM 一時的に作成する表名」 のような形式です。一時的に作成する表名の後のASの後ろにカッコがいります。覚えれるかな。

　

WITH RECURSIVE

　再帰的な一時的表を作る必要がある場合に使う句です。例えば以下のようなカテゴリ表があったとしたら

商品カテゴリ

カテゴリID	カテゴリ名	親カテゴリID
1	電子機器	NULL
2	PC	1
3	スマートフォン	1
4	ノートPC	2
5	デスクトップPC	2
6	周辺機器	NULL
7	マウス	6
8	キーボード	6

sql number wrapCopy

WITH RECURSIVE カテゴリ階層 AS (
    -- アンカーメンバー：再帰の開始点（親カテゴリIDがNULLのトップレベルカテゴリ）
    SELECT
        カテゴリID,
        カテゴリ名,
        親カテゴリID,
        0 AS 階層レベル -- 階層の深さを示す
    FROM
        商品カテゴリ
    WHERE
        カテゴリID = 1 -- 電子機器カテゴリから開始

    UNION ALL

    -- 再帰メンバー：前回の結果（カテゴリ階層）を参照して子カテゴリを取得
    SELECT
        C.カテゴリID,
        C.カテゴリ名,
        C.親カテゴリID,
        CH.階層レベル + 1 AS 階層レベル
    FROM
        商品カテゴリ AS C
    JOIN
        カテゴリ階層 AS CH ON C.親カテゴリID = CH.カテゴリID
)
SELECT
    カテゴリID,
    カテゴリ名,
    親カテゴリID,
    階層レベル
FROM
    カテゴリ階層
ORDER BY
    階層レベル, カテゴリID;

sql

のようにすると

電子機器カテゴリ階層

カテゴリID	カテゴリ名	親カテゴリID	階層レベル
1	電子機器	NULL	0
2	PC	1	1
3	スマートフォン	1	1
4	ノートPC	2	2
5	デスクトップPC	2	2

のように再帰的にカテゴリを読み込むことができます。これを一時表とすることで、このようなカテゴリ分類をうまく利用した表の作成を可能とします。11行目をカテゴリID = 6 にすると周辺機器についてのカテゴリを再帰的に検索できます。重複がなければUNION ALLでなくてもUNIONとしても同じ結果が得られますので、間違えても満点になる問題であることは多いですが、UNION ALLで再帰検索する必要があることを覚えておきましょう。

WITH RECURSIVE カテゴリ階層 SELECT カテゴリID, … UNION ALL SELECT C.親カテゴリID, … FROM 商品カテゴリ AS C JOIN カテゴリ階層 AS CH ON C.親カテゴリID = CH.カテゴリIDのような構造です。

複雑です。

　

AP データベース SQL基礎2へ進む。

AP データベースに戻る